How to make data-binding cells dirty for recalc

How to make data-binding cells dirty for recalc

Posted by: ichioka.yuji on 13 December 2024, 8:24 am EST

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 13 December 2024, 8:24 am EST

  Hello,

  There is a table binding to a data array. By directly changing the data array, the value display of the table on the worksheet will also change. This is very useful.
  
  However, formula cells that reference values on the table are not re-calculated. I expect this is probably because the dirty flag indicating changes in the values on the table did not change. Is there a way to make data-binding cells dirty for recalc?

  For an example, using the demo below, I set D4 cell to a fomula “=C4*2” as a result of the doubled age. When I change a vlue in the Age textbox, the re-calculation of D4 doesn’t work.

  https://developer.mescius.com/spreadjs/demos/features/data-binding/cell-level-binding/purejs

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 16 December 2024, 2:28 am EST

  Hi,

  When the value of the binding object is changed, SpreadJS doesn’t calculate the formulas or repaints the cell. You need to call the APIs to calculate and repaint the cell.

  You could call the “calculate” method to calculate the cells. For better performance, only calculate the cells that refer to the binding cells, you could use the following code snippet:

  // Get Cell Reference for the Binding
  var getBindingCellReference = function (sheet, cellBindingPath) {
      var range = sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.data);
      for (let i = range.row; i < range.row + range.rowCount; i++) {
          for (let j = range.col; j < range.col + range.colCount; j++) {
              var bindingPath = sheet.getBindingPath(i, j);
              if (bindingPath && bindingPath === cellBindingPath) {
                  return {
                      row: i,
                      col: j
                  }
              }
          }
      }
      return null;
  }
  // Update the Formulas for the Binding
  var updateBindingFormulas = function (sheet, bindingPath) {
      var bindingRef = getBindingCellReference(sheet, bindingPath);
      if (bindingRef) {
          let dependentCells = sheet.getDependents(bindingRef.row, bindingRef.col);
          // Loop Through All the Dependent Cells
          dependentCells.forEach((ref) => {
              // Calcualte The Dependent Cell
              spread.calculate(GC.Spread.Sheets.CalculationType.all,
                  `${ref.sheetName}!${GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(ref.row, ref.col, 1, 1))}`)
          })
      }
  }
  var updateAge = function (value) {
      if (person && sheet) {
          // Parse the value as an integer
          var age = parseInt(value, 10);
  
          // Check if the parsed value is a number
          person.age = isNaN(age) ? null : age;
          updateBindingFormulas(sheet, "age");
          // Trigger the sheet repaint
          sheet.repaint();
      }
  };

  Sample: https://jscodemine.mescius.io/share/r4Jby760g0q_dHTOSFXuUg/?defaultOpen={"OpenedFileName"%3A["%2Fapp.js"]%2C"ActiveFile"%3A"%2Fapp.js"}

  References:

  calculate method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Workbook#calculate

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

  Get Dependents Demo: https://developer.mescius.com/spreadjs/demos/features/calculation/formula-trace/get-dependent/purejs

  Regards,

  Ankit

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 18 December 2024, 1:18 am EST

  In the case of my application, I am using the GC.Spread.Sheets.Tables.Table.bind method to bind a JavaScript two-dimensional array to a Table. The Table can be as large as 20 columns and 1000 rows. What should I do in this case?

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 19 December 2024, 1:23 am EST

  Hi,

  You could use the similar approach with the table binding and then change only the cells where the data has been changed. For example, refer to the attached sample that I have created for you. In the sample, it changes the values for 2000 rows and then the calculation is performed for 2000 rows.

  Sample: https://jscodemine.mescius.io/share/bEOdpyijyEOXFkEUmq9n4g/?defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  The idea is that whenever the data is changed, perform the following steps:

  1. Repaint to reflect the changed values.

  2. Calculate the changed range so that the correct values are shown.

  You could use the calculate() method to calculate the changed range. You could perform the calculation for the whole sheet by just passing the sheetname as second parameter and excluding the range, but for better performance, you could only recalculate only the changed range/table.

  References:

  Calculation Service Demo: https://developer.mescius.com/spreadjs/demos/features/calculation/calc-service/purejs

  calculate API: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Workbook#calculate
  
  Let me know if you face any issues.

  Regards,

  Ankit