Export Nested GridView (GridView inside GridView) to Excel in ASP.Net

michael123
 
on Jan 04, 2014 05:08 AM
16914 Views

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. 

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Azim
 
on Jan 04, 2014 08:51 AM
on Jan 04, 2014 09:10 AM

I have solved it using these two links

Gridview inside Gridview in ASP.Net using C# and VB.Net

 
html:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        .Grid td
        {
            background-color: #A1DCF2;
            color: black;
            font-size: 10pt;
            line-height: 200%;
        }
        .Grid th
        {
            background-color: #3AC0F2;
            color: White;
            font-size: 10pt;
            line-height: 200%;
        }
        .ChildGrid td
        {
            background-color: #eee !important;
            color: black;
            font-size: 10pt;
            line-height: 200%;
        }
        .ChildGrid th
        {
            background-color: #6C6C6C !important;
            color: White;
            font-size: 10pt;
            line-height: 200%;
        }
    </style>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $("[src*=plus]").live("click", function () {
            $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
            $(this).attr("src", "images/minus.png");
        });
        $("[src*=minus]").live("click", function () {
            $(this).attr("src", "images/plus.png");
            $(this).closest("tr").next().remove();
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="Grid"
        DataKeyNames="CustomerID" OnRowDataBound="OnRowDataBound">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <img alt="" style="cursor: pointer" src="images/plus.png" />
                    <asp:Panel ID="pnlOrders" runat="server" Style="display: none">
                        <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" CssClass="ChildGrid">
                            <Columns>
                                <asp:BoundField ItemStyle-Width="150px" DataField="OrderId" HeaderText="Order Id" />
                                <asp:BoundField ItemStyle-Width="150px" DataField="OrderDate" HeaderText="Date" />
                            </Columns>
                        </asp:GridView>
                    </asp:Panel>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />
            <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="Button1" Text="Export" OnClick="ExportExcel" runat="server" />
    </form>
</body>
</html>

c#:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvCustomers.DataSource = GetData("select top 10 * from Customers");
            gvCustomers.DataBind();
        }
    }

    private static DataTable GetData(string query)
    {
        string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = query;
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
    }

    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string customerId = gvCustomers.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView gvOrders = e.Row.FindControl("gvOrders") as GridView;
            gvOrders.DataSource = GetData(string.Format("select top 3 * from Orders where CustomerId='{0}'", customerId));
            gvOrders.DataBind();
        }
    }

    protected void ExportExcel(object sender, EventArgs e)
    {
        DataTable dt = new DataTable("GridView_Data");
        GridView gvOrders = (GridView)gvCustomers.Rows[1].FindControl("gvOrders");
        foreach (TableCell cell in gvCustomers.HeaderRow.Cells)
        {
            dt.Columns.Add(cell.Text);
        }
        foreach (TableCell cell in gvOrders.HeaderRow.Cells)
        {
            dt.Columns.Add(cell.Text);
        }
        dt.Columns.RemoveAt(0);
        foreach (GridViewRow row in gvCustomers.Rows)
        {                     
                GridView gvOrderscell = (row.FindControl("gvOrders") as GridView);
                for (int j = 0; j < gvOrderscell.Rows.Count; j++)
                {
                    dt.Rows.Add(row.Cells[1].Text, row.Cells[2].Text, gvOrderscell.Rows[j].Cells[0].Text, gvOrderscell.Rows[j].Cells[1].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();
            }
        }
    }

GridView:

Excel:

Thank You.