Javascript – Importing and exporting excel files to javascript array

exceljavascript

I'm currently looking for a fast and efficient way to import an excel file into a javascript array, and export it also. I've tried the suggestion here:

https://stackoverflow.com/a/27474951/4346569

But the problem is that checking the console, it shows that the output are objects, so they're JSON objects? Is it because my excel file has more than 1 column? In that case, how would I be able to do this :(?

I also need a way to do the same thing, but the opposite way. Can someone advise me on that?

Thank you!

Edit: I figured importing out but now I'm stuck on how to be able to output to excel. I would need to have some cells be colored as specified by a rule. Can anyone give me some guides for this?

Best Answer

This is an example of more flexible gridExportToExcel() function, where you can color and format cells. Probably, you need to change it before you can run it directly.

All data stored in the grid variable with the following columns:

  • grid.data - array of objects
  • grid.columns - array of column objects
    • type - "numeric" - for numeric datatypes
    • kindid - subtype, like "money" or date

THis code uses some functions of Lodash library.

$(function() {
    window.gridExportToExcel = (function () {
        var a = document.createElement("a");
        document.body.appendChild(a);
        a.style = "display: none";
        return function () {
            var s = gridGenerateExcel();
            var blob = new Blob([s], { type: 'application/vnd.ms-excel' });
            url = window.URL.createObjectURL(blob);
            a.href = url;
            a.download = page.pagename+".xls";
            a.click();
            window.URL.revokeObjectURL(url);
        };
    }());
});

function gridGenerateExcel(title) { 
    if (_.isUndefined(title)) title = "Sheet1";
    var s = '<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> \
    <meta charset="utf-8" /> \
    <!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets> \
      <x:ExcelWorksheet><x:Name>' + title + '</x:Name><x:WorksheetOptions><x:DisplayGridlines/>     </x:WorksheetOptions> \
    </x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>';

    s += '<colgroups>';
    grid.columns.forEach(function (col) {
        s += '<col style="width: '+col.width+'px"></col>';
    });

    s += '<thead><tr>';
    grid.columns.forEach(function (col) {
        s += '<th style="background-color: #E5E5E5; border: 1px solid black;">' + col.name + '</th>';
    });

    s += '<tbody>';
    _.forEach(grid.data,function(d){
        s += '<tr>';

        grid.columns.forEach(function (col) {
            var value = d[col.id];

            s += '<td ';
            if (col.kindid == "money") {
               s += "style = 'mso-number-format:\"\\#\\,\\#\\#0\\\\ _р_\\.\";white-space:normal;'"; 
            } else if (col.type == "numeric") s += "";//" style = 'mso-number-format:\"\\@\";'";
            else if (col.kindid == "date") s += " style='mso-number-format:\"Short Date\";'";
            else s += " style='mso-number-format:\"\\@\";'";
            s += '>';
            if(_.isUndefined(value) || _.isNull(value)) { 
                s += ''; 
            } else if (col.kindid == "date") {
                s += moment(value).format('DD.MM.YY');
            } else if (col.kindid == "money") {
                s += formatMoney(value.toFixed(2));
            } else if (col.type == "numeric") {
                s += value.toString(); 
            } else s += d[col.id];
        });
    });
    s += '</table></body></html>';

    return s;
}
Related Topic