工作过程中经常会用到将数据导出到Excel中，一般情况下需要导出的数据都是几百几千条或者上万条，这都没有什么问题，但有时候会遇到特殊的需求，客户要求把几十万条甚至上百万条的数据导出到Excel中，这就比较麻烦了。当数据量很大导出到Excel表格的时候通常会遇到两个问题：一个是内存溢出，由于需要处理的数据量比较大，导致Excel占用的内存过多从而导致数据还没有导出完就因为内存溢出而失败；另一个是每张Excel表单最多只能有65536行，所以当DataTable或DataSet中数据多于65536行时会报错，这时候需要考虑Excel分页功能。
/// <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[];//取得Sheet1 try
{
//单张Excel表格最大行数
if (rowNum > )
{
long pageRows = ; //定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows); //导出数据生成的表单数
if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + ;
}
for (int sc = ; sc <= scount; sc++)
{
if (sc > ) //这里由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 + , columnNum]; for (int i = ; i < columnNum; i++) //写入字段
{
datas[, i] = dtTmp.Columns[i].Caption; //表头信息
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[, columnNum]);
range.Interior.ColorIndex = ; //15代表灰色
range.Font.Bold = true;
range.Font.Size = ; int init = int.Parse(((sc - ) * pageRows).ToString());
int r = ;
int index = ;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
} for (r = init; r < result; r++)
{
index = index + ;
for (int i = ; 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[, ], worksheet.Cells[index + , columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化
range = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[index + , columnNum]);
range.Font.Size = ;
range.RowHeight = 14.25;
range.Borders.LineStyle = ;
range.HorizontalAlignment = ;
}
}
else
{
string[,] datas = new string[rowNum + , columnNum];
for (int i = ; i < columnNum; i++) //写入字段
{
datas[, i] = dtTmp.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[, columnNum]);
range.Interior.ColorIndex = ; //15代表灰色
range.Font.Bold = true;
range.Font.Size = ; int r = ;
for (r = ; r < rowNum; r++)
{
for (int i = ; i < columnNum; i++)
{
object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];
datas[r + , i] = obj == null ? "" : obj.ToString().Trim();
}
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[rowNum + , columnNum]);
fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; range = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[rowNum + , columnNum]);
range.Font.Size = ;
range.RowHeight = 14.25;
range.Borders.LineStyle = ;
range.HorizontalAlignment = ;
}
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 = ; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch
{
}
}
}
