Calculate() breaks the resulting value

[prof1k]

EPPlus 4.5.3.3

Code provided:

var cellRange = excelWorksheet.Cells[row, column];
cellRange.Calculate();
cellRange.GetValue();

The cell contains the formula for working days.
After calling the Calculate () method, the Value field cannot be obtained, through Debug its value becomes #VALUE, and when GetValue is called, an exception is thrown.

[swmal]

Hi,

please provide more detailed information on how we can recreate this problem. We need either a failing unit test or a workbook together with code that causes the issue.

[Vasar007]

Hi, @swmal

I encountered similar issue. In my case, I implemented some statistical formulas that EPPlus lib doesn't have (using Microsoft.Office.Interop.Excel). One of these formulas is the array formula FREQUENCY. So, when I tried to calculate cell values with FREQUENCY formula EPPlus lib cannot calculate them. Besides, I tried to save workbook to a file and open it with Excel, and all cells formulas were valid including FREQUENCY.

However, when I replaced FREQUENCY with COUNTIFS all errors are gone. Therefore, I suppose that issue may be related to calculating (user-defined) array formulas.

I don't know whether it will be helpful to provide some code samples to reproduce the error because of Microsoft.Office.Interop.Excel assembly which I'm using in my function implementations.

[swmal]

@Vasar007 Did you try to log the errors as described in this section of our wiki? Also, the support for array formulas in EPPlus 4 calc engine is not very good. That is something we will look in to in EPPlus 5.

[Vasar007]

@swmal, yeah, I tried. However, no issues were logged:

=================================
500 cells parsed, time 0.07311 seconds

=================================
1000 cells parsed, time 0.0839823 seconds

=================================
Timestamp: 02/23/2020 13:38:02

Starting... number of cells to parse: 1131

=================================
1500 cells parsed, time 0.1501462 seconds
MAX  - 1
MIN  - 1
STDEV  - 1
ROUNDUP  - 1

=================================
2000 cells parsed, time 0.1610453 seconds

=================================
2500 cells parsed, time 0.7612927 seconds
BETADIST  - 34
FREQUENCY  - 18
COUNTIFS  - 18
STDEV  - 2
AVERAGE  - 2
VAR  - 2
MIN  - 1
MAX  - 1
ROUNDUP  - 1
SUM  - 1
CHIINV  - 1
CHITEST  - 1

=================================
3000 cells parsed, time 0.7719612 seconds

=================================
3500 cells parsed, time 0.8146113 seconds
MIN  - 1
STDEV  - 1
FREQUENCY  - 1

=================================
4000 cells parsed, time 0.8243852 seconds

================================

As you can see, I implemented following functions: FREQUENCY, CHITEST, CHIINV, BETADIST. The last three were calculated properly. Only FREQUENCY array function cannot be calculated. Or the values of cells with this function cannot be obtained somewhere later.

Finally, I switched library version to EPPlus 5 but the issue still remains.

[swmal]

Ok. One known issue is that EPPlus not yet handles array formulas properly. Dont know If that is causing this problem though. Would you mind sharing the code of your custom function that calls FREQENCY/Interop? This is btw functionality that we intend to implement native in EPPlus 5 on our mid term roadmap.

[Vasar007]

Sure, you can find all my functions here.

[Vasar007]

@swmal, yeah, I tried. However, no issues were logged:

=================================
500 cells parsed, time 0.07311 seconds

=================================
1000 cells parsed, time 0.0839823 seconds

=================================
Timestamp: 02/23/2020 13:38:02

Starting... number of cells to parse: 1131

=================================
1500 cells parsed, time 0.1501462 seconds
MAX  - 1
MIN  - 1
STDEV  - 1
ROUNDUP  - 1

=================================
2000 cells parsed, time 0.1610453 seconds

=================================
2500 cells parsed, time 0.7612927 seconds
BETADIST  - 34
FREQUENCY  - 18
COUNTIFS  - 18
STDEV  - 2
AVERAGE  - 2
VAR  - 2
MIN  - 1
MAX  - 1
ROUNDUP  - 1
SUM  - 1
CHIINV  - 1
CHITEST  - 1

=================================
3000 cells parsed, time 0.7719612 seconds

=================================
3500 cells parsed, time 0.8146113 seconds
MIN  - 1
STDEV  - 1
FREQUENCY  - 1

=================================
4000 cells parsed, time 0.8243852 seconds

================================

As you can see, I implemented following functions: FREQUENCY, CHITEST, CHIINV, BETADIST. The last three were calculated properly. Only FREQUENCY array function cannot be calculated. Or the values of cells with this function cannot be obtained somewhere later.

Finally, I switched library version to EPPlus 5 but the issue still remains.

@swmal I suppose that EPPlus lib cannot properly process returning value of array function. Why else would library call FREQUENCY function 18 times (equals to calls number of ordinary function COUNIFS)? Shouldn't there are be exactly one call?

[swmal]

I think you will get more interesting output If you do this (typing this from my phone, so excuse if the code is not 100%).

1. Wrap the code in your Frequency function in a try-catch(Exception ex)
2. In the catch block. if(context.Debug) context.Configuration.Logger.Log(context, ex); throw;
3. Log again.

You could be right in that this could have something todo with arrayformulas. EPPlus doesn’t really support them yet.

[Vasar007]

@swmal, hmm, I did what you had suggested but I got exactly the same output (except calculation time).

However, when I changed returning value from

return CreateResult(convertedResult, DataType.Enumerable);

to

return CreateResult(convertedResult[0], DataType.Decimal);

all issues were gone and cell values were calculated (with invalid results, of course). I think that EEplus lib cannot process DataType.Enumerable properly.