项目中需要将DataTable中的内容汇出到Excel文件中,采用循环的方式逐个Cell写数据到Excel文件中,代码如下:
//把数据表的内容导出到Excel文件中
public static void OutDataToExcel2(System.Data.DataTable srcDataTable, string excelFilePath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
//导出到execl
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel!");
return;
}
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
//让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写
xlApp.Visible = false;
//生成Excel中列头名称
for (int i = 0; i < srcDataTable.Columns.Count; i++)
{
xlSheet.Cells[1, i + 1] = srcDataTable.Columns[i].ColumnName;//输出DataGridView列头名
}
//把DataGridView当前页的数据保存在Excel中
if (srcDataTable.Rows.Count > 0)
{
for (int i = 0; i < srcDataTable.Rows.Count; i++)//控制Excel中行
{
for (int j = 0; j < srcDataTable.Columns.Count; j++)//控制Excel中列
{
xlSheet.Cells[i + 2, j + 1] = srcDataTable.Rows[i][j];//i控制行,从Excel中第2行开始输出第一行数据,j控制列,从Excel中第1列输出第1列数据
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
if (xlSheet != null)
{
xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
KillProcess(xlApp);
}
}
catch (Exception ex)
{
KillProcess(xlApp);
throw ex;
}
}
测试时发现很不好,汇出一个19列203行的Table时,用时竟然高达24秒多。
后来采用整体赋值的方法修改,代码如下:
//把数据表的内容导出到Excel文件中
public static void OutDataToExcel(System.Data.DataTable srcDataTable, string excelFilePath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
//导出到execl
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel!");
return;
}
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
//让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写
xlApp.Visible = false;
object[,] objData = new object[srcDataTable.Rows.Count + 1, srcDataTable.Columns.Count];
//首先将数据写入到一个二维数组中
for (int i = 0; i < srcDataTable.Columns.Count; i++)
{
objData[0, i] = srcDataTable.Columns[i].ColumnName;
}
if (srcDataTable.Rows.Count > 0)
{
for (int i = 0; i < srcDataTable.Rows.Count; i++)
{
for (int j = 0; j < srcDataTable.Columns.Count; j++)
{
objData[i + 1, j] = srcDataTable.Rows[i][j];
}
}
}
string startCol = "A";
int iCnt = (srcDataTable.Columns.Count / 26);
string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
string endCol = endColSignal + ((char)('A' + srcDataTable.Columns.Count - iCnt * 26 - 1)).ToString();
Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + (srcDataTable.Rows.Count - iCnt * 26 + 1).ToString());
range.Value = objData; //给Exccel中的Range整体赋值
range.EntireColumn.AutoFit(); //设定Excel列宽度自适应
xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1;//Excel文件列名 字体设定为Bold
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
if (xlSheet != null)
{
xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//xlApp.Quit();
KillProcess(xlApp);
}
}
catch (Exception ex)
{
KillProcess(xlApp);
throw ex;
}
}
测试之前的19列203行的DataTable,用时为4秒多一些。