xls后台模版生成文件

Microsoft.Office.Interop.Excel.Application ThisApplication = new Application(); 

         Microsoft.Office.Interop.Excel._Workbook ThisWorkbook = null;

         Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;

         object missing = System.Reflection.Missing.Value;
         string strOpenFileName = Server.MapPath("~/Print/MARKET/工业品买卖合同.xls");            //定义模板Excel路径

         //打开模板Excel
         ThisWorkbook = ThisApplication.Workbooks.Open(strOpenFileName, missing,
             missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

         xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)ThisWorkbook.Sheets[1];         //获取绘制的第一个Sheet

         int gENRowsNum = grvEngineeringName.Rows.Count;                                     //定义页面工程名称的行数
         int gEDRowsNum = grvEquipmentDetail.Rows.Count;                                     //定义页面设备明细的行数

         Range range = xlSheet.get_Range(xlSheet.Cells[6, "d"], xlSheet.Cells[6, "g"]);      //获取工程合同编号值的位置
         range.set_Value(missing, lblContractCd.Text.ToString());                            //给工程编号赋值

         range = xlSheet.get_Range(xlSheet.Cells[6, "k"], xlSheet.Cells[6, "n"]);            //获取需方公司名称的位置
         range.set_Value(missing, lblDeCo.Text.ToString());                                  //给需方公司名称赋值

         range = xlSheet.get_Range(xlSheet.Cells[7, "d"], xlSheet.Cells[7, "g"]);            //获取合同额的位置
         range.set_Value(missing, lblAmount.Text.ToString());                                //给合同额赋值

         range = xlSheet.get_Range(xlSheet.Cells[7, "k"], xlSheet.Cells[7, "n"]);            //获取质保金的位置
         range.set_Value(missing, lblGuaranty.Text.ToString());                              //给质保金赋值

         range = xlSheet.get_Range(xlSheet.Cells[8, "d"], xlSheet.Cells[8, "g"]);            //获取合同签订日的位置
         range.set_Value(missing, lblEndorsementDate.Text.ToString());                       //给合同签订日赋值

         range = xlSheet.get_Range(xlSheet.Cells[8, "k"], xlSheet.Cells[8, "n"]);            //获取合同签订地的位置
         range.set_Value(missing, lblEndorsementAddress.Text.ToString());                    //给合同签订地赋值

         range = xlSheet.get_Range(xlSheet.Cells[9, "d"], xlSheet.Cells[9, "g"]);            //获取交货期的位置
         range.set_Value(missing, lblConsignmentTime.Text.ToString());                       //给交货期赋值

         range = xlSheet.get_Range(xlSheet.Cells[9, "k"], xlSheet.Cells[9, "n"]);            //获取合同签订人的位置
  range.set_Value(missing, lblUserCd.Text.ToString());                                //给合同签订人赋值

         //从工程项目名称的最后一行开始插入一行
         for (int i = gENRowsNum - 1; i >= 0; i--)
         {
             xlSheet.get_Range(xlSheet.Cells[13, "a"], xlSheet.Cells[13, "f"]).EntireRow.Insert(missing, missing); //插入新的一行
             range = xlSheet.get_Range(xlSheet.Cells[13, "a"], xlSheet.Cells[13, "f"]);                            //获取工程项目名称的位置
             range.MergeCells = true;                                                                              //合并单元格
             //给合并单元格赋值
             range.set_Value(missing, (grvEngineeringName.Rows[i].Cells[0].FindControl("labEngineeringName")as System.Web.UI.WebControls.Label).Text.ToString());
             range.Font.Bold = false;                                                                              //单元格内字体不加粗
         }

         range = xlSheet.get_Range(xlSheet.Cells[12, "g"], xlSheet.Cells[12 + gENRowsNum, "n"]);
         range.MergeCells = true;

         for (int i = gEDRowsNum - 1; i >= 0; i--)
         {
             xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "a"], xlSheet.Cells[16 + gENRowsNum, "n"]).EntireRow.Insert(missing, missing);

             range = xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "a"], xlSheet.Cells[16 + gENRowsNum, "c"]);
             range.MergeCells = true;
             range.set_Value(missing, (grvEquipmentDetail.Rows[i].Cells[0].FindControl("labEquipmentName") as System.Web.UI.WebControls.Label).Text.ToString());
             range.Font.Bold = false;
             range.Borders.LineStyle = 1;

             range = xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "d"], xlSheet.Cells[16 + gENRowsNum, "f"]);
             range.MergeCells = true;
             range.set_Value(missing, (grvEquipmentDetail.Rows[i].Cells[0].FindControl("labSpecification") as System.Web.UI.WebControls.Label).Text.ToString());
             range.Font.Bold = false;
             range.Borders.LineStyle = 1;

             range = xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "g"], xlSheet.Cells[16 + gENRowsNum, "g"]);
             xlSheet.Cells[16 + gENRowsNum, "g"] = (grvEquipmentDetail.Rows[i].Cells[0].FindControl("labCount") as System.Web.UI.WebControls.Label).Text.ToString();
             range.Font.Bold = false;
             range.Borders.LineStyle = 1;

             range=xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "h"], xlSheet.Cells[16 + gENRowsNum, "h"]);
             xlSheet.Cells[16 + gENRowsNum, "h"] = (grvEquipmentDetail.Rows[i].Cells[0].FindControl("labUnit") as System.Web.UI.WebControls.Label).Text.ToString();
             range.Font.Bold = false;
             range.Borders.LineStyle = 1;

             range = xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "i"], xlSheet.Cells[16 + gENRowsNum, "j"]);
             range.MergeCells = true;
             range.set_Value(missing, (grvEquipmentDetail.Rows[i].Cells[0].FindControl("labPrice") as System.Web.UI.WebControls.Label).Text.ToString());
             range.Font.Bold = false;

         }

         range = xlSheet.get_Range(xlSheet.Cells[16 + gENRowsNum, "k"], xlSheet.Cells[16 + gENRowsNum + gEDRowsNum - 1, "n"]);
         range.MergeCells = true;
         range.set_Value(missing, (grvEquipmentDetail.Rows[0].Cells[0].FindControl("labRemark") as System.Web.UI.WebControls.Label).Text.ToString());
         range.Font.Bold = false;
         range.Borders.LineStyle = 1; //绘制边框
         range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

         range = xlSheet.get_Range(xlSheet.Cells[17 + gEDRowsNum + gENRowsNum, "c"], xlSheet.Cells[17 + gEDRowsNum + gENRowsNum, "e"]);
         range.set_Value(missing, lblAmount1.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[19 + gEDRowsNum + gENRowsNum, "c"], xlSheet.Cells[19 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem1.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[21 + gEDRowsNum + gENRowsNum, "f"], xlSheet.Cells[21 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem2.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[23 + gEDRowsNum + gENRowsNum, "d"], xlSheet.Cells[23 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem3.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[25 + gEDRowsNum + gENRowsNum, "g"], xlSheet.Cells[25 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem4.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[27 + gEDRowsNum + gENRowsNum, "d"], xlSheet.Cells[27 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem5.Text.ToString());

         range = xlSheet.get_Range(xlSheet.Cells[29 + gEDRowsNum + gENRowsNum, "e"], xlSheet.Cells[29 + gEDRowsNum + gENRowsNum, "n"]);
         range.set_Value(missing, lblItem6.Text.ToString());

             //更新数据后另存为新文件
         //xlSheet.SaveAs(@"MARKET103_01.xls", missing,
         //    missing, missing, missing, missing, missing, missing, missing, missing);

         //      不显示确认对话框   
         //ThisApplication.Application.DisplayAlerts = false; 
   
         ThisApplication.Quit();
         ThisWorkbook = null;
         ThisApplication = null;