Workbook.CalculateFormula is very time consuming （with non-existing external links, maybe)

Aspose.Cells Product Family
1

Workbook wb = new Workbook(@“Test.xlsx”);
Stopwatch sw = Stopwatch.StartNew();
wb.CalculateFormula();
Console.WriteLine($“{sw.ElapsedMilliseconds}ms”);

It will take about 1 minite to calculate formula.

Test.7z (556.0 KB)

2

@cnbjw

Thank you for reaching out to Aspose support with your inquiry.

It appears that calculating formulas in your workbook takes significantly longer than expected, which might be due to non-existing external links. Let’s address potential causes and work towards a solution step by step:

  1. Check for External Links: Make sure you do not have any references to external files in your workbook, as these might be causing delays.
  2. Minimize Workbook Size: The size of the workbook might contribute to the delay. Ensure the workbook contains only essential data and formatting.
  3. Optimization Settings: Use advanced configuration settings in Aspose.Cells to optimize the calculation speed.

To provide you with the best assistance, please upload a sample workbook (you may create a simplified version) so we can analyze it and suggest specific optimizations.

Sample Code Optimization

Below is a modified version of your code with additional settings to potentially enhance performance:

using Aspose.Cells;
using System;
using System.Diagnostics;

class Program
{
    static void Main()
    {
        Workbook wb = new Workbook(@"Test.xlsx");

        // Disable auto calculation which recalculates formulas on every value change
        wb.Settings.CalculationOptions.SetCalculateMode(CalculateMode.MANUAL);

        Stopwatch sw = Stopwatch.StartNew();
        wb.CalculateFormula();
        Console.WriteLine($"{sw.ElapsedMilliseconds}ms");

        // If you have identified external links and want to warn about them
        Console.WriteLine("Do you have any external links in your workbook?");
    }
}

Reference and Documentation

3

Other files with external links don’t have this problem. I suspect it’s a bug triggered under specific conditions.

4

@cnbjw
By testing with sample code and files on the latest version v25.8, we can reproduce the issue. The program is very time consuming when calling Workbook.CalculateFormula method.

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-58883

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.

1 Like
5

@cnbjw,

We have analyzed the performance issue for your template file.
For formulas in column E:

=IFERROR(VLOOKUP(B5,IF({1,0},'G:\L22\design\手游项目策划文档\数值文档\[L22_数值文档 - 近战武器.xlsx]招式列表'!$P:$P,'G:\L22\design\手游项目策划文档\数值文档\[L22_数值文档 - 近战武器.xlsx]招式列表'!$I:$I),2,0),"None")

the bottle neck is calculating VLOOKUP function. If the search range is simple reference, such as 'G:\L22\design\手游项目策划文档\数值文档[L22_数值文档 - 近战武器.xlsx]招式列表'!$P:$P, our formula engine can optimize the calculation by caching and re-arranging the range data(so even though the formulas in column G are more complex, there is no performance issue for them). But for the specified formula in column E, the search range is the calculated result of another function (IF). For such kind of data, creating cache is impossible because generally the calculated result is different between cells so creating cache is useless and even degrade the performance.

For your specific formula, because the condition of IF function is fixed, the calculated result for the search data is same for all cells in column E. However, we cannot construct special logic specifically to create cache for such kind of special data. Instead, to improve the performance, we think you may change the formula accordingly to use simple reference:

=IFERROR(VLOOKUP(B5,'G:\L22\design\手游项目策划文档\数值文档\[L22_数值文档 - 近战武器.xlsx]招式列表'!$P:$P,2,0),"None")

this formula can give the same result with the IF({1,0},…) function with much better performance than the original one.

1 Like