how to Export Data in excel from child GridView?

Question

3.00/5 (1 vote)

See more:

hi Expert this is my first Question ever!
i am having problem with exporting required Data from child GridView, although it`s working fine but fetching all unnecessary Data.
[An example of my Parent/Child Gridview]

aspx file

ASP.NET

<%@ Page Title="" Language="C#" EnableEventValidation="false" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="InstallationView.aspx.cs" Inherits="Uster_PortalApp.InstallationView" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
<script type="text/javascript">
    function switchViews(layer_ref) {

        obj = document.getElementById(layer_ref);
        img = document.getElementById('img' + layer_ref);

        if (obj.style.display == "none") {

            obj.style.display = "";
            img.src = "Images/Expand_Button_white_Down.jpg";
            img.alt = "Close to view other customers";
        }
        else {
            obj.style.display = "none";
            img.src = "Images/Expand_button_white.jpg";
            img.alt = "Expand to show orders";
        }

    } 
</script>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">


<!--start parent gridview -->
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"  ForeColor="#B02720" GridLines="None"OnRowDataBound="gv_RowDataBound" AllowPaging="True" PageSize="20" onpageindexchanging="GridView1_PageIndexChanging" onselectedindexchanging="GridView1_SelectedIndexChanging" >
<EditRowStyle BackColor="#999999" />
<HeaderStyle CssClass="dataTable" />
<RowStyle CssClass="dataTable" />
<AlternatingRowStyle BackColor="White" ForeColor="Black" />
   <Columns>
     <asp:TemplateField>
       <ItemTemplate>
          <a href="javascript:switchViews('div<%# Container.DataItemIndex + 1 %>', 'none');">
          <img id="imgdiv<%# GridView1.Rows.Count %>" alt="Click to show/hide orders" border="0" src="images/aeredd.gif" />
          </a>
          </ItemTemplate>
          <AlternatingItemTemplate>
            <a href="javascript:switchViews('div<%# Container.DataItemIndex + 1 %>', 'alt');">
            <img id="imgdiv<%# GridView1.Rows.Count %>" alt="Click to show/hide orders" border="0" src="images/prrerevpg.gif" />
                            </a>
                        </AlternatingItemTemplate>
                    </asp:TemplateField>
                  
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:BoundField DataField="UO_ID" HeaderText="UO_ID" Visible="false" />
                    <asp:BoundField DataField="UO_Name" HeaderText="Unit Office Name"
                        HeaderStyle-HorizontalAlign="Left" ItemStyle-HorizontalAlign="Left">
                    <HeaderStyle HorizontalAlign="Left" />
                    <ItemStyle HorizontalAlign="Left" />
                    </asp:BoundField>
                    <asp:BoundField DataField="machineID" HeaderText="machineId,machineIds" Visible="false" />
                    <asp:BoundField DataField="machineName" HeaderText="Machine Name"
                        HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">
                    <HeaderStyle HorizontalAlign="Center" />
                    <ItemStyle HorizontalAlign="Center" />
                    </asp:BoundField>
                    <asp:BoundField DataField="Number" HeaderText="Number"
                        HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

                    <HeaderStyle HorizontalAlign="Center" />
                    <ItemStyle HorizontalAlign="Center" />
                    </asp:BoundField>

                    <asp:TemplateField>
                        <ItemTemplate>
                            </td></tr>
                            <tr>
                                <td colspan="100%">
                                    <div id="div<%# Container.DataItemIndex + 1 %>" style="display:none;position:relative;left:25px;" >
                                   <!--start child gridview --> 
   <asp:GridView ID="GridView2" runat="server" Width="80%" OnRowCreated="Child_gv_RowCreated"
AutoGenerateColumns="false" DataKeyNames="machineID" OnRowCommand="gvEG_RowCommand"
EmptyDataText="No orders for this customer." ShowFooter="True">
   <HeaderStyle />
   <AlternatingRowStyle  />
   <RowStyle  />
    <Columns>
      <asp:BoundField DataField="machineID" HeaderText="MachineID" Visible="false" />
      <asp:BoundField DataField="UO_ID" HeaderText="UO_ID" Visible="false"/>
      <asp:BoundField DataField="EquipNo" HeaderText="Equipment Number" />
      <asp:BoundField DataField="Serial_No" HeaderText="Serial Number" />
      <asp:BoundField DataField="Date_Installation" HeaderText="Installation Date" />
      <asp:BoundField DataField="Warranty_End" HeaderText="Warranty End" />
      <asp:BoundField DataField="Machine_Type" HeaderText="Machine Type" />
      <asp:BoundField DataField="Winding_Spng_Pos_No" HeaderText="Winding Position" />
      <asp:BoundField DataField="CCU_Security_No" HeaderText="CCU Security Number" />
      <asp:BoundField DataField="ServiceEngineer" HeaderText="Service Engineer" />
      <asp:BoundField DataField="CCU_Type" HeaderText="CCU Type" />
      <asp:BoundField DataField="MAC" HeaderText="MAC" />
      <asp:BoundField DataField="iMH_Type" HeaderText="iMH Type" />
      <asp:BoundField DataField="iMH_Sr_No" HeaderText="iMH SrNo" />

</Columns>

<!-- End child gridview -->
</asp:GridView>
</div>
</td>
</tr>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
<!--End parent gridview -->           
</asp:GridView>
</asp:Content>

i added a button pragmatically in footer of child gridview in order to export from gridview to excel
(Code Behind)

C#

 private void BindGrid()
 {
      // stroed procedure for parent GridView
      ds = _cls.NewView2(UnitName.Text, Instrument.Text, FromDate.Text, ToDate.Text, Session["CountryID"].ToString());

      DataView dvinstallation = ds.Tables[0].DefaultView;

         if (dvinstallation.ToTable().Rows.Count > 0)
         {
             GridView1.DataSource = dvinstallation;
             GridView1.DataBind();
         }
         else
         {
             GridView1.DataSource = null;
             GridView1.DataBind();
             GridView1.EmptyDataText = "No Record Found";
         }

         GridView1.Visible = true;
     }

 protected void btnView_Click(object sender, EventArgs e)
 {
  BindGrid();
 }

 // Child GridView RowCreated Event to add button control pragmatically to child gridview footer
 protected void Child_gv_RowCreated(object sender, GridViewRowEventArgs e)
 {
     if (e.Row.RowType == DataControlRowType.Footer)
     {
         Button btn_exportToexcel = new Button();
         btn_exportToexcel.Text = "Export";
         btn_exportToexcel.ID = "btn_exportToexcel";
         btn_exportToexcel.CssClass = "buttonstyle";
         btn_exportToexcel.CommandName = "ExportToExcel";
         //add handler


         e.Row.Cells[2].Controls.Add(btn_exportToexcel);
         // Create an button Click event for latter user in order to export to excel
         btn_exportToexcel.Click += new System.EventHandler(btn_exportToexcel_Clicked);
     }
 }

 public override void VerifyRenderingInServerForm(Control control)
 {

 }
 protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
 {
     GridView Child_gv = e.Row.FindControl("GridView2") as GridView;

     if (e.Row.RowType == DataControlRowType.DataRow)
     {
         int uoid = Convert.ToInt32(((DataRowView)e.Row.DataItem)["UO_ID"]);
         int machineid = Convert.ToInt32(((DataRowView)e.Row.DataItem)["MachineID"]);
         string countryid = Session["CountryID"].ToString();
             // Stroed Procedure for Chil Grid View
             ds = _cls.ViewDetail(uoid, machineid, countryid);

             Child_gv.DataSource = ds;
             Child_gv.DataBind();

      }
 }
// export Data from Child GridView to excel on button click
 private void btn_exportToexcel_Clicked(object sender, EventArgs e)
 {
     Response.Clear();
     Response.AddHeader("content-disposition","attachment;filename=xyz.xls");
     Response.ContentType = "application/vnd.xls";
     StringWriter sw = new StringWriter();
     HtmlTextWriter htw = new HtmlTextWriter(sw);

     foreach (GridViewRow row in GridView1.Rows)
     {
         //find control of child grid view to render control
         GridView grv = GridView1.Rows[row.RowIndex].FindControl("GridView2") as GridView;

         grv.RenderControl(htw);

     }
     Response.Write(sw.ToString());
     Response.End();

 }

 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
 {
     GridView1.PageIndex = e.NewPageIndex;
     BindGrid();
 }

Now Problem is that when i click on button it export all the data of child gridview in excel, while it should only export data from child gridview in which button is clicked (not a whole Child grid view data)
Any suggestions?? How to export child grid data in which button is clicked?
sorry for my english

Posted 8-Nov-12 1:47am

anglo0072

Updated 8-Nov-12 5:14am

v12

Add a Solution

2 solutions

Solution 2

Thankx onenomi
what i have done having seen your suggestion i got the unique value from selected row and save it in hidden field then checked if the selected value is exist in child grid then export it.

C#

protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            GridView Child_gv = e.Row.FindControl("GridView2") as GridView;


            if (e.Row.RowType == DataControlRowType.DataRow)
            {

                int uoid = Convert.ToInt32(((DataRowView)e.Row.DataItem)["UO_ID"]);
                int machineid = Convert.ToInt32(((DataRowView)e.Row.DataItem)["MachineID"]);
                string countryid = Session["CountryID"].ToString();
                // javascrtip onclick event to get the selected row UO_ID and machineID
                e.Row.Attributes.Add("onclick", "javascript:setSelected('" + Convert.ToString(uoid) + "','"+Convert.ToString(machineid) +"','"+e.Row.RowIndex+"')");
                    // Stroed Procedure for Chil Grid View
                    ds = _cls.ViewDetail(uoid, machineid, countryid);
            }
        }

and the rowcommand event of child gridview

protected void gvEG_RowCommand(object sender, GridViewCommandEventArgs e)
        {            
            if (e.CommandName.Equals("ExportToExcel"))
            {
                                       
                    foreach (GridViewRow rows in GridView1.Rows)
                    {
                        string unitofficename = GridView1.Rows[rows.RowIndex].Cells[2].Text;                       
                        string machinename = GridView1.Rows[rows.RowIndex].Cells[4].Text;                      
                        GridView grv = GridView1.Rows[rows.RowIndex].FindControl("GridView2") as GridView;
                        foreach (GridViewRow child in grv.Rows)
                        {                           
                            string child_Machineid = grv.Rows[child.RowIndex].Cells[0].Text;
                            string child_uoid = grv.Rows[child.RowIndex].Cells[1].Text;
                            Button btn = grv.FooterRow.FindControl("btn_exportToexcel") as Button;
                            if (UOID.Value == child_uoid && MachineID.Value == child_Machineid)
                                {
                                    Response.Clear();
                                    btn.Visible = false;
                                    string style = @"<style> .text { mso-number-format:\@; } </style> ";

                                    string attachment = "attachment; filename=" + machinename + "_" + unitofficename + ".xls ";
                                    string atachmnt = process(attachment).Replace(" ",string.Empty); 
                                    Response.ContentType = "application/vnd.ms-excel";
                                    Response.AddHeader("content-disposition", atachmnt);
                                    Response.Charset = "";
                                    Page.EnableViewState = false;
                                    StringWriter sw = new StringWriter();
                                    HtmlTextWriter htw = new HtmlTextWriter(sw);                                   
                                    GridViewRow parentrow = GridView1.Rows[rows.RowIndex];
                                    parentrow.RenderControl(htw);                                
                                    Response.Write(style);
                                    Response.Write(sw.ToString());
                                    Response.End();
                                }                            
                        }

                    }                 
                
            }
        }

Posted 14-Nov-12 6:01am

anglo0072

Comments

njammy 15-Nov-12 8:19am

Glad you solved it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

njammy · Accepted Answer · 2012-11-11T23:39:00

The reason all data is being exported is

foreach (GridViewRow row in GridView1.Rows)

Also, you need to change the line

btn_exportToexcel.Click += new System.EventHandler(btn_exportToexcel_Clicked);

to be

btn_exportToexcel.Command += new System.CommandEventHandler(btn_exportToexcel_Clicked)

Then assign a commandargument value to btn_exportToExcel. Set the command argument for the export button to the unique key field value of the child gridview row. You might need to do this inside protected void Child_gv_RowDataBound.

Then change the private void btn_exportToexcel_Clicked to private void btn_exportToexcel_Command(object sender, CommandEventArgs e) and you have the child row id as the 'e' value.

Inside this method, get rid of the for each row code and just export the child row data found using the 'e' value.