sheet_to_json does not skip blank rows #1078

New Issue

2018-04-18T21:33:05Z

cemremengu commented

2018-04-18 21:33:05 +00:00

(Migrated from github.com)

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 ?

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 ?

👍 2

SheetJSDev commented

2018-04-18 21:37:29 +00:00

(Migrated from github.com)

Can you share the file?

cemremengu commented

2018-04-18 21:54:13 +00:00

(Migrated from github.com)

@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 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](https://github.com/SheetJS/js-xlsx/files/1925918/sample.xlsx)

SheetJSDev commented

2018-04-18 22:12:18 +00:00

(Migrated from github.com)

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;

This is a funny logical error: [when a default value is specified, the row is marked as nonempty](https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L54). That doesn't really make sense in retrospect. The fix is simple: ```diff --- 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; ```

🎉 1

cemremengu commented

2018-04-18 22:21:09 +00:00

(Migrated from github.com)

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

SheetJSDev commented

2018-04-18 22:22:00 +00:00

(Migrated from github.com)

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

cemremengu commented

2018-04-18 22:23:01 +00:00

(Migrated from github.com)

Fantastic, keep up the great work!

sainiankit commented

2018-12-17 15:30:19 +00:00

(Migrated from github.com)

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 ?

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 ?

👍 2

samimw commented

2019-01-03 17:10:51 +00:00

(Migrated from github.com)

@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: ''});

@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: ''});`

👍 3

CamiNefilim commented

2019-04-11 14:57:58 +00:00

(Migrated from github.com)

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 };
    }

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 commented

2019-05-11 15:20:33 +00:00

(Migrated from github.com)

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

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

👍 20 ❤️ 4 🚀 3

zzbo commented

2019-05-21 01:59:47 +00:00

(Migrated from github.com)

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

❤️ 2

b-hexsoul commented

2022-01-11 17:21:40 +00:00

(Migrated from github.com)

There is some issue with the sheet I was using. If you are running into an issue with { blankrows: false } then make sure to clear contents of all cells below.

nithin-neewee commented

2022-02-07 08:45:31 +00:00

(Migrated from github.com)

@SheetJSDev

Please help
//Note: Empty cell are there, shown when uploaded the excel but i need one favor
//how can i higlight the empty cell with border-color and an error message to the user for understanding the empty cell.

let workBook = null;
let jsonData = null;
this.fileData = this.files[0]
const reader = new FileReader();
const file = this.files[0];
reader.onload = (event) => {
const data = reader.result;
workBook = XLSX.read(data, { type: 'binary' });
jsonData = workBook.SheetNames.reduce((initial, name) => {
const sheet = workBook.Sheets[name];

   this.fileData = XLSX.utils.sheet_to_json(sheet,{header:1,defval:"",blankrows :false

//Note: Empty cell are there, shown when uploaded the excel but i need one favor
//how can i higlight the empty cell with border-color and an error message to the user for understanding the empty cell.
});

   alert(this.fileData)

this.router.navigate(['/demand']);
this.heroService.tableDemostructure.next( this.fileData)
return initial;
}, {}
);
var dataString = JSON.stringify(jsonData);
localStorage.setItem("testJSON", dataString);

  let text = localStorage.getItem("testJSON");

let obj = JSON.parse(text);
//document.getElementById("demo").innerHTML = obj;
//document.getElementById('output').innerHTML = dataString.slice(0, 300).concat("...");
//this.setDownload(dataString);
}

reader.readAsBinaryString(file)

@SheetJSDev Please help //Note: Empty cell are there, shown when uploaded the excel but i need one favor //how can i higlight the empty cell with border-color and an error message to the user for understanding the empty cell. let workBook = null; let jsonData = null; this.fileData = this.files[0] const reader = new FileReader(); const file = this.files[0]; reader.onload = (event) => { const data = reader.result; workBook = XLSX.read(data, { type: 'binary' }); jsonData = workBook.SheetNames.reduce((initial, name) => { const sheet = workBook.Sheets[name]; this.fileData = XLSX.utils.sheet_to_json(sheet,{header:1,defval:"",blankrows :false //Note: Empty cell are there, shown when uploaded the excel but i need one favor //how can i higlight the empty cell with border-color and an error message to the user for understanding the empty cell. }); alert(this.fileData) this.router.navigate(['/demand']); this.heroService.tableDemostructure.next( this.fileData) return initial; }, {} ); var dataString = JSON.stringify(jsonData); localStorage.setItem("testJSON", dataString); let text = localStorage.getItem("testJSON"); let obj = JSON.parse(text); //document.getElementById("demo").innerHTML = obj; //document.getElementById('output').innerHTML = dataString.slice(0, 300).concat("..."); //this.setDownload(dataString); } reader.readAsBinaryString(file)

carivash-modelo commented

2022-05-11 15:48:38 +00:00

(Migrated from github.com)

@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: ''});

You must be add the blankrows: true

> @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: ''});` You must be add the `blankrows: true`

crazyjat commented

2022-06-13 22:11:32 +00:00

(Migrated from github.com)

I am experiencing this issue when trying to read an xlsx file from user upload that contains empty formulas. For example, I have cells that are empty that contain a formula such as =IF($A12 <> "", VLOOKUP(ObjectivesUI!G12,ENUM!E:F,2,0),"") The imported cells look like { t: "s", v: "" } and nothing I do will filter them out.

This is what my import function looks like:

const getXlsxData = (file: File): Promise<WorkBook> => {
  return new Promise(resolve => {
    let reader = new FileReader();
    reader.onload = (e) => {
      if (e.target?.result) {
        let workbook = read(e.target.result, {
          cellDates: true,
          cellFormula: false,
          cellNF: false,
          cellHTML: false,
          cellText: false,
        });
        resolve(workbook);
      }
    };
    reader.readAsArrayBuffer(file);
  });
}

I'm on version 0.17.5. Please help.

I am experiencing this issue when trying to read an xlsx file from user upload that contains empty formulas. For example, I have cells that are empty that contain a formula such as `=IF($A12 <> "", VLOOKUP(ObjectivesUI!G12,ENUM!E:F,2,0),"")` The imported cells look like `{ t: "s", v: "" }` and nothing I do will filter them out. This is what my import function looks like: const getXlsxData = (file: File): Promise<WorkBook> => { return new Promise(resolve => { let reader = new FileReader(); reader.onload = (e) => { if (e.target?.result) { let workbook = read(e.target.result, { cellDates: true, cellFormula: false, cellNF: false, cellHTML: false, cellText: false, }); resolve(workbook); } }; reader.readAsArrayBuffer(file); }); } I'm on version 0.17.5. Please help.

👍 2

Sign in to join this conversation.