Iterate over cells

Iterate over cells

Posted by: brian on 26 February 2020, 10:48 am EST

• 

  brian

  • Post Options:
  • Link
    Copy

  Posted 26 February 2020, 10:48 am EST

  Hello, I’d like to iterate all cells that have a value set in a worksheet. How should I go about doing this? I’m going through each worksheet like so:

  	const numSheets = spread.getSheetCount();
              for (let sheetInd = 0; sheetInd < numSheets; sheetInd++) {
                  const sheet = spread.getSheet(sheetInd);
                  sheet.isPaintSuspended(true);
                  // ... logic for grabbing all edited cells
              }
  

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 27 February 2020, 6:11 am EST

  Hi,

  For now, the only available option is to loop through all cells and check if the value is null or not. Refer to the following code snippet:

  const rowCount = sheet.getRowCount();
  const colCount = sheet.getColumnCount();
  
  for(let r = 0; r < rowCount; r++){
      for(let c = 0; c < colCount; c++) {
          let value = sheet.getValue(r, c);
          if(value != null){
               // non null cell
          }
      }
  }
  

  Regards
  
  Sharad

• 

  brian

  • Post Options:
  • Link
    Copy

  Posted 27 February 2020, 11:38 am EST

  Hello, thanks for your reply. That seems like it may be rather inefficient. I was digging through the minified SpreadJS code and discovered there is a method for sheets called toJSON() which provides a dataTable property with only the populated cells, so this is what I am using in my code now. Is that problematic?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 28 February 2020, 1:40 am EST

  toJSON method is used to serialize the whole worksheet into JSON format. Using the datatable property of the json would indeed be more efficient than simple looping. You may use this approach too without any issues.
  
  API reference:
  
  • toJSON method: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~toJSON.html