2020-10-19C#中大量数据导出到EXCEL

private Excel.Application m_xlApp = new Excel.Application();  /// <summary> /// 将DataTable数据导出到Excel表 /// </summary> /// <param name="dtTmp">要导出的DataTable</param> /// <param name="filePath">Excel的保存路径及名称</param> public void DataTabletoExcel(System.Data.DataTable dtTmp, string filePath) {     if (dtTmp == null)     {         return;     }     long rowNum = dtTmp.Rows.Count;//行数     int columnNum = dtTmp.Columns.Count;//列数     m_xlApp = new Excel.Application();     m_xlApp.DisplayAlerts = false;//不显示更改提示     m_xlApp.Visible = false;       Excel.Workbooks workbooks = m_xlApp.Workbooks;     Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);     Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1       try     {         //单张Excel表格最大行数         if (rowNum > 65536)         {             long pageRows = 65535;  //定义每页显示的行数,行数必须小于65536             int scount = (int)(rowNum / pageRows);  //导出数据生成的表单数             if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准             {                 scount = scount + 1;             }             for (int sc = 1; sc <= scount; sc++)             {                 if (sc > 3) //这里由1改为3，20140922                 {                     object missing = System.Reflection.Missing.Value;                     worksheet = (Excel.Worksheet)workbook.Worksheets.Add(                                 missing, missing, missing, missing);    //添加一个sheet                 }                 else                 {                     worksheet = (Excel.Worksheet)workbook.Worksheets[sc];   //取得sheet1                 }                 string[,] datas = new string[pageRows + 1, columnNum];                   for (int i = 0; i < columnNum; i++) //写入字段                 {                     datas[0, i] = dtTmp.Columns[i].Caption; //表头信息                 }                 Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);                 range.Interior.ColorIndex = 15; //15代表灰色                 range.Font.Bold = true;                 range.Font.Size = 9;                   int init = int.Parse(((sc - 1) * pageRows).ToString());                 int r = 0;                 int index = 0;                 int result;                 if (pageRows * sc >= rowNum)                 {                     result = (int)rowNum;                 }                 else                 {                     result = int.Parse((pageRows * sc).ToString());                 }                   for (r = init; r < result; r++)                 {                     index = index + 1;                     for (int i = 0; i < columnNum; i++)                     {                         object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];                         datas[index, i] = obj == null ? "" : obj.ToString().Trim();                     }                 }                   Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);                 fchR.Value2 = datas;                 worksheet.Columns.EntireColumn.AutoFit();   //列宽自适应。                 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;  //Sheet表最大化                 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);                 range.Font.Size = 9;                 range.RowHeight = 14.25;                 range.Borders.LineStyle = 1;                 range.HorizontalAlignment = 1;             }         }         else         {             string[,] datas = new string[rowNum + 1, columnNum];             for (int i = 0; i < columnNum; i++) //写入字段             {                 datas[0, i] = dtTmp.Columns[i].Caption;             }             Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);             range.Interior.ColorIndex = 15;     //15代表灰色             range.Font.Bold = true;             range.Font.Size = 9;               int r = 0;             for (r = 0; r < rowNum; r++)             {                 for (int i = 0; i < columnNum; i++)                 {                     object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];                     datas[r + 1, i] = obj == null ? "" : obj.ToString().Trim();                 }             }             Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);             fchR.Value2 = datas;               worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。             m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;               range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);             range.Font.Size = 9;             range.RowHeight = 14.25;             range.Borders.LineStyle = 1;             range.HorizontalAlignment = 1;         }         workbook.Saved = true;         workbook.SaveCopyAs(filePath);           lblMsg.Text = "导出成功！" + DateTime.Now.ToString();     }     catch (Exception ex)     {         string dd = Server.MapPath("~/Tmp/1.txt");         System.IO.File.AppendAllText(dd, "导出异常：" + ex.Message + Environment.NewLine);     }     finally     {         EndReport();     } }   /// <summary> /// 退出报表时关闭Excel和清理垃圾Excel进程 /// </summary> private void EndReport() {     object missing = System.Reflection.Missing.Value;     try     {         m_xlApp.Workbooks.Close();         m_xlApp.Workbooks.Application.Quit();         m_xlApp.Application.Quit();         m_xlApp.Quit();     }     catch     {       }     finally     {         try         {             System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);             System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);             System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);             m_xlApp = null;         }         catch         {           }         try         {             //清理垃圾进程             this.killProcessThread();         }         catch         {           }         GC.Collect();     } }   /// <summary> /// 杀掉不死进程 /// </summary> private void killProcessThread() {     ArrayList myProcess = new ArrayList();     for (int i = 0; i < myProcess.Count; i++)     {         try         {             System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();         }         catch         {         }     } }

工作过程中经常会用到将数据导出到Excel中，一般情况下需要导出的数据都是几百几千条或者上万条，这都没有什么问题，但有时候会遇到特殊的需求，客户要求把几十万条甚至上百万条的数据导出到Excel中，这就比较麻烦了。当数据量很大导出到Excel表格的时候通常会遇到两个问题：一个是内存溢出，由于需要处理的数据量比较大，导致Excel占用的内存过多从而导致数据还没有导出完就因为内存溢出而失败；另一个是每张Excel表单最多只能有65536行，所以当DataTable或DataSet中数据多于65536行时会报错，这时候需要考虑Excel分页功能。

程序由原始的将数据一个表格一个表格地写入到EXCEL中，改变成程序先将数据存入二维数组中，然后再将数组值赋予EXCEL应用程序对象的VALUE属性，这样就使导出的效率提高很多，经测试导出100万数据没问题。

 

愿你我既可以朝九晚五，也可以浪迹天涯；愿你我既可以拈花把酒，也能围炉诗书茶。