You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by sapan533 <sa...@stnwireless.com> on 2009/01/13 10:11:53 UTC
SUMIF function does not work with POI
I have an excel sheet with some formulas in it. I am trying to use poi 3.5
beta 4 library to update all formula cells in that excel.All cells using
SUMIF display nothing and the error says
"invalid value was used while saving to WK1 format"
Can anyone please let me know the reason for the same? Pressing F2 and enter
on the cell after opening excel gives correct results.
Rgds,
Sapan
--
View this message in context: http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21431786.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: SUMIF function does not work with POI
Posted by Josh Micich <jo...@gmail.com>.
Hello Sapan,
Most of the function implementations present already were added before
version 3.1. Since then there have been about a dozen implementations
added. This usually happens when someone notices a function missing that
they need. There is pretty good coverage of the most commonly used
functions (judging from the lack of requests in bugzilla). However there
are at least 150 built-in functions and another 90 odd "Analysis ToolPak"
functions still to be done. Some of these sound like they might be commonly
required (e.g. COUNTBLANK, DATEDIFF, IRR) but it's hard to tell which are
worth spending time on. The POI project is always happy to receive code
contributions even if it is just for unimplemented functions.
As a follow-on from my comment above ("Perhaps an exception should have been
thrown instead of silently setting an invalid error code."), I have made a
change to that effect:
http://svn.apache.org/viewvc?view=rev&revision=736505
>From POI 3.5 onwards, if you evaluate a function that is not implemented
yet, a public unchecked exception (NotImplementedException) will be thrown.
regards,
Josh
Re: SUMIF function does not work with POI
Posted by sapan533 <sa...@stnwireless.com>.
Hi Josh,
Thanks a lot for the help and the quick response. This has been my first
time that i raised a query at this forum and I really appreciate the urgency
with which you have fixed this bug and resolved my issue. Thanks a lot for
your help.
Just one more question. Do u plan to add implementation for all excel
functions in 3.5FINAL? Just like SUMIF I can come across some other function
which is not yet implemented in poi.
Thanks & Regards,
Sapan
Josh Micich wrote:
>
> Hello Sapan,
>
>> ... In fact i solved this issue in a different way. I
>> read all the formula cells in the excel, and added following code:
>> ...
>> String str = cell.getCellFormula();
>> cell.setCellFormula(str);
>
> If these 2 lines really make a difference, that would represent a bug in
> POI. Can you please upload an example where this is the case?
>
>
>> From the bug it seems it is fixed in version 3.0. But I am using version
> 3.5
>> beta.
>
> I marked the bug as 'present in version 3.0'. Each fixed bugzilla usually
> has a comment linking to the svn revision, which should tell you at
> exactly
> what point on the trunk the fix was applied. If there is no link, you can
> make a guess from the fix date.
>
>
>> Can you please send me all the related java files you have modified or
>> the
>> updated jar file or patch.
>
> There are generally 4 ways to get a recent POI fix. Here they are (in
> order
> of reliability):
> 1 - Wait for the next release - in this case that would be 3.5FINAL, due
> in
> Feb
> 2 - Get the next nightly build from
> http://encore.torchbox.com/poi-svn-build/
> 3 - Check out the latest svn trunk, and build it yourself
> 4 - Find the relevant changes to individual files, patch them into your
> local copy and re-build
>
> If you really need to go the last route, Here are the two changes that
> should do the trick:
> http://svn.apache.org/viewvc?view=rev&revision=734243
> http://svn.apache.org/viewvc?view=rev&revision=734252
>
> regards,
> Josh
>
>
--
View this message in context: http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21471334.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: SUMIF function does not work with POI
Posted by Josh Micich <jo...@gmail.com>.
Hello Sapan,
> ... In fact i solved this issue in a different way. I
> read all the formula cells in the excel, and added following code:
> ...
> String str = cell.getCellFormula();
> cell.setCellFormula(str);
If these 2 lines really make a difference, that would represent a bug in
POI. Can you please upload an example where this is the case?
> From the bug it seems it is fixed in version 3.0. But I am using version
3.5
> beta.
I marked the bug as 'present in version 3.0'. Each fixed bugzilla usually
has a comment linking to the svn revision, which should tell you at exactly
what point on the trunk the fix was applied. If there is no link, you can
make a guess from the fix date.
> Can you please send me all the related java files you have modified or the
> updated jar file or patch.
There are generally 4 ways to get a recent POI fix. Here they are (in order
of reliability):
1 - Wait for the next release - in this case that would be 3.5FINAL, due in
Feb
2 - Get the next nightly build from
http://encore.torchbox.com/poi-svn-build/
3 - Check out the latest svn trunk, and build it yourself
4 - Find the relevant changes to individual files, patch them into your
local copy and re-build
If you really need to go the last route, Here are the two changes that
should do the trick:
http://svn.apache.org/viewvc?view=rev&revision=734243
http://svn.apache.org/viewvc?view=rev&revision=734252
regards,
Josh
Re: SUMIF function does not work with POI
Posted by sapan533 <sa...@stnwireless.com>.
Hi Josh,
>From the patch i got the java file. But I need the complete updated jar file
because I guess you have modified Countif.java file as well. When i try to
compile Sumif.java file it gives me an error:
The method createCriteriaPredicate(Eval) in the type Countif is not
applicable for the arguments (Eval, int, int)
Can you please send me all the related java files you have modified or the
updated jar file or patch.
Thanks & Regards,
Sapan
Josh Micich wrote:
>
> Hello Sapan,
>
> I am guessing that you are using HSSFFormulaEvaluator (or
> XSSFFormulaEvaluator) , and are having trouble evaluating formulas
> containing SUMIF. To be clear, the specific error message you described
> appears in Excel as a tool tip of a cell warning icon on a cell containing
> '#VALUE!'. This seems to be the case in any error value formula cell that
> has an invalid error *code*. Internally, POI uses a special error code
> (-30) to represent a 'not implemented' function. The origin of this bug
> is
> that SUMIF was not implemented yet in POI. Another problem is the way the
> evaluation was handled. Perhaps an exception should have been thrown
> instead of silently setting an invalid error code.
>
> The first fix was to add an implementation of SUMIF. See the related
> bugzilla:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=46523
> This should be enough to get you up and running.
>
> regards,
> Josh
>
>
> On Tue, Jan 13, 2009 at 1:11 AM, sapan533
> <sa...@stnwireless.com>wrote:
>
>>
>> I have an excel sheet with some formulas in it. I am trying to use poi
>> 3.5
>> beta 4 library to update all formula cells in that excel.All cells using
>> SUMIF display nothing and the error says
>> "invalid value was used while saving to WK1 format"
>>
>> Can anyone please let me know the reason for the same? Pressing F2 and
>> enter
>> on the cell after opening excel gives correct results.
>>
>> Rgds,
>> Sapan
>> --
>> View this message in context:
>> http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21431786.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
>>
>>
>
>
--
View this message in context: http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21451361.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: SUMIF function does not work with POI
Posted by sapan533 <sa...@stnwireless.com>.
Hello Josh,
Thanks a lot for your response.You are right , I am using
HSSFFormulaEvaluator . In fact i solved this issue in a different way. I
read all the formula cells in the excel, and added following code:
if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String str = cell.getCellFormula();
cell.setCellFormula(str);
evaluator.evaluateFormulaCell(cell);
}
and it worked.Earlier I was using just
HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
Now I iterate over all formula cells in the workbook and get/set the same
formula and it now works.
>From the bug it seems it is fixed in version 3.0. But I am using version 3.5
beta.
Thanks & Regards,
Sapan
Josh Micich wrote:
>
> Hello Sapan,
>
> I am guessing that you are using HSSFFormulaEvaluator (or
> XSSFFormulaEvaluator) , and are having trouble evaluating formulas
> containing SUMIF. To be clear, the specific error message you described
> appears in Excel as a tool tip of a cell warning icon on a cell containing
> '#VALUE!'. This seems to be the case in any error value formula cell that
> has an invalid error *code*. Internally, POI uses a special error code
> (-30) to represent a 'not implemented' function. The origin of this bug
> is
> that SUMIF was not implemented yet in POI. Another problem is the way the
> evaluation was handled. Perhaps an exception should have been thrown
> instead of silently setting an invalid error code.
>
> The first fix was to add an implementation of SUMIF. See the related
> bugzilla:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=46523
> This should be enough to get you up and running.
>
> regards,
> Josh
>
>
> On Tue, Jan 13, 2009 at 1:11 AM, sapan533
> <sa...@stnwireless.com>wrote:
>
>>
>> I have an excel sheet with some formulas in it. I am trying to use poi
>> 3.5
>> beta 4 library to update all formula cells in that excel.All cells using
>> SUMIF display nothing and the error says
>> "invalid value was used while saving to WK1 format"
>>
>> Can anyone please let me know the reason for the same? Pressing F2 and
>> enter
>> on the cell after opening excel gives correct results.
>>
>> Rgds,
>> Sapan
>> --
>> View this message in context:
>> http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21431786.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
>>
>>
>
>
--
View this message in context: http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21449409.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: SUMIF function does not work with POI
Posted by Josh Micich <jo...@gmail.com>.
Hello Sapan,
I am guessing that you are using HSSFFormulaEvaluator (or
XSSFFormulaEvaluator) , and are having trouble evaluating formulas
containing SUMIF. To be clear, the specific error message you described
appears in Excel as a tool tip of a cell warning icon on a cell containing
'#VALUE!'. This seems to be the case in any error value formula cell that
has an invalid error *code*. Internally, POI uses a special error code
(-30) to represent a 'not implemented' function. The origin of this bug is
that SUMIF was not implemented yet in POI. Another problem is the way the
evaluation was handled. Perhaps an exception should have been thrown
instead of silently setting an invalid error code.
The first fix was to add an implementation of SUMIF. See the related
bugzilla:
https://issues.apache.org/bugzilla/show_bug.cgi?id=46523
This should be enough to get you up and running.
regards,
Josh
On Tue, Jan 13, 2009 at 1:11 AM, sapan533 <sa...@stnwireless.com>wrote:
>
> I have an excel sheet with some formulas in it. I am trying to use poi 3.5
> beta 4 library to update all formula cells in that excel.All cells using
> SUMIF display nothing and the error says
> "invalid value was used while saving to WK1 format"
>
> Can anyone please let me know the reason for the same? Pressing F2 and
> enter
> on the cell after opening excel gives correct results.
>
> Rgds,
> Sapan
> --
> View this message in context:
> http://www.nabble.com/SUMIF-function-does-not-work-with-POI-tp21431786p21431786.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
>
>