Aspose Workbook CalculateFormula

Aspose.Cells Product Family
1

@here we are using Aspose.cells dll in our projects. we have use case like evaluate formula and result into value when formulas are set to cell. as of now we are explictily call CalculateFormula method which causes few performance challenge(s). do we have alternate approaches to do same thing?

2

@sudhirdharani
Calling the CalculateFormula method is the only way to calculate all formulas in the Workbook. If you have any questions or confusion, please feel free to contact us at any time.

1 Like
3

@sudhirdharani

And please note, for performance consideration, it is preferred to calculate formulas together by Workbook.CalculateFormula instead of calculating formulas one by one by Cell.Calculate.

The performance of calculation itself depends on many factors, such as the size of the used data set, the complexity of the formulas and functions, …etc. If you get performance issue with simple data and formulas, you may provide us the runnable project and we will check whether we can improve it for you.

1 Like
4

@johnson.shi Thank you for your response. here i’m attaching sample project in this i’m trying to populate 10000 rows and trying to set forumulas in K,L,M,N columns i have taken stats for same.To populate 10K rows with data and formulas taken 203.0689 ms, but only for evaluate formulas taking 237.5024 ms. Please let us know is there any way to reduce that formula evaluation time.

here i’m uanble to upload total project(console test application with c# language) due to size constraint.

i will attach program.cs zip file please install Aspose.cells depedency packages from Visual studio Nuget package manager.
AsposeProject.zip (1.1 KB)

Thank you.

5

@sudhirdharani
Thank you for the provided code. We are afraid we cannot improve the performance more for such kind of formulas. For 40K formulas which need to accumulate values for 400K cells, we think current time cost to calculate them is reasonable. To improve the performance, maybe you can check the formulas to see whether they can be optimized for calculation. Take those in your code as example, make formulas of L column take the result of K column as base:

                cells[$"L{row + 1}"].Formula = $"=K{row + 1}";
                ...