我曾使用过这种方法,将对象数组复制到Excel单元格区域,而不是逐行逐列地复制,这证明是一种相当有效的方式。
public void ExportToExcel(DataTable dataTable, String pathToSave)
{
var excelApp = new ApplicationClass();
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
var rawData = new object[dataTable.Rows.Count + 1, dataTable.Columns.Count];
for (var col = 0; col < dataTable.Columns.Count; col++)
{
rawData[0, col] = dataTable.Columns[col].ColumnName;
}
for (var col = 0; col < dataTable.Columns.Count; col++)
{
for (int row = 0; row < dataTable.Rows.Count; row++)
{
rawData[row + 1, col] = dataTable.Rows[row].ItemArray[col];
}
}
string finalColLetter = string.Empty;
const string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dataTable.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dataTable.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dataTable.Columns.Count - 1) % colCharsetLen, 1);
var excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.Item[++sheetIndex], Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dataTable.TableName;
var excelRange = string.Format("A1:{0}{1}", finalColLetter, dataTable.Rows.Count + 1);
excelSheet.Range[excelRange, Type.Missing].Value2 = rawData;
var headerColumnRange = (Range)excelSheet.Rows[1, Type.Missing];
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
headerColumnRange.EntireColumn.AutoFit();
excelWorkbook.SaveAs(pathToSave, XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
Write
也使其有点冗余。 - Panagiotis Kanavos