Here is some sample code that should help. Basically, you need to refer to
those cells that have the formula using CellReference object and then pass
it to the HSSFFormulaEvaluator. Also look at this link for more
information: http://jakarta.apache.org/poi/hssf/eval.html
FileInputStream fis = new FileInputStream("c:/temp/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
// suppose your formula is in B3
CellReference cellReference = new CellReference("B3");
HSSFRow row = sheet.getRow(cellReference.getRow());
HSSFCell cell = row.getCell(cellReference.getCol());
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cellValue.getBooleanValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cellValue.getNumberValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cellValue.getStringValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
// CELL_TYPE_FORMULA will never happen
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
"Blanchette, Nicole" <[EMAIL PROTECTED]>
08/09/2006 09:05 AM
Please respond to
"POI Users List" <[email protected]>
To
"'Avik Sengupta'" <[EMAIL PROTECTED]>
cc
[email protected]
Subject
RE: stuck on formula evaluations
Still getting the same error, but thanks, I didn't even think about that.
I
just changed it to convert them to doubles before setting the cell values.
It still isn't evaluating the formula.
-----Original Message-----
From: Avik Sengupta [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 09, 2006 9:51 AM
To: Blanchette, Nicole
Cc: [email protected]
Subject: Re: stuck on formula evaluations
Also, you seem to be settings string as values to the cell. You need to
set
them as numbers if you want to use a numeric function on them.
On Wednesday 09 August 2006 19:14, Blanchette, Nicole wrote:
> The Jakarta page with info on modifying spreadsheets (the HOWTO for
HSSF)
> said that you had to remove the cells and then put them back in... which
> you're right, it does seem weird, and it may not be right for this case.
> But I just tried fixing that, and it didn't change my results at all.
I'm
> still getting the same error message, and the same result is popping up.
>
>
> -----Original Message-----
> From: Avik Sengupta [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 09, 2006 9:41 AM
> To: [email protected]
> Cc: Blanchette, Nicole
> Subject: Re: stuck on formula evaluations
>
>
> What's that delete cell/ create cell thing ... dont understand why you
need
> to
> do it, but I dont think the formula is going to work if you delete the
> cells
> that it refers to...
>
> On Wednesday 09 August 2006 18:57, Blanchette, Nicole wrote:
> > Hi,
> > I'm trying to read in an existing worksheet with formulas, modify it,
and
> > then read the results after the formulas have been calculated (for a
> > GUI). I have the scratchpad jar and a recent version of the poi. I
can't
> > seem to evaluate the formula though, and then read back the result.
I've
> > been testing with a very simple worksheet and program. I've read the
> > Formula Evaluation page on the Jakarta website several times, and
still
> > can't figure it out... Any sort of help would be greatly appreciated.
> >
> > My error message reads:
> > java.lang.NullPointerException
> > at
>
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFor
>
> >m ulaEvaluator.java:281)
> > at
>
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEval
>
> >u ator.java:181)
> > at testexcel.Frame1.jbInit(Frame1.java:175)
> > at testexcel.Frame1.<init>(Frame1.java:81)
> > at testexcel.testexcel.<init>(testexcel.java:20)
> > at testexcel.testexcel.main(testexcel.java:50)
> >
> > relevant part of my code:
> >
> > import org.apache.poi.hssf.usermodel.HSSFCell;
> > 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.usermodel.HSSFFormulaEvaluator;
> >
> >
> > POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("file
>
> path"));
>
> > FileOutputStream stream = new FileOutputStream("new file
path");
> > HSSFWorkbook wb = new HSSFWorkbook(fs);
> > HSSFSheet sheet = wb.getSheetAt(0);
> > HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet,
> > wb);
> > wb.write(stream);
> >
> > //get necessary rows & cells
> > HSSFRow row1 = sheet.getRow(1);
> > HSSFRow row2 = sheet.getRow(2);
> > HSSFCell cell1 = row1.getCell((short) 0);
> > HSSFCell cell2 = row2.getCell((short) 0);
> >
> > //remove cells that you're writing to
> > row1.removeCell(cell1);
> > row2.removeCell(cell2);
> >
> > //create new cells to insert
> > HSSFCell cell5 = row1.createCell((short)0);
> > HSSFCell cell6 = row2.createCell((short)0);
> > HSSFCell cell7 = row1.getCell((short)1);
> > HSSFCell cell8 = row2.getCell((short)1);
> >
> > //set values of cells
> > cell5.setCellValue("5"); //these values actually come from
user
> > input, from textfield
> > cell6.setCellValue("1");
> > cell7.setCellValue("bob");
> >
> > //write to workbook
> > wb.write(stream);
> > stream.close();
> >
> > //evaluate simple formula in cell8 (=cell5 - cell6)
> > HSSFFormulaEvaluator.CellValue cellValue =
> > evaluator.evaluate(cell8);
> > String a = String.valueOf(cellValue); //value must be string
to
> > read out in textfield
> >
> > //retrieve values
> > jLabel6.setText(cell7.getStringCellValue());
> > jTextField3.setText(a); //these text fields are showing up
blank
> > when I run it
> >
> >
> > I'm new to the list, new to poi, and new to Java, so my apologies if
this
> > has already been gone over a bunch of times or if I ask a lot of
stupid
> > questions.
> > Thanks,
> > Nicole
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
-----------------------------------------
This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and
any attachments are believed to be free of any virus or other
defect that might affect any computer system into which it is
received and opened, it is the responsibility of the recipient to
ensure that it is virus free and no responsibility is accepted by
JPMorgan Chase & Co., its subsidiaries and affiliates, as
applicable, for any loss or damage arising in any way from its use.
If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.
