c# 数据放入excel导出，卡顿

前言：导出数据到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;