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.Remove all style and Table format when exporting to Excel using ClosedXML in ASP.Net<p>Hi <a class="username" rel="BugHunter"> BugHunter</a>,</p> <p>Refer below sample.</p> <p><span style="text-decoration: underline;"><strong>Database</strong></span></p> <p>For this sample I have used of NorthWind database that you can download using the link given below.</p> <p><a href="https://www.aspsnippets.com/Articles/Install-the-Northwind-and-Pubs-Sample-Databases-in-SQL-Server-Express.aspx">Download Northwind Database</a></p> <p><span style="text-decoration: underline;"><strong>HTML</strong></span></p> <pre class="brush: html">&lt;asp:GridView ID=&#34;GridView1&#34; runat=&#34;server&#34; AutoGenerateColumns=&#34;false&#34; AllowPaging=&#34;true&#34; OnPageIndexChanging=&#34;OnPageIndexChanging&#34; PageSize=&#34;5&#34;&gt; &lt;Columns&gt; &lt;asp:BoundField DataField=&#34;ContactName&#34; HeaderText=&#34;Contact Name&#34; ItemStyle-Width=&#34;150px&#34; /&gt; &lt;asp:BoundField DataField=&#34;City&#34; HeaderText=&#34;City&#34; ItemStyle-Width=&#34;100px&#34; /&gt; &lt;asp:BoundField DataField=&#34;Country&#34; HeaderText=&#34;Country&#34; ItemStyle-Width=&#34;100px&#34; /&gt; &lt;/Columns&gt; &lt;/asp:GridView&gt; &lt;br /&gt; &lt;asp:Button ID=&#34;btnExport&#34; runat=&#34;server&#34; Text=&#34;Export To Excel&#34; OnClick=&#34;ExportExcel&#34; /&gt;</pre> <p><span style="text-decoration: underline;"><strong>Namespaces</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.IO; using System.Data; using ClosedXML.Excel; using System.Data.SqlClient; using System.Configuration;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.IO Imports System.Data Imports ClosedXML.Excel Imports System.Data.SqlClient Imports System.Configuration</pre> <p><span style="text-decoration: underline;"><strong>Code</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { this.BindGrid(); } } private void BindGrid() { string strConnString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(strConnString)) { using (SqlCommand cmd = new SqlCommand(&#34;SELECT TOP 7 * FROM Customers&#34;)) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } } protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; this.BindGrid(); } protected void ExportExcel(object sender, EventArgs e) { using (XLWorkbook wb = new XLWorkbook()) { //Set the Current Page. GridView1.AllowPaging = false; this.BindGrid(); //Create a DataTable with schema same as GridView columns. DataTable dt = new DataTable(&#34;Customers&#34;); foreach (TableCell cell in GridView1.HeaderRow.Cells) { dt.Columns.Add(cell.Text); } DataRow dr = dt.NewRow(); foreach (TableCell cell in GridView1.HeaderRow.Cells) { dr[cell.Text] = cell.Text; } //Add Header rows from GridView to DataTable. dt.Rows.Add(dr); //Loop and add rows from GridView to DataTable. 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] = HttpUtility.HtmlDecode(row.Cells[j].Text); } } var ws = wb.Worksheets.Add(dt.TableName); ws.Cell(1, 1).InsertData(dt.Rows); ws.Columns().AdjustToContents(); //Export the Excel file. Response.Clear(); Response.Buffer = true; Response.Charset = &#34;&#34;; Response.ContentType = &#34;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#34;; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=GridView.xlsx&#34;); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not Me.IsPostBack Then Me.BindGrid() End If End Sub Private Sub BindGrid() Dim strConnString As String = ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString Using con As SqlConnection = New SqlConnection(strConnString) Using cmd As SqlCommand = New SqlCommand(&#34;SELECT TOP 7 * FROM Customers&#34;) Using sda As SqlDataAdapter = New SqlDataAdapter() cmd.Connection = con sda.SelectCommand = cmd Using dt As DataTable = New DataTable() sda.Fill(dt) GridView1.DataSource = dt GridView1.DataBind() End Using End Using End Using End Using End Sub Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) GridView1.PageIndex = e.NewPageIndex Me.BindGrid() End Sub Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs) Using wb As XLWorkbook = New XLWorkbook() GridView1.AllowPaging = False Me.BindGrid() Dim dt As DataTable = New DataTable(&#34;Customers&#34;) For Each cell As TableCell In GridView1.HeaderRow.Cells dt.Columns.Add(cell.Text) Next Dim dr As DataRow = dt.NewRow() For Each cell As TableCell In GridView1.HeaderRow.Cells dr(cell.Text) = cell.Text Next dt.Rows.Add(dr) For Each row As GridViewRow In GridView1.Rows dt.Rows.Add() For j As Integer = 0 To row.Cells.Count - 1 dt.Rows(dt.Rows.Count - 1)(j) = HttpUtility.HtmlDecode(row.Cells(j).Text) Next Next Dim ws = wb.Worksheets.Add(dt.TableName) ws.Cell(1, 1).InsertData(dt.Rows) ws.Columns().AdjustToContents() Response.Clear() Response.Buffer = True Response.Charset = &#34;&#34; Response.ContentType = &#34;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&#34; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=GridView.xlsx&#34;) Using MyMemoryStream As MemoryStream = New MemoryStream() wb.SaveAs(MyMemoryStream) MyMemoryStream.WriteTo(Response.OutputStream) Response.Flush() Response.[End]() End Using End Using End Sub</pre> <p><span style="text-decoration: underline;"><strong>Screenshot</strong></span></p> <p><img src="https://i.imgur.com/C6tCmZD.gif" alt="" width="420" height="397" /></p>https://www.aspforums.net:443/Threads/166087/Remove-all-style-and-Table-format-when-exporting-to-Excel-using-ClosedXML-in-ASPNet/https://www.aspforums.net:443/Threads/166087/Remove-all-style-and-Table-format-when-exporting-to-Excel-using-ClosedXML-in-ASPNet/Mon, 15 Jul 2019 04:04:11 GMT