···c#
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName){/// ///不要图省劲省略了System.Data。Microsoft.office.Interop.DataTable 和System.Data.DataTable 会产生歧义///先得到datatable的行数int rowNum = tmpDataTable.Rows.Count;///列数int columnNum = tmpDataTable.Columns.Count;///声明一个应用程序类实例//Application xlApp = new Application();Excel.Application xlApp = new Excel.Application(); ; //新建excel对象;xlApp.Visible = true;//xlApp.DefaultFilePath = ""; ///默认文件路径，将其设置路径后发现没什么变化。导出excel的路径还是在参数strFileName里设置//xlApp.DisplayAlerts = true;//xlApp.SheetsInNewWorkbook = 1;///返回或设置 Microsoft Excel 自动插入到新工作簿中的工作表数目。Long 类型，可读写。设置为2之后没发现什么区别//创建一个新工作簿Excel.Workbook xlBook = xlApp.Workbooks.Add();///在工作簿中得到sheet。_Worksheet oSheet = (_Worksheet)xlBook.Worksheets[1];#region 绘制列//加载图片，并设置图片大小 oSheet.PageSetup.LeftHeaderPicture.Filename = AppDomain.CurrentDomain.BaseDirectory + "\\Logo.png";oSheet.PageSetup.LeftHeader = "&G XXXXXX有限公司 \n"+ "xxxx Machinery Co., Ltd.";oSheet.PageSetup.TopMargin = 100;///自定义方法，想sheet中绘制列RangeBuild2(oSheet, "A1", "I1", "XXX检验表");RangeBuild(oSheet, "A2", "A2", "零件编号");RangeBuild(oSheet, "B2", "B2", txtljbh.Text);RangeBuild(oSheet, "C2", "C2", "检验机床");RangeBuild(oSheet, "D2", "E2", txtsbbh.Text);RangeBuild(oSheet, "F2", "F2", "表格编号");if (txtgx.Text.Contains("滚道")){RangeBuild(oSheet, "G2", "I2", "TC J05.02-01");}elseif (txtgx.Text.Contains("齿")){RangeBuild(oSheet, "G2", "I2", "TC J05.02-02");}else{RangeBuild(oSheet, "G2", "I2", "");}RangeBuild(oSheet, "A3", "A3", "产品型号");RangeBuild(oSheet, "B3", "B3", txtcpxh.Text);RangeBuild(oSheet, "C3", "C3", "检验员工号");RangeBuild(oSheet, "D3", "E3", txtjyy.Text);RangeBuild(oSheet, "F3", "F3", "结论");RangeBuild(oSheet, "G3", "I3", "合格");RangeBuild(oSheet, "A4", "A4", "圈别");RangeBuild(oSheet, "B4", "B4", txtqb.Text);RangeBuild(oSheet, "C4", "C4", "检验日期");RangeBuild(oSheet, "D4", "E4", txtjyyrq.Text);RangeBuild(oSheet, "F4", "F4", "");RangeBuild(oSheet, "G4", "I4", "");RangeBuild(oSheet, "A5", "A5", "序号");RangeBuild(oSheet, "B5", "B5", "技术要求项目");RangeBuild(oSheet, "C5", "C5", "要求值");RangeBuild(oSheet, "D5", "D5", "上偏差");RangeBuild(oSheet, "E5", "E5", "下偏差");RangeBuild(oSheet, "F5", "F5", "实测值1");RangeBuild(oSheet, "G5", "G5", "实测值2");RangeBuild(oSheet, "H5", "H5", "实测值3");RangeBuild(oSheet, "I5", "I5", "结论");#endregion //将DataTable中的数据导入Excel中for (int i = 0; i < rowNum; i++){for (int j = 0; j < columnNum; j++){///excel中的列是从1开始的xlApp.Cells[i + 6, j + 1] = tmpDataTable.Rows[i][j].ToString();xlApp.Cells[i + 6, j + 1].Borders.LineStyle = 1;}}string hang = "A"+ (rowNum + 6).ToString();string lie = "I" + (rowNum + 6).ToString();RangeBuild(oSheet, hang,lie, "注：1、检验合格在检验结论栏打(合格）.不合格打（不合格)、 2、未标注上下偏差的为大于等于。");///保存,路径一块穿进去。否则回到一个很奇妙的地方，貌似是system32里 temp下....oSheet.SaveAs(@"C:\Users\users\Desktop\222\" + strFileName);MessageBox.Show("导出完毕！", "提示！", MessageBoxButtons.OK, MessageBoxIcon.Information);}private static void RangeBuild(_Worksheet oSheet, string startcell, string endcell, string value){///创建一个区域对象。第一个参数是开始格子号，第二个参数是终止格子号。比如选中A1——D3这个区域。Range range = (Range)oSheet.get_Range(startcell, endcell);///合并方法，0的时候直接合并为一个单元格range.Merge(0);///合并单元格之后，设置其中的文本range.Value = value;//横向居中range.HorizontalAlignment = XlVAlign.xlVAlignCenter;///字体大小range.Font.Size = 10;///字体range.Font.Name = "宋体";///行高range.RowHeight = 24;//自动调整列宽range.EntireColumn.AutoFit();//填充颜色//range.Interior.ColorIndex = 20;//设置单元格边框的粗细range.Cells.Borders.LineStyle = 1;}private static void RangeBuild2(_Worksheet oSheet, string startcell, string endcell, string value){///创建一个区域对象。第一个参数是开始格子号，第二个参数是终止格子号。比如选中A1——D3这个区域。Range range = (Range)oSheet.get_Range(startcell, endcell);///合并方法，0的时候直接合并为一个单元格range.Merge(0);///合并单元格之后，设置其中的文本range.Value = value;//横向居中range.HorizontalAlignment = XlVAlign.xlVAlignCenter;///字体大小range.Font.Size = 18;///字体range.Font.Name = "黑体";///行高range.RowHeight = 24;//自动调整列宽range.EntireColumn.AutoFit();//填充颜色//range.Interior.ColorIndex = 20;//设置单元格边框的粗细range.Cells.Borders.LineStyle = 1;}
···
