Export table to excel with css formatting - gives alert message in excel - "The file format and extension of filename.xls don't match."

Question

I'm using the below code to generate the excel from the table with css formatting.

<script src="https://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
<script type="text/javascript">
    var tableToExcel = (function () {
        // Define your style class template.
        var style = "<style>.green { background-color: green; }</style>";
        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]-->' + style + '</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))  
            let a = document.createElement('a');
        a.href = uri + base64(format(template, ctx));
        a.download = this.fileName + '.xls';
        a.click();
        }
    })()
</script>
<style type="text/css">
    .green
    {
        background-color: green;
    }
</style>    
    <input type="button" onclick="tableToExcel('testTable', 'W3CExampleTable')" value="Export to Excel" />
    <table id="testTable" summary="Code page support in different versions of MS Windows."
        rules="groups" frame="hsides" border="2">
    <caption>
        CODE-PAGE SUPPORT IN MICROSOFT WINDOWS
    </caption>
    <colgroup align="center"></colgroup>
    <colgroup align="left"></colgroup>
    <colgroup span="2" align="center"></colgroup>
    <colgroup span="3" align="center"></colgroup>
    <thead valign="top">
        <tr>
            <th>Code-Page<br />ID</th>
            <th>Name</th>
            <th>ACP</th>
            <th>OEMCP</th>
            <th>Windows<br />NT 3.1</th>
            <th>Windows<br />NT 3.51</th>
            <th>Windows<br />95</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1200</td>
            <td style="background-color: #00f; color: #fff">Unicode (BMP of ISO/IEC-10646)</td>
            <td></td>
            <td></td>
            <td>X</td>
            <td>X</td>
            <td>*</td>
        </tr>
        <tr>
            <td>1250</td>
            <td style="font-weight: bold">
                <a href="http://www.jquery2dotnet.com/">http://www.jquery2dotnet.com/</a>
            </td>
            <td>X</td>
            <td></td>
            <td>X</td>
            <td>X</td>
            <td>X</td>
        </tr>
        <tr>
            <td class="green">1255</td>
            <td>Hebrew</td>
            <td>X</td>
            <td></td>
            <td></td>
            <td></td>
            <td>X</td>
        </tr>
        <tr>
            <td>437</td>
            <td>MS-DOS United States</td>
            <td></td>
            <td>X</td>
            <td>X</td>
            <td>X</td>
            <td>X</td>
        </tr>
        <tr>
            <td>708</td>
            <td>Arabic (ASMO 708)</td>
            <td></td>
            <td>X</td>
            <td></td>
            <td></td>
            <td>X</td>
        </tr>
        <tr>
            <td>709</td>
            <td>Arabic (ASMO 449+, BCON V4)</td>
            <td></td>
            <td>X</td>
            <td></td>
            <td></td>
            <td>X</td>
        </tr>
        <tr>
            <td>710</td>
            <td>Arabic (Transparent Arabic)</td>
            <td></td>
            <td>X</td>
            <td></td>
            <td></td>
            <td>X</td>
        </tr>
    </tbody>
</table>

Above code is generating the excel file.

When opening the excel file, I'm getting the following message.

"The file format and extension of filename.xls don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"

Anybody have solution to avoid the message which is displaying as above from code.

score 0 · Answer 1 · answered Dec 31 '20 at 07:19

0

You'll just need to change this line

a.download = this.fileName + '.xls';

To this

 a.download = this.fileName + '.xlsx';

xlsx is the file format for the latest office.

answered Dec 31 '20 at 07:19

Vaulient

335
1
4
14

I tried the above solution already and got the following alert message - "Excel cannot open the file filename.xlsx because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." – Rajesh Kumar Dec 31 '20 at 08:02
If that is the case, I feel it would be better if you would make use of a readily made plugin .This answer should really help you out and it's not much work to implement. https://stackoverflow.com/a/43488998/11117237 – Vaulient Dec 31 '20 at 08:22

Export table to excel with css formatting - gives alert message in excel - "The file format and extension of filename.xls don't match."

1 Answers1