Workbook hangs on calculateformula and RefreshDynamicArrayFormulas

Aspose.Cells Product Family
1

PFA workbook (9.6 MB)
Sample code:

        var workbook = new Aspose.Cells.Workbook(@"Slow Calc.xlsx");

        var options = new CalculationOptions
        {
            CustomEngine = null,
            IgnoreError = true,
            PrecisionStrategy = CalculationPrecisionStrategy.Decimal
        };

        workbook.CalculateFormula(options); 
        workbook.RefreshDynamicArrayFormulas(true, options);
2

@Moonglum

It seems that you are experiencing performance issues with the CalculateFormula and RefreshDynamicArrayFormulas methods in Aspose.Cells. To address this, I recommend changing the order of your method calls. Specifically, you should call RefreshDynamicArrayFormulas before CalculateFormula. This is important because dynamic array formulas need to be refreshed with the latest data before performing calculations.

Here’s how you can modify your code:

var workbook = new Aspose.Cells.Workbook(@"Slow Calc.xlsx");

var options = new CalculationOptions
{
    CustomEngine = null,
    IgnoreError = true,
    PrecisionStrategy = CalculationPrecisionStrategy.Decimal
};

// Refresh dynamic array formulas first
workbook.RefreshDynamicArrayFormulas(true, options);

// Then calculate the formulas
workbook.CalculateFormula(options);

By refreshing the dynamic array formulas first, you ensure that they are updated with the latest data, which can help improve performance and prevent the workbook from hanging during calculations.

If you continue to experience issues, consider checking the complexity of the formulas in your workbook, as highly complex formulas can also lead to performance degradation.

For more detailed information, you can refer to the official documentation on RefreshDynamicArrayFormulas and CalculateFormula.

If you have any further questions or need additional assistance, feel free to ask!

3

@Moonglum
By testing on the latest version v25.4 using sample files and code, we can reproduce the issue. The program hangs on CalculateFormula and RefreshDynamicArrayFormulas.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58316

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

Is the bot correct?

Should I change the order of the method calls. Specifically, RefreshDynamicArrayFormulas before CalculateFormula ?

5

@Moonglum,

This is a bug which is already logged as “CELLSNET-58316” into our database. Please spare us little time to thoroughly evaluate your issue. Hopefully, it will be figured out soon. Once we have an update on it, we will let you know here.

1 Like
6

@Moonglum

Though the bug should not depend on the order of method calls, generally RefreshDynamicArrayFormulas should be called before CalculateFormula to get dynamic array formulas be spilled and then calculated correctly.

7

@Moonglum,

This is to inform you that your issue (Ticket ID: “CELLSNET-58316”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v25.6) that we plan to release in the first half of June 2025. You will be notified when the next version is published.

Just for your information, the formula calculation engine was not stuck, but it did take a significant amount of time to complete. We have enhanced the performance of formula calculations in the latest fix/update for your situation. However, due to the large number of formulas in the template file, we anticipate that the calculations may still take more than a few seconds to finish, even with these improvements.