Export GridView to multiple Excel sheet

Question

I have two Gridview in my Web application.I need ,while clicking the (ExcelExpot) button the values to be Export in Excel Accordingly Sheet1 and Sheet2.

  protected void ExportToExcel()
    {

        this.GridView1.EditIndex = -1;
        Response.Clear();
        Response.Buffer = true;
        string connectionString = (string)ConfigurationSettings.AppSettings["ConnectionString"];
        SqlConnection sqlconnection = new SqlConnection(connectionString);
        String sqlSelect = "select * from login";
        sqlconnection.Open();
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(sqlSelect, connectionString);
        //DataTable dt1
        DataTable dt1 =new DataTable();
        mySqlDataAdapter.Fill(dt1);

        //LinQ Query for dt2
        var query = (from c in dt.AsEnumerable()
        select new {id= c.Field<string>("id"),name=c.Field<string>("name"),city=c.Field<string>("city")}) ;
        DataTable dt2 = new DataTable();
        d2=query.CopyToDatatable();

        DataSet ds=new DataSet();
        ds.Tabls.Add(dt1);
        ds.Tabls.Add(dt2);
        Excel.Application excelHandle1 = PrepareForExport(ds);
        excelHandle1.Visible = true;

    } 
  // code for PrepareForExport(ds);
         PrepareForExport(ds)
             {

                two tables in two worksheets of Excel...

              }

Answer 1

Doing this with EPPlus is a piece of cake. No Interop assemblies required and literally 2 lines of code do all the magic:

ws.Cells["A1"].LoadFromDataTable(dt1, true);
ws2.Cells["A1"].LoadFromDataTable(dt2, true);

Complete code:

protected void ExportExcel_Click(object sender, EventArgs e)
{

     //LinQ Query for dt2
    var query = (from c in dt.AsEnumerable()
    select new {id= c.Field<string>("id"),name=c.Field<string>("name"),city=c.Field<string>("city")}) ;
    DataTable dt2 = new DataTable();
    dt2=query.CopyToDatatable();

    //DataTable dt1
    DataTable dt1 =new DataTable();
    mySqlDataAdapter.Fill(dt1);

    using (ExcelPackage pck = new ExcelPackage())
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Page 1");
        ExcelWorksheet ws2 = pck.Workbook.Worksheets.Add("Page 2");

        ws.Cells["A1"].LoadFromDataTable(dt1, true);
        ws2.Cells["A1"].LoadFromDataTable(dt2, true);

        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.Flush();
        Response.End();
    }
}

Note that I copied and paste it your code to gather the data. I expect these lines to produce a DataTable.