Sheetjs: sheet_to_json doesn't output anything for a blank cell

[marcelosomers]

It seems like using sheet_to_json doesn't handle blank cells very well. I want to keep them in my script (I'm parsing an xlsx file and outputting a csv), but a blank cell outputs an array like:

[ 'foo',
  'bar',
  ,      // the blank value
  'another value' ]

It'd be nice if you got a blank string, since this library seems to recognize the blank value. Would this be possible?

[SheetJSDev]

The code as it stands now won't generate an empty string. The simplest way is to just add a post-processing loop to your code:

for(var i = 0; i != json.length; ++i) for(var j = 0; j != json[i].length; ++j) if(typeof json[i][j] === 'undefined') json[i][j] = "";

As for what it "should" be, why not the number 0? an empty string would be weird in a case like:

[
  [1, 2, 3],
  [4, 5, 6],
  [7, ? ,9]
]

I'd be amenable to adding an option (maybe something called "default" in the options object?), and the changes are relatively simple: https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js#L5293

            val = sheet[cols[C] + rr];
            if(val === undefined || val.t === undefined) continue;

In the bottom half of sheet_to_json, we walk in row-major order across the worksheet. The first line attempts to find if the sheet has a cell at the prescribed address. If the cell doesn't exist or if its type is not set, then we skip the cell. Instead of just continuing, that second line should set the cell to the empty string:

            if(val === undefined || val.t === undefined) { row[hdr[C]] = ""; continue; }

[marcelosomers]

Wow, really appreciate the lightning quick response! Your for loop snippet worked perfectly - I'm probably not experienced enough to comment on the intricacies of what it "should" be, but thank you!

[tmthyjames]

Changing line 11549 from
if(val === undefined || val.t === undefined) continue;
to
if(val === undefined || val.t === undefined) { row[hdr[C]] = ""; continue; }
fixed the issue for me.

[IgorShch]

@SheetJSDev Thank you for the suggestion. It fixed the issue for me too.

It would be really nice to turn this into an option.

Thanks a lot.

[bhaskarsontakke]

Great work! You saved my day. Thanks alot :)

[prasanthvel]

Me too having same problem.

 var XLSX = require('xlsx');
   var fs = require('fs');
   var workbook = XLSX.readFile('Sports.xlsx');
   var sheetNames = workbook.SheetNames;

 for(var i = 0; i<sheetNames.length; i++) {
//console.log(sheetNames[i]);
name = sheetNames[i];
var sheet = workbook.Sheets[name];
fs.appendFile("test.json", "[", 'utf8');
sheet = XLSX.utils.sheet_to_json(sheet);
var sheetNames = workbook.SheetNames;
var sum = 0;
for (var cell in sheet) {
//  console.log(sheet[cell]);
    data = sheet[cell];

    const content = JSON.stringify(data);
    fs.appendFile("test.json", content, 'utf8');
sum += 1;
}
fs.appendFile("test.json", "]", 'utf8');
console.log(sum+" Rows readed from sheet "+name);
//console.log(sum);
}
//console.log("File Saved!!")`

How to rectify that to read the empty cell, like
[{custName : "Ben", custCity:"city1", phno:123}, {custName : "Ken", custCity:"city2", phno:}]

[hmnshmshr]

Solution 1 .Condition "if(h===undefined)continue;" in "xlsx.core.min.js" comment it out.

or do it properly...

Solution 2 . By passing Condition extra param while running this XLSX.utils.sheet_to_json(wb.Sheets[name] , {blankCell : false}). add a condition on line no. 19150 "if(defval === undefined && blankCell) continue;" in file xlsx.js etc..

[varunchandran333]

@hmnshmshr I did this but no change?? can you please ellaborate?/

[yang5664]

you don't need to change anythings, just in function pass opt defval:'' that's it.

XLSX.utils.sheet_to_json(workbook.Sheets[sheet],{defval:""})

[wuyuedefeng]

please try JSON.parse(JSON.stringify(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]))

[ssnadiminti]

@yang5664 That worked like a charm. Thank you

[pns09]

@yang5664. Thank you so much. This worked for me!

[magarwal19]

thank you @yang5664 . It helped me to solve the problem in flick !

[awaisawanbscs]

we apply condition but it doesnot work.
please if you have another solution guide it
thanks.
XLSX.utils.sheet_to_json(workbook.Sheets[sheet],{defval:""})

[awaisawanbscs]

thank you @yang5664 . It helped me to solve the problem in flick !

if(val === undefined || val.t === undefined) { row[hdr[C]] = ""; continue; }

we can use this but cannot work please if you have another solution then guide me
thanks

[awaisawanbscs]

for (C = r.s.c; C <= r.e.c; ++C) {
        cols[C] = encode_col(C);
        val = sheet[cols[C] + rr];
        switch (header) {
            case 1: hdr[C] = C; break;
            case 2: hdr[C] = cols[C]; break;
            case 3: hdr[C] = o.header[C - r.s.c]; break;
            default:
              //  if (val === undefined) continue;

if (val === undefined || val.t === undefined) { row[hdr[C]] = ""; continue; }

              //  hdr[C] = format_cell(val);
        }
    }

we can change the line but doenot work