https://issues.apache.org/bugzilla/show_bug.cgi?id=44691
Summary: Error accessing formula values
Product: POI
Version: 3.0
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: [email protected]
ReportedBy: [EMAIL PROTECTED]
Created an attachment (id=21721)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=21721)
The workbook.xls file is generated by the Java code referenced. The PMT
calculation works correctly in the workbook.
Per Nick Burch's request, I am including the Java code and generated
workbook.xls file that I referenced in a posting to the mailing list.
Following is the text from the original posting I sent:
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.
package test.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
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.hssf.util.CellReference;
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();
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
