Search code examples
c#asp.netexcelepplus

EPPlus Range Formula calculation takes forever on larger files

I am working on a worksheet that contains around 87,000 rows and I am using a formula to add up purchase amounts that only belong to a certain invoice.

Here is my formula:

workSheet.Cells[2, 17, workSheet.Dimension.End.Row, 17].Formula = "SUMIF(A:A,A"+2+",N:N)";

And then I run an epplus range calculation so I can work with the values programmatically:

workSheet.Cells[2, 17, workSheet.Dimension.End.Row, 17].Calculate();

However, this .Calculate() takes a super long time to run. It works fine with smaller files! But once the data gets too big it increases the time complexity tremendously.

Is there anything I can do with the formula or EPPlus to have this .Calculate() execute quickly? Thanks!

Solution

  • Answer in comments:

    I have found out that the particular Excel formula is O(n^2) time complexity because it recursively rechecks all the rows again for each row it passes when .calculate() is called within EPPlus, so I just had to make my own formula in C# that basically does what the Excel formula is supposed to do, but this time it is O(n) time complexity which tremendously sped things up.