sheet_to_json doesn't output anything for a blank cell #159

New Issue

2015-01-11T05:09:58Z

marcelosomers commented

2015-01-11 05:09:58 +00:00

(Migrated from github.com)

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?

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 commented

2015-01-11 06:44:46 +00:00

(Migrated from github.com)

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; }

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; } ```

👍 2

marcelosomers commented

2015-01-11 13:24:58 +00:00

(Migrated from github.com)

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 commented

2015-09-21 20:23:19 +00:00

(Migrated from github.com)

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.

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.

👍 1

IgorShch commented

2015-11-18 14:19:06 +00:00

(Migrated from github.com)

@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.

@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 commented

2016-12-14 11:15:59 +00:00

(Migrated from github.com)

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

prasanthvel commented

2017-08-08 11:32:12 +00:00

(Migrated from github.com)

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:}]

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 commented

2018-03-04 06:58:43 +00:00

(Migrated from github.com)

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..

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..

👍 1

varunchandran333 commented

2018-03-22 07:18:24 +00:00

(Migrated from github.com)

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

yang5664 commented

2018-07-07 16:52:57 +00:00

(Migrated from github.com)

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:""})

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:""}) ```

👍 41 ❤️ 6 🎉 4 🚀 4

wuyuedefeng commented

2018-08-15 04:03:06 +00:00

(Migrated from github.com)

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

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

ssnadiminti commented

2019-03-07 17:36:13 +00:00

(Migrated from github.com)

@yang5664 That worked like a charm. Thank you

pns09 commented

2019-09-30 20:01:35 +00:00

(Migrated from github.com)

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

magarwal19 commented

2020-04-09 08:20:39 +00:00

(Migrated from github.com)

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

awaisawanbscs commented

2020-04-22 10:35:52 +00:00

(Migrated from github.com)

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:""})

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 commented

2020-04-22 10:43:01 +00:00

(Migrated from github.com)

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

> 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 commented

2020-04-22 10:46:51 +00:00

(Migrated from github.com)

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

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**

vic-codes commented

2021-04-24 15:48:30 +00:00

(Migrated from github.com)

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:""})

2021, this still works flawlessly. Thank you so very much.

> 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:""}) > ``` 2021, this still works flawlessly. Thank you so very much.

👍 1

Reejesh-PK commented

2022-08-05 05:40:35 +00:00

(Migrated from github.com)

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:""})

Works if you are using the latest version of js from https://cdn.sheetjs.com/ (Read "how to use")

> 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:""}) > ``` Works if you are using the latest version of js from https://cdn.sheetjs.com/ (Read "how to use")

Sign in to join this conversation.