CODEWITHSUNDEEP
javaexcelapache-poi
Federico Destefanis
Federico Destefanis

Reputation: 1078

Apache POI Evaluate Formula in SXSSF workbook

In my project I use the SXSSFWorkbook (Apache-POI 3.9) class to manage a large spreadsheet. Now I need to evaluate formulas for some cells, so I tried with a FormulaEvaluator like this

...
SXSSFWorkbook streamingWorkbook = new SXSSFWorkbook(100);
...
FormulaEvaluator fe = streamingWorkbook.getCreationHelper().createFormulaEvaluator();
...
fe.evaluateInCell(cell);

but doing so, an exception is thrown 

java.lang.ClassCastException: org.apache.poi.xssf.streaming.SXSSFCell cannot be cast to org.apache.poi.xssf.usermodel.XSSFCell
at     org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:177)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:44)

...

The direct cause of this error is clear: the method .evaluateInCell takes a Cell object, but internally casts the Cell into a XSSFCell. Since I'm passing instead an SXSSFCell the exception is thrown.

So the question is: Is there a way to implement formula evaluation in streaming workbooks (SXSSF)?

Upvotes: 0

Views: 4224

Answers (3)

Alireza Alallah
Alireza Alallah

Reputation: 2534

the bellow image showing the problem(in SXSSF you can't evaluate the formula) enter image description here

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48326

TL;DR - The support you need isn't in the older 3.9 version that you're using, so you'll need to upgrade to 3.13 beta 2 or later.

Your problem is that you're using too old a version of Apache POI. That's why you're getting exceptions when you try to evaluate SXSSF cells. As detailed towards the end of the Formula Evaluation documentation, for SXSSF formula evaluation, you need to be using 3.13 beta 2 or later.

One thing to be aware of though - Formula Evaluation needs not only the cell with the formula in to be in memory, but also any other cells it refers to, and any they refer to. As such, a call to FormulaEvaluator.evaluateAll() is unlikely to work; you'll normally need to evaluate cells one-by-one just after writing. You'll also struggle with formulas which refer all over the place, as they'll only work if the cells they refer to are in the current window, and haven't been flushed to disk.

Upvotes: 2

prashant thakre
prashant thakre

Reputation: 5147

Just try to add the below dependency looks like you are missing other jars for XSSF

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>????</version>
</dependency>

Upvotes: 1

Related Questions