#region 将数据表保存到Excel表格中 /// <summary> /// 将数据表保存到Excel表格中 /// </summary> /// <param name="addr">Excel表格存放地址</param> /// <param name="dt">要输出的DataTable</param> public string SaveToExcel(string addr, System.Data.DataTable dt) { //0.注意： // * Excel中形如Cells[x][y]的写法，前面的数字是列，后面的数字是行! // * Excel中的行、列都是从1开始的，而不是0 //1.制作一个新的Excel文档实例 Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application(); xlsApp.DisplayAlerts = false; xlsApp.Workbooks.Add(true); //2.设置Excel分页卡标题 xlsApp.ActiveSheet.Name = "材料见证记录"; //3.合并第一行的单元格 string temp = "U"; //if (dt.Columns.Count < 26) //{ // temp = ((char)('A' + dt.Columns.Count)).ToString(); //} //else if (dt.Columns.Count <= 26 + 26 * 26) //{ // temp = ((char)('A' + (dt.Columns.Count - 26) / 26)).ToString() // + ((char)('A' + (dt.Columns.Count - 26) % 26)).ToString(); //} //else throw new Exception("列数过多"); for (int i = 1; i < 21; i++) { for (int j = 1; j <= i; j++) { xlsApp.Rows[i][j].ColumnWidth = 2.3; } } Microsoft.Office.Interop.Excel.Range range = xlsApp.get_Range("A1", "M1"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //4.填写第一行：表名，对应DataTable的TableName xlsApp.Cells[1][1] = "材料见证记录"; xlsApp.Cells[1][1].Font.Name = "黑体"; xlsApp.Cells[1][1].Font.Size = 15; xlsApp.Cells[1][1].Font.Bold = true; //xlsApp.Cells[1][1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 xlsApp.Cells[1][1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 //5.合并第二行单元格，用于书写表格生成日期 range = xlsApp.get_Range("A2", "M2"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][2] = "表 B-14"; xlsApp.Cells[1][2].Font.Name = "黑体"; xlsApp.Cells[1][2].Font.Size = 15; xlsApp.Cells[1][2].Font.Bold = true; xlsApp.Cells[1][2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("N1", "P2"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][1] = "资料编号"; //xlsApp.Cells[14][2].Font.Name = "宋体"; //xlsApp.Cells[14][2].Font.Size = 15; //xlsApp.Cells[14][2].HorizontalAlignment = 4;//右对齐 xlsApp.Cells[14][1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("Q1", "U2"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //xlsApp.Rows[2].RowHeight = 30; //第一行行高为60（单位：磅） //7.填写各列的标题行。从Datatable中拉出来的列标题为数据库中字段，我们把他改成自己的 //xlsApp.Cells[1][3] = "资料编号"; //xlsApp.Cells[2][3] = "2233114455"; range = xlsApp.get_Range("A3", "C3"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][3] = "工程名称"; xlsApp.Cells[4][3] = dt.Rows[0]["ProjectName"].ToString(); xlsApp.Cells[1][3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("D3", "u3"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //------------------------------- range = xlsApp.get_Range("A4", "C4"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //xlsApp.Cells[4][3].ColumnWidth = 40; xlsApp.Cells[1][4] = "试件名称"; range = xlsApp.get_Range("D4", "M4"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N4", "P4");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q4", "U4"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[4][4] = dt.Rows[0]["specimenName"].ToString();//试件名称 xlsApp.Cells[14][4] = "生产厂家"; xlsApp.Cells[17][4] = dt.Rows[0]["Manufacturer"].ToString(); //************************************ range = xlsApp.get_Range("A5", "C5"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][5] = "试件品种"; range = xlsApp.get_Range("D5", "M5"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N5", "P5");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q5", "U5"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][5] = "材料出厂编号"; xlsApp.Cells[4][5] = dt.Rows[0]["Specimen"].ToString(); xlsApp.Cells[17][5] = dt.Rows[0]["Materialnumber"].ToString();//材料出厂编号 //************************************ range = xlsApp.get_Range("A6", "C6"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][6] = "试件规格型号"; range = xlsApp.get_Range("D6", "M6"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N6", "P6");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q6", "U6"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][6] = "材料进场时间"; xlsApp.Cells[4][6] = dt.Rows[0]["SpecimenSpecification"].ToString();//试件规格型号 //************************************ range = xlsApp.get_Range("A7", "C7"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][7] = "材料进场数量"; range = xlsApp.get_Range("D7", "M7"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N7", "P7");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q7", "U7"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][7] = "代表数量"; xlsApp.Cells[17][7] = dt.Rows[0]["TheNumber"].ToString(); //************************************ range = xlsApp.get_Range("A8", "C8"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][8] = "试样编号"; range = xlsApp.get_Range("D8", "M8"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[4][8] = dt.Rows[0]["SampleNumber"].ToString();//试样编号 range = xlsApp.get_Range("N8", "P8");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q8", "U8"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][8] = "取样组数"; xlsApp.Cells[15][8] = "取样组数...."; //************************************ range = xlsApp.get_Range("A9", "C9"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][9] = "抽样时间"; range = xlsApp.get_Range("D9", "M9"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N9", "P9");//生厂厂家 range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("Q9", "U9"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[14][9] = "取样地点"; xlsApp.Cells[15][9] = "取样地点...."; //************************************ range = xlsApp.get_Range("A9", "C9"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][9] = "使用部位（取样部位）"; xlsApp.Cells[1][9].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("D9", "u9"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[4][9] = dt.Rows[0]["SamplingLocation"].ToString(); //************************************ range = xlsApp.get_Range("A10", "C10"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][10] = "检测项目（设计要求）"; xlsApp.Cells[1][10].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("D10", "u10"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[4][10] = dt.Rows[0]["TestItems"].ToString(); //************************************ range = xlsApp.get_Range("A11", "C13"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D11", "U13"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][11] = "见证记录"; //************************************ range = xlsApp.get_Range("A14", "C16"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][14] = "检测结果判定 依据"; range = xlsApp.get_Range("G14", "U14"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("G15", "U15"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("G16", "U16"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D14", "F14"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D15", "F15"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D16", "F16"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[4][14] = "产品标准"; xlsApp.Cells[4][15] = "验收规范"; xlsApp.Cells[4][16] = "设计要求"; xlsApp.Cells[7][14] = dt.Rows[0]["productStandard"].ToString();//产品标准 xlsApp.Cells[7][15] = dt.Rows[0]["Acceptance"].ToString();//验收规范 xlsApp.Cells[4][14].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 xlsApp.Cells[4][15].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 xlsApp.Cells[4][16].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 //************************************ range = xlsApp.get_Range("A17", "C18"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][17] = "抽样人"; xlsApp.Cells[4][17] = "签字"; xlsApp.Cells[4][18] = "日期"; range = xlsApp.get_Range("E17", "J17"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("E18", "J18"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("K17", "L18"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[11][17] = "见证人"; xlsApp.Cells[13][17] = "签字"; xlsApp.Cells[13][18] = "日期"; range = xlsApp.get_Range("N17", "U17"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("N18", "U18"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //********************************* range = xlsApp.get_Range("A19", "C19"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D19", "U19"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][19] = "有见证送检章"; //********************************* range = xlsApp.get_Range("A20", "C21"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D20", "F20"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("D21", "F21"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("G20", "U20"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 range = xlsApp.get_Range("G21", "U21"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 xlsApp.Cells[1][20] = "送检情况"; xlsApp.Cells[4][20] = "检测单位"; xlsApp.Cells[4][21] = "送检时间"; xlsApp.Cells[7][20] = "中国建材检验认证集团北京天誉有限公司"; xlsApp.Cells[7][21] = "年 月 日"; //9.描绘边框 range = xlsApp.get_Range("A1", "U21"); range.Borders.LineStyle = 1; range.Borders.Weight = 3; //10.打开制作完毕的表格 //xlsApp.Visible = true; //11.保存表格到根目录下指定名称的文件中 string path = AppDomain.CurrentDomain.BaseDirectory + ("Upload\Excel\" + addr + ".xls"); xlsApp.ActiveWorkbook.SaveAs(path); xlsApp.Quit(); xlsApp = null; GC.Collect(); return path; } #endregion