How to make data-binding cells dirty for recalc

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

    • Post Options:
    • Link

    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

  • 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

  • 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?

  • 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

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels