You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mark Beardsley <ma...@tiscali.co.uk> on 2015/12/01 17:34:12 UTC
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
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