Cannot create new workbook when exporting json to xlsx

[barneyliu708]

I am trying to export an json object to xlsx. First I try the package sheetjs, everything is good but it does not support styling in the excel so then I try to use this package js-xlsx-style. However when I try to create an new workbook object using XLSX.utils.book_new(), it throws an error, saying "TypeError: XLSX.utils.book_new is not a function".

So my question is that, how could I create the workbook object and export my json object into excel?

[Khanji]

It seems that a lot of functions that are available to us with sheetjs, is not available for xlsx-style. A workaround can be to use sheetjs to create the objects needed to write the xlsx file. And then use the xlsx-style to actually write the file. Only problem I found so far, is that I am not able anymore to set the height of the row with this workaround.

[jugaltheshah]

I don't understand - so how does one initialize a new workbook with this project? Is creating a wb object with SheetJS the official way to do it? Or parsing an empty workbook and using that?

The aforementioned seem like clunky solutions; Seems like I'm missing something. I have data in JS objects with which i'd like to create a workbook, what's the accepted way to do this?

[Amerzel]

Really strange that this isn't documented or built in.

[pietersv]

I concur the docs could be expanded. The root project https://github.com/SheetJS/js-xlsx defines an object structure which it calls the Common Spreadsheet Framework. it is open and can represent data for a wide variety of workbooks.

There are a number of examples in the XLSX test files, one is below

var workbook = {
      SheetNames : ["Sheet1"],
      Sheets: {
        "Sheet1": {
          "B2": {v: "Top left", s: { border: { top: { style: 'medium', color: { rgb: "FFFFAA00"}}, left: { style: 'medium', color: { rgb: "FFFFAA00"}} }}},
          "C2": {v: "Top right", s: { border: { top: { style: 'medium', color: { rgb: "FFFFAA00"}}, right: { style: 'medium', color: { rgb: "FFFFAA00"}} }}},
          "B3": {v: "Bottom left", s: { border: { bottom: { style: 'medium', color: { rgb: "FFFFAA00"}}, left: { style: 'medium', color: { rgb: "FFFFAA00"}} }}},
          "C3": {v: "", s: { border: { bottom: { style: 'medium', color: { rgb: "FFFFAA00"}}, right: { style: 'medium', color: { rgb: "FFFFAA00"}} }}},
          "!ref":"B2:C3"
        }
      }
    };

    // write the file and read it back...
    XLSX.writeFile(workbook, OUTFILE, {bookType: 'xlsx', bookSST: false});
    var workbook2 = XLSX.readFile(OUTFILE, {cellStyles: true});
    assert(basicallyEquals(workbook.Sheets, workbook2.Sheets));

For authoring, there is a convenience project https://github.com/protobi/workbook that simplifies generating this workbook structure from arrays of arrays of cells.

This styles branch merely aimed to expand the read/write ability for the styles ( s:) attribute, but expanding the docs would be a great way to contribute.