I am working on conversion of a xlsx sheet to json. The heading starts at row 3. And there are 4 columns. There's only one row of data in row 4.
But when I convert it to a JSON, I get range as { s: { c: 0, r: 0 }, e: { c: 6, r: 1048575 } } and responses as:
[
{
"Column A": "123456789",
"Column B": "2345",
"Column C": "S",
"Column D": "01/01/2020",
"__EMPTY": "",
"__EMPTY_1": "",
"__EMPTY_2": ""
},
{
"Column A": "",
"Column B": "",
"Column C": "",
"Column D": "",
"__EMPTY": "",
"__EMPTY_1": " ",
"__EMPTY_2": ""
},
{
"Column A": "",
"Column B": "",
"Column C": "",
"Column D": "",
"__EMPTY": "",
"__EMPTY_1": "",
"__EMPTY_2": " "
}
]
How to avoid such weird behavior with blank rows? I need defVal since i need to set it as blank for empty values, and all keys need to be present in all objects.My code is below:
const wb = xlsx.read(fileRes.buffer,{type: 'buffer',
cellDates: true, dateNF:'mm/dd/yyyy'});
var range = xlsx.utils.decode_range(wb.Sheets[wb.SheetNames[0]]['!ref']);
console.log(range);
range.s.r = 2; // start with 3rd row
var new_range = xlsx.utils.encode_range(range);
const json = xlsx.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]],{
defval: "",
blankrows: false,
range: new_range,
raw: false,
forceQuotes: true
});