poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 44636] New: HSSF formula cells not calculating
Date Wed, 19 Mar 2008 17:22:09 GMT

           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: dev@poi.apache.org
        ReportedBy: jkeller@oberonassociates.com

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:
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

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)
         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();

            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);
      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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message