ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Exporting Multiple GridView record to single Excel SpreadSheet in ASP.Net<p>Hi <a class="username" rel="santosh86"> santosh86</a>,</p> <p>I have created sample. Refer the below code.</p> <p>Here i have used two approach. <font size="2"><font face="Arial">ClosedXML and OpenXml Libraries and simple rendering process.</font></font></p> <div style="margin: 0in 0in 8pt;"><font size="2"><font face="Arial">You can download the libraries using the following download locations.</font></font></div> <div style="margin: 0in 0in 8pt;"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=5124">Download OpenXml SDK 2.0</a></div> <div style="margin: 0in 0in 8pt;"><a href="http://closedxml.codeplex.com/" target="_blank">Download ClosedXML Library</a></div> <div style="margin: 0in 0in 8pt;"><span style="text-decoration: underline;">HTML</span></div> <pre class="brush: html">&lt;div&gt; &lt;asp:GridView runat=&#34;server&#34; ID=&#34;GridView1&#34; /&gt; &lt;br /&gt; &lt;br /&gt; &lt;asp:GridView runat=&#34;server&#34; ID=&#34;GridView2&#34; /&gt; &lt;/div&gt; &lt;asp:Button ID=&#34;btnExport&#34; runat=&#34;server&#34; Text=&#34;Export&#34; OnClick=&#34;Export&#34; /&gt;</pre> <p><span style="text-decoration: underline;">Code</span></p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { string strQuery = &#34;select * from customers&#34;; SqlCommand cmd = new SqlCommand(strQuery); DataTable dt = GetData(cmd); GridView1.DataSource = dt; GridView1.DataBind(); strQuery = &#34;select * from customers&#34;; 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[&#34;conString&#34;].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) { &nbsp;&nbsp;&nbsp; using (XLWorkbook wb = new XLWorkbook()) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GridView1.AllowPaging = false; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GridView2.AllowPaging = false; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DataTable dt = new DataTable(&#34;Page_1&#34;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (System.Web.UI.WebControls.TableCell cell in GridView1.HeaderRow.Cells) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt.Columns.Add(cell.Text); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (GridViewRow row in GridView1.Rows) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt.Rows.Add(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int j = 0; j &lt; row.Cells.Count; j++) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (GridViewRow row in GridView2.Rows) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt.Rows.Add(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int j = 0; j &lt; row.Cells.Count; j++) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wb.Worksheets.Add(dt); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Clear(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Buffer = true; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Charset = &#34;&#34;; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.ContentType = &#34;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#34;; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=GridView.xlsx&#34;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; using (MemoryStream MyMemoryStream = new MemoryStream()) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wb.SaveAs(MyMemoryStream); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MyMemoryStream.WriteTo(Response.OutputStream); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Flush(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.End(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; } }</pre> <p>If you do't want to use closed xlm library then use the below code to export.</p> <pre class="brush: csharp">protected void Export(object sender, EventArgs e) { DataTable dt = new DataTable(); Response.Clear(); Response.Buffer = true; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=GridViewExport.xls&#34;); Response.Charset = &#34;&#34;; Response.ContentType = &#34;application/vnd.ms-excel&#34;; 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 &lt; 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 &lt; 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 = @&#34;&lt;style&gt; .textmode { } &lt;/style&gt;&#34;; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); } } public override void VerifyRenderingInServerForm(Control control) { &nbsp;&nbsp;&nbsp; /* Verifies that the control is rendered */ }</pre> <p><span style="text-decoration: underline;">Screenshot</span></p> <p><img src="https://i.imgur.com/yHnV2SZ.gif" alt="" />&nbsp;</p>https://www.aspforums.net:443/Threads/162251/Exporting-Multiple-GridView-record-to-single-Excel-SpreadSheet-in-ASPNet/https://www.aspforums.net:443/Threads/162251/Exporting-Multiple-GridView-record-to-single-Excel-SpreadSheet-in-ASPNet/Fri, 28 Oct 2016 05:37:32 GMT