Export multiple GridViews (DataTables) to multiple Sheets in one Excel file using C# in ASP.Net

Hi navyam,

I have created one sample that full-fill your requirement.

HTML

<div>
    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="Id" HeaderText="Id" />
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="Id" HeaderText="Id" />
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="btnExport" Text="Export" OnClick="ExportExcel" runat="server" />
</div>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        gvCustomers.DataSource = this.Customers();
        gvCustomers.DataBind();
        gvEmployees.DataSource = this.Employees();
        gvEmployees.DataBind();
    }
}

private DataTable Customers()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    return dt;
}

private DataTable Employees()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
    dt.Rows.Add(1, "Christina Berglund", "Italy");
    dt.Rows.Add(2, "Laurence Lebihan", "USA");
    dt.Rows.Add(3, "Victoria Ashworth", "UK");
    dt.Rows.Add(4, "Patricio Simpson", "Argentina");
    return dt;
}

protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = Customers();
    DataTable dt1 = Employees();
    dt.TableName = "Customers Data";
    dt1.TableName = "Employees Data";
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt);
        wb.Worksheets.Add(dt1);
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Medications_Report_" + DateTime.Now.ToShortDateString().Replace(@"/", "_").Replace(" ", "_") + ".xls");
        using (MemoryStream memoryStream = new MemoryStream())
        {
            wb.SaveAs(memoryStream);
            byte[] bytes = memoryStream.ToArray();
            memoryStream.WriteTo(Response.OutputStream);
            memoryStream.Close();
            Response.Flush();
            Response.End();
        }
    }
}

Screenshot