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();//强行销毁