I am using POI2.0-RC1-2003
----- Original Message -----
From: [EMAIL PROTECTED]
Date: Thursday, December 18, 2003 2:09 pm
Subject: Memo: Re: RE: How to get the value of a formula cell?
>
>
>
>
> I'm using a version I built from the CVS source tree on 12th December.
> Which version do you have ??
>
>
>
>
> "Suma G Shanthappa" <[EMAIL PROTECTED]> on 18 Dec 2003 04:51
>
> 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?
>
>
> 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.
> >
> >
>
>
> ******************************************************************
> 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.
> ******************************************************************
>
>
> -------------------------------------------------------------------
> --
> 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 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]
