sheetStubs doesn't work

[aytugulis]

I tried sheetStubs option and it didn't work. My code:

const result = excelToJson({ sourceFile: __dirname + '/file.xlsx', header: { rows: 1, }, sheetStubs: true });

I got: (i got this without sheetStubs)

{
      "A": "p3",
      "B": "seller_product_name",
      "D": 5,
      "E": 1,
      "F": 50,
      "G": "yes",
      "H": "seller_product_code"
}

I want to get "C":null.

[troyyer]

@aytugulis I also had the same issue as you where the sheetStubs wasnt working. I surpirisingly sorted this out by changing the font size in the excel file (something out of a programmerhumor subreddit). I don't know the exact way that this occurs. Potentially it has to do with the way I had copied and pasted in the data I put into the Excel file so below is the data in the excel sheet 'Sheet3' it had calibri font size 11 my code is very similar to yours

const excelToJson = require('convert-excel-to-json');
const fs = require('fs');
const sourceFile = `test/fixtures/test.xlsx`
const jsonResult = excelToJson({
    sourceFile: sourceFile,
    sheets: [{
        name: 'Sheet3',
    }],
    header: {
        rows: 1
    },
    sheetStubs: true
});
  console.log(jsonResult.Sheet3[jsonResult.Sheet3.length - 10]);
  console.log(jsonResult.Sheet3[jsonResult.Sheet3.length - 5]);
  console.log(jsonResult.Sheet3[jsonResult.Sheet3.length - 3]);

when i first ran it 'as is' the data was missing the null

but then, (I tested it out for one row to test this out) when i changed the font size up to 12 and changed it again back to 11 and then saved the excel file, the null appears o_0

I have no clue why this worked...

[pachecoder]

The same error is happening to me, D column with null values, the sheetStubs parameter is not working. Is strange because it works with the F column.

[aytugulis]

I didn't solve this problem with this package. I solve this with : https://www.npmjs.com/package/xlsx

[yuri1709]

troyyer

Thank you so much!! I was facing the same problem as you. const planilha = read(excel, { sheetStubs:true, cellDates:true, }); Even if sheetStubs: true, my node server was not reading my blank cell. So after I changed my excel font from 10 to 11 the blank cell came back to be recognize in my server. God bless you!!

[DiegoZoracKy]

This is a really strange case. The problem could be in the way Excel generates its own file in this specific case or in the xlsx package, which is used under the hood to extract data from an Excel file. I'm glad you guys found this solution and I still ended up discovering something new.

I'm closing this issue for now.