https://issues.apache.org/bugzilla/show_bug.cgi?id=44636
Summary: HSSF formula cells not calculating
Product: POI
Version: 3.0
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: [email protected]
ReportedBy: [EMAIL PROTECTED]
Created an attachment (id=21689)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21689)
Input spreadsheet (created in Excel)
Using the sample "recalculate all" code from this page:
http://poi.apache.org/hssf/eval.html
does recalculate the cells, but does not seem to correctly handle the formulas
in all cases.
If you call cell.setCellForumla after evaluating the cell, it seems to work
fine.
Here's a simple test case.
1) Run the code below. It will open the attached simple.xls, change one cell,
and save it as changed.xls.
2) Open changed.xls in Excel.
3) Change the same cell (C1, which should now contain 25).
4) Note how the calculated cell (D1) does not recalc.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Recalc
{
public static void main (String[] args)
{
try
{
File ssFile = new File ("simple.xls");
FileInputStream ssIn = new FileInputStream (ssFile);
HSSFWorkbook wb = new HSSFWorkbook (ssIn);
HSSFSheet sheet = wb.getSheetAt (0);
HSSFRow row = sheet.getRow (0);
HSSFCell cell = row.getCell ((short) 2);
cell.setCellValue (25);
// recalc
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);
}
}
FileOutputStream ssOut = new FileOutputStream ("changed.xls");
wb.write (ssOut);
ssOut.close();
}
catch (Exception x)
{
System.err.println (x);
}
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
