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.Download (Save) Excel file created using ClosedXML on client machine in ASP.Net<p>Hi&nbsp;ashfaq,</p> <p>Check the below sample code.</p> <p><span style="text-decoration: underline;">The Excel File</span></p> <p><span style="text-decoration: underline;"><img src="https://i.imgur.com/tQ4aZOF.jpg" alt="" width="246" height="317" /></span></p> <p><span style="text-decoration: underline;"><strong>HTML</strong></span></p> <pre class="brush: csharp">&lt;asp:FileUpload ID=&#34;fuUpload&#34; runat=&#34;server&#34; /&gt; &lt;asp:Button Text=&#34;Upload&#34; OnClick=&#34;Button1_Click&#34; runat=&#34;server&#34; /&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Data; using System.IO; using System.Linq; using ClosedXML.Excel;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data Imports System.IO Imports System.Linq Imports ClosedXML.Excel</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Button1_Click(object sender, EventArgs e) { DataTable dt = GetDataTableFromExcel(); List&lt;DataTable&gt; dts = dt.AsEnumerable() .GroupBy(row =&gt; row.Field&lt;string&gt;(&#34;Name&#34;)) .Select(g =&gt; g.CopyToDataTable()).ToList(); string path = Server.MapPath(&#34;~/Excel/&#34;); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } using (XLWorkbook wb = new XLWorkbook()) { for (int i = 0; i &lt; dts.Count; i++) { if (!string.IsNullOrEmpty(dts[i].Rows[0][0].ToString())) { wb.Worksheets.Add(dts[i], dts[i].Rows[0][0].ToString()); } } wb.SaveAs(path + &#34;OrderDetails.xlsx&#34;); } DownloadFile(path + &#34;OrderDetails.xlsx&#34;); } public void DownloadFile(string path) { Response.Clear(); Response.ContentType = &#34;application/octet-stream&#34;; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=&#34; + Path.GetFileName(path)); Response.WriteFile(path); Response.End(); } private DataTable GetDataTableFromExcel() { DataTable dt = new DataTable(); string myfile_name = Path.GetFileName(fuUpload.PostedFile.FileName); fuUpload.SaveAs(Server.MapPath(&#34;~/Excel/&#34;) + myfile_name); string filePath = Server.MapPath(&#34;~/Excel/&#34;) + myfile_name; using (XLWorkbook workBook = new XLWorkbook(filePath)) { IXLWorksheet workSheet = workBook.Worksheet(1); bool firstRow = true; foreach (IXLRow row in workSheet.Rows()) { if (firstRow) { foreach (IXLCell cell in row.Cells()) { dt.Columns.Add(cell.Value.ToString()); } firstRow = false; } else { dt.Rows.Add(); int i = 0; foreach (IXLCell cell in row.Cells()) { dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString(); i++; } } } } return dt; }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Dim dt As DataTable = GetDataTableFromExcel() Dim dts As List(Of DataTable) = dt.AsEnumerable() _ .GroupBy(Function(row) row.Field(Of String)(&#34;Name&#34;)) _ .Select(Function(g) g.CopyToDataTable()).ToList() Dim path As String = Server.MapPath(&#34;~/Excel/&#34;) If Not Directory.Exists(path) Then Directory.CreateDirectory(path) End If Using wb As XLWorkbook = New XLWorkbook() For i As Integer = 0 To dts.Count - 1 If Not String.IsNullOrEmpty(dts(i).Rows(0)(0).ToString()) Then wb.Worksheets.Add(dts(i), dts(i).Rows(0)(0).ToString()) End If Next wb.SaveAs(path &amp; &#34;OrderDetails.xlsx&#34;) End Using DownloadFile(path &amp; &#34;OrderDetails.xlsx&#34;) End Sub Public Sub DownloadFile(ByVal path As String) Response.Clear() Response.ContentType = &#34;application/octet-stream&#34; Response.AddHeader(&#34;content-disposition&#34;, &#34;attachment;filename=&#34; &amp; System.IO.Path.GetFileName(path)) Response.WriteFile(path) Response.End() End Sub Private Function GetDataTableFromExcel() As DataTable Dim dt As DataTable = New DataTable() Dim myfile_name As String = Path.GetFileName(fuUpload.PostedFile.FileName) fuUpload.SaveAs(Server.MapPath(&#34;~/Excel/&#34;) &amp; myfile_name) Dim filePath As String = Server.MapPath(&#34;~/Excel/&#34;) &amp; myfile_name Using workBook As XLWorkbook = New XLWorkbook(filePath) Dim workSheet As IXLWorksheet = workBook.Worksheet(1) Dim firstRow As Boolean = True For Each row As IXLRow In workSheet.Rows() If firstRow Then For Each cell As IXLCell In row.Cells() dt.Columns.Add(cell.Value.ToString()) Next firstRow = False Else dt.Rows.Add() Dim i As Integer = 0 For Each cell As IXLCell In row.Cells() dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString() i += 1 Next End If Next End Using Return dt End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><strong><span style="text-decoration: underline;"><img src="https://i.imgur.com/KQECVH3.gif" alt="" width="365" height="231" /></span></strong></p>https://www.aspforums.net:443/Threads/569394/Download-Save-Excel-file-created-using-ClosedXML-on-client-machine-in-ASPNet/https://www.aspforums.net:443/Threads/569394/Download-Save-Excel-file-created-using-ClosedXML-on-client-machine-in-ASPNet/Mon, 29 Jul 2019 02:23:21 GMT