You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2008/03/27 15:54:18 UTC
DO NOT REPLY [Bug 44691] New: Error accessing formula values
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: dev@poi.apache.org
ReportedBy: forthwind@gmail.com
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44691] Error accessing formula values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44691
Nick Burch <ni...@torchbox.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |ASSIGNED
--- Comment #1 from Nick Burch <ni...@torchbox.com> 2008-03-27 11:23:29 PST ---
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... :)
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44691] Error accessing formula values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44691
--- Comment #3 from Josh Micich <jo...@gildedtree.com> 2008-03-27 15:21:41 PST ---
(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.
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 44691] Error accessing formula values
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=44691
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|ASSIGNED |RESOLVED
Resolution| |FIXED
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2008-03-27 13:08:10 PST ---
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.
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org