You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by vedgunjan <ve...@yagnaiq.com> on 2007/10/07 11:01:55 UTC
How to read the Content of the cell whose type is : CELL_TYPE_FORMULA
Hi Experts,
This is my requirements:
Am creating Excel Workbook through Java code and storing the workbook in DB
as a BLOB.
I then have to read that Excel file and Put the data back in to PDF
document.
For this am reading the Excel sheet and converting the excel data into
in-memory xml stream.
The Problem is, the cell which are of type HSSFCell.CELL_TYPE_FORMULA, on
reading the value of the cell,using cell.getCellFormula(), am getting the
formula as string, but actually I want the value which is present in that
cell.
For example: lets say a cell has formula as =+G5*F5, and its value as 200.
Below is the code for reading the cell contents.
if (totalPriceCell != null)
{
int cellType = totalPriceCell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_FORMULA)
{
cellValue =
String.valueOf(totalPriceCell.getNumericCellValue());//this return 0.0
cellValue = totalPriceCell. getCellFormula();//this return
=+G5*F5
}
}
This is my Problem:
How will the get the content of that cell i.e 200.
Hope am able to explain my problem.
Can some one guide me as how I will be able to get the content of the cell
of type FORMULA.
Thanks & Regards
Ved Gunjan.
RE: How to read the Content of the cell whose type is : CELL_TYPE_FORMULA
Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 8 Oct 2007, vedgunjan wrote:
> I didn't actually get what you want to say. Do you mean that I will have
> to evaluate the formula result myself in the code?
Something has to evaluate the formula. If it's an existing one, then
normally excel will have already done this. If excel was in a funny mood,
or the file didn't come from excel, then it might not already be
evaluated. In that case, yes, you'll need to evaluate it yourself
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: How to read the Content of the cell whose type is : CELL_TYPE_FORMULA
Posted by vedgunjan <ve...@yagnaiq.com>.
~~Nick Wrote:
IIRC, calling getNumericCellValue() on a formula cell ought to return the
result of the formula, but only if the formula has been evaluated. If
nothing has evaluated the formula, the answer will be blank.
Opening the file in excel and saving it again ought to sort it, as would
using the formula parser code in the scratchpad to calculate it yourself
I didn't actually get what you want to say. Do you mean that I will have to
evaluate the formula result myself in the code?
Isn't there's a way by which I can get the result of the formula?
Thanks & Regards
Ved Gunjan.
Incubation Center, C -201/ Unit No: 5, Pune IT Park,
Aundh Rd, Pune, India.
Meet Me Details: Tel: 91.20.30223.100 | Fax: 91.20.30223.100 | Cell:
9890015916
www.yagnaiq.com
________________________________
"Lack of will power has caused more failure than lack of intelligence or
ability"
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Monday, October 08, 2007 5:26 PM
To: POI Users List
Subject: Re: How to read the Content of the cell whose type is :
CELL_TYPE_FORMULA
On Sun, 7 Oct 2007, vedgunjan wrote:
> The Problem is, the cell which are of type HSSFCell.CELL_TYPE_FORMULA,
> on reading the value of the cell,using cell.getCellFormula(), am getting
> the formula as string, but actually I want the value which is present in
> that cell.
IIRC, calling getNumericCellValue() on a formula cell ought to return the
result of the formula, but only if the formula has been evaluated. If
nothing has evaluated the formula, the answer will be blank.
Opening the file in excel and saving it again ought to sort it, as would
using the formula parser code in the scratchpad to calculate it yourself
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to read the Content of the cell whose type is : CELL_TYPE_FORMULA
Posted by Nick Burch <ni...@torchbox.com>.
On Sun, 7 Oct 2007, vedgunjan wrote:
> The Problem is, the cell which are of type HSSFCell.CELL_TYPE_FORMULA,
> on reading the value of the cell,using cell.getCellFormula(), am getting
> the formula as string, but actually I want the value which is present in
> that cell.
IIRC, calling getNumericCellValue() on a formula cell ought to return the
result of the formula, but only if the formula has been evaluated. If
nothing has evaluated the formula, the answer will be blank.
Opening the file in excel and saving it again ought to sort it, as would
using the formula parser code in the scratchpad to calculate it yourself
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org