You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Boenisch, Philipp" <pb...@csc.com> on 2015/11/30 16:58:48 UTC
Import Excel: How to get a blank, when the result of a formula is
blank?
Hello,
I am trying to read an excel file via poi. Everything works fine except one thing. In my imported file there are two sheets. Some cells on the first page refers via a formula to cells on sheet 2, for example =Sheet2!U3. I only import sheet 0 and take the values from cells like this:
final ForumaleEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
final DataFormatter formatter = new DataFormatter();
final String value = formatter.format( cell, evaluator );
If Sheet2!U3 is empty, I always get "0" as value. After debugging I found out why:
WorkbookEvaluator.derefenceResult():
public static ValueEval dereferenceResult( ValueEval evaluationResult, int srcRowNum, int srcColNum )
{
...
if ( value == BlankEval.instance ) {
return NumberEval.ZERO;
// Formulas never evaluate to blank. If a formula appears to have evlautated to blank,
// the actual value is empty string. This can be verified with ISBLANK().
...
}
Is there any possibility to change this behavior? What is about the method ISBLANK()? I could not find it anywhere? It also would be helpful, if I have access to the returned ValueEval (to do something like this: if ( value == NumberEval.ZERO ) return null; ). But as I have seen the ValueEval is wasted during the format()-operation and only the number value of NumberEval is evaluated.
Kind regards,
Philipp Bönisch
Software Engineer Rail & Transit Solutions
CSC Deutschland GmbH
p: +49 351 47771 50 | f: +49 351 47771 11 | pboenisch@csc.com<ma...@csc.com> | www.csc.com/de | 01069 Dresden Germany | Bergstr. 2
CSC Deutschland GmbH * Registered Office: Abraham-Lincoln-Park 1, 65189 Wiesbaden, Germany * Board of Directors: Claus Schünemann (Chairman), Josef Ranner * Chairman of the Supervisory Board: William L. Deckelman * Registered in Germany: HRB 11307 * CSC * This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. * NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose.
Re: AW: Import Excel: How to get a blank, when the result of a
formula is blank?
Posted by Javen O'Neal <ja...@gmail.com>.
It might be possible for the workbook to store formula evaluation state so
you can switch between EVALUATE_BLANK_CELLS_AS_ZERO (Excel behavior) and
EVALUATE_BLANK_CELLS_AS_NULL. Alternatively, internal state might be
avoidable if the call to evaluate the cell specifies the desired behavior,
though I'd need to read the code to see if that's doable without editing
tons of functions or constructors.
Sounds like you have an ugly workaround, though it has some negative
consequences:
* Unnecessarily editing formulas
* Storing temporary formulas in workbook
* HSSFWorkbook has a limit 8 nested functions. By adding a nested function,
you may exceed that (make sure POI returns the formula the original formula
so Excel doesn't balk)
* evaluates essentially the same expression twice. Might be expensive
depending on the function.
* potential unknown (such as User-Defined VBA functions) evaluations.
For your application, you're evaluating the formula anyway, so it might not
be a big deal. I'm not sure if this causes any problems for the rest of the
evaluation chain.
On Dec 11, 2015 11:44 PM, "Mark Beardsley" <ma...@tiscali.co.uk> wrote:
> Morning Philipp
>
> Thanks for letting us know that you found a solution - and what that was as
> it may well help someone else out in the future. I would say that not
> changing the core api is the better option as it means you do not have to
> modify any future releases of POI that you may download.
>
> Any further problems or issues, just post here and someone will try to help
> out.
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Import-Excel-How-to-get-a-blank-when-the-result-of-a-formula-is-blank-tp5721160p5721272.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: AW: Import Excel: How to get a blank, when the result of a
formula is blank?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Morning Philipp
Thanks for letting us know that you found a solution - and what that was as
it may well help someone else out in the future. I would say that not
changing the core api is the better option as it means you do not have to
modify any future releases of POI that you may download.
Any further problems or issues, just post here and someone will try to help
out.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Import-Excel-How-to-get-a-blank-when-the-result-of-a-formula-is-blank-tp5721160p5721272.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
AW: Import Excel: How to get a blank, when the result of a formula is
blank?
Posted by "Boenisch, Philipp" <pb...@csc.com>.
Hello Mark,
Thanks for your help. I found another way so I do not have to manage my own POI-implementation. I do something like this:
if ( cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA )
{
final String oldFormula = cell.getCellFormula();
final String newFormula = "IF( \"\" = ( " + oldFormula + " ), \"BlAnK\", " + oldFormula + " )";
cell.setCellFormula( newFormula );
}
String value = formatter.formatCellValue( cell, formulaEvaluator );
if ( " BlAnK ".equals( value ) ) {
vValue = null;
}
I do not know, if it works for every formula. For my import everything works fine. Only BlAnK has to be unique.
Kind regards,
Philipp Bönisch
Software Engineer Rail & Transit Solutions
CSC Deutschland GmbH
p: +49 351 47771 50 | f: +49 351 47771 11 | pboenisch@csc.com | www.csc.com/de | 01069 Dresden Germany | Bergstr. 2
-----Ursprüngliche Nachricht-----
Von: Mark Beardsley [mailto:markbrdsly@tiscali.co.uk]
Gesendet: Dienstag, 1. Dezember 2015 17:34
An: user@poi.apache.org
Betreff: Re: Import Excel: How to get a blank, when the result of a formula is blank?
Well, ISBLANK() is an Excel function -
https://support.office.com/en-us/article/IS-functions-0f2d7971-6019-40a0-a171-f2d869135665
- that will indicate whether or not a cell is blank. In addition, there is nothing at all preventing you from creating your own version of POI with that change made to the dereferenceResult() method. All you would need to do is obtain the source for the project, modify it and build your own local version. This page will get you started - https://poi.apache.org/howtobuild.html - and it may be a better option than waiting for a change to the project itself as that may require some discussion.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Import-Excel-How-to-get-a-blank-when-the-result-of-a-formula-is-blank-tp5721160p5721173.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
CSC Deutschland GmbH • Registered Office: Abraham-Lincoln-Park 1, 65189 Wiesbaden, Germany • Board of Directors: Claus Schünemann (Chairman), Josef Ranner • Chairman of the Supervisory Board: William L. Deckelman • Registered in Germany: HRB 11307 • CSC • This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. • NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Import Excel: How to get a blank, when the result of a formula
is blank?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Well, ISBLANK() is an Excel function -
https://support.office.com/en-us/article/IS-functions-0f2d7971-6019-40a0-a171-f2d869135665
- that will indicate whether or not a cell is blank. In addition, there is
nothing at all preventing you from creating your own version of POI with
that change made to the dereferenceResult() method. All you would need to do
is obtain the source for the project, modify it and build your own local
version. This page will get you started -
https://poi.apache.org/howtobuild.html - and it may be a better option than
waiting for a change to the project itself as that may require some
discussion.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Import-Excel-How-to-get-a-blank-when-the-result-of-a-formula-is-blank-tp5721160p5721173.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org