|
»
»
Cannot get a numeric value from a error formula cell
|
View:
New views
5 Messages
—
Rating Filter:
Alert me
|
 |
Cannot get a numeric value from a error formula cell
by Adrian Butnaru
::
Rate this Message:
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@... For additional commands, e-mail: user-help@... |
|
|
Re: Cannot get a numeric value from a error formula cell
by MSB
::
Rate this Message:
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
|
|
|
Re: Cannot get a numeric value from a error formula cell
by Adrian Butnaru
::
Rate this Message:
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@... >> For additional commands, e-mail: user-help@... >> >> >> >> > > --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@... For additional commands, e-mail: user-help@... |
|
|
Re: Cannot get a numeric value from a error formula cell
by MSB
::
Rate this Message:
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
|
|
|
Re: Cannot get a numeric value from a error formula cell
by Josh Micich
::
Rate this Message:
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@... For additional commands, e-mail: user-help@... |
| Free embeddable forum powered by Nabble | Forum Help |
