Export HTML Table to Excel Using ASP.NET

Export html table to excel file in asp.net core

You could refer the following method to export the table to the excel file.

Without using Client-Side Library or Server-Side package, and use the following code to export html table to excel.

 @using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>

 <input type="button" onclick="tableToExcel('tblExport', 'W3C Example Table')" value="Export to Excel">
 @section scripts{ 
     <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
     <script type="text/javascript">
         var tableToExcel = (function () {
             var uri = 'data:application/vnd.ms-excel;base64,'
                 , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
                 , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
                 , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
             return function (table, name) {
                 if (!table.nodeType) table = document.getElementById(table)
                 var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
                 window.location.href = uri + base64(format(template, ctx))
             }
         })()
     </script>
 }

[Note] By using this method, it will export a .xls excel file. You will get a Warning message from Microsoft Excel application when you try to open the generated Excel file. This Warning is shown because the generated file is not a valid Excel format as the plugin simply exports the HTML content to an Excel file.

The excel content like this:

Sample Image

using FileSaver.js plugin and TableExport plugin to export html table to excel.

Right click the wwwroot folder, then click Add and Client-Side Library..., enter FileSaver.js and click the Install button to install the library. Do the same with the TableExport plugin.

Then, using the following code export data.

 @using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>
 @section scripts{
     <link rel="stylesheet" href="https://netdna.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
     <link rel="stylesheet" href="~/lib/TableExport/css/tableexport.min.css" />
     <script src="~/lib/jquery/dist/jquery.min.js"></script>
     <script src="~/js/Blob.js"></script>
     <script src="~/js/xls.core.min.js"></script>
     <script src="~/lib/FileSaver.js/FileSaver.min.js"></script>
     <script src="~/lib/TableExport/js/tableexport.min.js"></script>
     <script type="text/javascript">
         $(function () {
             var tables = $("#tblExport").tableExport({
                 bootstrap: true,
                 headings: true,
                 footers: true,
                 formats: ["xlsx", "xls", "csv", "txt"],
                 fileName: "MyExcel",
                 position: "top",
                 ignoreRows: null,
                 ignoreCols: null,
                 ignoreCSS: ".tableexport-ignore",
                 emptyCSS: ".tableexport-empty",
                 trimWhitespace: true
             });
         });
     </script> 
 }

You can create a Blob.js and xls.core.min.js file, and add content from the js content in these links: Blob.js and xls.core.min.js.

The web page looks like this:

Sample Image

By using this method, you can export the table to excel, csv and txt file.

using ClosedXML package. This is an server-side method.

Install the ClosedXML package via NuGet Package Manager.

Create a action to export the DataTable to excel, code as below:

 //index page: display the table data.
 public IActionResult ExportExcel()
 {
     var custTable = CreateDataTable();
     return View(custTable);
 }
 public IActionResult ExportDataTabletoExcel()
 {
     var dt = CreateDataTable();
     using (XLWorkbook wb = new XLWorkbook())
     {
         wb.Worksheets.Add(dt);
         using (MemoryStream stream = new MemoryStream())
         {
             wb.SaveAs(stream);
             return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
         }
     }
 }
 public DataTable CreateDataTable()
 {
     // Create a new DataTable.    
     DataTable custTable = new DataTable("Customers");
     DataColumn dtColumn;
     DataRow myDataRow;
     ... //add columns and rows.
     return custTable;
 }

Code in the view page:

 @using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>

 <a href='@Url.Action("ExportDataTabletoExcel","Home")'> export to excel</a>

By using this method, it will generate a .xlsx file, the content as below:

Sample Image

Export HTML Table to Excel using ASP.NET

You want Export HTML table (Not Gridview) customized structure and data to Excel using ASP.NET.

Try the following Approach

Provide the ID and add runat="server" attribute
<table id="tbl" runat="server" >

Add the following code

Response.ContentType = "application/x-msexcel"; 
Response.AddHeader("Content-Disposition", "attachment;
filename=ExcelFile.xls");
Response.ContentEncoding = Encoding.UTF8; 
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
tbl.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();

How to download an Html table into excel file using asp.net C#

So I figured out two solutions, the first one is redirecting from my page to a generic handler (send the ByteStream) and in the handler do something like user3851829 suggested below

What I did was something similar, only I took the content from a repeater and then rendered it into the stringBuilder as such:

protected void ExportToXLS(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=Notifications.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter tw = new HtmlTextWriter(sw);

        repeaterID.DataSource = Session["SomeDataFromSession"] as List<SomeObject>;
        repeaterID.DataBind();

        rptNotifications.RenderControl(tw);

        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

Both answers are working !

Save html table to Excel

You can import HTML table to excel using Aspose.Cells. The data from HTML table will be imported, however, you will be required to take care of the table formatting.

//table.txt contains HTML table
string contents = File.ReadAllText(@"C:\data\table.txt");

using (var stream = new MemoryStream(Encoding.ASCII.GetBytes(contents)))
{
    var loadOptions = new LoadOptions(LoadFormat.Html)
    {
        ConvertNumericData = false
    };

    //Load the HTML, this will import all the HTML table data to Excel file
    var workbook = new Workbook(stream, loadOptions);

    //Adding a new List Object to the worksheet, after this you can format the table
    Aspose.Cells.Tables.ListObject listObject = workbook.Worksheets[0].ListObjects[workbook.Worksheets[0].ListObjects.Add("A3", "E23", true)];

    //Adding predefined Style to the table
    listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium10;

    //Save using Response
    workbook.Save(this.Response, "Customer.xls", ContentDisposition.Inline, new XlsSaveOptions());
}

Hope this helps.

ASP.NET MVC export html table to excel not working

In essence it looks like you're merely dumping the contents into a file and just renaming it to an XLSX. However, that extension follows a specific XML-based schema, and that's why it doesn't play well.

You have a few options:

Find a library that can do this for you - initial searches list a few but they're often fickle little beings.
Use something like HTML Agility Pack to parse the HTML into a usable format and write it into an excel file. You might have to create an excel file manually, possibly using the Office Interop stuff.
If the excel format itself isn't that much of an issue, you could choose to write a CSV file instead (and can be opened by excel), using CSV Helper - but you'd still have to parse the HTML.

Export HTML Table to Excel Using ASP.NET

Export html table to excel file in asp.net core

Export HTML Table to Excel using ASP.NET

How to download an Html table into excel file using asp.net C#

Save html table to Excel

ASP.NET MVC export html table to excel not working

Related Topics

Leave a reply