You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Blanchette, Nicole" <ni...@pw.utc.com> on 2006/08/09 15:27:51 UTC
stuck on formula evaluations
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(HSSFForm
ulaEvaluator.java:281)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvalu
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: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: stuck on formula evaluations
Posted by Nouman Shaikh <no...@yahoo.com>.
I think its too late but I just saw your mail
just add this line before calling the 'evaluate()' method.
evaluator.setCurrentRow(cellReference.getRow());
It'll work.
Nouman
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(HSSFForm
> ulaEvaluator.java:281)
> at
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvalu
> 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: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
>
--
View this message in context: http://www.nabble.com/stuck-on-formula-evaluations-tf2078797.html#a7375275
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: stuck on formula evaluations
Posted by Avik Sengupta <av...@itellix.com>.
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: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/