'Importing and exporting excel files to javascript array

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?

javascript excel

Solution 1:^[1]

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

Solution 2:^[2]

https://www.npmjs.com/package/xlsx

here try this its quiet efficient

var workbook = XLSX.read(data, {
        type: rABS ? 'binary' : 'array',
        WTF: 1,
        header: 1 });
    console.log(workbook.Strings);

this is something i used to get array of objects

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	marc_s
Solution 2	Rahil Lakhani

'Importing and exporting excel files to javascript array

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]