Export gridview data to Excel sheets

Question

Hello Everyone,

 

I have four gridviews, here with below code I can export all the four sheets in one Excel Workbook. 

I want something like, gridview1&2 in one sheet and gridview3&4 in another sheet within same excel workbook. 

Can anyone help.... 

 

Thanks. 

 

protected void Button3_Click(object sender, EventArgs e)

{

GridView1.Visible = true;

GridView2.Visible = true;

GridView3.Visible = true;

GridView4.Visible = true;

XLWorkbook wb = new XLWorkbook();

GridView[] gvExcel = new GridView[] { GridView1, GridView2, GridView3, GridView4 };

string[] name = new string[] { "s1", "s2", "s3", "s4" };

for (int i=0; i < gvExcel.Length; i++)

{

if (gvExcel[i].Visible)

{

gvExcel[i].AllowPaging = false;

gvExcel[i].DataBind();

DataTable dt = new DataTable(name[i].ToString());

for (int Z=0; Z<gvExcel[i].Columns.Count;Z++ )

{

dt.Columns.Add(gvExcel[i].Columns[Z].HeaderText);

}

foreach (GridViewRow row in gvExcel[i].Rows)

{

dt.Rows.Add();

for (int c = 0; c < row.Cells.Count; c++)

{

dt.Rows[dt.Rows.Count - 1][c] = row.Cells[c].Text;

}

}

wb.Worksheets.Add(dt);

gvExcel[i].AllowPaging = true;

}

}

Response.Clear();

Response.Buffer = true;

Response.Charset = "";

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

Response.AddHeader("content-disposition", "attachment;filename=Workbook_Name.xlsx");

using (MemoryStream MyMemoryStream = new MemoryStream())

{

wb.SaveAs(MyMemoryStream);

MyMemoryStream.WriteTo(Response.OutputStream);

Response.Flush();

Response.End();

}

}