转自http://www.cnblogs.com/hechaoyang/archive/2011/01/17/1937668.html
首先添加对Microsoft.Office.Interop.Excel的引用:
1 | using Microsoft.Office.Interop.Excel; |
02 | /// 将DataTable的数据导出到Excel中。 |
04 | /// <param name="dt">DataTable</param> |
05 | /// <param name="xlsFileDir">导出的Excel文件存放目录(绝对路径,最后带“/”)</param> |
06 | /// <param name="nameList">DataTable中列名的中文对应表</param> |
07 | /// <param name="strTitle">Excel表的标题</param> |
08 | /// <returns>Excel文件名</returns> |
09 | public static string ExportDataToExcel(System.Data.DataTable dt, string xlsFileDir, Hashtable nameList, string strTitle) |
11 | if (dt == null ) return "" ; |
13 | Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); |
14 | Microsoft.Office.Interop.Excel.Workbooks workBooks = excel.Workbooks; |
15 | Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); |
16 | Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1]; |
18 | int titleRowsCount = 0; |
19 | if (strTitle != null && strTitle.Trim() != "" ) |
22 | excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true ; |
23 | excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Size = 16; |
24 | excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).MergeCells = true ; |
25 | workSheet.Cells[1, 1] = strTitle; |
27 | if (!System.IO.Directory.Exists(xlsFileDir)) |
29 | System.IO.Directory.CreateDirectory(xlsFileDir); |
31 | string strFileName = DateTime.Now.ToString( "yyyyMMddHHmmssff" ) + ".xls" ; |
33 | string tempColumnName = "" ; |
35 | for ( int i = 0; i < dt.Rows.Count; i++) |
37 | for ( int j = 0; j < dt.Columns.Count; j++) |
41 | tempColumnName = dt.Columns[j].ColumnName.Trim(); |
44 | IDictionaryEnumerator Enum = nameList.GetEnumerator(); |
45 | while (Enum.MoveNext()) |
47 | if (Enum.Key.ToString().Trim() == tempColumnName) |
49 | tempColumnName = Enum.Value.ToString(); |
53 | workSheet.Cells[titleRowsCount + 1, j + 1] = tempColumnName; |
55 | workSheet.Cells[i + titleRowsCount + 2, j + 1] = dt.Rows[i][j].ToString(); |
58 | excel.get_Range(excel.Cells[titleRowsCount + 1, 1], excel.Cells[titleRowsCount + 1, dt.Columns.Count]).Font.Bold = true ; |
59 | excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter; |
60 | excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).EntireColumn.AutoFit(); |
62 | workBook.Saved = true ; |
63 | workBook.SaveCopyAs(xlsFileDir + strFileName); |
64 | System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); |
66 | System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); |
69 | System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks); |
72 | System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); |