导出多个GridView的到多个Excel选项卡（表） [英] Export multiple gridviews to multiple excel tabs (sheets)
本文介绍了导出多个GridView的到多个Excel选项卡（表）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在我的网站6 GridView的，我需要导出到Excel，但每一个在一个不同的表。
这个链接出口GridView控件到多个Excel工作表使用非常类似的东西，但他使用的数据集，我不是。我是新的C＃，所以我不能改变它适合我的解决方案。
我的code出口的所有GridView的到同一张纸上。我创建了一个循环throught我的GridView的运行，现在我需要把code，生成每一个每一个Excel工作表。
保护无效btExcel_Click（对象发件人，EventArgs的发送）
{
Response.Clear（）;
将Response.Buffer =真;
Response.AddHeader（内容处置，附件;文件名= FARPOP_Mensal_+ txDt.Text +的.xls）;
Response.ContentEncoding = System.Text.Encoding.GetEncoding（视窗1252）;
Response.Charset的=ISO-8859-1;
Response.ContentType =应用程序/ vnd.ms-EXCEL; 使用（StringWriter的SW =新的StringWriter（））
{
HtmlTextWriter的HW =新的HtmlTextWriter（SW）;
GridView控件[] = gvExcel新的GridView [] {gvAnual，gvPorUF，gvPorFarmacia，gvPorMunicipio，gvPorPV，gvPorCNPJ};
的for（int i = 0; I＆LT; gvExcel.Length;我++）
{
GridView控件GV = gvExcel [I] //
//需要重定向到这里的每个表？
// gvExcel [I] .HeaderRow.BackColor = Color.White;
gvExcel [I] .UseAccessibleHeader = FALSE;
gvExcel [I] .AllowPaging = FALSE;
对于（INT X = 0; X＆LT; gvExcel [I] .Columns.Count; X ++）
{
gvExcel [I] .Columns [X]。可见=真实的;
} gvExcel [I] .DataBind（）;
的foreach（在gvExcel TableCell的细胞[I] .HeaderRow.Cells）
{
cell.BackColor = Color.CadetBlue;
cell.Enabled = FALSE;
} 的foreach（在gvExcel [I] .Rows GridViewRow行）
{
row.BackColor = Color.White;
的foreach（在row.Cells TableCell的细胞）
{ cell.Controls.Clear（）;
如果（row.RowIndex％2 == 0）
{
cell.BackColor = Color.White;
}
其他
{
cell.BackColor = Color.LightBlue;
}
cell.CssClass =文本模式;
}
} gvExcel [I] .RenderControl（HW）;
} //风格格式的数字串
串风格= @＆LT;风格＆GT; .textmode {}＆LT; /风格与GT;
的Response.Write（样式）;
Response.Output.Write（sw.ToString（））;
Response.Flush（）;
到Response.End（）;
}
}
解决方案
我跟着 maniak1982 的意见，并找到了一个很好的解决方案
所以，如果你需要多个GridView的到倍数表，使用.NET，你将需要下载的 ClosedXML 也 DocumentFormat.OpenXml.dll 。
引用的DLL后，请使用以下code：
保护无效btExcel_Click（对象发件人，EventArgs的发送）
{
XLWorkbook WB =新XLWorkbook（）;
GridView控件[] = gvExcel新的GridView [] {GridView1，GridView2，GridView3，GridView4，GridView5，GridView6};
字符串[] NAME =新的String [] {名1，名称2，NAME3，NAME4，NAME5，Name6};
的for（int i = 0; I＆LT; gvExcel.Length;我++）
{
如果（gvExcel [I]。可见）
{
gvExcel [I] .AllowPaging = FALSE;
gvExcel [I] .DataBind（）;
DataTable的DT =新的DataTable（名称[I]的ToString（））;
对于（INT Z = 0; z，其中，gvExcel [I] .Columns.Count; Z ++）
{
dt.Columns.Add（gvExcel [I] .Columns [Z] .HeaderText）;
} 的foreach（在gvExcel [I] .Rows GridViewRow行）
{
dt.Rows.Add（）;
对于（INT C = 0;℃下row.Cells.Count; C ++）
{
dt.Rows [dt.Rows.Count - 1] [C] = row.Cells [C]。文本;
}
} wb.Worksheets.Add（DT）;
gvExcel [I] .AllowPaging = TRUE; } }
Response.Clear（）;
将Response.Buffer =真;
Response.Charset的=;
Response.ContentType =应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet
Response.AddHeader（内容处置，附件;文件名= Workbook_Name.xlsx）;
使用（MemoryStream的MyMemoryStream =新的MemoryStream（））
{
wb.SaveAs（MyMemoryStream）;
MyMemoryStream.WriteTo（Response.OutputStream）;
Response.Flush（）;
到Response.End（）;
}
}
我花了搜索的日子......但它的工作完美。
要快乐
I have 6 gridviews in my website that I need to export to excel, but each one in one different sheet.
This link Export GridView to multiple Excel sheet uses something quite similar, but he is using datasets and I am not. I am new to C#, so I couldnt change it to fit my solution.
My code exports all gridviews to the same sheet. I created a loop to run throught my gridviews, now I need to put a code to generate each one to each excel sheet.
protected void btExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=FARPOP_Mensal_" + txDt.Text + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("Windows-1252");
Response.Charset = "ISO-8859-1";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView[] gvExcel = new GridView[] { gvAnual, gvPorUF, gvPorFarmacia, gvPorMunicipio, gvPorPV, gvPorCNPJ };
for (int i = 0; i < gvExcel.Length; i++)
{
GridView gv = gvExcel[i];
//
// Need to redirect to each sheet here?
//
gvExcel[i].HeaderRow.BackColor = Color.White;
gvExcel[i].UseAccessibleHeader = false;
gvExcel[i].AllowPaging = false;
for (int x = 0; x < gvExcel[i].Columns.Count; x++)
{
gvExcel[i].Columns[x].Visible = true;
}
gvExcel[i].DataBind();
foreach (TableCell cell in gvExcel[i].HeaderRow.Cells)
{
cell.BackColor = Color.CadetBlue;
cell.Enabled = false;
}
foreach (GridViewRow row in gvExcel[i].Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
cell.Controls.Clear();
if (row.RowIndex % 2 == 0)
{
cell.BackColor = Color.White;
}
else
{
cell.BackColor = Color.LightBlue;
}
cell.CssClass = "textmode";
}
}
gvExcel[i].RenderControl(hw);
}
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
解决方案
I followed maniak1982 comments and found a good solution.
So, if you need multiple gridviews to multiples worksheets, using .NET, you will need to download ClosedXML and also DocumentFormat.OpenXml.dll.
After referencing the dll, use the following code:
protected void btExcel_Click(object sender, EventArgs e)
{
XLWorkbook wb = new XLWorkbook();
GridView[] gvExcel = new GridView[] { GridView1, GridView2, GridView3, GridView4, GridView5, GridView6};
string[] name = new string[] { "Name1", "Name2", "Name3", "Name4", "Name5", "Name6" };
for (int i = 0; i < gvExcel.Length; i++)
{
if (gvExcel[i].Visible)
{
gvExcel[i].AllowPaging = false;
gvExcel[i].DataBind();
DataTable dt = new DataTable(name[i].ToString());
for (int z = 0; z < gvExcel[i].Columns.Count; z++)
{
dt.Columns.Add(gvExcel[i].Columns[z].HeaderText);
}
foreach (GridViewRow row in gvExcel[i].Rows)
{
dt.Rows.Add();
for (int c = 0; c < row.Cells.Count; c++)
{
dt.Rows[dt.Rows.Count - 1][c] = row.Cells[c].Text;
}
}
wb.Worksheets.Add(dt);
gvExcel[i].AllowPaging = true;
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=Workbook_Name.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
It took me days of searching... But it's working perfectly. Be happy
这篇关于导出多个GridView的到多个Excel选项卡（表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
