将DataTable中的数据导出到Excel中

转自http://www.cnblogs.com/hechaoyang/archive/2011/01/17/1937668.html

 

首先添加对Microsoft.Office.Interop.Excel的引用：

view source

print?

1using Microsoft.Office.Interop.Excel;

 

view source

print?

01/// <summary>

02/// 将DataTable的数据导出到Excel中。

03/// </summary>

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>

09public static string ExportDataToExcel(System.Data.DataTable dt, string xlsFileDir, Hashtable nameList, string strTitle)

10{

11    if (dt == null) return "";

12 

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];

17 

18    int titleRowsCount = 0;

19    if (strTitle != null && strTitle.Trim() != "")

20    {

21        titleRowsCount = 1;

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;

26    }

27    if (!System.IO.Directory.Exists(xlsFileDir))

28    {

29        System.IO.Directory.CreateDirectory(xlsFileDir);

30    }

31    string strFileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";

32 

33    string tempColumnName = "";

34 

35    for (int i = 0; i < dt.Rows.Count; i++)

36    {

37        for (int j = 0; j < dt.Columns.Count; j++)

38        {

39            if (i == 0)

40            {

41                tempColumnName = dt.Columns[j].ColumnName.Trim();

42                if (nameList != null)

43                {

44                    IDictionaryEnumerator Enum = nameList.GetEnumerator();

45                    while (Enum.MoveNext())

46                    {

47                        if (Enum.Key.ToString().Trim() == tempColumnName)

48                        {

49                            tempColumnName = Enum.Value.ToString();

50                        }

51                    }

52                }

53                workSheet.Cells[titleRowsCount + 1, j + 1] = tempColumnName;

54            }

55            workSheet.Cells[i + titleRowsCount + 2, j + 1] = dt.Rows[i][j].ToString();

56        }

57    }

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();

61 

62    workBook.Saved = true;

63    workBook.SaveCopyAs(xlsFileDir + strFileName);

64    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

65    workSheet = null;

66    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

67    workBook = null;

68    workBooks.Close();

69    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks);

70    workBooks = null;

71    excel.Quit();

72    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

73    excel = null;

74    return strFileName;

75}