Importing and exporting excel files to javascript array

Question

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?

have a look at d3, it has multiple csv parsers. the other option is jquery csv — Dinesh
– Dinesh, Commented Dec 16, 2014 at 18:45
Thanks, but how would I convert it back to a javascript array from jquery csv? — Johnti
– Johnti, Commented Dec 16, 2014 at 18:51
I haven't done this, just happened that a coworker of mine did share this with me today. You may want to have a look at it (github.com/SheetJS/js-xls). — Prusse
– Prusse, Commented Dec 16, 2014 at 19:55
Do a console.log('parsed data', data) and inspect the data structure. It may give you better hints of what are you getting. — Prusse
– Prusse, Commented Dec 16, 2014 at 20:03
I have gotten it to convert to a multidimensional array but now I'm trying to do the opposite thing, which is to output into an excel file. Some of the cells will need to be filled with different colors, while some will need to have text bolded, colored, etc. Do you happen to know how I can achieve this? — Johnti
– Johnti, Commented Dec 16, 2014 at 20:42

Rahil Lakhani · Accepted Answer · 2018-01-06 12:33:24Z

1

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

answered Jan 6, 2018 at 12:33

Rahil Lakhani

4184 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2017-03-19 22:01:33Z

0

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

edited Mar 19, 2017 at 22:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 23, 2014 at 8:19

agershun

4,10742 silver badges42 bronze badges

13 Comments

Johnti Over a year ago

Thank you so much for the reply. Can you give me an example of the grid variable? For example if I have an array like [[a,b,c],[d,e,f],[g,h,i]], what should I put in in order to get the grid variable? And if I want to format cells individually based on the cell location? IE A1 is bolded, B2 is not, etc.

agershun Over a year ago

var grid = {data: [{a:1,b:"one"},{a:2,b:"two"}], columns:[{id:'a', kindid:'int',type:'number'},{id:'b',kindid:'string',type:'string'}]};

agershun Over a year ago

TO color specific cells, you need to check x and y variables inside loops, for example: grid.data.forEach(function(d,y){ grid.columns.forEach(function(col,x} { if(x==3 && y == 4} /* change style */ })});

Johnti Over a year ago

Do you know what's the best way for me to convert it from array form like above into the objects list like yours?

agershun Over a year ago

|

Collectives™ on Stack Overflow

Importing and exporting excel files to javascript array

2 Answers 2

Comments

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related