Calculate() Method is slow

Feb 9, 2015 at 11:38 AM
Have a template which contains formulas , I dump lot if data from SQL Server to sheet and then need to read few values.
when I invoke mySheet.Calculate() it takes too much time. any suggestion(s) to increase performance for Calculate.
Developer
Feb 10, 2015 at 12:33 PM
One thing you can do is to calculate only the value you want to read.
mySheet.Cells["A1"].Calculate();
You can also try to download the lastest code of EPPlus and build it. The lastest code has some performance improvements of the formula parser compared to version 4.0.0.2. If you do this, be sure to build in release mode since the Caluclate() method is much slower in debug mode.
Feb 11, 2015 at 2:58 AM
swmal , thanks for response. I tried same and have better performance but I have to optimize bit more.
Wanted to know how calculate parser works. for e.g.
Cell["A1"] has formula which refers another cell Cell["B1"] which too had formula. if I invoke Calculate on Cell["B1"] first and then on Cell["A1"] will it be faster than invoking calculate directly on Cell[A1"]. or I can say if calculate is invoke on Cell[B1"] does the value persist ?
Please let me know If I had to provide some sample sheet ? Thanks in advance.
Developer
Feb 11, 2015 at 7:14 PM
It might be useful to know that every call to Calculate will reset the values on the worksheet. Therefore this:
mySheet.Cells["A1:B2"].Calculate();
...will be faster than this:
mySheet.Cells["A1"].Calculate();
mySheet.Cells["A2"].Calculate();
mySheet.Cells["B1"].Calculate();
mySheet.Cells["B2"].Calculate();
The formula parser will start by building a calc chain and calculate the formulas in the appropriate order, so you don't need to think about that.

How many formulas do you have in your worksheet?