You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Adrian Butnaru <am...@gmail.com> on 2009/11/07 11:15:05 UTC
Cannot get a numeric value from a error formula cell
Hi,
I am building a web application using POI-3.5-FINAL-20090928.jar.
When I am calculating a sum in a cell wtih this code:
--------------------
Cell cell = risksheet.getRow(60).getCell(k);
evalA.evaluateFormulaCell(cell);
evalA.clearAllCachedResultValues();
val = risksheet.getRow(60).getCell(k).getNumericCellValue();
--------------------
I am receiving the following error:
java.lang.IllegalStateException: Cannot get a numeric value from a error
formula cell
org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:616)
org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:621)
org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:646)
The formula in that cell is
=SUM(E46:E54)+SUM(E56:E57)+SUM(E59:E60)
I need some help please, maybe someone more experienced can suggest me
what am I doing wrong.
Thanks,
Adrian
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Cannot get a numeric value from a error formula cell
Posted by Josh Micich <jo...@gmail.com>.
The formula result value is an Excel error code (like #DIV/0! or
#VALUE!). Use getErrorCellValue() here instead.
For the most part, POI does not do implicit type conversions when
reading cell values. This means that you need to determine the cell's
value type before calling the appropriate getXxxxCellValue() method.
In this case if you inspect the return value of
"evalA.evaluateFormulaCell(cell)" you should find that it is
Cell.CELL_TYPE_ERROR, which directs you to call getErrorCellValue() to
get the evaluation result.
In spite of some poor grammar, the error message is attempting to
communicate that the formula cell has evaluated to an error value, and
cannot be treated as a numeric quantity. The word "error" in the
message refers to the data type of the formula result, not your error
of calling the wrong getXxxxCellValue() method.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Cannot get a numeric value from a error formula cell
Posted by MSB <ma...@tiscali.co.uk>.
The obvious answer would be to evaluate all of the formulae in the
workbook(s) I guess and there is an example at the end of that page I
referenced that illustrates how to do this.
Must admit that I have never been in the position where I needed to do this
myself and so cannot guarantee that this is the answer but it does seem
reasonable given the situation you have.
Yours
Mark B
Adrian Butnaru wrote:
>
> I have investigated more and it looks that is not the SUM the problem.
> My spreadsheet is quite complicated (more than 20 sheets) and 4 links to
> external files. That is why I am using HSSFFormulaEvaluators to setup
> workbook environment and then I am using clearAllCachedResultValues() to
> clear the cache. And I can say that in simple cases this works.
>
> My question is now: If I am setting the numeric value of the cell what
> should I actually do to recalculate all the cells of the spreadsheet
> which are referencing this cell?
> I have the impression that the cause of my error is that somehow some
> dependant cells are not recalculated when I set the value of cell.
> Can somebody give me a hint how to exactly to this? The problem is that
> my spreadsheet is so large and complex that I cannot follow which cell
> should be updated when necessary.
>
> Thanks for help.
>
> Adrian
>
>
> MSB schreef:
>> Well, the only thing that I can see frm the snippet of code you posted is
>> this line;
>>
>> evalA.clearAllCachedResultValues();
>>
>> Whilst it is a perfectly valid method to call, it does not appear in any
>> of
>> the examples on this page as far as I can remember;
>>
>> http://poi.apache.org/spreadsheet/eval.html
>>
>> and I wonder if it could - and that is could - be causing you problems.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Adrian Butnaru wrote:
>>
>>> Hi,
>>> I am building a web application using POI-3.5-FINAL-20090928.jar.
>>> When I am calculating a sum in a cell wtih this code:
>>> --------------------
>>> Cell cell = risksheet.getRow(60).getCell(k);
>>> evalA.evaluateFormulaCell(cell);
>>> evalA.clearAllCachedResultValues();
>>> val =
>>> risksheet.getRow(60).getCell(k).getNumericCellValue();
>>> --------------------
>>> I am receiving the following error:
>>>
>>> java.lang.IllegalStateException: Cannot get a numeric value from a error
>>> formula cell
>>> org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:616)
>>> org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:621)
>>> org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:646)
>>>
>>>
>>> The formula in that cell is
>>>
>>> =SUM(E46:E54)+SUM(E56:E57)+SUM(E59:E60)
>>>
>>> I need some help please, maybe someone more experienced can suggest me
>>> what am I doing wrong.
>>>
>>> Thanks,
>>> Adrian
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>>
>>
>>
>
>
> ---------------------------------------------------------------------
> 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://old.nabble.com/Cannot-get-a-numeric-value-from-a-error-formula-cell-tp26243512p26254545.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: Cannot get a numeric value from a error formula cell
Posted by Adrian Butnaru <am...@gmail.com>.
I have investigated more and it looks that is not the SUM the problem.
My spreadsheet is quite complicated (more than 20 sheets) and 4 links to
external files. That is why I am using HSSFFormulaEvaluators to setup
workbook environment and then I am using clearAllCachedResultValues() to
clear the cache. And I can say that in simple cases this works.
My question is now: If I am setting the numeric value of the cell what
should I actually do to recalculate all the cells of the spreadsheet
which are referencing this cell?
I have the impression that the cause of my error is that somehow some
dependant cells are not recalculated when I set the value of cell.
Can somebody give me a hint how to exactly to this? The problem is that
my spreadsheet is so large and complex that I cannot follow which cell
should be updated when necessary.
Thanks for help.
Adrian
MSB schreef:
> Well, the only thing that I can see frm the snippet of code you posted is
> this line;
>
> evalA.clearAllCachedResultValues();
>
> Whilst it is a perfectly valid method to call, it does not appear in any of
> the examples on this page as far as I can remember;
>
> http://poi.apache.org/spreadsheet/eval.html
>
> and I wonder if it could - and that is could - be causing you problems.
>
> Yours
>
> Mark B
>
>
> Adrian Butnaru wrote:
>
>> Hi,
>> I am building a web application using POI-3.5-FINAL-20090928.jar.
>> When I am calculating a sum in a cell wtih this code:
>> --------------------
>> Cell cell = risksheet.getRow(60).getCell(k);
>> evalA.evaluateFormulaCell(cell);
>> evalA.clearAllCachedResultValues();
>> val =
>> risksheet.getRow(60).getCell(k).getNumericCellValue();
>> --------------------
>> I am receiving the following error:
>>
>> java.lang.IllegalStateException: Cannot get a numeric value from a error
>> formula cell
>> org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:616)
>> org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:621)
>> org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:646)
>>
>>
>> The formula in that cell is
>>
>> =SUM(E46:E54)+SUM(E56:E57)+SUM(E59:E60)
>>
>> I need some help please, maybe someone more experienced can suggest me
>> what am I doing wrong.
>>
>> Thanks,
>> Adrian
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Cannot get a numeric value from a error formula cell
Posted by MSB <ma...@tiscali.co.uk>.
Well, the only thing that I can see frm the snippet of code you posted is
this line;
evalA.clearAllCachedResultValues();
Whilst it is a perfectly valid method to call, it does not appear in any of
the examples on this page as far as I can remember;
http://poi.apache.org/spreadsheet/eval.html
and I wonder if it could - and that is could - be causing you problems.
Yours
Mark B
Adrian Butnaru wrote:
>
> Hi,
> I am building a web application using POI-3.5-FINAL-20090928.jar.
> When I am calculating a sum in a cell wtih this code:
> --------------------
> Cell cell = risksheet.getRow(60).getCell(k);
> evalA.evaluateFormulaCell(cell);
> evalA.clearAllCachedResultValues();
> val =
> risksheet.getRow(60).getCell(k).getNumericCellValue();
> --------------------
> I am receiving the following error:
>
> java.lang.IllegalStateException: Cannot get a numeric value from a error
> formula cell
> org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:616)
> org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:621)
> org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:646)
>
>
> The formula in that cell is
>
> =SUM(E46:E54)+SUM(E56:E57)+SUM(E59:E60)
>
> I need some help please, maybe someone more experienced can suggest me
> what am I doing wrong.
>
> Thanks,
> Adrian
>
> ---------------------------------------------------------------------
> 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://old.nabble.com/Cannot-get-a-numeric-value-from-a-error-formula-cell-tp26243512p26251766.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