How to Export Multiple Data Tables to Multiple Worksheets Inside a Single Excel File

Question

Below is my C# code for export single table data, but I need to export 3 tables data into one single excel file. I need to Export Multiple Data Tables to Multiple Worksheets Inside a Single Excel File.

 protected void lnkbtn_Submit_Click(object sender, EventArgs e)
  {
    DateTime Fromdate = DateTime.ParseExact(txt_FromDate.Text, "MM/dd/yyyy", CultureInfo.InvariantCulture);
    DateTime Todate = DateTime.ParseExact(txt_ToDate.Text, "MM/dd/yyyy", CultureInfo.InvariantCulture);

    bo.Dateused = Fromdate;
    bo.Dateused2 = Todate;

    DataTable dt = bl.Get_Registrationdetailsbydate(bo);
    gv_Regdetails.DataSource = dt;
    gv_Regdetails.DataBind();
    Session["Fromdate"] = txt_FromDate.Text;
    Session["Todate"] = txt_ToDate.Text;
    if (gv_Regdetails.Rows.Count > 0)
    {
        Session["registrationtable_date"] = dt;
        btnExport.Visible = true;
    }

    else
    {
        Session["registrationtable_date"] = null;
        btnExport.Visible = false;
    }
}
protected void Button1_Click(object sender, ImageClickEventArgs e)
{
    DataGrid dg = new DataGrid();
    if (Session["registrationtable_date"] != null)
    {
        dg.DataSource = (DataTable)Session["registrationtable_date"];
        dg.DataBind();
        System.Web.HttpContext.Current.Response.Clear();
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=TotalRegistrationDetails.xls");
        System.Web.HttpContext.Current.Response.Charset = "";
        System.Web.HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringwrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlwrite = new HtmlTextWriter(stringwrite);
        dg.RenderControl(htmlwrite);
        System.Web.HttpContext.Current.Response.Write(stringwrite.ToString());
        System.Web.HttpContext.Current.Response.End();
        Session.Remove("registrationtable_date");

        }
      }
    }

My stored procedure script:

ALTER PROCEDURE [dbo].[Get_Registrationdetailsbydate]
 (
  @Fromdate datetime,
  @Todate datetime
 )
 AS
BEGIN

 select Name,Email,Mobile,SubmissionDate from Registration  WHERE  (CONVERT(varchar(50), SubmissionDate, 101)  BETWEEN @Fromdate AND @Todate) 
END

Thank you in Advance.

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

You might take a look at this Codeproject link, which describes exporting a dataset to multiple Excel Sheets

http://www.codeproject.com/Articles/31516/Export-DataSet-to-Multiple-Excel-Sheets

The above link was available in this SO question

Please find the SO question on how to create an Excel file using C#

edited May 23 '17 at 12:24

Community

1
1

answered Jan 21 '16 at 05:35

Krishna P S

340
2
9

I need according to my above code, if posible. @Krishna P S – Mr doubt Jan 21 '16 at 05:37
@Zahed I'm afraid, you need to use an Excel Converter as given in above link to get a proper excel file. Writing the grid data to response doesn't help the situation either. – Krishna P S Jan 21 '16 at 05:41
Can we do using stored procedure, the above link we calling directly na. @Krishna P S – Mr doubt Jan 21 '16 at 05:54
@Zahed Yes you can use it. That example was for illustration purposes. The Code will work with SP also. – Krishna P S Jan 21 '16 at 05:58
1

But below i got one answer according my code. @Krishna P S – Mr doubt Jan 21 '16 at 06:04

score 0 · Answer 2 · answered Jan 21 '16 at 05:43

use Dataset to Store two Datatables,

 public DataSet getDataSetExportToExcel()
        {
            DataSet ds = new DataSet();
            DataTable dtEmp = new DataTable("Employee");
            dtEmp = getAllEmployeesList();

            DataTable dtEmpOrder = new DataTable("Order List");
            dtEmpOrder = getAllEmployeesOrderList();
            ds.Tables.Add(dtEmp);
            ds.Tables.Add(dtEmpOrder);
            return ds;
        }

and use

dg.DataSource = (DataSet)Session["registrationtable_date"];
        dg.DataBind();

I am doing using stored procedure can you update answer according to above method, my another method for stored procedure is bl.Get_educationdetails. @Kutty Rajesh Valangai — Mr doubt, Jan 21 '16 at 05:52

How to Export Multiple Data Tables to Multiple Worksheets Inside a Single Excel File

2 Answers2