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: [email protected] >>> For additional commands, e-mail: [email protected] >>> >>> >>> >>> >> >> > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > -- 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: [email protected] For additional commands, e-mail: [email protected]
