Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <1594C3C2674B7F4F9BFC8BD8F8854D260112CBAD@pusehe0q.eh.pweh.com>
From: "Blanchette, Nicole" <nicole.blanchette@pw.utc.com>
To: "'Avik Sengupta'" <avik.sengupta@itellix.com>
Cc: poi-user@jakarta.apache.org
Subject: RE: stuck on formula evaluations
Date: Wed, 9 Aug 2006 10:05:44 -0400 
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"


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:avik.sengupta@itellix.com]
Sent: Wednesday, August 09, 2006 9:51 AM
To: Blanchette, Nicole
Cc: poi-user@jakarta.apache.org
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:avik.sengupta@itellix.com]
> Sent: Wednesday, August 09, 2006 9:41 AM
> To: poi-user@jakarta.apache.org
> 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: 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/