Excel导出大数据量处理,分多个sheet导出

在数据量较大时，有时需要分sheet导出。

效果和代码如下，希望对大家有帮助。

 1 SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
 2 private void btn_Export_Click(object sender, EventArgs e)
 3 {
 4     //导出时单个sheet存储的行数
 5     int perSheetRowCount = 30000;
 6     string TitlePrefix = "合格统计";
 7     string filepath = Path.Combine(GlobalV.c.ExportDataPath, $"{TitlePrefix}_{DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")}.xlsx");
 8     try
 9     {
10         DateTime start_time = DateTime.Parse(date_start_time.Text.ToString());
11         DateTime end_time = DateTime.Parse(date_end_time.Text.ToString());
12         if (end_time < start_time)
13         {
14             ShowErrorTip("结束时间小于开始时间,请重新选择!");
15             return;
16         }
17         List<pms_statistics_daily> dataList = PmsStatisticsDailyDao.querypms_statistics_daily(start_time, end_time);
18         if (dataList == null || dataList.Count == 0)
19         {
20             MessageBox.Show($"查询的数据为空，无法导出！");
21             return;
22         }
23         this.sfd.Filter = "Excel files (*.xlsx)|*.xlsx";
24         this.sfd.DefaultExt = "*.xlsx";
25         this.sfd.FileName = $"{TitlePrefix}_{DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")}.xlsx";
26         //设置默认文件类型显示顺序 
27         sfd.FilterIndex = 1;
28         //保存对话框是否记忆上次打开的目录 
29         sfd.RestoreDirectory = true;
30 
31         if (this.sfd.ShowDialog() != DialogResult.OK)
32             return;
33         filepath = sfd.FileName;
34         try
35         {
36             if (File.Exists(filepath)) File.Delete(filepath);
37             int totalCount = dataList.Count;
38             //一次全部导出到一个sheet中
39             if (totalCount <= perSheetRowCount)
40             {
41                 MiniExcel.SaveAsAsync(filepath, dataList);
42             }
43             else
44             {
45                 //分多个sheet导出
46                 var sheets = new Dictionary<string, object>();
47                 int sheetCount = totalCount / perSheetRowCount + 1;
48                 for (int i = 0; i < sheetCount; i++)
49                 {
50                     string key = i.ToString();
51                     int start = i * perSheetRowCount;
52                     if (!sheets.ContainsKey(key))
53                     {
54                         var values = dataList.Skip(start).Take(perSheetRowCount).ToList();
55                         sheets.Add(key, values);
56                     }
57                 }
58                 MiniExcel.SaveAs(filepath, sheets);
59             }
60             ShowSuccessTip("数据导出成功！");
61         }
62         catch { }
63     }
64     catch (Exception ex)
65     {
66         throw ex;
67     }
68 }