CalculateFormula - slow calculation in complex workbook

Hi

I have a relatively complex Workbook (about 40MB). When I update a cell’s value with PutValue, the values in referencing cells is not updated. So I thought I have to call CalculateFormula first, but this takes more than 30min to complete.

The scenario is as follows:

Starting position:
wb.Worksheets(“hi”).Cells(“C7”).Value returns 8049
wb.Worksheets(“hi”).Cells(“E7”).Value returns 8049
wb.Worksheets(“hi”).Cells(“E7”).Formula returns "=C7"

Calling
wb.Worksheets(“hi”).Cells(“C7”).PutValue(5000, true)

results in
wb.Worksheets(“hi”).Cells(“C7”).Value returns 5000
wb.Worksheets(“hi”).Cells(“E7”).Value still returns 8049
wb.Worksheets(“hi”).Cells(“E7”).Formula returns "=C7"

Calling CalculateFormula again takes another 38min and after this, the values are returned correctly.

Is there a way to accelerate the process? Am I doing something wrong? I am using v17.2.0.0 Eval.

Thanks in advance for your help!

1 Like

Hi Silvio,

Thank you for contacting support. Please download and try the latest version 17.02.9 of Aspose.Cells for .NET API and let us know how that goes on your side.

Latest Version Link:

Aspose.Cells for .NET (Latest Version)

However, if this does not help, then please send us your source Excel file for the testing purposes. You can upload your source Excel file to any free file sharer server, e.g. Dropbox and Google drive, and share its download URL. It will help us to replicate the same performance issue on our side. We shall then investigate and reply you appropriately. Your response is awaited.

Hi

Many thanks for your reply. Unfortunately, I cannot provide you the XLS as it contains sensitive customer data. However, I managed to reduce the calc time (second time execution with CreateCalcChain = true) to a few seconds. The first time it still takes half an hour, but we can live with that for now.

Thanks again and best regards

Hi Silvio,


Thank you for the details. We use customer’s source files for testing purposes only. We also recommend our clients to remove the sensitive data. You may add dummy data in place of the sensitive data or share the confidential files in private via an email: Send your License File to an Aspose Staff Member

Anyways, please also refer to these two help topics: Opening a File with Data Only and Loading Visible Sheets Only