Comments (9)
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.
from epplus.
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.
from epplus.
@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.
from epplus.
@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.
from epplus.
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.
from epplus.
Sure, you can find all my functions here.
from epplus.
@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. OnlyFREQUENCY
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?
from epplus.
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%).
- Wrap the code in your Frequency function in a try-catch(Exception ex)
- In the catch block. if(context.Debug) context.Configuration.Logger.Log(context, ex); throw;
- Log again.
You could be right in that this could have something todo with arrayformulas. EPPlus doesnโt really support them yet.
from epplus.
@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.
from epplus.
Related Issues (20)
- INSERTROW System.ArgumentOutOfRangeException HOT 1
- Prevent index out of array bounds exception
- Exporting to excel shows date time value as UTC time while using EPPlus
- What is the status of this project? Is it still active? HOT 23
- NET Core doesn't support SignedCms yet.
- Does EPPLUS support Axis Title font format and series color change in scatterchart
- The type initializer for 'Gdip' threw an exception.Unable to load shared library 'libgdiplus' or one of its dependencies HOT 1
- Sumsq issue HOT 1
- Carriage return not retained
- Row_Delete doesn't delete the whole row
- .Net Core 3.0: use of foreach loop causes Excel file to balloon HOT 2
- Formula Calculation in 4.5.3.3 has bug after calculate call the cell value property return #Value HOT 4
- [NullReferenceException] ResetPageOffset HOT 1
- Don't work PrinterSettings with net core 3.1 HOT 1
- excelWorksheet.InsertRow NullReferenceException HOT 1
- Exception on loading xlsx having multiple `_xlnm.Print_Area`
- "Start cell Address must be less or equal to End cell address" Exception
- TokenHandler uses static TokenSeparatorHandler which ignores custom ITokenSeparatorProvider HOT 1
- Property Indexed of class ExcelColorXml always returns 0
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. ๐๐๐
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google โค๏ธ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from epplus.