将datatable中数据导出到excel中

 

该程序需要一个名为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)
             {
                 MessageBoxEx.Show("无法创建Excel对象，可能您的机子未安装Excel");
                 return;
             }
 
             //日志信息
             Excel.Workbooks Workbooks_ZB = xlApp.Workbooks;
             Excel.Workbook Workbook_ZB = Workbooks_ZB.Add(Excel.XlWBATemplate.xlWBATWorksheet);
             Excel.Worksheet Worksheet_ZB = (Excel.Worksheet)Workbook_ZB.Worksheets[1];//取得sheet1
 
 
             //设置格式
             Excel.Range range = Worksheet_ZB.get_Range(Worksheet_ZB.Cells[1, 1], Worksheet_ZB.Cells[parDtLogInfo.Rows.Count + 1, parDtLogInfo.Columns.Count]);
 
             range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
             range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
             range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
             range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
             range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
             range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
             range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
 
 
             //设置每一列所影响的单元格范围
             string logInfoCount = Convert.ToString(parDtLogInfo.Rows.Count - 1);
             string operatorNameRange = "A1:A" + logInfoCount;
             string operatorTimeRange = "B1:B" + logInfoCount;
             string operatorContentRange = "C1:C" + logInfoCount;
 
             //设置单元格的宽度
             xlApp.get_Range(operatorNameRange, Type.Missing).ColumnWidth = 20;
             xlApp.get_Range(operatorTimeRange, Type.Missing).ColumnWidth = 20;
             xlApp.get_Range(operatorContentRange, Type.Missing).ColumnWidth = 65;
 
 
             //写入数值
             for (int i = 0; i < parDtLogInfo.Rows.Count + 1; i++)//第0行表示datatable的标题栏
             {
                 for (int j = 0; j < parDtLogInfo.Columns.Count; j++)
                 {
                     if (i == 0)//写表头
                     {
                         Worksheet_ZB.Cells[1, j + 1] = parDtLogInfo.Columns[j].ColumnName;
                         //设置标题栏格式
                         range = (Excel.Range)Worksheet_ZB.Cells[1, j + 1];
                         range.Interior.ColorIndex = 15;
                         range.Font.Bold = true;
                     }
                     else//写内容
                     {
                         Worksheet_ZB.Cells[i + 1, j + 1] = parDtLogInfo.Rows[i - 1][j].ToString();
                     }
                 }
             }
 
             Workbook_ZB.Saved = true;
             int index = parFileName.LastIndexOf("\\") + 1;
             //保存到Excel
             Workbook_ZB.SaveCopyAs(parFileName.Substring(0, index) + "日志信息-" + parFileName.Substring(index, parFileName.Length - index));
             xlApp.Quit();//退出
             GC.Collect();//强行销毁 
         }