SheetJS - XLSX npm - Weird behavior on rendering sheet as JSON

Viewed 15

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
    });
0 Answers
Related