匯出帶下拉框的Excel（資料超出長度解決方案）

標籤：style   code   http   tar   ext   color   

 　　注意：此文檔中標註的行號和列號都是下標，從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();
　　}