I used the exact code from the "Re-calculating all formulas in a Workbook"
section of the page linked: http://poi.apache.org/hssf/eval.html
I think I'm doing it in the right order. The only change I made was to the
paths of the document.
------------------
FileInputStream fis = new FileInputStream("/somepath/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for(Iterator cit = r.cellIterator(); cit.hasNext();) {
HSSFCell c = (HSSFCell)cit.next();
if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
wb.write(new FileOutputStream("/somepath/changed.xls"));
------------------
Although the values were correctly calculated, the formula cells would not
recalculate when I changed the normal data cells. Adding the setCellFormula
fixed it.
I'll try to produce a simple example.
-J
-----Original Message-----
From: Nick Burch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 11:06 AM
To: POI Users List
Subject: RE: HSSF formula cells not calculating
On Wed, 19 Mar 2008, J Keller wrote:
> According to the docs, that is the method I want. But it didn't work
> correctly for me. When I used the "recalc spreadsheet" code from the
> docs page, it did put calculated values into the cells, but they would
> not update when I changed any of the data in the other cells referenced
> by the formula.
Hmm, not sure if what you're seeing is a bug or not. Are you doing:
* update all cell values
* recalculate all formulas
* save
Or are you trying to do it in a different order? If you're doing it in
that order, and things aren't being correctly re-calculated, please do
open a new bug and upload a little unit test that shows off the issue.
> Once I added the call to setCellFormula, it worked fine. This looks
> like a bug.
That shouldn't be needed, assuming you're doing everything in the right
order
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
