How can export multiple Gridview in one Excel In Asp.net.

How can export multiple Gridview in one  Excel In Asp.net.

It is very common to work on multiple gridview when we are working on some good project.

Here I am showing how can export multiple gridview in asp.net.Because it is normal to download data for report section.so if data is organize in many gridview ,than how can we deal with it ?

So here is a simple logic for doing this .

Step 1 : Data Base Tables.

In this article I am using two table  Profile_Detail and State_Detail.

But you can use Your own tables according to your need.

Step 2: Page Desgin.

For page Content  I am using two gridview for displaying data from Database and a Button for export data.

<div>

     <asp:GridView ID="gr1" runat="server"

        AutoGenerateColumns = "false" Font-Names = "Arial"

        Font-Size = "11pt" AlternatingRowStyle-BackColor = "DarkGray"

        HeaderStyle-BackColor ="GrayText"  

        >

       <Columns>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "EmpFName" HeaderText = "f_name" />

        <asp:BoundField ItemStyle-Width = "150px" DataField = "EmplName" HeaderText = "l_name"/>

       </Columns>

    </asp:GridView>

    <br /><br />

    <asp:GridView ID="gr2" runat="server"

        AutoGenerateColumns = "false" Font-Names = "Arial"

        Font-Size = "11pt" AlternatingRowStyle-BackColor = "DarkGray"

        HeaderStyle-BackColor = "GrayText"

        >

       <Columns>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "stateid" HeaderText = "s ID" />

        <asp:BoundField ItemStyle-Width = "150px" DataField = "statename" HeaderText = "s name"/>

       </Columns>

    </asp:GridView>

    </div>

    <br />

    <br />

    <asp:Button ID="btnExportExcel" runat="server" OnClick="btnExportExcel_Click"

        Text="ExportToExcel" />

Step 3: Code behind logic.

Here is our code .

//bind both gridviw at page load.

protected void Page_Load(object sender, EventArgs e)

    {

        string strQuery = "select top(5) * from Profile_Detail";

        SqlCommand cmd = new SqlCommand(strQuery);

        SqlDataAdapter AD1 = new SqlDataAdapter(strQuery, con);

        DataTable dt = new DataTable();

        AD1.Fill(dt);

       

        gr1.DataSource = dt;

        gr1.DataBind();

       string  strQuery2 = "select top(5) * from  State_Detail";

        cmd = new SqlCommand(strQuery);

        SqlDataAdapter AD2 = new SqlDataAdapter(strQuery2, con);

        DataTable dt1 = new DataTable();

        AD2.Fill(dt1);

        gr2.DataSource = dt1;

        gr2.DataBind();

    }

//Button event for download.

    protected void btnExportExcel_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.xls");

        Response.Charset = "";

        Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        Table tb = new Table();

        TableRow tr1 = new TableRow();

        TableCell cell1 = new TableCell();

        cell1.Controls.Add(gr1);

        tr1.Cells.Add(cell1);

        TableCell cell3 = new TableCell();

        cell3.Controls.Add(gr2);

        TableCell cell2 = new TableCell();

        cell2.Text = "&nbsp;";

      

            TableRow tr2 = new TableRow();

            tr2.Cells.Add(cell2);

            TableRow tr3 = new TableRow();

            tr3.Cells.Add(cell3);

            tb.Rows.Add(tr1);

            tb.Rows.Add(tr2);

            tb.Rows.Add(tr3);

      

        tb.RenderControl(hw);

        //style to format numbers to string

        string style = @"<style> .textmode { mso-number-format:\@; } </style>";

        Response.Write(style);

        Response.Output.Write(sw.ToString());

        Response.Flush();

        Response.End();

    }

The output will look like :