在数据量较大时,有时需要分sheet导出。
效果和代码如下,希望对大家有帮助。
![](/i/l/?n=23&i=blog/686217/202211/686217-20221129101946382-1206373783.png)
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 }
标签:sheet,导出,DateTime,start,ToString,数据量,time,sfd From: https://www.cnblogs.com/egreen/p/16934669.html