Bug 44691 - Error accessing formula values
Summary: Error accessing formula values
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-03-27 07:54 UTC by Keith Bennett
Modified: 2008-03-27 15:21 UTC (History)
0 users



Attachments
The workbook.xls file is generated by the Java code referenced. The PMT calculation works correctly in the workbook. (4.00 KB, application/vnd.ms-excel)
2008-03-27 07:54 UTC, Keith Bennett
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Keith Bennett 2008-03-27 07:54:18 UTC
Created attachment 21721 [details]
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();
	}

}
Comment 1 Nick Burch 2008-03-27 11:23:29 UTC
Thanks for the code, I've created a failing test in svn

Not sure why we're getting an error back, but at least we have something to test against. Maybe Josh might be able to enlighten us... :)
Comment 2 Josh Micich 2008-03-27 13:08:10 UTC
fixed in svn r641964

This was a problem in ExternalNameRecord.serialize(int, byte[]).  I originally left a TODO in that method to write a junit, because the code seemed difficult to read.

Perhaps at some stage we could re-visit these serialize() / fillFields() methods of the org.apache.poi.hssf.record.Record hierarchy.  It's difficult to keep track of byte array offsets while streaming record fields.  The JDK (with DataInputStream / DataOutputStream) shows better examples of how to do this.
Comment 3 Josh Micich 2008-03-27 15:21:41 UTC
(In reply to comment #2)
> fixed in svn r641964

oops - that message was for bug 44695. This bug was just fixed in svn r641996.

According to Excel help doc, PMT() takes between 3 and 5 args, POI was only allowing 4-5.

I deleted  TestBug44691.java (after making sure it works) because the bug turned out to be localized to Pmt.java. A new test TestPmt.java was added to confirm this bug-fix.