将DataTable导出Excel的三种方式

一、使用Microsoft.Office.Interop.Excel.DLL

　　需要安装Office

　　代码如下： 

 

 2           public   static   bool  ExportExcel(System.Data.DataTable dt,  string  path)
 3          {
 4               bool  succeed  =   false ;
 5               if  (dt  !=   null )
 6              {
 7                  Microsoft.Office.Interop.Excel.Application xlApp  =   null ;
 8                   try
 9                  {
10                      xlApp  =   new  Microsoft.Office.Interop.Excel.ApplicationClass();
11                  }
12                   catch  (Exception ex)
13                  {
14                       throw  ex;
15                  }
16 
17                   if  (xlApp  !=   null )
18                  {
19                       try
20                      {
21                          Microsoft.Office.Interop.Excel.Workbook xlBook  =  xlApp.Workbooks.Add( true );
22                           object  oMissing  =  System.Reflection.Missing.Value;
23                          Microsoft.Office.Interop.Excel.Worksheet xlSheet  =   null ;
24 
25                          xlSheet  =  (Worksheet)xlBook.Worksheets[ 1 ];
26                          xlSheet.Name  =  dt.TableName;
27 
28                           int  rowIndex  =   1 ;
29                           int  colIndex  =   1 ;
30                           int  colCount  =  dt.Columns.Count;
31                           int  rowCount  =  dt.Rows.Count;
32 
33                           // 列名的处理
34                           for  ( int  i  =   0 ; i  <  colCount; i ++ )
35                          {
36                              xlSheet.Cells[rowIndex, colIndex]  =  dt.Columns[i].ColumnName;
37                              colIndex ++ ;
38                          }
39                           // 列名加粗显示
40                          xlSheet.get_Range(xlSheet.Cells[rowIndex,  1 ], xlSheet.Cells[rowIndex, colCount]).Font.Bold  =   true ;
41                          xlSheet.get_Range(xlSheet.Cells[rowIndex,  1 ], xlSheet.Cells[rowCount  +   1 , colCount]).Font.Name  =   " Arial " ;
42                          xlSheet.get_Range(xlSheet.Cells[rowIndex,  1 ], xlSheet.Cells[rowCount  +   1 , colCount]).Font.Size  =   " 10 " ;
43                          rowIndex ++ ;
44 
45                           for  ( int  i  =   0 ; i  <  rowCount; i ++ )
46                          {
47                              colIndex  =   1 ;
48                               for  ( int  j  =   0 ; j  <  colCount; j ++ )
49                              {
50                                  xlSheet.Cells[rowIndex, colIndex]  =  dt.Rows[i][j].ToString();
51                                  colIndex ++ ;
52                              }
53                              rowIndex ++ ;
54                          }
55                          xlSheet.Cells.EntireColumn.AutoFit();
56 
57                          xlApp.DisplayAlerts  =   false ;
58                          path  =  Path.GetFullPath(path);
59                          xlBook.SaveCopyAs(path);
60                          xlBook.Close( false ,  null ,  null );
61                          xlApp.Workbooks.Close();
62                          Marshal.ReleaseComObject(xlSheet);
63                          Marshal.ReleaseComObject(xlBook);
64                          xlBook  =   null ;
65                          succeed  =   true ;
66                      }
67                       catch  (Exception ex)
68                      {
69                          succeed  =   false ;
70                      }
71                       finally
72                      {
73                          xlApp.Quit();
74                          Marshal.ReleaseComObject(xlApp);
75                           int  generation  =  System.GC.GetGeneration(xlApp);
76                          xlApp  =   null ;
77                          System.GC.Collect(generation);
78                      }
79                  }
80              }
81               return  succeed;
82          }

二、使用Aspose.Cells.dll

Aspose.Cells 是 Aspose公司推出的导出Excel的控件，不依赖Office，商业软件，网上有破解 （下载见附件）。

代码如下： 

 1          public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path)
 2          {
 3               bool  succeed  =   false ;
 4               if  (dt  !=   null )
 5              {
 6                   try
 7                  {
 8                      Aspose.Cells.License li  =   new  Aspose.Cells.License();
 9                       string  lic  =  Resources.License;
10                      Stream s  =   new  MemoryStream(ASCIIEncoding.Default.GetBytes(lic));
11                      li.SetLicense(s);
12 
13                      Aspose.Cells.Workbook workbook  =   new  Aspose.Cells.Workbook();
14                      Aspose.Cells.Worksheet cellSheet  =  workbook.Worksheets[ 0 ];
15 
16                      cellSheet.Name  =  dt.TableName;
17 
18                       int  rowIndex  =   0 ;
19                       int  colIndex  =   0 ;
20                       int  colCount  =  dt.Columns.Count;
21                       int  rowCount  =  dt.Rows.Count;
22 
23                       // 列名的处理
24                       for  ( int  i  =   0 ; i  <  colCount; i ++ )
25                      {
26                          cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
27                          cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold  =   true ;
28                          cellSheet.Cells[rowIndex, colIndex].Style.Font.Name  =   " 宋体 " ;
29                          colIndex ++ ;
30                      }
31 
32                      Aspose.Cells.Style style  =  workbook.Styles[workbook.Styles.Add()];
33                      style.Font.Name  =   " Arial " ;
34                      style.Font.Size  =   10 ;
35                      Aspose.Cells.StyleFlag styleFlag  =   new  Aspose.Cells.StyleFlag();
36                      cellSheet.Cells.ApplyStyle(style, styleFlag);
37 
38                      rowIndex ++ ;
39 
40                       for  ( int  i  =   0 ; i  <  rowCount; i ++ )
41                      {
42                          colIndex  =   0 ;
43                           for  ( int  j  =   0 ; j  <  colCount; j ++ )
44                          {
45                              cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
46                              colIndex ++ ;
47                          }
48                          rowIndex ++ ;
49                      }
50                      cellSheet.AutoFitColumns();
51 
52                      path  =  Path.GetFullPath(path);
53                      workbook.Save(path);
54                      succeed  =   true ;
55                  }
56                   catch  (Exception ex)
57                  {
58                      succeed  =   false ;
59                  }
60              }
61 
62               return  succeed;
63          }

 

三、 使用XML导出Excel

不依赖Office和其他第三方控件，需要事先准备一个模版。新建一个Excel文档，另存为XML 表格格式，将另存为的文件的扩展名改为xls，作为导出的模版。导出Excel时，用XMLDocment先打开模版，然后对模版进行增加修改操作，操作方法就是一般的XML操作方法。因为导出的文件扩展名是xls，与XML的文件格式不符，所以用Excel打开时会弹出提示。

代码如下：

  1          public static bool ExportExcelWithXML(System.Data.DataTable dt, string path)
  2          {
  3               bool  succeed  =   false ;
  4               if  (dt  ==   null )
  5              {
  6                   //  导出为XML格式的Excel文件，需要事先准备好XML格式的Excel文件作为模版
  7                   try
  8                  {
  9                      XmlDocument doc  =   new  XmlDocument();
 10                      doc.Load(System.Windows.Forms.Application.StartupPath  +   @" \XLS\ExportXML.xls " );
 11                      XmlNode root  =  doc.DocumentElement;
 12                      XmlNodeList xnlist  =  root.ChildNodes;
 13                      XmlElement sheet  =   null ;
 14                      XmlElement documentPro  =   null ;
 15                      XmlElement styles  =   null ;
 16                       foreach  (XmlNode xn  in  xnlist)
 17                      {
 18                          XmlElement xe  =  (XmlElement)xn;
 19                           if  (xe.Name  ==   " DocumentProperties " )
 20                          {
 21                              documentPro  =  xe;
 22                          }
 23                           else   if  (xe.Name  ==   " Worksheet " )
 24                          {
 25                              sheet  =  xe;
 26                          }
 27                           else   if  (xe.Name  ==   " Styles " )
 28                          {
 29                              styles  =  xe;
 30                          }
 31                      }
 32 
 33                       if  (documentPro  ==   null   ||  sheet  ==   null   ||  styles  ==   null )
 34                      {
 35                           return   false ;
 36                      }
 37 
 38                       //  写入Sheet名
 39                      sheet.SetAttribute( " Name " , ssNameSpace, dt.TableName);
 40 
 41                       //  添加Style
 42                      XmlElement styleColumnName  =  doc.CreateElement( " Style " , ssNameSpace);
 43                      styleColumnName.SetAttribute( " ID " , ssNameSpace,  " s16 " );
 44                      XmlElement fontColumnName  =  doc.CreateElement( " Font " , ssNameSpace);
 45                      fontColumnName.SetAttribute( " FontName " , ssNameSpace,  " Arial " );
 46                      fontColumnName.SetAttribute( " Family " , xNameSpace,  " Swiss " );
 47                      fontColumnName.SetAttribute( " Color " , ssNameSpace,  " #000000 " );
 48                      fontColumnName.SetAttribute( " Bold " , ssNameSpace,  " 1 " );
 49                      styleColumnName.AppendChild(fontColumnName);
 50                      styles.AppendChild(styleColumnName);
 51 
 52                      XmlElement styleRow  =  doc.CreateElement( " Style " , ssNameSpace);
 53                      styleRow.SetAttribute( " ID " , ssNameSpace,  " s17 " );
 54                      XmlElement fontRow  =  doc.CreateElement( " Font " , ssNameSpace);
 55                      fontRow.SetAttribute( " FontName " , ssNameSpace,  " Arial " );
 56                      fontRow.SetAttribute( " Family " , xNameSpace,  " Swiss " );
 57                      fontRow.SetAttribute( " Color " , ssNameSpace,  " #000000 " );
 58                      styleRow.AppendChild(fontRow);
 59                      styles.AppendChild(styleRow);
 60 
 61                       //  写入表格内容
 62                      XmlNode table  =  sheet.FirstChild;
 63 
 64                       //  写入行列个数
 65                      ((XmlElement)table).SetAttribute( " ExpandedColumnCount " , ssNameSpace, dt.Columns.Count.ToString());
 66                      ((XmlElement)table).SetAttribute( " ExpandedRowCount " , ssNameSpace, (dt.Rows.Count  +   2 ).ToString());
 67 
 68                       //  添加列宽
 69                       for  ( int  i  =   0 ; i  <  dt.Columns.Count; i ++ )
 70                      {
 71                          XmlElement column  =  doc.CreateElement( " Column " , ssNameSpace);
 72                          column.SetAttribute( " Width " , ssNameSpace, "100");
 73                          column.SetAttribute( " AutoFitWidth " , ssNameSpace,  " 1 " );
 74                          table.AppendChild(column);
 75                      }
 76 
 77                       //  添加列名
 78                      XmlElement columnName  =  doc.CreateElement( " Row " , ssNameSpace);
 79                       for  ( int  i  =   0 ; i  <  dt.Columns.Count; i ++ )
 80                      {
 81                          XmlElement columnCell  =  doc.CreateElement( " Cell " , ssNameSpace);
 82                          columnCell.SetAttribute( " StyleID " , ssNameSpace,  " s16 " );
 83 
 84                          XmlElement data  =  doc.CreateElement( " ss:Data " , ssNameSpace);
 85                          data.SetAttribute( " Type " , ssNameSpace,  " String " );
 86                          data.InnerText  =  dt.Columns[i].ToString();
 87 
 88                          columnCell.AppendChild(data);
 89                          columnName.AppendChild(columnCell);
 90                      }
 91                      table.AppendChild(columnName);
 92 
 93                       //  添加行
 94                       for  ( int  i  =   0 ; i  <  dt.Rows.Count; i ++ )
 95                      {
 96                          XmlElement row  =  doc.CreateElement( " Row " , ssNameSpace);
 97                           for  ( int  j  =   0 ; j  <  dt.Columns.Count; j ++ )
 98                          {
 99                              XmlElement cell  =  doc.CreateElement( " Cell " , ssNameSpace);
100                              cell.SetAttribute( " StyleID " , ssNameSpace,  " s17 " );
101 
102                              XmlElement data  =  doc.CreateElement( " Data " , ssNameSpace);
103                              data.SetAttribute( " Type " , ssNameSpace,  " String " );
104                              data.InnerText  =  dt.Rows[i][j].ToString();
105 
106                              cell.AppendChild(data);
107                              row.AppendChild(cell);
108                          }
109                          table.AppendChild(row);
110                      }
111 
112                      DateTime now  =  DateTime.Now;
113                       string  timeString  =   string .Format( " {0}T{1}Z " , now.ToShortDateString(), now.ToLongTimeString());
114                      XmlNodeList docProNodeList  =  documentPro.ChildNodes;
115                       foreach  (XmlNode xn  in  docProNodeList)
116                      {
117                           if  (xn.Name  ==   " Author "   ||  xn.Name  ==   " LastAuthor " )
118                          {
119                               //  写入作者和修改者
120                              xn.InnerText  =  Environment.UserName;
121                          }
122                           else   if  (xn.Name  ==   " Created "   ||  xn.Name  ==   " LastSaved " )
123                          {
124                               //  写入创建时间和修改时间
125                              xn.InnerText  =  timeString;
126                          }
127                           else   if  (xn.Name  ==   " Company " )
128                          {
129                               //  写入公司名
130                              xn.InnerText  =  System.Windows.Forms.Application.CompanyName;
131                          }
132                      }
133 
134                      doc.Save(path);
135                      succeed  =   true ;
136                  }
137                   catch  (Exception e)
138                  {
139                      succeed  =   false ;
140                  }
141              }
142 
143               return  succeed;
144          }

 

 

总结：第二、三种方法导出速度很快；第二种方法使用上最简单，但需要购买或破解；第三种方法依赖最小，便于代码移植，但使用上略显繁琐（需要大量的XML操作）。所以如果有条件购买或不介意破解的话，极力推荐第二种方法。

ExportExcelWithXML的模版： /Files/zhyt1985/ExportXML.rar 

Aspose.Cells 4.8 & Help & License： /Files/zhyt1985/Aspose.Cells.rar 

转载于:https://www.cnblogs.com/zhyt1985/archive/2011/01/15/1936343.html