动态生成多个DataGrid 并导出Excel

项目中有需要动态的生成多个DataGrid,并且可以一起导出到Excel.实现如下:

前台

1.放置div: 用于装载DataGrid

    <div id="divGrid" style="BORDER-TOP-STYLE: none; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; BORDER-BOTTOM-STYLE: none"       runat="server"></div>

2.隐藏TextBox ,用于导出Excel,里面放DataGrid的outHtml

<asp:TextBox ID="txtHid" Runat="server" Width="0px"></asp:TextBox>

3.JS 方法

 

  function GetHtml(dgdId)    //把DataGrid 的outerHTML 写入textbox ,用于后台Excport excel
  {   
   var o = document.getElementById(dgdId);
   document.getElementById("txtHid").value = document.getElementById("txtHid").value + o.outerHTML + "<br />" //o.outerHTML 
  }

后台 div 中动态加载:

 

        private void ShowDataGrid(Hashtable htSite,DataTable dt)
        {
            if(dt.Rows.Count > 0 )
            {
                ViewState["lane"] = dt.Rows[0]["lane"].ToString();
                DataGrid dgd = new DataGrid();
                dgd.AutoGenerateColumns=false;
                string dgdId = "dgd" + ViewState["lane"].ToString();
                dgd.ID = dgdId;            
                dgd.BackColor = ColorTranslator.FromWin32(Convert.ToInt32("F6FBFE",16));
                dgd.AlternatingItemStyle.BackColor = ColorTranslator.FromWin32(Convert.ToInt32("EAEAEA",16));
                dgd.HeaderStyle.BackColor = ColorTranslator.FromWin32(Convert.ToInt32("E0E9F8",16));


                //周别
                BoundColumn colWeek = new BoundColumn(); 
                colWeek.DataField = "ship_week"; 
                colWeek.HeaderText = "Week";
                colWeek.ItemStyle.Width= Unit.Pixel(50);
                dgd.Columns.Add(colWeek); 
                //Lsp
                BoundColumn colLsp = new BoundColumn(); 
                colLsp.DataField = "lsp"; 
                colLsp.HeaderText = "LSP"; 
                colLsp.ItemStyle.Width= Unit.Pixel(60);
                dgd.Columns.Add(colLsp); 
                //以下循环Site 
                foreach (DictionaryEntry de in htSite) 
                {
                    //各company 需要动态增加
                    string[] companyList = de.Value.ToString().Split(';'); 
                    for(int i = 0; i < companyList.Length; i ++)
                    {
                        BoundColumn col = new BoundColumn(); 
                        col.DataField = companyList[i]; 
                        col.HeaderText = companyList[i]; 
                        col.ItemStyle.Width= Unit.Pixel(60);
                        col.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                        col.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
                        dgd.Columns.Add(col); 
                    }
                    string site = de.Key.ToString().Replace("/",""); 
                    //Demand
                    BoundColumn colDemand = new BoundColumn(); 
                    colDemand.DataField = site + "_demand"; 
                    colDemand.HeaderText = "Demand"; 
                    colDemand.ItemStyle.Width= Unit.Pixel(100);
                    colDemand.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                    colDemand.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
                    dgd.Columns.Add(colDemand); 
                    //Supply
                    BoundColumn colSupply = new BoundColumn(); 
                    colSupply.DataField = site + "_supply"; 
                    colSupply.HeaderText = "Supply"; 
                    colSupply.ItemStyle.Width= Unit.Pixel(100);
                    colSupply.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                    colSupply.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
                    dgd.Columns.Add(colSupply); 
                    //Demand/Supply
                    BoundColumn colDemandSupply = new BoundColumn(); 
                    colDemandSupply.DataField = site + "_ds";  
                    colDemandSupply.HeaderText = "Demand/Supply%"; 
                    colDemandSupply.ItemStyle.Width= Unit.Pixel(100);
                    colDemandSupply.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;                
                    dgd.Columns.Add(colDemandSupply); 
                    //配仓比(Target):
                    BoundColumn colTarget = new BoundColumn(); 
                    colTarget.DataField = site + "_priority"; 
                    colTarget.HeaderText = "Target"; 
                    colTarget.ItemStyle.Width= Unit.Pixel(100);
                    colTarget.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                    dgd.Columns.Add(colTarget); 
                    //配仓比(Actual):
                    BoundColumn colActual = new BoundColumn(); 
                    colActual.DataField = site + "_dsact"; 
                    colActual.HeaderText = "Actual"; 
                    colActual.ItemStyle.Width= Unit.Pixel(100);                
                    colActual.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                    dgd.Columns.Add(colActual); 

                }
                dgd.ItemCreated +=new DataGridItemEventHandler(dgd_ItemCreated);  //DataGrid 表头重写
                dgd.DataSource = dt; 
                dgd.DataBind();
                this.MergeGridCell(dgd,0);  //week 列合并Cell
                this.FormatCellColor(dgd);  //Demand/Supply% 列 > 80%, 红色显示文字 
                divGrid.Controls.Add(dgd);    //把DataGrid 加载到Div中        
                RegisterStartupScript(dgdId, "<script>GetHtml('"+dgdId+"');</script>"); //把DataGrid 用前台JS写入到隐藏控件中,用于Export to Excel
            }
                        
        }

 

 

用流导出Excle

 

        private void btnExport_Click(object sender, System.EventArgs e)
        {
            try
            {
                string strHtml = this.txtHid.Text;
                if(strHtml == string.Empty)
                {
                    UIUtility.AlertMessage("No data to export!",this);
                }
                else
                {
                    string name = "Supply" +DateTime.Now.Ticks.ToString() + ".xls";
                    Response.Clear();
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.Buffer = false;
                    Response.ContentType = "application/ms-excel"; 
                    Response.AddHeader("Content-Disposition", "attachment;FileName=" + name); //fileName);
                    Response.BinaryWrite(Encoding.UTF8.GetBytes(strHtml));
                    Response.Flush();
                    Response.Close();
                    Response.End();
                }
            }
            catch(Exception ex)
            {
                ComponentFactory.GetLogger(System.Reflection.MethodBase.GetCurrentMethod(),"").Error(ex.ToString());
                UIUtility.AlertMessage("Export to excel failed!"+ ex.Message,this);
            }
        }