poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sapan533 <sapan.agar...@stnwireless.com>
Subject Re: SUMIF function does not work with POI
Date Wed, 14 Jan 2009 03:29:56 GMT

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
> <sapan.agarwal@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


Mime
View raw message