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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
