poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Blanchette, Nicole" <nicole.blanche...@pw.utc.com>
Subject stuck on formula evaluations
Date Wed, 09 Aug 2006 13:27:51 GMT
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/


Mime
View raw message