evaluateInCell(HSSFCell) raises a ClassCastException

[Barrett, Paul]

Hello all

 

I am having a problem while trying to evaluate a formula in POI.

I am using the poi-3.0-rc2-20070329.jar and the
poi-scratchpad-3.0-rc2-20070329.jar

Whenever I call HSSFFormulaEvaluator.evaluateInCell(HSSFCell) I get a
ClassCastException.

(I also get NullPointerExceptions when calling the evaluate method)

 

The stack trace is:

Exception in thread main

java.lang.ClassCastException:
org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate

            at
org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(HSSFCell.java:585)

            at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFo
rmulaEvaluator.java:236)

            at tester.XLFormulaTester.<init>(XLFormulaTester.java:31)

            at tester.XLFormulaTester.main(XLFormulaTester.java:47)

I did look in the mailing list archives and someone had this same
problem in November 2006 but was told that the problem had been fixed in
a new release, also in November.

However I am using a release candidate only made available this year.

All of the formula references point to data in the same workbook and
there are no custom formulas involved.

Here is code to use which generates this error.  Change the row, cell
and worksheet numbers to point to a formula in your file:

 

 

import java.io.BufferedInputStream;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

 

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;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

 

public class XLFormulaTester {

 

    public XLFormulaTester() {

        

        try {

            BufferedInputStream b = new BufferedInputStream(new
FileInputStream("path\\to\\your\\file.xls"));

            POIFSFileSystem fs = new POIFSFileSystem(b);

            HSSFWorkbook workbook = new HSSFWorkbook(fs);

            HSSFSheet sheet = workbook.getSheetAt(2);

            

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet,
workbook);

            

            HSSFRow row = sheet.getRow(1);

            fe.setCurrentRow(row);

            HSSFCell cell = row.getCell((short)3);

            System.out.println(cell.getCellFormula());

            HSSFCell newcell = fe.evaluateInCell(cell);

            System.out.println(newcell.getCellType());

            b.close();

            

        }

        catch (FileNotFoundException fnfe) {

            fnfe.printStackTrace();

        }

        catch (IOException ioe) {

            ioe.printStackTrace();

        }

        

    }

 

    public static void main(String[] args) {

        XLFormulaTester xLFormulaTester = new XLFormulaTester();

    }

 

}

 

 

 

Any help would be appreciated.

Thanks

Paul


Evotec (UK) Ltd is a limited company registered in England and Wales. 
Registration number:2674265. Registered office: 111 Milton Park, Abingdon, 
Oxfordshire, OX14 4RZ, United Kingdom.