NonEmptyRowCount Alternate in Spread JS

NonEmptyRowCount Alternate in Spread JS

Posted by: pooja.bansal on 20 July 2020, 10:47 am EST

• 

  pooja.bansal

  • Post Options:
  • Link
    Copy

  Posted 20 July 2020, 10:47 am EST

  Hi,

  While importing excel in Spread JS , we would require nonemptyrowcount or it’s alternate(like in Farpoint) to iterate over the data in Spread JS.

  Regards
  
  Pooja Bansal

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 21 July 2020, 10:59 am EST

  Hi Pooja,

  There is no direct method to achieve this functionality but You may use to JSON method which provides the nonempty rows in the sheet. Please refer to the following code snippet and attached sample which demonstrates the same.

  var nonEmptyRows = Object.keys(sheet.toJSON().data.dataTable).length;
  

  sample: https://codesandbox.io/s/spreadjs-custom-formula-reference-pulhl?file=/src/index.js

  API references
  
  toJSON: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~toJSON.html

  Regards

• 

  pooja.bansal

  • Post Options:
  • Link
    Copy

  Posted 15 July 2021, 1:50 am EST

  Hi,

  We have applied the below method to get the nonemptyrowcount in excel -
  
  var nonEmptyRows = Object.keys(sheet.toJSON().data.dataTable).length;

  Scenario - Suppose in excel we have data in 5 rows and by mistaken we paste any data into random row like 10th row and then remove data from 10th row.
  
  In that case above methods returns nonemptyrowcount equals to 10.
  
  However, it should be 5 as only 5 rows have the data.

  Please reply asap.

  Regards
  
  Pooja Bansal

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 16 July 2021, 1:08 am EST

  Hi Pooja,

  This is expected behavior. Actually, When we copy and paste the cell then the style is also get pasted in the above sample all the style is marked as nonempty which is why it is showing this result. Further, if you want to mark style cells as empty you may use the following code snippet.

  
  function getNonEmptyRowCount(sheet, ignoreStyleOnlyCells) {
    let dataTable = sheet.toJSON().data.dataTable;
    if (!ignoreStyleOnlyCells) {
      return Object.keys(dataTable).length;
    } else {
      let rowCount = 0;
      let hasData = false;
      let rowArr = Object.keys(dataTable);
      let colArr = null;
      for (let row = 0; row < rowArr.length; row++) {
        hasData = false;
        colArr = Object.keys(dataTable[rowArr[row]]);
        for (let col = 0; col < colArr.length; col++) {
          if (dataTable[rowArr[row]][colArr[col]].value) {
            hasData = true;
            break;
          }
        }
        if (hasData) rowCount++;
      }
      return rowCount;
    }
  }
  
  

  updated Sample: https://codesandbox.io/s/spreadjs-custom-formula-reference-forked-qsp05?file=/src/index.js:541-1207

  Regards,
  
  Avinash