You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Keith Bennett <fo...@gmail.com> on 2008/03/26 22:48:59 UTC
How do you access formula values?
*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();
}
}
Re: How do you access formula values?
Posted by Keith Bennett <fo...@gmail.com>.
Thanks, Nick. Per your request, I've created Bug 44691. Yes, I have
verified that the formula is correctly evaluated by Excel. I appreciate you
looking at this. What I'm trying to do with HSSF is use it to compute the
PMT value and use this value in my system without having to generate an
actual .xls file. I simply generated the .xls file in my test code to
ensure that the formula is good. If I can do this, it will be a solution
that my client will absolutely love. I really like the work you've done.
It's a very useful framework.
Keith
On 3/27/08, Nick Burch <ni...@torchbox.com> wrote:
>
> On Wed, 26 Mar 2008, Keith Bennett wrote:
> > *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.
>
> Could you upload your code and workbook to a new bugzilla issue? Your
> email client appears to have added asterisks all over your code, so I'm
> not able to try it out.
>
> Also, have you ensured that your formula can really be correctly evaluated
> by excel? Getting back CELL_TYPE_ERROR tends to indicate that that's
> something up with the formula
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: How do you access formula values?
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 26 Mar 2008, Keith Bennett wrote:
> *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.
Could you upload your code and workbook to a new bugzilla issue? Your
email client appears to have added asterisks all over your code, so I'm
not able to try it out.
Also, have you ensured that your formula can really be correctly evaluated
by excel? Getting back CELL_TYPE_ERROR tends to indicate that that's
something up with the formula
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org