Exporting Multiple GridView record to single Excel SpreadSheet in ASP.Net

Last Reply on Oct 28, 2016 05:37 AM By dharmendr

Posted on Oct 28, 2016 04:25 AM

Dear sir want to export  two gridview data in same excel sheet if first gridview data is end then start second grdview data in excel sheet.

Posted on Oct 28, 2016 05:37 AM Modified on 11 months ago

Hi santosh86,

I have created sample. Refer the below code.

Here i have used two approach. ClosedXML and OpenXml Libraries and simple rendering process.

You can download the libraries using the following download locations.
HTML
<div>
    <asp:GridView runat="server" ID="GridView1" />
    <br />
    <br />
    <asp:GridView runat="server" ID="GridView2" />
</div>
<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="Export" />

Code

protected void Page_Load(object sender, EventArgs e)
{
    string strQuery = "select * from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
    GridView1.DataSource = dt;
    GridView1.DataBind();

    strQuery = "select * from  customers";
    cmd = new SqlCommand(strQuery);
    dt = GetData(cmd);
    GridView2.DataSource = dt;
    GridView2.DataBind();
}

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        con.Open();
        sda.Fill(dt);
        con.Close();
    }
    return dt;
}

protected void Export(object sender, EventArgs e)
{
    using (XLWorkbook wb = new XLWorkbook())
    {
        GridView1.AllowPaging = false;
        GridView2.AllowPaging = false;
        DataTable dt = new DataTable("Page_1");
        foreach (System.Web.UI.WebControls.TableCell cell in GridView1.HeaderRow.Cells)
        {
            dt.Columns.Add(cell.Text);
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.Cells.Count; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
            }
        }
        foreach (GridViewRow row in GridView2.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.Cells.Count; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
            }
        }

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

If you do't want to use closed xlm library then use the below code to export.

protected void Export(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        GridView2.AllowPaging = false;
        foreach (System.Web.UI.WebControls.TableCell cell in GridView1.HeaderRow.Cells)
        {
            dt.Columns.Add(cell.Text);
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.Cells.Count; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
            }
        }
        foreach (GridViewRow row in GridView2.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.Cells.Count; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
            }
        }

        GridView gridView = new GridView();
        gridView.DataSource = dt;
        gridView.DataBind();
        gridView.RenderControl(hw);
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Screenshot