RangeChanged + Custom Formula Loading

RangeChanged + Custom Formula Loading

Posted by: khauam.cardoso on 15 January 2024, 2:01 am EST

• 

  khauam.cardoso

  • Post Options:
  • Link
    Copy

  Posted 15 January 2024, 2:01 am EST

  Hello,
  
  Is there any way for the RangeChanged event to ignore the “loading” value of the custom formula?

  I’m painting the formula cell values ​​yellow in case any value changes after recalculating, but it’s always changing, the value changes from “55” to “loading” and then to the same old value already being “55”

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 15 January 2024, 11:22 pm EST

  Hi,

  As per my understanding, you are using async custom function, and during recalculation, the rangeChanged event is triggered for the “loading” value, which you want to ignore.
  
  I attempted to reproduce the behavior on my end but was unsuccessful. Please refer to the attached video and sample.

  To better assist you, could you share a sample along with the steps to replicate, or modify the existing sample to replicate the issue? Additionally, could you provide information about the version and control (designer or spread) you are using. If there are differences in our implementations, it would be helpful if you could share your implementation through a sample. Additionally, a GIF or video illustrating the issue would be valuable for a more in-depth investigation.

  Sample: https://jscodemine.grapecity.com/share/FOmKPOvZ5Ua-qa-lc9eOuQ/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultn={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2COpe"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Best regards,

  Ankit

  Video: loadingValue.zip

• 

  khauam.cardoso

  • Post Options:
  • Link
    Copy

  Posted 15 January 2024, 11:31 pm EST

  It’s exactly as you demonstrated, but you need to use

  this.workbook.calculate(GC.Spread.Sheets.CalculationType.rebuild);

  so that the “loading” appears whenever it is recalculated

  ps: spread 16.2.2 on angular 16.2.5

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 17 January 2024, 12:16 am EST

  Hi,

  I am working on your case. I will update you soon with a solution.

  Regards,

  Ankit

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 17 January 2024, 7:56 pm EST

  Hi,

  I successfully reproduced the observed behavior, and by using “GC.Spread.Sheets.CalculationType.rebuild,” all the formulas within the specified range are rebuilt and marked as dirty for recalculation. This, in turn, triggers the rangeChanged event, even for default values like “Loading…” . There is no direct way to prevent the event from triggering for the default value of an asynchronous formula.

  But you could use below steps to achieve your requirements:

  1. Before recalculating formulas, store information about the cell values where the formula is used by using the getUsedRange and getArray methods. Refer to the snippet below.

  getFomulaInfo( spread: GC.Spread.Sheets.Workbook){
          let data = {};
          spread.sheets.forEach( sheet => {
              const usedRangeFormula = sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.formula);
              const formulaLocation = sheet.getArray(usedRangeFormula.row, usedRangeFormula.col, usedRangeFormula.rowCount, usedRangeFormula.colCount, true);
  
              // creating datamap for formula cells only
              for( let i = usedRangeFormula.row; i < usedRangeFormula.row + usedRangeFormula.rowCount; i++){
                  for( let j = usedRangeFormula.col; j < usedRangeFormula.col + usedRangeFormula.colCount; j++){
                      if(formulaLocation[i - usedRangeFormula.row][j - usedRangeFormula.col]){
                          if(!data[sheet.name()]){
                              data[sheet.name()] = {};
                              data[sheet.name()][i] = {};
                          }else if(!data[sheet.name()][i]){
                              data[sheet.name()][i] = {}
                          }
                          data[sheet.name()][i][j] = sheet.getValue( i, j);
                      }
                  }
              }
  
          })
          return data;
      }

  2. Then do recalculation.

  3. After that, simply check in the rangeChanged event whether the changed value is neither the default value nor the old value. If this condition is met, apply a background color to indicate the changed value. Refer to the snippet below.

  spread.bind(GC.Spread.Sheets.Events.RangeChanged, (e: any, info: any) => {
              // check if formula info is not null and formual present in sheet
              if( formulaInfo && formulaInfo[info.sheetName]){
                  info.changedCells.forEach( (cell: any) => {
                      // ignore defualt and old value
                      if( info.sheet.getValue(cell.row, cell.col) !== "Loading..." && formulaInfo[info.sheetName][cell.row][cell.col] !== info.sheet.getValue(cell.row, cell.col)){
                          info.sheet.getCell( cell.row, cell.col).backColor("yellow")
                      }
                  })
              }
          })

  I have attached a sample to refer.

  Sample: https://jscodemine.grapecity.com/share/Sqh1F5a6VU2TABI_ZDpXzA/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.component.ts"%2C"%2Fsrc%2Fapp.component.html"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.component.ts"}

  References:

  getUsedRange: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getusedrange

  getArray: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getarray

  Best regards,

  Ankit