sheetJs - no output for empty cell

Question

I am using sheetjs. The empty cell is not getting populated in the json object including the header. I need that information also if a column is not entered. Here is my code:

workbook.SheetNames.forEach((sheetName) => {
    let XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {defval:""});
    console.log( JSON.stringify(XL_row_object ));
}

refer this https://stackoverflow.com/a/66658841/3099258 , if this helps your motive — Supriyo Bhattacherjee, Mar 17 '21 at 13:48

score 6 · Answer 1 · answered Mar 29 '21 at 17:51

You can solved it with this part, at the begining, when you are including your SheetJS:

**const data = xlsx.utils.sheet_to_json((ws),{defval:""});**

data is just a const which holds conversion from sheet to json
ws is the name of our sheets which we are using for conversion and manipulation
defval presents empty cell values. In our case it is an empty string ("")

score 1 · Answer 2 · edited May 07 '20 at 09:29

1

I was having the same issue and I solved this problem with this:

XLSX.readFile('file', {sheetStubs:true})

edited May 07 '20 at 09:29

António Ribeiro

4,129
5
32
49

answered May 07 '20 at 09:08

burakkp

53
9

score 0 · Answer 3 · answered Jul 23 '21 at 17:11

To extend the sheetStubs answer, XLSX.utils.sheet_to_json will ignore stubs so you should first convert them with a simple function:

function removeStubs(wb) {
  Object.values(wb.Sheets).forEach(ws => {
    Object.values(ws).filter(v => v.t === 'z').forEach(v => Object.assign(v,{t:'s',v:''}));
  });
  return wb;
}

then call removeStubs( XLSX.readFile('file', {sheetStubs:true}) )

sheetJs - no output for empty cell

3 Answers3

Linked