Im using SheetJS when i upload the file xlsx using the method

_file(file) {
   /* Boilerplate to set up FileReader */
      const reader = new FileReader();
      reader.onload = (e) => {
      /* Parse data */
      const bstr = e.target.result;
      const wb = XLSX.read(bstr, {type:'binary'});
      /* Get first worksheet */
      const wsname = wb.SheetNames[0];
      const ws = wb.Sheets[wsname];
      /* Convert array of arrays */
      const data = XLSX.utils.sheet_to_json(ws, {header:1});
      /* Update state */
      this.data = data;
      this.cols = make_cols(ws['!ref']);
     };
   reader.readAsBinaryString(file);
}

I got some of the cell that has data but it includes some of the rows that has no data how to remove it? I only want to see rows that has data. TIA

Im using SheetJS when i upload the file xlsx using the method

_file(file) {
   /* Boilerplate to set up FileReader */
      const reader = new FileReader();
      reader.onload = (e) => {
      /* Parse data */
      const bstr = e.target.result;
      const wb = XLSX.read(bstr, {type:'binary'});
      /* Get first worksheet */
      const wsname = wb.SheetNames[0];
      const ws = wb.Sheets[wsname];
      /* Convert array of arrays */
      const data = XLSX.utils.sheet_to_json(ws, {header:1});
      /* Update state */
      this.data = data;
      this.cols = make_cols(ws['!ref']);
     };
   reader.readAsBinaryString(file);
}

I got some of the cell that has data but it includes some of the rows that has no data how to remove it? I only want to see rows that has data. TIA

• javascript
• sheetjs

3 Answers 3

In order to remove the rows without any data you can use the option blankRows: false, like this:

const data = XLSX.utils.sheet_to_json(ws, {header:1, blankRows: false});

From what I noticed, this option doesn't work together with option defval, so if you want to have a default option for some empty cells, but to remove blank rows, you'll have to write a custom function. There is an open issue about this bug in here: sheet_to_json does not skip blank rows

As mentioned in the previous ment by Naupad,
setting the attribute blankRows all in lowercase works as expected...

data = XLSX.utils.sheet_to_json(ws, {
    header:1,
    blankrows: false
});

I also have a problem like this, defval and blankrows does not meet my condition, because defval also read empty rows with border while blankrows is skipping empty cell in a row, so i create some function to iterate the output sheetdata to a new data array see below.

// body...

 /* var sd= XLSX.utils.sheet_to_json(ws,{header:1,defval:""}); */
 /* var sheetdata = sheetjs_cleanEmptyRows(sd) */
 function sheetjs_cleanEmptyRows(sd) {
    const data = []

        for (var row = 0; row < sd.length; row++) {

              var i = sd[row].length;
              var j = 0;

            for ( var cell = 0; cell < sd[row].length; cell++){

                if (sd[row][cell].length == 0 ) { j++}
            }
          if (j < i) {
            data.push(sd[row]);
          }

        }
        return data;
 }