You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Lenny Wintfeld <le...@cecilrep.com> on 2007/12/12 21:09:03 UTC
HSSFFormulaEvaluator returns null pointer exception on call to evaluator.evaluate(cell)
From the http://poi.apache.org/hssf/eval.html web page, using the
HSSFFormulaEvaluator all seems simple. But I'm having trouble getting a
simple test program to run. I'm sure it must be cockpit error on my
part, but I just don't see what it is.
I've tried using a simple one sheet spreadsheet from OpenOffice, saved
as a Excel 97/2000 xls workbook and a similar workbook generated by a
recent copy of MS Excel, saved as an Excel 2003 xls. I've tried using
the released POI 3.0.1 and the POI 3.0.2 beta 1. Both of these
spreadsheets have cell A5 = 1.0 B5=2.0 and C5 has the formula =A5+B5
My long run programming objective is to accept a xls file and extract
certain data from its cells including the results of formula evaluations
in certain cells and format them into a special purpose xml.
Right now my goal is just to get the HSSFFormulaEvaluator to return a
3.0 from cell C5 in my test sheet using the simple test program below.
The commandline takes the xls file name and optionally an output file
name. In this code the output file is not even used (yet). Note too if
my CellReference constructor is given "B5", HSSFFormulaEvaluator
correctly returns 2.0 but with "C5" the call
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
generates a null pointer exception. The stack (not including the caller
itself) is (from the top) HSSFFormulaEvaluator.java:306 perceded by
HSSFFormulaEvaluator.java:203 with the POI 3.0.2 beta 1 library. Similar
results come out of 3.0.1.
What am I doing wrong?
Thanks in advance.
Lenny Wintfeld
CecilRep LLC
package fmlaeval;
import java.util.*;
import java.text.*;
import java.io.*;
import java.lang.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.util.*;
public class TestFmlaEval
{
public HSSFWorkbook xlsFile = null;
File outfile = null;
public static void main(String args[])
{
TestFmlaEval tfe = new TestFmlaEval();
if(args[0] != "")
{
if(args.length > 1 && args[1] != "")
{
tfe.outfile = new File(args[1]);
}
else
{
tfe.outfile = new File(args[0].split("\\.")[0] + ".xml");
//outfile name = excel file root name + xml
}
File excelfile = new File(args[0]);
FileInputStream efis = null;
try
{
efis = new FileInputStream (excelfile);
} catch (FileNotFoundException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
if(excelfile.exists())
{
HSSFWorkbook wb = null;
try
{
wb = new HSSFWorkbook(efis);
} catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, wb);
// suppose your formula is in B3
CellReference cellReference = new CellReference("C5");
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
//HSSFRow row = sheet.getRow(4);
//HSSFCell cell = row.getCell((short)1);
if (cell!=null) {
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
switch(cellValue.getCellType()){
//switch (evaluator.evaluateInCell(cell).getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.println(cell.getErrorCellValue());
break;
// CELL_TYPE_FORMULA will never occur
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
}
}
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFFormulaEvaluator returns null pointer exception on call to
evaluator.evaluate(cell)
Posted by Lenny Wintfeld <le...@cecilrep.com>.
Nick,
Thanks very much. It (of course) worked. I should have read more of the
javadoc and figured it out myself..
Lenny Wintfeld
Nick Burch wrote:
> On Wed, 12 Dec 2007, Lenny Wintfeld wrote:
>> From the http://poi.apache.org/hssf/eval.html web page, using the
>> HSSFFormulaEvaluator all seems simple. But I'm having trouble getting
>> a simple test program to run. I'm sure it must be cockpit error on my
>> part, but I just don't see what it is.
>
> There was one crucial line missing from the docs. Fixed docs should be
> on the site in an hour or so.
>
> You need to tell the evaluator what row it's working on, with a call
> to setCurrentRow(row)
>
>
>> // suppose your formula is in B3
>> CellReference cellReference = new CellReference("C5");
>> HSSFRow row = sheet.getRow(cellReference.getRow());
>> HSSFCell cell = row.getCell(cellReference.getCol());
>
> // This was missing
> evaluator.setCurrentRow(row)
>
>> if (cell!=null) {
>
>
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFFormulaEvaluator returns null pointer exception on call to
evaluator.evaluate(cell)
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 12 Dec 2007, Lenny Wintfeld wrote:
> From the http://poi.apache.org/hssf/eval.html web page, using the
> HSSFFormulaEvaluator all seems simple. But I'm having trouble getting a
> simple test program to run. I'm sure it must be cockpit error on my
> part, but I just don't see what it is.
There was one crucial line missing from the docs. Fixed docs should be on
the site in an hour or so.
You need to tell the evaluator what row it's working on, with a call to
setCurrentRow(row)
> // suppose your formula is in B3
> CellReference cellReference = new CellReference("C5");
> HSSFRow row = sheet.getRow(cellReference.getRow());
> HSSFCell cell = row.getCell(cellReference.getCol());
// This was missing
evaluator.setCurrentRow(row)
> if (cell!=null) {
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org