Export Multi-Level Nested GridView to Excel using OpenXml in ASP.Net

srisai00...

SOLVED CLOSED ACTIVE ANSWERED
User: srisai0012
Posted: on Sep 23, 2014 12:22 AM
Forum: Data Controls
Answer: 1
Views: 11074

Hello to everyone. I have a problem with exporting three gridviews (main and nested gridview and sub nested gridview) to Excel fromat using ClosedXML (XLWorkbook). I read tutorial on aspsnippets and it works fine for main gridview. i refered in link http://www.aspforums.net/Threads/147544/Export-Nested-GridView-GridView-inside-GridView-to-Excel-in-ASPNet/Replies/8

but its only for two gridview but i need three nested gridviews export to excel please help me ..

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();
            }
        }
    }

Azim

0 3

Reply
ANSWER
User: Azim
Replied: on Sep 23, 2014 06:00 AM
Modified: on Sep 23, 2014 06:00 AM
Report

Ref:

http://www.aspforums.net/Threads/147544/Export-Nested-GridView-GridView-inside-GridView-to-Excel-in-ASPNet/Replies/8

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%;
        }
        .Nested_ChildGrid td
        {
            background-color: #fff !important;
            color: black;
            font-size: 10pt;
            line-height: 200%;
        }
        .Nested_ChildGrid th
        {
            background-color: #2B579A !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">
        $(function () {
            $("[id*=imgOrdersShow]").each(function () {
                if ($(this)[0].src.indexOf("minus") != -1) {
                    $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>");
                    $(this).next().remove();
                }
            });
            $("[id*=imgProductsShow]").each(function () {
                if ($(this)[0].src.indexOf("minus") != -1) {
                    $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>");
                    $(this).next().remove();
                }
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="gvCustomers" runat="server" DataKeyNames="CustomerId" AutoGenerateColumns="false"
        CssClass="Grid">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:ImageButton ID="imgOrdersShow" runat="server" OnClick="Show_Hide_OrdersGrid"
                        ImageUrl="~/images/plus.png" CommandArgument="Show" />
                    <asp:Panel ID="pnlOrders" runat="server" Visible="false" Style="position: relative">
                        <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" PageSize="5"
                            AllowPaging="true" OnPageIndexChanging="OnOrdersGrid_PageIndexChanging" CssClass="ChildGrid"
                            DataKeyNames="OrderId">
                            <Columns>
                                <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:ImageButton ID="imgProductsShow" runat="server" OnClick="Show_Hide_ProductsGrid"
                                            ImageUrl="~/images/plus.png" CommandArgument="Show" />
                                        <asp:Panel ID="pnlProducts" runat="server" Visible="false" Style="position: relative">
                                            <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="false" PageSize="2"
                                                AllowPaging="true" OnPageIndexChanging="OnProductsGrid_PageIndexChanging" CssClass="Nested_ChildGrid">
                                                <Columns>
                                                    <asp:BoundField ItemStyle-Width="150px" DataField="ProductId" HtmlEncode="false" HeaderText="Product Id" />
                                                    <asp:BoundField ItemStyle-Width="150px" DataField="ProductName" HtmlEncode="false"
                                                        HeaderText="Product Name" />
                                                </Columns>
                                            </asp:GridView>
                                        </asp:Panel>
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <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" HtmlEncode="false"
                HeaderText="Contact Name" />
            <asp:BoundField ItemStyle-Width="150px" DataField="City" HtmlEncode="false" HeaderText="City" />
        </Columns>
    </asp:GridView>
    <asp:Button Text="Export Excel" OnClick="ExportExcel" runat="server" />
    </form>
</body>
</html>

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using ClosedXML.Excel;

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

private static DataTable GetData(string query)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        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 Show_Hide_OrdersGrid(object sender, EventArgs e)
{
    ImageButton imgShowHide = (sender as ImageButton);
    GridViewRow row = (imgShowHide.NamingContainer as GridViewRow);
    if (imgShowHide.CommandArgument == "Show")
    {
        row.FindControl("pnlOrders").Visible = true;
        imgShowHide.CommandArgument = "Hide";
        imgShowHide.ImageUrl = "~/images/minus.png";
        string customerId = gvCustomers.DataKeys[row.RowIndex].Value.ToString();
        GridView gvOrders = row.FindControl("gvOrders") as GridView;
        BindOrders(customerId, gvOrders);
    }
    else
    {
        row.FindControl("pnlOrders").Visible = false;
        imgShowHide.CommandArgument = "Show";
        imgShowHide.ImageUrl = "~/images/plus.png";
    }
}

private void BindOrders(string customerId, GridView gvOrders)
{
    gvOrders.ToolTip = customerId;
    gvOrders.DataSource = GetData(string.Format("select * from Orders where CustomerId='{0}'", customerId));
    gvOrders.DataBind();
}

protected void OnOrdersGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView gvOrders = (sender as GridView);
    gvOrders.PageIndex = e.NewPageIndex;
    BindOrders(gvOrders.ToolTip, gvOrders);
}

protected void Show_Hide_ProductsGrid(object sender, EventArgs e)
{
    ImageButton imgShowHide = (sender as ImageButton);
    GridViewRow row = (imgShowHide.NamingContainer as GridViewRow);
    if (imgShowHide.CommandArgument == "Show")
    {
        row.FindControl("pnlProducts").Visible = true;
        imgShowHide.CommandArgument = "Hide";
        imgShowHide.ImageUrl = "~/images/minus.png";
        int orderId = Convert.ToInt32((row.NamingContainer as GridView).DataKeys[row.RowIndex].Value);
        GridView gvProducts = row.FindControl("gvProducts") as GridView;
        BindProducts(orderId, gvProducts);
    }
    else
    {
        row.FindControl("pnlProducts").Visible = false;
        imgShowHide.CommandArgument = "Show";
        imgShowHide.ImageUrl = "~/images/plus.png";
    }
}

private void BindProducts(int orderId, GridView gvProducts)
{
    gvProducts.ToolTip = orderId.ToString();
    gvProducts.DataSource = GetData(string.Format("SELECT ProductId, ProductName FROM Products WHERE ProductId IN (SELECT ProductId FROM [Order Details] WHERE OrderId = '{0}')", orderId));
    gvProducts.DataBind();
}

protected void OnProductsGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView gvProducts = (sender as GridView);
    gvProducts.PageIndex = e.NewPageIndex;
    BindProducts(int.Parse(gvProducts.ToolTip), gvProducts);
}

protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    gvCustomers.AllowPaging = false;
    GridView gvOrders = new GridView();
    GridView gvProducts = new GridView();
    for (int i = 0; i < gvCustomers.Rows.Count; i++)
    {
        gvOrders = (GridView)gvCustomers.Rows[i].FindControl("gvOrders");
        gvOrders.AllowPaging = false;
        gvOrders.DataSource = GetData(string.Format("select * from Orders where CustomerId='{0}'", gvCustomers.DataKeys[i].Value.ToString()));
        gvOrders.DataBind();
    }
    for (int i = 0; i < gvOrders.Rows.Count; i++)
    {
        gvProducts = (GridView)gvOrders.Rows[i].FindControl("gvProducts");
        gvProducts.AllowPaging = false;
        gvProducts.DataSource = GetData(string.Format("SELECT ProductId, ProductName FROM Products WHERE ProductId IN (SELECT ProductId FROM [Order Details] WHERE OrderId = '{0}')", Convert.ToInt32(gvOrders.DataKeys[i].Value)));
        gvProducts.DataBind();
    }

    foreach (TableCell cell in gvCustomers.HeaderRow.Cells)
    {
        if (cell.Text != "&nbsp;")
        {
            dt.Columns.Add(cell.Text);
        }
    }
    foreach (TableCell cell in gvOrders.HeaderRow.Cells)
    {
        if (cell.Text != "&nbsp;")
        {
            dt.Columns.Add(cell.Text);
        }
    }
    foreach (TableCell cell in gvProducts.HeaderRow.Cells)
    {
        if (cell.Text != "&nbsp;")
        {
            dt.Columns.Add(cell.Text);
        }
    }

    foreach (GridViewRow row in gvCustomers.Rows)
    {
        GridView gvOrderscell = (row.FindControl("gvOrders") as GridView);
        for (int j = 0; j < gvOrderscell.Rows.Count; j++)
        {
            GridView gvProduct = (gvOrders.Rows[j].FindControl("gvProducts") as GridView);
            for (int i = 0; i < gvProduct.Rows.Count; i++)
            {
                dt.Rows.Add(row.Cells[1].Text, row.Cells[2].Text, gvOrderscell.Rows[j].Cells[1].Text, gvOrderscell.Rows[j].Cells[2].Text, gvProduct.Rows[i].Cells[0].Text, gvProduct.Rows[i].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();
        }
    }
}

Screenshot

Export Multi-Level Nested GridView to Excel using OpenXml in ASP.Net

Multilevel (N Level) Nested GridView (GridView inside GridView) in ASP.Net with Paging

Company

Explore

Follow us

Contact Us