Re: stuck on formula evaluations

[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/