动态生成多个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 中动态加载:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
}
}
{
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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);
}
}
{
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);
}
}