Sheetjs: JSON array to xlsx file

[gotero89]

I noticed section of writing in the documentation, but assumes workbook object is already created.
What's the format of this workbook object or how can I generate from a JSON array?

I have the JSON array and just want to convert it to a spreadsheet file

[mhodes]

Gabriel,

I am doing something similar, but I also have column and cell format
information in my JSON object. Below is the module I use to create the
excel file.

ExcelExport = (function () {

    var data;



    var load = function (dataset) {

        if (!dataset) return;

        data = dataset;

        modalPopup.load({ title: 'Report Export', msg: 'Enter filename:',
inputType: 'text', buttons: [{ descr: 'OK', value: 1 }, { descr: 'Cancel',
value: 0 }], callback: export2 });

    }



    var export2 = function(btn, val) {

        if (btn == 0 || val == '') return;



        if (/[^a-z0-9\ \-\_]/gi.test(val)){

            modalPopup.load({msg: 'Filename is invalid.  The filename can
only contain letters, numbers, spaces, hyphens and underscores.'});

            return;

        }



        var wsName = 'Physician Metrix Report';

        var wb = {};

        wb.Sheets = {};

        wb.Props = {};

        wb.SheetNames = [];



        ws = {};

        range = { 's': { 'c': 0, 'r': 0 }, 'e': { 'c': 0, 'r': 0} };



        loadReportTitles();

        var dataStartRow = loadReportHeaders();

        loadReportData(dataStartRow);

        setColumnWidth();



        ws['!ref'] = XLSX.utils.encode_range(range);

        wb.SheetNames.push(wsName);

        wb.Sheets[wsName] = ws;



        var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type:
'binary' });

        saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream"
}), val + '.xlsx');



        range = {};

        ws = {};

    }



    function s2ab(s) {

        var buf = new ArrayBuffer(s.length);

        var view = new Uint8Array(buf);

        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) &
0xFF;

        return buf;

    }



    function loadReportTitles() {

        for (var R = 0; R != data.titles.length; ++R) {

            if (range.e.r < R) { range.e.r = R; }

            var C = 0;

            var cell = { 'v': data.titles[R] };

            if (cell.v == null) continue;



            var cell_ref = XLSX.utils.encode_cell({ 'c': C, 'r': R });

            if (typeof cell.v === 'number') cell.t = 'n';

            else if (typeof cell.v === 'boolean') cell.t = 'b';

            else cell.t = 's';



            ws[cell_ref] = cell;

        }

    }



    function loadReportHeaders() {

        var startRow = data.titles.length + 2

        var rtn = startRow;

        for (var R = 0; R < data.headers.length; R++) {

            if (range.e.r < R + startRow) { range.e.r = R + startRow; }

            var colOffset = 0;



            for (var C = 0; C < data.headers[R].length; C++) {

                if (range.e.c < C + colOffset) { range.e.c = C + colOffset;
}



                var cell = { 'v': data.headers[R][C].text };

                if (cell.v == null) continue;



                var cell_ref = XLSX.utils.encode_cell({ 'c': C + colOffset,
'r': R + startRow });

                if (typeof cell.v === 'number') cell.t = 'n';

                else if (typeof cell.v === 'boolean') cell.t = 'b';

                else cell.t = 's';

                ws[cell_ref] = cell;

                if (data.headers[R][C].span > 1) colOffset +=
(data.headers[R][C].span - 1)

            }

            rtn = startRow + R;

        }

        return rtn + 1

    }



    function loadReportData(startRow) {

        for (var R = 0; R < data.data.length; R++) {

            if (range.e.r < R + startRow) { range.e.r = R + startRow; }

            for (var C = 0; C < data.data[R].length; C++) {

                if (range.e.c < C) { range.e.c = C; }

                var v = (isNaN(data.data[R][C]) || data.data[R][C] == '' ||
data.data[R][C] == ' ') ? data.data[R][C] : parseFloat(data.data[R][C]);



                var cell = { 'v': v };

                if (cell.v == null) continue;



                if (typeof cell.v === 'number') {

                    cell.t = 'n';

                    cell.z = numberFormat(R, C);

                }

                else if (typeof cell.v === 'boolean') cell.t = 'b';

                else cell.t = 's';



                ws[XLSX.utils.encode_cell({ 'c': C, 'r': R + startRow })] =
cell;

            }

        }

    }



    function numberFormat(R, C) {

        var fmt = (data.format == 'column' || data.formats.length - 1 < R)
? data.columns[C].format

            : data.formats[R].length - 1 < C ? data.columns[C].format

            : (data.formats[R][C] == 'undefined' || data.formats[R][C] == '')
? data.formats[R][C]

            : data.columns[C].format;



        switch (fmt) {

            case 'G':

                return '#.#';

            case 'C0':

            case 'N0':

                return '#,##0 ;(#,##0)';

            case 'N1':

                return '#,##0.0;(#,##0.0)';

            case 'N2':

                return '#,##0.00;(#,##0.00)';

            case 'N8':

                return '#,##0.00000000;(#,##0.00000000)';

            case 'P0':

                return '0%';

            case 'P1':

                return '0.0%';

            case 'P2':

                return '0.00%';

            default:

                return '#,##0 ;(#,##0)';

        }

    }



    function dateString() {

        var today = new Date();

        var str = ' - ' + today.getFullYear().toString()

        str += (today.getMonth() < 9) ? '0' + (today.getMonth() + 1) :
today.getMonth() + 1;

        str += (today.getDate() < 10) ? '0' + today.getDate() :
today.getDate();

        str += (today.getHours() < 10) ? ':0' + today.getHours() : '-' +
today.getHours();

        str += (today.getMinutes() < 10) ? '0' + today.getMinutes() :
today.getMinutes();

        str += (today.getSeconds() < 10) ? '0' + today.getSeconds() :
today.getSeconds();



        return str;

    }



    function formatString(format) {

        var t = format.substring(0, 1);

        if (!(t == 'N' || t == 'C' || t == 'P')) return '';



        var baseFmt = '#,##0'



        var d = parseInt(format.substring(1, 20));

        if (d > 0) baseFmt += '.';

        for (var i = 1; i < d; i++) {

            baseFmt += '0';

        }



        if (t == 'P') {

            baseFmt += '%';

            return baseFmt + ';-' + baseFmt + ';-';

        }

        else if (t == 'C') {

            return '$ ' + baseFmt + '_;($ ' + baseFmt + ');-';

        }

        else {

            return baseFmt + '_;(' + baseFmt + ');-';

        }

    }





    var setColumnWidth = function() {

        var wsCols = [];

        for (var i = 0; i < data.columns.length; i++) {

            wsCols.push({ "wch": parseInt(data.columns[i].width) });

        }

        ws['!cols'] = wsCols

    }



    return {

        load: load

    }



})(jQuery);

[reviewher]

XLSX.utils.aoa_to_sheet will take your array and produce a worksheet object, then you can lift it to a workbook:

function sheet_to_workbook(sheet/*:Worksheet*/, opts)/*:Workbook*/ {
    var n = opts && opts.sheet ? opts.sheet : "Sheet1";
    var sheets = {}; sheets[n] = sheet;
    return { SheetNames: [n], Sheets: sheets };
}

function aoa_to_workbook(data/*:Array<Array<any> >*/, opts)/*:Workbook*/ {
    return sheet_to_workbook(XLSX.utils.aoa_to_sheet(data, opts), opts);
}

var wb = aoa_to_workbook(
[
  ["a","b","c"],
  [ 1 , 2 , 3 ]
]
); // wb will be a workbook with one sheet aligning with the data
XLSX.writeFile(wb, "test.xlsx"); // save to test.xlsx