Sheetjs: sheet_to_json does not skip blank rows

[cemremengu]

I have the following configuration which works fine in general:

    let lte = await XLSX.utils.sheet_to_json(workbook.Sheets['Sheet1'], {
      blankRows: false,
      defval: '',
    });

However, I have an excel with 14K rows. I deleted the values in all but one row for test (there is styling in empty rows such as borders and colors but no values). When parsing, sheetjs still attempts to parse all of 14K rows for some reason.

Any ideas what's causing it ?

[SheetJSDev]

Can you share the file?

[cemremengu]

@SheetJSDev There has to be something with the file itself... If I copy contents to a new file, it would not behave like that
sample.xlsx

[SheetJSDev]

This is a funny logical error: when a default value is specified, the row is marked as nonempty. That doesn't really make sense in retrospect. The fix is simple:

--- a/bits/90_utils.js
+++ b/bits/90_utils.js
@@ -51,7 +51,7 @@ function sheet_to_json(sheet/*:Worksheet*/, opts/*:?Sheet2JSONOpts*/) {
                        val = dense ? sheet[R][C] : sheet[cols[C] + rr];
                        if(val === undefined || val.t === undefined) {
                                if(defval === undefined) continue;
-                               if(hdr[C] != null) { row[hdr[C]] = defval; isempty = false; }
+                               if(hdr[C] != null) { row[hdr[C]] = defval; }
                                continue;
                        }
                        v = val.v;

[cemremengu]

@SheetJSDev Yes, that does it! Thanks a million, you saved my life 💯

[SheetJSDev]

This will be in the next release, we're aiming for tomorrow and will close the issue then.

[cemremengu]

Fantastic, keep up the great work!

[sainiankit]

I'm currently using the version "0.14.0". I'm facing the same issue.

range.s.c = 0;
range.e.c = rules.length - 1;
const new_range = XLSX.utils.encode_range(range);
let excelInJSON = XLSX.utils.sheet_to_json(ws, { header: 1, blankRows: false, defval: '', range: new_range });

still parses all the blank rows. Does this issue still persists ?

[samimw]

@SheetJSDev I am getting same issue as @sainiankit.

using version 0.14.0 setting blankRows and defVal doesn't work:

var sheetThreeArray = X.utils.sheet_to_row_object_array(workbook.Sheets[sheetThree], {blankRows: false, defval: ''});

[CamiNefilim]

Hi, on version 0.14.2, I modified the plugin on the function adding on line 20545:

if (typeof v === 'string' || v instanceof String) {
                v = v.toString().trim();
            }

and modifying on line 20562
if (v != null && v.toString().trim().length > 0) isempty = false;

Complete function code:

function make_json_row(sheet, r, R, cols, header, hdr, dense, o) {
        var rr = encode_row(R);
        var defval = o.defval, raw = o.raw || !o.hasOwnProperty("raw");
        var isempty = true;
        var row = (header === 1) ? [] : {};
        if (header !== 1) {
            if (Object.defineProperty) try { Object.defineProperty(row, '__rowNum__', { value: R, enumerable: false }); } catch (e) { row.__rowNum__ = R; }
            else row.__rowNum__ = R;
        }
        if (!dense || sheet[R]) for (var C = r.s.c; C <= r.e.c; ++C) {
            var val = dense ? sheet[R][C] : sheet[cols[C] + rr];
            if (val === undefined || val.t === undefined || val === " " ) {
                if (defval === undefined) continue;
                if (hdr[C] != null) { row[hdr[C]] = defval; }
                continue;
            }
            var v = val.v;
            if (typeof v === 'string' || v instanceof String) {
                v = v.toString().trim();
            }
            switch (val.t) {
                case 'z': if (v == null) break; continue;
                case 'e': v = void 0; break;
                case 's': case 'd': case 'b': case 'n': break;
                default: throw new Error('unrecognized type ' + val.t);
            }
            if (hdr[C] != null) {
                if (v == null) {
                    if (defval !== undefined) row[hdr[C]] = defval;
                    else if (raw && v === null) row[hdr[C]] = null;
                    else continue;
                } else {
                    row[hdr[C]] = raw ? v : format_cell(val, v, o);
                }
                if (v != null && v.toString().trim().length > 0) isempty = false;
            }
        }
        return { row: row, isempty: isempty };
    }

[mehars-jalin]

I faced the same issue, and after spending 1 hour, I decided to take a look at the function and discovered that the keyname is NOT camelcase, as documented. So use, blankrows NOT blankRows

[zzbo]

Thanks for @mehars-jalin , it worked for me.