Cannot calculate after refreshing the spreadsheet in UI level.

[Ngoc Hoang]

Hi team. I need the team to support me.

In sheetA, when you enter numbers into cells A2 and A3, cell A1 functions normally.

After refreshing, nothing happens when entering numbers into cells A2 and A3.

Input 

A2: 1 

A3: 2 

=> A1 = 3

After hiding sheetC Input 

A2: 5 

A3: 10 

=> A1 still equals 3

Here is my code: https://stackblitz.com/edit/react-yu8m9t-evq7m9?file=index.js

[Janakiraman Sakthivel]

Hi Ngoc Hoang,

We have validated your query and were able to replicate the reported problem. We would like to inform you that we maintain cells referred to in formulas as the formula's dependent cells collection. Based on these dependent cells collection, we update the formula cells when entering values in the dependent cells.

The reason for your reported problem is that, while using the refresh() method, the values in the dependent cells collection are removed except for the active sheet's formula's dependent cells collection. Because of this, the formula is not calculated, and the result is not updated in cells that reference non-active sheet cells as arguments in the formula.

We would also like to inform you that we have already confirmed the need to provide a public method to update the formula's dependent cell collections as an improvement, and we have logged it as a feature. This public method will resolve your reported problem and maintain the formula's dependent cell collections properly. It will be available in our upcoming weekly patch release scheduled for July 16, 2024. You can track the status of the issue via the feedback link below.

Feedback link: https://www.syncfusion.com/feedback/53917/provide-a-public-method-to-update-the-formulas-dependent-cell-collections

Kindly get back to us if you need any further clarifications.

[Ngoc Hoang]

Hi Janakiraman Sakthivel, thanks for your response. Has this issue been resolved?

[Babu Periyasamy]

Hi Ngoc Hoang,

Sorry for the inconvenience caused.

We are glad to announce that we have provided the improvement of “Provide a public method to update the formula's dependent cell collections” public method. We suggest you update the Spreadsheet package to the version(26.1.42) or to the later versions after (v26.1.42)in your end to use this method.

Package links:

https://www.npmjs.com/package/@syncfusion/ej2-react-spreadsheet/v/26.1.42

https://www.npmjs.com/package/@syncfusion/ej2-react-spreadsheet/v/26.2.9 (Latest version)

Feedback link: https://www.syncfusion.com/feedback/53917/provide-a-public-method-to-update-the-formulas-dependent-cell-collections

Using this calculateNow method, you can recalculate the formula and its dependent cells all over the Workbook and Sheet based on the ‘scope’ argument given inside calculateNow method.

For more information regarding the calculateNow method, please refer the below API,

https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#calculatenow

For your reference, we have shared the calculateNow method argument details below.

Argument	Details
scope: string; (optional)	Specifies the scope of the calculation. Acceptable values are `Sheet` or `Workbook`.   `Sheet`: Calculates formulas only on the current sheet or a specified sheet. `Workbook`: Calculates formulas across the entire workbook.   By default, scope is `Sheet`
sheet: string | number; (optional)	Specifies the index or name of the sheet to calculate if the scope is set to `Sheet`.   If not provided and the scope is `Sheet`, the current active sheet will be used.

Also, we have modified the previously shared sample using the calculateNow method to resolve the issue and attached below along with the code snippet for your reference,

Code snippet:

const onClick = React.useCallback(() => {

    let spreadsheet = spreadsheetRef.current;

 

    spreadsheet.sheets[2].state = 'Hidden';

 

    spreadsheet.refresh(); // This line cause bug.

    //To recalculate the formula and its dependent cells all over the Workbook.

    spreadsheet.calculateNow('Workbook');

Modified sample: https://stackblitz.com/edit/react-yu8m9t-es3fxt?file=package.json,index.js

Kindly, check the above information and please let us know if you need further assistance on this.