前言:导出数据到Excel,导出的时候特别卡顿
原代码:
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace IPC.Helper { class ExcelExportUtil { public static string expotExcel(List<DataTable> hisChartTables,string[] typeNames,string saveFileName) { try { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { return "无法创建Excel对象,您的电脑可能未安装Excel"; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing); worksheet.Name = typeNames[0]; //写入标题 int titleCount = 1; worksheet.Cells[1, titleCount++] = "时间1"; worksheet.Cells[1, titleCount++] = "参数值"; worksheet.Cells[1, titleCount++] = "最大值"; worksheet.Cells[1, titleCount++] = "最小值"; // 获取当前时间 DateTime now = DateTime.Now; string formattedTime = now.ToString("yyyy-MM-dd HH:mm:ss"); Console.WriteLine(formattedTime); //写入数值 for (int r = 0; r < hisChartTables[0].Rows.Count; r++) { int rowCount = 1; worksheet.Cells[r + 2, rowCount++] = " " + hisChartTables[0].Rows[r]["data_time"].ToString() + "\t"; worksheet.Cells[r + 2, rowCount++] = hisChartTables[0].Rows[r]["conc"].ToString(); worksheet.Cells[r + 2, rowCount++] = hisChartTables[0].Rows[r]["max"].ToString(); worksheet.Cells[r + 2, rowCount++] = hisChartTables[0].Rows[r]["min"].ToString(); System.Windows.Forms.Application.DoEvents(); } // 获取当前时间 DateTime now1 = DateTime.Now; string formattedTime1 = now1.ToString("yyyy-MM-dd HH:mm:ss"); Console.WriteLine(formattedTime1); worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 workbook.Saved = true; workbook.SaveCopyAs(saveFileName); //fileSaved = true; xlApp.Quit(); GC.Collect();//强行销毁 return "导出成功"; } catch(Exception e) { return e.Message; } } } }
代码中增加了时间记录,发现循环写入这里,近3000条数据要耗时35s左右。于是进行优化,最终优化到仅1s。
优化思路:1、for循环中,遍历时count单独写在外面,否则每次.size()会耗时;
2、写入Excel时,不要单个单元格写入,使用range范围写入;
写入数据部分,修改后代码:
object[,] datasHead = worksheet.Range["A1", "D1"].Value2; datasHead[1, 1] = "时间"; datasHead[1, 2] = "参数值"; datasHead[1, 3] = "最大值"; datasHead[1, 4] = "最小值"; worksheet.Range["A1", "D1"].Value2 = datasHead; //写入数值 int rowCounts = hisChartTables[0].Rows.Count; string lastRange = "D" + rowCounts + 1; object[,] datas = worksheet.Range["A2", lastRange].Value2; for (int r = 0; r < rowCounts; r++) { datas[r+1, 1] = " " + hisChartTables[0].Rows[r]["data_time"].ToString() + "\t"; datas[r+1, 2] = hisChartTables[0].Rows[r]["conc"].ToString(); datas[r + 1, 3] = hisChartTables[0].Rows[r]["max"].ToString(); datas[r + 1, 4] = hisChartTables[0].Rows[r]["min"].ToString(); System.Windows.Forms.Application.DoEvents(); } worksheet.Range["A2", lastRange].Value2 = datas;
标签:Rows,c#,worksheet,excel,Excel,++,hisChartTables,ToString,卡顿 From: https://www.cnblogs.com/webttt/p/17989634