Hi,
I just tried running your code and the xls file you sent. Everything
works fine here. I am able to get the value of cell B4 as 600.0
Which version of POI are you using? May the problem is with the POI
version you are using.
Regards,
Suma
----- Original Message -----
From: [EMAIL PROTECTED]
Date: Wednesday, December 17, 2003 9:13 pm
Subject: Memo: Re: RE: How to get the value of a formula cell?
>
>
>
>
> Hmm... I tried this, but it did not seem to work.
>
> Here's my code:
>
> import java.io.*;
> import org.apache.poi.hssf.usermodel.*;
> import org.apache.poi.hssf.util.*;
> import org.apache.poi.poifs.filesystem.*;
> import java.net.URL;
>
> public class PoiExperiment {
>
> public static void main (String[] args) {
>
> PoiExperiment pe = new PoiExperiment();
>
> try {
>
>
> FileInputStream fis = new FileInputStream("E:/Desig
> POIFSFileSystem fs = new POIFSFileSystem(fis);
>
> HSSFWorkbook wb = new HSSFWorkbook(fs);
>
> HSSFSheet sh = wb.getSheet("Sheet1");
>
> HSSFRow row = sh.getRow(3);
> HSSFCell cell = row.getCell((short)1);
>
> String cellVal = "";
> String fVal = "";
>
> if (cell.getCellType() ==
> HSSFCell.CELL_TYPE_STRING) {
> cellVal = cell.getStringCellValue();
> System.out.println("String: " + cellVal);
> } else if (cell.getCellType() ==
> HSSFCell.CELL_TYPE_NUMERIC) {
> cellVal = new
> Double(cell.getNumericCellValue()).toString();
> System.out.println("Numeric: " + cellVal);
> } else if (cell.getCellType() ==
> HSSFCell.CELL_TYPE_FORMULA) {
> fVal = cell.getCellFormula();
>
> System.out.println("Formula: " + fVal + "
> : Value: " + new Double(cell.getNumericCellValue()));
> if (fVal.startsWith("SUM")) {
> Double d = null;
> d = calcSum(fVal);
> System.out.println("Sum: " + d);
> }
>
> }
>
> }
> catch (FileNotFoundException e) {
> System.out.println("cannot find file: " +
> e.getMessage()); }
> catch (IOException e) {
> System.out.println("IO Exception: "
> + e.getMessage());
> }
>
> }
>
> public static Double calcSum(String formula) {
>
> return new Double(0.0);
>
> }
>
> }
>
> ===========
>
> Ignore the calcSum() method as I haven't really implemented it yet
> - I was going to create a sum function because I couldn't get the
> value.
> Here's the spreadsheet:
>
> (See attached file: formula.xls)
>
> Regards,
>
> Alex B.
>
>
>
>
> "Suma G Shanthappa" <[EMAIL PROTECTED]> on 17 Dec 2003 08:49
>
> Please respond to "POI Users List" <[EMAIL PROTECTED]>
>
> To: "POI Users List" <[EMAIL PROTECTED]>
> cc:
> bcc:
>
> Subject: Re: RE: How to get the value of a formula cell?
>
>
> If the data is written and saved in Excel only, then you can get the
> value of formula cell also. Just get the formula cell and use
> cell.getNumericCellValue() this should give you the value of the
> formula cell also.
>
> Hope this helps you.
>
>
> ----- Original Message -----
> From: [EMAIL PROTECTED]
> Date: Wednesday, December 17, 2003 2:06 pm
> Subject: Memo: RE: How to get the value of a formula cell?
>
> >
> >
> >
> >
> > Hi,
> >
> > I'm having a similar problem. I read Excel 97 spreadsheets that were
> > written and saved in Excel itself, but I do not get the formula
> > values -
> > they are always 0.0.
> >
> > I'm a little confused by what you say about POI reading formulas.
> > You say
> > that POI can only read the formula out, then that if you open and
> > save in
> > Excel POI gives the correct result (is that the formula value ??).
> >
> > If you could clarify the process by which POI can read a formula
> > value(i.e. the result of the formula not the formula itself) I
> > would be very
> > grateful as it will currently save me a lot of work !!
> >
> > Regards,
> >
> > Alex B.
> >
> >
> >
> >
> > Michael Zalewski <[EMAIL PROTECTED]> on 17 Dec 2003 05:32
> >
> > Please respond to "POI Users List" <[EMAIL PROTECTED]>
> >
> > To: POI Users List <[EMAIL PROTECTED]>
> > cc:
> > bcc:
> >
> > Subject: RE: How to get the value of a formula cell?
> >
> >
> > You can't do that with POI. If the cell contains a formula, you
> > can only
> > get
> > the formula out.
> >
> > You must let Excel do the calculation. Excel does this
> > automatically when
> > the spreadsheet is opened. That's why opening and saving with
> > Excel, then
> > looking at the cell with POI yields the correct result.
> >
> > The idea here is that if you build a spreadsheet, you know what
> > numbers you
> > put into C1 and C2, and can easily add them in your program.
> >
> > Or, if you are processing an uploaded spreadsheet, you can read
> > the values
> > of C1 and C2, then add them in your program.
> >
> > POI does not, and perhaps never will, interpret or execute the
> > formulas you
> > put into the spreadsheet.
> >
> > -----Original Message-----
> > From: Suma G Shanthappa [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, December 16, 2003 4:16 AM
> > To: POI Users List
> > Subject: How to get the value of a formula cell?
> >
> >
> > I have a sheet with formula for cell C3. formula is sum(c1:c2)
> > I insert values to c1 and c2. If I try to get the value of C3 I
> > get 0.0
> > How do I get the value of c3 cell? Even if i save all the data and
> > thentry to open the file and try reading the value of c3 cell I
> > get 0.0
> > But if I do save as and save the file with a diff name and try
> to read
> > the value of c3 cell, I get the correct value?
> >
> > Any Idea how to retrieve the value of a formula cell from the same
> > fileto which the data is stored to?
> >
> > Thanks
> > Suma
> >
> >
> > -----------------------------------------------------------------
> --
> > --
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> > -----------------------------------------------------------------
> --
> > --
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
> > ******************************************************************
> > This message originated from the Internet. Its originator may or
> > may not be who they claim to be and the information contained in
> > the message and any attachments may or may not be accurate.
> > ******************************************************************
> >
> >
> >
> >
> >
> >
> >
> >
> > _____________________________________________________
> >
> > This transmission has been issued by a member of the HSBC Group
> > ("HSBC") for the information of the addressee only and should not
> > be
> > reproduced and / or distributed to any other person. Each page
> > attached hereto must be read in conjunction with any disclaimer
> > which
> > forms part of it. This transmission is neither an offer nor the
> > solicitation
> > of an offer to sell or purchase any investment. Its contents are
> > based
> > on information obtained from sources believed to be reliable but
> > HSBC
> > makes no representation and accepts no responsibility or liability
> > as to
> > its completeness or accuracy.
> >
> >
> > -----------------------------------------------------------------
> --
> > --
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
>
> -------------------------------------------------------------------
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
> ******************************************************************
> This message originated from the Internet. Its originator may or
> may not be who they claim to be and the information contained in
> the message and any attachments may or may not be accurate.
> ******************************************************************
>
>
>
>
>
>
> _____________________________________________________
>
> This transmission has been issued by a member of the HSBC Group
> ("HSBC") for the information of the addressee only and should not
> be
> reproduced and / or distributed to any other person. Each page
> attached hereto must be read in conjunction with any disclaimer
> which
> forms part of it. This transmission is neither an offer nor the
> solicitation
> of an offer to sell or purchase any investment. Its contents are
> based
> on information obtained from sources believed to be reliable but
> HSBC
> makes no representation and accepts no responsibility or liability
> as to
> its completeness or accuracy.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
