*Hi, I am new to this list and am new to POI HSSF. I am trying to find a
way to programatically invoke the PMT formula and access the resulting
value. The code below generates an Excel spreasheet file successfully and
even stores the correct value in the A1 cell when I manually open it.
However, when I use the HSSFFormulaEvaluator to access the value that the
formula computes, I keep getting a CELL_TYPE_ERROR. I don't know what I'm
doing wrong, and I can't find any documentation to help me. So, I'm turning
to the users on this mailing list. The code below is an attempt to follow
the examples available at http://poi.apache.org/hssf/eval.html. If anyone
can point out what I'm doing incorrectly, please let me know. When I run
the code below, I get the following printed to the console: *
**
*
inCell type before evaluation: 2
CELL_TYPE_ERROR!!!
inCellValue type after evaluation: 5
What's the value of the formula? 0.0
*
**
*Thanks in advance for your help!*
**
*Keith*
**
*
public* *class* BuildDynamicPmtWorkbook {
*public* *void* buildDynamicPmtWorkbook() {
HSSFWorkbook outWorkbook = *new* HSSFWorkbook();
HSSFSheet outPMTSheet = outWorkbook.createSheet("PMT Sheet");
HSSFRow row = outPMTSheet.createRow((*short*) 0);
HSSFCell cell = row.createCell((*short*) 0);
cell.setCellType(HSSFCell.*CELL_TYPE_FORMULA*);
cell.setCellFormula("PMT(0.09/12,48,-10000)");
FileOutputStream fileOutputStream = *null*;
*try* {
fileOutputStream = *new* FileOutputStream("workbook.xls");
} *catch* (FileNotFoundException e) {
// *TODO* Auto-generated catch block
e.printStackTrace();
}
*try* {
outWorkbook.write(fileOutputStream);
} *catch* (IOException e) {
// *TODO* Auto-generated catch block
e.printStackTrace();
}
*try* {
fileOutputStream.close();
} *catch* (IOException e) {
// *TODO* Auto-generated catch block
e.printStackTrace();
}
InputStream inputStream = *null*;
*try* {
inputStream = *new* FileInputStream("workbook.xls");
} *catch* (FileNotFoundException e) {
// *TODO* Auto-generated catch block
e.printStackTrace();
}
HSSFWorkbook inWorkbook = *null*;
*try* {
inWorkbook = *new* HSSFWorkbook(inputStream);
} *catch* (IOException e) {
// *TODO* Auto-generated catch block
e.printStackTrace();
}
HSSFSheet inPMTSheet = inWorkbook.getSheet("PMT Sheet");
HSSFFormulaEvaluator evaluator = *new* HSSFFormulaEvaluator(inPMTSheet,
inWorkbook);
CellReference cellReference = *new* CellReference("A1");
HSSFRow inRow = inPMTSheet.getRow(cellReference.getRow());
HSSFCell inCell = inRow.getCell(cellReference.getCol());
System.*out*.println("inCell type before evaluation: " + inCell.getCellType
());
evaluator.setCurrentRow(inRow);
HSSFFormulaEvaluator.CellValue inCellValue = evaluator.evaluate(inCell);
*switch* (inCellValue.getCellType()) {
*case* HSSFCell.*CELL_TYPE_BOOLEAN*:
System.*out*.println(inCellValue.getBooleanValue());
*break*;
*case* HSSFCell.*CELL_TYPE_NUMERIC*:
System.*out*.println(inCellValue.getNumberValue());
*break*;
*case* HSSFCell.*CELL_TYPE_STRING*:
System.*out*.println(inCellValue.getStringValue());
*break*;
*case* HSSFCell.*CELL_TYPE_BLANK*:
System.*out*.println("CELL_TYPE_BLANK");
*break*;
*case* HSSFCell.*CELL_TYPE_ERROR*:
System.*out*.println("CELL_TYPE_ERROR!!!");
*break*;
// CELL_TYPE_FORMULA will never happen
*case* HSSFCell.*CELL_TYPE_FORMULA*:
*break*;
}
System.*out*.println("inCellValue type after evaluation: " +
inCellValue.getCellType());
System.*out*.println("What's the value of the formula? " +
inCellValue.getNumberValue());
}
*public* *static* *void* main(String[] args) {
*new* BuildDynamicPmtWorkbook().buildDynamicPmtWorkbook();
}
}
