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