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;
C#
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 != " ")
{
dt.Columns.Add(cell.Text);
}
}
foreach (TableCell cell in gvOrders.HeaderRow.Cells)
{
if (cell.Text != " ")
{
dt.Columns.Add(cell.Text);
}
}
foreach (TableCell cell in gvProducts.HeaderRow.Cells)
{
if (cell.Text != " ")
{
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
