JS export to excel is formatting numeric columns as text

Question

I am using a js function exporting few tables to tabs to a single Excel file, the problem is that the columns from the 2ed column and further, are numeric columns and they are exported as text, how can I set these columns data type to number by this code? I have tried to render these columns but with no success. Thanks in advance

var tablesToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,'
        , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
            + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
            + '<Styles>'
            + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
            + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
            + '</Styles>'
            + '{worksheets}</Workbook>'
        , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
        , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
        , 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 (tables, wsnames, wbname, appname) {
        var ctx = "";
        var workbookXML = "";
        var worksheetsXML = "";
        var rowsXML = "";

        for (var i = 0; i < tables.length; i++) {
            if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
            for (var j = 0; j < tables[i].rows.length; j++) {
                rowsXML += '<Row>'
                for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                    var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                    var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                    var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                    dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                    var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                    dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                    ctx = {
                        attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
                        , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String'
                        , data: (dataFormula) ? '' : dataValue
                        , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                    };
                    rowsXML += format(tmplCellXML, ctx);
                }
                rowsXML += '</Row>'
            }
            ctx = { rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
        }

        ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML };
        workbookXML = format(tmplWorkbookXML, ctx);

        console.log(workbookXML);

        var link = document.createElement("A");
        link.href = uri + base64(workbookXML);
        link.download = wbname || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
})();

<!DOCTYPE html>
<html>
<head>
    

    <script src="exportfewtabs.js"></script>

    <meta charset=utf-8 />
    <title>DataTables - JS Bin</title>

    <style>
    
    </style>
</head>
<body>
    <table id="tbl1" class="table2excel">
        <tr>
            <td>Product</td>
            <td>Price</td>
            <td>Available</td>
            <td>Count</td>
        </tr>
        <tr>
            <td>Bred</td>
            <td>
                1
            </td>
            <td>
                2
            </td>
            <td>
                3
            </td>
        </tr>
        <tr>
            <td>Butter</td>
            <td>
                4
            </td>
            <td>
                5
            </td>
            <td>
                6
            </td>
        </tr>
    </table>
    <hr>

    <table id="tbl2" class="table2excel">
        <tr>
            <td>Product</td>
            <td>Price</td>
            <td>Available</td>
            <td>Count</td>
        </tr>
        <tr>
            <td>Bred</td>
            <td>
                7
            </td>
            <td>
                8
            </td>
            <td>
                9
            </td>
        </tr>
        <tr>
            <td>Butter</td>
            <td>
                14
            </td>
            <td>
                15
            </td>
            <td>
                16
            </td>
        </tr>
    </table>


    <button onclick="tablesToExcel(['tbl1','tbl2'], ['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to Excel</button>
</body>
</html>

I have by adding data table plugin and from there tried to convert it but I guess I was not doing it right — refaelo1979, Jun 08 '21 at 13:30

score 1 · Answer 1 · answered Jun 08 '21 at 13:51

1

Check this: JsFiddle

Basically you must add this to every td

<td style='mso-number-format:"#,##0.00"'>100.00</td>

You can change the format...

Source

answered Jun 08 '21 at 13:51

S. Wasta

599
9
35

S. Wasta Thanks, do you know how can it be implemented to export few tables to tabs? – refaelo1979 Jun 08 '21 at 14:05
What do you mean "export few tables to tabs"? Visualize? With the code you can call tableToExcel() everytime you want... on click event instead of calling once, call two... Also take a look to jQueryTables. – S. Wasta Jun 08 '21 at 14:29
S. Wasta I meant in your code (one table), assuming there are multiple tables exported, table per each tab, how can it be implemented. – refaelo1979 Jun 08 '21 at 14:36

JS export to excel is formatting numeric columns as text

1 Answers1