.NET开发人员首选的方法，通过COM组件调用Office软件本身来实现文件的创建和读写，但是数据量较大的时候异常缓慢；如下代码所示已经做了优化，将一个二维对象数组赋值到一个单元格区域中（下面的代码中只能用于导出列数不多于26列的数据导出）：
Office PIA
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.Add(
workbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
sheet.Name = dt.TableName;
string range = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
sheet.get_Range(range, Type.Missing).Value2 = data;
((Excel.Range)sheet.Rows[1, Type.Missing]).Font.Bold = true;
}
workbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
excel.Quit();
KillSpecialExcel(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int processId);
static void KillSpecialExcel(Excel.Application app)
{
try
{
if (app != null)
{
int processId;
GetWindowThreadProcessId(new IntPtr(app.Hwnd), out processId);
System.Diagnostics.Process.GetProcessById(processId).Kill();
}
}
catch (Exception ex)
{
throw ex;
}
}
</div>
文件流
这种方法的效率明显高于第一种，而且也不需要安装Office，但是导出的xls文件并不符合Excel的格式标准，在打开生成的xls文件时会提示：The file you are trying to open is in a different format that specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.
</div>