Hello to everyone. I have a problem with exporting two gridviews (main and nested gridview) to Excel fromat using ClosedXML (XLWorkbook). I read tutorial on aspsnippets and it works fine for main gridview. I posted comment there but have some problem or something because comment is not appeared.
My code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDataBound="something">
<Columns>
<asp:TemplateField >
<ItemTemplate>
<asp:Panel ID="Panel1" runat="server" Style="display: none" meta:resourcekey="panelPodrobnostiZaposlenihResource1">
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" ShowHeader="False">
<Columns>
asp-BoundFields...
</Columns>
</asp:GridView>
</asp:Panel>
</ItemTemplate>
</asp:TemplateField>
asp-BoundFields...
</Columns>
</asp:GridView>
Code in CS file is from tutorial.
protected void ExportExcel(object sender, EventArgs e)
{
DataTable dt = new DataTable("GridView_Data");
foreach(TableCell cell in GridView1.HeaderRow.Cells)
{
dt.Columns.Add(cell.Text);
}
foreach (GridViewRow row in GridView1.Rows)
{
dt.Rows.Add();
for (int i=0; i<row.Cells.Count; i++)
{
dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
}
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
So I want to export both gridviews to Excel. Every row in Gridview1 (Main Gridview) has only one row (details row) from (based on OnRowDataBound) Gridview2(nested Gridview).
So Excel File should look similar to that:
Header1 Header2 Header3 (all from gridview1)
Row1Field1 Row1Field2 Row1Field3 (all from gridview1)
DetailsRow1Field1 DetailsRow1Field2 DetailsRow1Field3 (all from gridview2 - nested gridview)
Row2Field1 Row2Field2 Row2Field3 (all from gridview1)
DetailsRow2Field1 DetailsRow2Field2 DetailsRow2Field3 (all from gridview2 - nested gridview)
..etc
Please help me out.