将DataTable导出到Excel

.NET中导出Excel的方法大致有三类，一是导出文本文件（csv），或者导出符合某种格式的xml文件，例如此文介绍的Export a DataSet to Microsoft Excel without the use of COM objects；二是利用Ado.NET，没用过，据说效率不高。三是利用COM。这三类方法第三种是最自由的，可以设置Excel单元格的格式等等，几乎可以完全控制整个Excel。前两种方法适合于导出的数据对格式没有任何要求的情况。

下面主要介绍第三种方法。首先需要加入一个引用：

Code Snippet

1. using Microsoft.Office.Interop.Excel;

将Datatable导出到Excel的代码是很直观的：

Code Snippet

1. public static bool Export(System.Data.DataTable data, string file)
2.         {
3.             Application app = new Microsoft.Office.Interop.Excel.Application();
4.             try
5.             {
6.                 if (app == null)
7.                 {
8.                     throw new Exception("new Microsoft.Office.Interop.Excel.Application() returns null");
9.                 }
10.                 else
11.                 {
12.                     Workbooks xlBooks = app.Workbooks;
13.                     Workbook xlBook = xlBooks.Add(XlWBATemplate.xlWBATWorksheet);
14.                     Worksheet xlSheet = xlBook.Worksheets[1] as Worksheet;
15.                     int n=1;
16.                     foreach (System.Data.DataColumn col in data.Columns)
17.                     {                        
18.                         xlSheet.Cells[1, n++] = col.ColumnName;
19.                     }
20.                     int row = 2;
21.                    
22.                     foreach (System.Data.DataRow r in data.Rows)
23.                     {
24.                         int column = 1;
25.                         foreach (System.Data.DataColumn col in data.Columns)
26.                         {
27.                             xlSheet.Cells[row, column] = data.Rows[row-2].ItemArray[column - 1];
28.                             column++;
29.                         }
30.                         row++;
31.                     }
32.                     object missing = System.Reflection.Missing.Value;
33.                     xlSheet.SaveAs(file, missing, missing, missing,
34.                             missing, missing, missing, missing, missing, missing);
35.                      
36.                 }                
37.                 return true;
38.             }
39.             catch (Exception e)
40.             {
41.                 Logger.Log(e);
42.                 
43.                 return false;
44.             }
45.             finally
46.             {
47.                 app.Quit();
48.                 GC.Collect();
49.             }
50.         }

但是这样操作非常非常慢。在存入数据的时候不能一个一个单元格设置，可以用一个二维数组对一个Range设置，具体方法如下：

Code Snippet

1. Workbooks xlBooks = app.Workbooks;
2.                     Workbook xlBook = xlBooks.Add(XlWBATemplate.xlWBATWorksheet);
3.                     Worksheet xlSheet = xlBook.Worksheets[1] as Worksheet;
4.                     int n=1;
5.                     foreach (System.Data.DataColumn col in data.Columns)
6.                     {                        
7.                         xlSheet.Cells[1, n++] = col.ColumnName;
8.                     }
9.                    // int row = 2;
10.                     int row = 0;
11.                     object[,] arr = new object[data.Rows.Count, data.Columns.Count];
12.  
13.                     foreach (System.Data.DataRow r in data.Rows)
14.                     {
15.                         //int column = 1;
16.                         int column = 0;
17.                         foreach (System.Data.DataColumn col in data.Columns)
18.                         {
19.                             //xlSheet.Cells[row, column] = data.Rows[row-2].ItemArray[column - 1];
20.                             arr[row, column] = data.Rows[row][column];
21.                             column++;
22.                         }
23.                         row++;
24.                     }
25.                     Range range=(Range)xlSheet.Cells[2, 1];
26.                     range=range.get_Resize(data.Rows.Count,data.Columns.Count);
27.                     range.Value2= arr;
28.                     object missing = System.Reflection.Missing.Value;
29.                     xlSheet.SaveAs(file, missing, missing, missing,
30.                             missing, missing, missing, missing, missing, missing);

上面仅贴出了关键部分。