Code
该程序需要一个名为excel.dll的文件,如果需要请加入群74085440，里面有下载
1 /// <summary>
2 /// 将datatable中的数据导出到excel中
3 /// </summary>
4 /// <param name="parDtLogInfo">DataTable数据集</param>
5 /// <param name="parFileName">保存的excel路径</param>
6 public void Export2Excel(System.Data.DataTable parDtLogInfo, string parFileName)
7 {
8 Excel.Application xlApp = new Excel.Application();
9 if (xlApp == null)
10 {
11 MessageBoxEx.Show("无法创建Excel对象，可能您的机子未安装Excel");
12 return;
13 }
14
15 //日志信息
16 Excel.Workbooks Workbooks_ZB = xlApp.Workbooks;
17 Excel.Workbook Workbook_ZB = Workbooks_ZB.Add(Excel.XlWBATemplate.xlWBATWorksheet);
18 Excel.Worksheet Worksheet_ZB = (Excel.Worksheet)Workbook_ZB.Worksheets[1];//取得sheet1
19
20
21 //设置格式
22 Excel.Range range = Worksheet_ZB.get_Range(Worksheet_ZB.Cells[1, 1], Worksheet_ZB.Cells[parDtLogInfo.Rows.Count + 1, parDtLogInfo.Columns.Count]);
23
24 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
25 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
26 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
27 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
28 range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
29 range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
30 range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
31
32
33 //设置每一列所影响的单元格范围
34 string logInfoCount = Convert.ToString(parDtLogInfo.Rows.Count - 1);
35 string operatorNameRange = "A1:A" + logInfoCount;
36 string operatorTimeRange = "B1:B" + logInfoCount;
37 string operatorContentRange = "C1:C" + logInfoCount;
38
39 //设置单元格的宽度
40 xlApp.get_Range(operatorNameRange, Type.Missing).ColumnWidth = 20;
41 xlApp.get_Range(operatorTimeRange, Type.Missing).ColumnWidth = 20;
42 xlApp.get_Range(operatorContentRange, Type.Missing).ColumnWidth = 65;
43
44
45 //写入数值
46 for (int i = 0; i < parDtLogInfo.Rows.Count + 1; i++)//第0行表示datatable的标题栏
47 {
48 for (int j = 0; j < parDtLogInfo.Columns.Count; j++)
49 {
50 if (i == 0)//写表头
51 {
52 Worksheet_ZB.Cells[1, j + 1] = parDtLogInfo.Columns[j].ColumnName;
53 //设置标题栏格式
54 range = (Excel.Range)Worksheet_ZB.Cells[1, j + 1];
55 range.Interior.ColorIndex = 15;
56 range.Font.Bold = true;
57 }
58 else//写内容
59 {
60 Worksheet_ZB.Cells[i + 1, j + 1] = parDtLogInfo.Rows[i - 1][j].ToString();
61 }
62 }
63 }
64
65 Workbook_ZB.Saved = true;
66 int index = parFileName.LastIndexOf("\\") + 1;
67 //保存到Excel
68 Workbook_ZB.SaveCopyAs(parFileName.Substring(0, index) + "日志信息-" + parFileName.Substring(index, parFileName.Length - index));
69 xlApp.Quit();//退出
70 GC.Collect();//强行销毁
71 }