Hi again,unfortunately I have some problem using HSSFFormulaEvaluator.evaluate() I can't manage to get CellValue object back. here the stack trace:
[java] Exception in thread "main" java.lang.RuntimeException: CELL[5,1]: B6 formula: SUM(B4:B5)null java.lang.NullPointerException
[java] at testpoi.getFormulaValue(Unknown Source)
[java] at testpoi.main(Unknown Source)
[java] Caused by: java.lang.NullPointerException
[java] at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:281)
[java] at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:181)
[java] ... 2 more
I attach a simple test case program that follows pretty much the
org.apache.poi.hssf.record.formula.eval.GenericFormulaTestCase.genericTest()
but simplified can someone take a look?
thanks in advance Eli
testone.xls
Description: MS-Excel spreadsheet
//package test.one;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.usermodel.*;
public class testpoi {
private InputStream is;
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private String sFileOpen;
public testpoi(String fileName) {
sFileOpen = new String(fileName);
openFile();
}
public void setNumValue(String sCellRef, Integer value ) {
sheet = wb.getSheetAt(0);
CellReference cellReference = new CellReference(sCellRef);
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
if (cell == null)
cell = row.createCell(cellReference.getCol());
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value.doubleValue());
writeFile();
}
public double getNumValue(String sCellRef) {
sheet = wb.getSheetAt(0);
CellReference cellReference = new CellReference(sCellRef);
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
if (cell != null)
return cell.getNumericCellValue();
else
return 0.0;
}
public double getFormulaValue(String sCellRef) throws RuntimeException {
sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
CellReference cellReference = new CellReference(sCellRef);
String formula="no formula";
try {
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
throw new RuntimeException("CELL["+cellReference.getRow()+","+cellReference.getCol()+"]: "+sCellRef +" Cell is null or is not a formula");
formula = cell.getCellFormula();
HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(cell);
if (actualValue.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
return actualValue.getNumberValue();
else
return 0.0;
} catch (RuntimeException re) {
throw new RuntimeException("CELL["+cellReference.getRow()+","+cellReference.getCol()+"]: "+sCellRef +" "+ "formula: " + formula + re.getMessage() + " " + re + " ", re);
}
}
private void openFile() {
try {
is = new FileInputStream(sFileOpen);
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
is.close();
} catch (IOException ioe) {
// TODO: handle exception
}
}
private void writeFile() {
try {
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(sFileOpen);
wb.write(fileOut);
fileOut.close();
} catch (IOException ioe) {
// TODO: handle exception
}
}
public static void main(String argh[]) {
testpoi exl = new testpoi("testone.xls");
exl.setNumValue("B4",new Integer(2));
exl.setNumValue("B5",new Integer(34));
System.out.println("try to get the result");
double exlResult = exl.getFormulaValue("B6");
System.out.println("result:" + exlResult);
}
}
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
