后台数据转化成execl

        #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