C#透过DataTable汇出Excel

1.参考Com Microsoft Excel 11.0 Object Libaray;

2.using Microsoft.Office.Interop.Excel;

3.using System.Reflection;

 

object missing = Missing.Value; string[] colName = new string[] { "考勤月份", "姓名", "部门", "正常天数", "周末天数", "加班小时", "夜班天数", "迟到次数", "迟到分钟", "早退次数", "早退分钟" }; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { ShowLog("无法创建Excel对象，可能您的机子未安装Excel"); SaveLog("无法创建Excel对象，可能您的机子未安装Excel"); } Workbooks wbs = app.Workbooks; Workbook workbook = wbs.Add(XlWBATemplate.xlWBATWorksheet); Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; Range range; worksheet.Cells[1, 1] = month + "考勤月报表"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 11]).MergeCells = true; //合并单元格 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 11]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//居中对齐 worksheet.get_Range(worksheet.Cells[1, 4], worksheet.Cells[1, 4]).ColumnWidth = 10;//列宽 //写入栏位名 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[2, i + 1] = colName[i]; range = (Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入栏位值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i]; } //自动换行 worksheet.get_Range(worksheet.Cells[r + 3, 4], worksheet.Cells[r + 1, 4]).Columns.WrapText = true; //自动加行高 worksheet.get_Range(worksheet.Cells[r + 3, 4], worksheet.Cells[r + 3, 4]).Rows.AutoFit(); System.Windows.Forms.Application.DoEvents(); } range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]); range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null); range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = XlColorIndex.xlColorIndexAutomatic; range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = XlColorIndex.xlColorIndexAutomatic; range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; range.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin; } string destDirName = AppDomain.CurrentDomain.BaseDirectory + "Excel"; if (!Directory.Exists(destDirName)) { Directory.CreateDirectory(destDirName); } string fileName = destDirName + "//" + month + "考勤月报表" + ".xls"; try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { ShowLog("导出文件时出错,文件可能正被打开！"); SaveLog("导出文件时出错,文件可能正被打开！" + ex.Message); } workbook.Close(false, missing, missing); app.Quit(); GC.Collect();//强行销毁