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

michael1...

SOLVED CLOSED ACTIVE ANSWERED
User: michael123
Posted: on Jan 04, 2014 05:08 AM
Forum: Data Controls
Answer: 1
Views: 24686

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.

Azim

0 8

Reply
ANSWER
User: Azim
Replied: on Jan 04, 2014 08:51 AM
Modified: on Jan 04, 2014 09:10 AM
Report

I have solved it using these two links

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

<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.

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

Company

Explore

Follow us

Contact Us