注意：此文档中标注的行号和列号都是下标，从0开始

      //下载模板

　　protected void btnDownLoad_ServerClick（object sender,EventArgs e）

　　{

  　　 　　//生成Excel模板

       　　CreateExcelTemp();

       　　//设置想要打开的模板路径

      　　 string path = this.MapPath("~/BaseInfo/Temp/GABProdectInfoMaintain.xls");

      　　 //将此模板打开或重新保存

       　　ExcelOutE(this, path);    

　　}

　　/// 生成Excel模板并打开
　　/// </summary>
　　private void CreateExcelTemp()

　　{
            //获取数据库中的产品组信息列表  
            List<ProductGroupModel> ProductGroupInfo = ProductGroupBusiness.GetUserAllProductGroup(CurrentUserCode);

            List<string> ProductGroupList = new List<string>();

            //将产品信息列表中是产品组名称和编号组合成一列，加入ProductGroupList集合

            foreach (ProductGroupModel item in ProductGroupInfo) 

            {
                   ProductGroupList.Add(item.Name + "(" + item.ProductGroupCode + ")"); 

            }
            //创建Excel模板(在模板上修改)
            //创建模板
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();    

            ISheet realSheet = hssfworkbook.CreateSheet("realSheet");
            ISheet hidden = hssfworkbook.CreateSheet("hidden");

　　　　设置hssfworkbook中下标为一的sheet隐藏           

 　　　　hssfworkbook.SetSheetHidden(1, true);

　　　　//循环ProductGroupList集合，将所有数据加入hidden中的第0列

           //注意：在别的sheet中第几列需要引用hidden中的数据，就应该将数据源中的数据插入到hidden中的第几列，列编号必须相同

            for (int i = 0; i < ProductGroupList.Count; i++) 

            {
                HSSFRow rowtemp = (HSSFRow)hidden.CreateRow(i);
                ICell celltemp = rowtemp.CreateCell(0);//将数据库查到到hidden中的第0列
                celltemp.SetCellValue(ProductGroupList[i].ToString());
            }

            //设置hidden中的一些属性
            IName namedCell = hssfworkbook.CreateName();
            namedCell.NameName="hidden";
            namedCell.RefersToFormula = "hidden!A1:A" + ProductGroupList.ToArray().Length;

            //将hidden中的数据加入到constraint里面

            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("hidden");

            CellRangeAddressList addressList = null;
            HSSFDataValidation validation = null;

            //给realSheet创建行和列 

            IRow row0 = realSheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("GAB其他产品信息维护");
            IRow row1 = realSheet.CreateRow(1);
            row1.CreateCell(0).SetCellValue("产品组");
            row1.CreateCell(1).SetCellValue("产品名称");
            row1.CreateCell(2).SetCellValue("产品价格");

            //设置realSheet中列的宽度

            realSheet.SetColumnWidth(0, 8000);
            realSheet.SetColumnWidth(1, 5000);
            realSheet.SetColumnWidth(2, 5000);

            //填充信息
            DataTable dt = A_GABCustomerRelationBusiness.GetGABProductInfo();
            if (dt != null || dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    //在realSheet的第2行第0列开始引用hidden中的数据
                    addressList = new CellRangeAddressList(i+2, i+2, 0, 0);

                   validation = new HSSFDataValidation(addressList, constraint);
                    realSheet.AddValidationData(validation);

                    HSSFRow rowtemp = (HSSFRow)realSheet.CreateRow(i + 2);
                    ICell celltemp = rowtemp.CreateCell(0);
                    celltemp.SetCellValue(dt.Rows[i]["ProductGroupName"].ToString());

                    ICell celltemp1 = rowtemp.CreateCell(1);
                    celltemp1.SetCellValue(dt.Rows[i]["ProductName"].ToString());

                    ICell celltemp2 = rowtemp.CreateCell(2);
                    celltemp2.SetCellValue(dt.Rows[i]["ProductPrice"].ToString());
                }
            }
            #endregion

            //保存修改的模板
            string savePath = this.MapPath("~/BaseInfo/Temp/GABProdectInfoMaintain.xls");
            using (FileStream file = new FileStream(savePath, FileMode.Create))
            {

                //写入文件到savePath路径下
                hssfworkbook.Write(file);

　　　　}         

　　}

      //将fileName文件打开或重新保存

 　　public static void ExcelOutE(Page Page, string fileName)
　　{
            HttpResponse Response = Page.Response;
            HttpServerUtility Server = Page.Server;

            string fn = Server.UrlDecode(fileName);
            FileStream fileStream = new FileStream(fn, FileMode.Open);
            long fileSize = fileStream.Length;
            fileStream.Close();
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("Content-Length", fileSize.ToString());
            Page.EnableViewState = false;
            Response.WriteFile(fn);
            Response.Flush();
            Response.End();
　　}