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