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;