读取excel并且显示进度条
通过C#实现DataGridView加载EXCEL文件,但加载时不能阻塞UI刷新线程,且向UI显示加载进度条。
#region 左上角导入
private async void ToolStripMenuItem_ClickAsync(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
progressBar1.Value = 0;
progressBar1.Visible = true;
// 打开文件对话框选择 Excel 文件
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel文件|*.xlsx";
if (file.ShowDialog() == DialogResult.OK)
{
string fname = file.FileName;
await LoadExcelDataAsync(fname); // 异步加载数据
}
}
// 异步加载 Excel 数据
private async Task LoadExcelDataAsync(string filePath)
{
var progress = new Progress<int>(value =>
{
progressBar1.Value = value; // 更新进度条
label11.Text = $"{value}%"; // Update label to show percentage
});
// 在后台线程中执行加载数据的任务
System.Data.DataTable dataTable = await Task.Run(() => LoadExcelData(filePath, progress));
// 加载完成后,将数据绑定到 DataGridView
dataGridView1.DataSource = dataTable;
progressBar1.Visible = false; // 隐藏进度条
label11.Visible = false; // 隐藏进度条
}
// 使用 EPPlus 按行读取 Excel 数据并动态更新进度
private System.Data.DataTable LoadExcelData(string filePath, IProgress<int> progress)
{
FileInfo fileInfo = new FileInfo(filePath);
using (var package = new ExcelPackage(fileInfo))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int rowCount = worksheet.Dimension.Rows;
int columnCount = worksheet.Dimension.Columns;
// 创建 DataTable 来存储数据
System.Data.DataTable dt = new System.Data.DataTable();
// 为 DataTable 添加列
for (int col = 1; col <= columnCount; col++)
{
dt.Columns.Add(worksheet.Cells[1, col].Text); // 第一行作为列名
}
// 逐行读取数据并添加到 DataTable 中
int progressInterval = rowCount / 100; // 每读取一定行数更新进度
int progressPercentage = 0;
int rowCountProcessed = 0;
for (int row = 2; row <= rowCount; row++) // 跳过第一行作为标题行
{
DataRow newRow = dt.NewRow();
for (int col = 1; col <= columnCount; col++)
{
newRow[col - 1] = worksheet.Cells[row, col].Text;
}
dt.Rows.Add(newRow);
rowCountProcessed++;
if (rowCountProcessed % progressInterval == 0)
{
progressPercentage = (int)((float)rowCountProcessed / rowCount * 100);
progress.Report(progressPercentage); // 更新进度条
}
}
return dt;
}
}