CODEWITHSUNDEEP
javascriptjquerycssexcelhtml-table
webpic
webpic

Reputation: 443

Export Html Table to Excel with CSS

I'm trying to export a HTML table to Excel by keeping table style. I have searched the web and found a few examples, but none of them work as expected. They have problems like CSS not working or headers not supported.

This is the code I have, but the file gets downloaded without an XLS extension.

$(function() {
  $("#btnExport").click(function(e) {
    window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
    e.preventDefault();
  });
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<div id="dvData">
  <table>
    <tr>
      <th>Column One</th>
      <th>Column Two</th>
      <th>Column Three</th>
    </tr>
    <tr>
      <td>row1 Col1</td>
      <td>row1 Col2</td>
      <td>row1 Col3</td>
    </tr>
    <tr>
      <td style="background-color: #ff0000">row2 Col1</td>
      <td>row2 Col2</td>
      <td>row2 Col3</td>
    </tr>
    <tr>
      <td>row3 Col1</td>
      <td>row3 Col2</td>
      <td><a href="http://www.jquery2dotnet.com/">http://www.jquery2dotnet.com/</a>
      </td>
    </tr>
  </table>
</div>

https://jsfiddle.net/lesson8/jWAJ7/

I used code from this link, but it didn't work: https://www.codeproject.com/Tips/755203/Export-HTML-table-to-Excel-With-CSS

table2excel.js plugin also not working https://www.jqueryscript.net/table/Export-Html-Table-To-Excel-Spreadsheet-using-jQuery-table2excel.html

Upvotes: 1

Views: 9051

Answers (2)

semicolonites
semicolonites

Reputation: 11

Export the HTML table in Excel file to offline view and more Excel editing work. This turns more tricky when we need the CSS of the table too. Here is the sample application demonstrating the Excel export. Put the below code in your Head part of the page.

<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>

Upvotes: -1

Niels040
Niels040

Reputation: 79

This is the code I use. it makes a Xls and gives a warning but it works and gets the data and css. Might be almost 2 years after you posted the question but I thought why not.

<a href="#" id="tests" onClick="javascript:fnExcelReport();">Download</a><br><br>
    
<table id="myTable">
        <tr  style="background-color: black; color: white; font-size: 10px;">
            <th>Name</th>
            <th>Last name</th>
            <th>age</th>
        </tr>
        <tr>
            <td>Bob</td>
            <td>John</td>
            <td>21</td>
       
        </tr>
    
    </table>
    
    <script src="//code.jquery.com/jquery-1.10.2.js"></script>
    <script>
        function fnExcelReport() {
            var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
            tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
    
            tab_text = tab_text + '<x:Name>Test Sheet</x:Name>';
    
            tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
            tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';
    
            tab_text = tab_text + "<table>";
            tab_text = tab_text + $('#myTable').html();
            tab_text = tab_text + '</table></body></html>';
    
            var data_type = 'data:application/vnd.ms-excel';
    
            var ua = window.navigator.userAgent;
            var msie = ua.indexOf("MSIE ");
    
            if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
                if (window.navigator.msSaveBlob) {
                    var blob = new Blob([tab_text], {
                        type: "application/csv;charset=utf-8;"
                    });
                    navigator.msSaveBlob(blob, 'Test file.xls');
                }
            } else {
                $('#tests').attr('href', data_type + ', ' + encodeURIComponent(tab_text));
                $('#tests').attr('download', 'Test file.xls');
            }
        }
    
   
    </script>

Upvotes: 3

Related Questions