Search code examples
javascriptjsonnode.jsmeteorjs-xlsx

How do you make js_xlsx save all empty headers?

I am working on a Meteor App, using the Node.js package called "js_xlsx" from "SheetJS", made by "SheetJSDev". I am using this package to transform an uploaded Excel sheet into JSON in the back-end, so I can store it in my MongoDB and use it to do some fancy classified stuff in the front-end. There's just one problem. The package skips columns that have an empty header, except for the right-most column with an empty header. I already figured out that the package processes columns with an empty header just fine, but because JSON keys need to be unique, it only saves the last column with an empty header in the JSON object.

Examples:

With all headers filled:

enter image description here

enter image description here

As you can see, all works well when all headers are filled.

With the first and 2nd last header empty:

enter image description here

enter image description here

Here you see that the 2nd last column gets put in the front, with an "undefined" header and the first column is not even in there (because it gets overwritten by the 2nd last column, because they both have "undefined" as their keys).

With the first 3 columns and the 2nd last header empty:

enter image description here

enter image description here

Same stuff as with the 3rd and 4th image. Only the last column with empty header will make it into the JSON object.

Now, I have scuttled through the sourcecode and searched the GitHub page and even asked Google to help me out, without much avail.

What I want to know is:

How do you make js_xlsx process all columns with empty headers?

The link to the GitHub page for the package is:

https://github.com/SheetJS/js-xlsx

Any help is much appreciated!

Solution

  • I suspect your problem is that, by default, the SheetJS (xlsx.js) sheet_to_json function completely skips empty cells. (there not being a universal "How to deal with empty cell"... they defaulted, unfortunately (imho), to simply skipping the entire cell... leaving your JSON in shambles.) Given they use the headers as keys, as you said, this might be keeping the entire column from getting generated.

    You can change this behavior by specifying a defval value in the options object that can be optionally passed to that function.

    For example:

    filledOutJSON = XLSX.utils.sheet_to_json(worksheet,{"defval":"whatever_you_want"});

    Note, you can specify the empty string, "", if you want.

    You can specify other options, as specified here: https://github.com/SheetJS/js-xlsx#json