Fomula calculation very slow

Aspose.Cells Product Family
1

Hi,


When we try to recalculate a workbook using workbook.CalculateFormula(true), it takes about 10 minutes to complete, even with the CreateCalcChain setting set to false. What can be done to speed it up?
2

Hi,


Thanks for providing us some details.

Could you provide us your template file, we will check your issue soon.

Thank you.
3

You can download the file here:

https://public.european-homes.fr/FILEUPLOAD/documents/a2aa2da498dc416ea4903d01fe09961c_639-VILLENOY.xlsm

4

Hi,


Thanks for the template file.

After an initial test, I observed the issue as you mentioned by using the following
sample code with your template file. I found Workbook.CalculateFormula
takes long time to calculate formulas in the template file.


e.g


Sample code:


string filePath = @"e:\test2\a2aa2da498dc416ea4903d01fe09961c_639-VILLENOY.xlsm";


Workbook workbook = new Workbook(filePath);
workbook.Settings.CreateCalcChain = false;

workbook.CalculateFormula(false);

I have logged a ticket with an id "CELLSNET-43471" for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
5

Hi,

Thanks for using Aspose.Cells.

This issue is caused by calculating too many complex SUMPRODUCT function.

For example, The formula of the cell “F525” in the worksheet " Prépa tres":=SUMPRODUCT(($A$4:$A$514=$A525)($C$4:$C$514=“TOTAL”)(F$4:F$514))

In the current calculating engine, we will calculate it with the following steps:

1, calculate ($A$4:$A$514=$A525) as array1
2, calculate ($C$4:$C$514=“TOTAL”) as array2
3, calculate (F$4:F$514) as array3
4, calculate array1 * array2 as array4
5, calculate array3 * array4 as array5.
6, calculate SUMPRODUCT with array5.

We have to loop for 511 * 6 times for this formula now.

We are re-building the calculate engine now, the steps will be changed as :

1, calculate (($A$4=$A525)($C$4:=“TOTAL”)(F$4))
2, loop 1 for 511 times to get array1
3. calculate SUMPRODUCT with array5.

We only need to loop for 511 * 2 times in the new engine.

But this new engine could not be available soon.

Now we only can improve performance little in the current calculate engine.

6

@hfroissart,

The template file is not accessible. We have implemented the new formula engine in past versions. You may try our latest version and fixes to see whether this issue has been fixed. If not, please provide us the template file again and we will make further investigation.