工作过程中经常会用到将数据导出到Excel中，一般情况下需要导出的数据都是几百几千条或者上万条，这都没有什么问题，但有时候会遇到特殊的需求，客户要求把几十万条甚至上百万条的数据导出到Excel中，这就比较麻烦了。当数据量很大导出到Excel表格的时候通常会遇到两个问题：一个是内存溢出，由于需要处理的数据量比较大，导致Excel占用的内存过多从而导致数据还没有导出完就因为内存溢出而失败；另一个是每张Excel表单最多只能有65536行，所以当DataTable或DataSet中数据多于65536行时会报错，这时候需要考虑Excel分页功能。
////// 将DataTable数据导出到Excel表 /// /// 要导出的DataTable /// Excel的保存路径及名称 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(); } } ////// 退出报表时关闭Excel和清理垃圾Excel进程 /// 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(); } } ////// 杀掉不死进程 /// 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 { } } }