3

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?

Johnti
  • 133
  • 2
  • 2
  • 8
  • have a look at d3, it has multiple csv parsers. the other option is jquery csv – Dinesh Dec 16 '14 at 18:45
  • Thanks, but how would I convert it back to a javascript array from jquery csv? – Johnti Dec 16 '14 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 (https://github.com/SheetJS/js-xls). – Prusse Dec 16 '14 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 Dec 16 '14 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 Dec 16 '14 at 20:42
  • Did you look at [http://stackoverflow.com/a/20847865/783219](http://stackoverflow.com/a/20847865/783219)? – Prusse Dec 18 '14 at 15:07
  • You can contact me at agershun at gmail, and I will try to help you. – agershun Dec 22 '14 at 08:50
  • @agershun I've emailed you! – Johnti Dec 22 '14 at 18:39

2 Answers2

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;
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
agershun
  • 4,077
  • 38
  • 41
  • 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. – Johnti Dec 23 '14 at 15:49
  • 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 Dec 23 '14 at 16:03
  • 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 */ })}); – agershun Dec 23 '14 at 16:06
  • Do you know what's the best way for me to convert it from array form like above into the objects list like yours? – Johnti Dec 23 '14 at 20:03
  • var data = []; arr.forEach(function(row){var r = {}; row.forEach(function(v,idx){ r[idx]=v}; data.push(r)});var columns = []; for(var i=0;i – agershun Dec 23 '14 at 20:20
  • Sorry but is there a way to make this support multiple sheets? For sheet1 with name XXX I have an array called dataA for it, for sheet2 with name YYY I have an array called dataB for it. – Johnti Dec 29 '14 at 16:54
  • I have not tried, but I think if you add inside tag multiple worksheets covered with tags, you will receive multiple sheets. I can try it tomorrow. – agershun Dec 29 '14 at 17:46
  • I've ran the array through the little code you posted above to convert an array to object and it seems to be fine. However, when I use that object with the export to excel, it just outputs an excel 5 columns of undefined. – Johnti Dec 29 '14 at 18:25
  • This is what I have right now: (main function is just the function that calls onto the other functions, branch is just the name of the output file) http://paste.ofcode.org/SvfYGF46iyzn3TRsMaz2RZ – Johnti Dec 29 '14 at 19:21
  • Can you also help me with adding on multiple worksheets? I really appreciate all the help! – Johnti Dec 29 '14 at 19:33
  • This is a single sheet working version (sorry, it not to accurate, but it works) http://jsfiddle.net/agershun/trvo4uLz/2/. I will try with multiple worksheets... – agershun Dec 29 '14 at 19:55
  • Thank you. I made it work and the object turns out ok, but the problem is that nothing downloads :( – Johnti Dec 29 '14 at 20:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67872/discussion-between-agershun-and-johnti). – agershun Dec 29 '14 at 20:42
0

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

Rahil Lakhani
  • 412
  • 4
  • 7