html asp.net-core html-table export export-to-excel

Export html table to excel file in asp.net core

I want to convert a html table of a view to excel format file. somthing like that:

@model DataTable
<table class="table table-hover table-responsive  table-striped">
<thead>
    <tr >
        @foreach (DataColumn col in Model.Columns)
        {
            <th class="border-white">@col.Caption</th>
        }
    </tr>

</thead>
<tbody>
    @foreach (DataRow row in Model.Rows)
    {
        <tr>
            @foreach (DataColumn col in Model.Columns)
            {
                <td class="border-white">@row[col.ColumnName]</td>
            }
        </tr>
    }

</tbody>

I searched in web, but I don't find anything. Information about this issue limit to export a SQL table or a model class to excel file. Can anyone help me, how I export html table to excel?

Solution

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:

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:

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: