You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rana Aich <ra...@yahoo.com> on 2006/08/24 20:18:07 UTC
Please help - getting '#VALUE!" in Cells of 2nd sheet
Hi,
I'm generating an Excel File with two sheets : Sheet1
and Sheet2. In Sheet1 I've calculated few formulas -
and they are working fine.
In the Sheet2 I put formulas based on values on
Sheet1. But instead of getting the Proper Values I'm
getting "#VALUE!" in all the cells. Interstingly after
clicking the individual cells in Sheet2 and pressing
the "Enter" key, the Cell value is shown.
How can I obtain the proper values in the cells of 2nd
sheet instead of "#VALUE!"?
Thanking you all in advance.
---------------------------------------------------------------------
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/
Re: Help reading formula in a spreadsheet..
Posted by de...@yahoo.com.
1. If the xls is created by Excel and not by using
POI, you can simply read the "cached" evaluation of
the formula by using the appropriate
HSSFCell.get###CellValue() method. eg. if you expect
the cell to have a formula that evaluates to a number,
you can simply do:
HSSFCell cell = ... // get the reference to the cell
double value = cell.getNumericCellValue();
2. I noticed you had:
-- snip --
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
String a = String.valueOf(cellValue);
System.out.println("THE FORMULA CELL HAS : " + a);
-- snip --
If you must use the FormulaEvaluator, you will need to
inspect "type" of the CellValue returned by the
formula evaluation and use the appropriate getter as
so:
-- code --
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
// blah
case HSSFCell.CELL_TYPE_STRING:
// blah
// etc...
}
-- code --
Of course, if you are sure that the formula evaluates
to a number, you can simply do:
cellValue.getNumberValue() after the call to
evaluator.evaluate(cell).
HTH,
~ amol
---------------------------------------------------------------------
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/
Help reading formula in a spreadsheet..
Posted by Mike Troxclaire <mi...@hotmail.com>.
Hello..
I am having trouble reading the cells with formula in them. I have read the
documentation on the apache site but am not successful. Can anybody please
tell me what am I doing wrong here....Here is my code..
****************************************************************************
InputStream input =
poiexample.class.getResourceAsStream("myss_01.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
wb);
CellReference cellReference = new CellReference("M87");
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
rows.next(); //skip column headers;
//while loop open - rows
while( rows.hasNext() ) {
//double shelfpack = 0.0;
String vndritemno = "";
String bwiitemno = "";
String itemdesc = "";
String stocknguom = "";
String sellinguom = "";
String priceuom = "";
String shelfpack = "";
String brkshlfpck = "";
String palletuom = "";
//String palletqty = "";
double palletqtydou = 0.00;
String plupccode = "";
double palletwgtdou = 0.00;
double palletcubedou = 0.00;
double pallethgtdou = 0.00;
double palletdepthdou = 0.00;
double palletwidthdou = 0.00;
HSSFRow row = (HSSFRow) rows.next();
System.out.println( "Row #" + row.getRowNum() );
// Iterate over each cell in the row and print out the
cell's content
Iterator cells = row.cellIterator();
//while loop open - cells
while( cells.hasNext() ) {
String string_temp = "";
HSSFCell cell = (HSSFCell) cells.next();
System.out.println( "Cell #" + cell.getCellNum() );
//HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
//switch open
switch ( cell.getCellType() ) {
case HSSFCell.CELL_TYPE_NUMERIC:
//System.out.println( cell.getNumericCellValue()
);
break;
case HSSFCell.CELL_TYPE_STRING :
// System.out.println(cell.getStringCellValue());
string_temp = cell.getStringCellValue();
break;
default:
System.out.println( "unsupported cell type" );
break;
} //switch close
switch (cell.getCellNum()) {
case 0 :
vndritemno = string_temp;
break;
case 12 :
/*
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
String a = String.valueOf(cellValue);
System.out.println("THE FORMULA CELL HAS : " + a);
palletcubedou = Double.valueOf(a).doubleValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC :
palletcubedou = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
if(!StringUtils.isBlank(string_temp))
palletcubedou = Double.valueOf(string_temp).doubleValue();
break;
default :
System.out.println("unsupported cell type");
break;
}
break;
*/
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println("VALUE 1 : " + cellValue.getBooleanValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println("VALUE 2 : " + cellValue.getNumberValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println("VALUE 3 : " + cellValue.getStringValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
// CELL_TYPE_FORMULA will never happen
case HSSFCell.CELL_TYPE_FORMULA:
break;
}
break;
****************************************************************************
Any help in this matter would be much appreciated.
Thanks in advance,
Mike.
_________________________________________________________________
Search from any web page with powerful protection. Get the FREE Windows Live
Toolbar Today! http://get.live.com/toolbar/overview
---------------------------------------------------------------------
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/
Re: Please help - COUNTIF yields '#VALUE!" in Cells of 2nd sheet
Posted by Rana Aich <ra...@yahoo.com>.
Hello All,
Is there any unresolved issue with setting CellFormula
with COUNTIF? I noticed the same issue in the
Jakarta-Poi mailing archive.
My Problem is:
When I use COUNTIF for seting the CellFormula I'm
getting '#VALUE!". I only get the values after
selecting the cell and pressing 'Enter' key.
Whereas, simple Formulas such as SUM and AVERAGE is
working fine.
Can anyone suggest a solution?
--- Rana Aich <ra...@yahoo.com> wrote:
> Thanks for your tips. Actually I went further and
> found that the Formula that I'm using has COUNTIF.
> The
> formula that gives me wrong result is as follows:
>
>
=COUNTIF(sheet1!E2:E183,"<=499")-COUNTIF(sheet1!E2:E183,"<200")
>
> Whereas, I've tried a simple formula as below:
> =SUM(sheet1!E2:E115) and Bingo! It is working. So it
> seems I have done something wrong with the String
> processing with "COUNTIF".
>
> My "Calculation Tab" in "Tool>Option" is in
> Automatic
> mode.
>
> --- deshmol-lists@yahoo.com wrote:
>
> > Check whether you have
> >
> > Tools > Options > [calculations_tab] >
> Calculation
> >
> > ...set to "Automatic".
> >
> >
> > Alternatively, you can also use
> > F9/Shift+F9/Ctrl+Alt+F9/Ctrl+Alt+Shift+F9 to
> > evaluate
> > all changed formulae in the sheet (look up the
> excel
> > help for details/differences of each key
> > combination.
> >
> >
> > ~ amol
> >
> > --- Rana Aich <ra...@yahoo.com> wrote:
> >
> > > Hi,
> > >
> > > I'm generating an Excel File with two sheets :
> > > Sheet1
> > > and Sheet2. In Sheet1 I've calculated few
> formulas
> > -
> > > and they are working fine.
> > >
> > > In the Sheet2 I put formulas based on values on
> > > Sheet1. But instead of getting the Proper Values
> > I'm
> > > getting "#VALUE!" in all the cells. Interstingly
> > > after
> > > clicking the individual cells in Sheet2 and
> > pressing
> > > the "Enter" key, the Cell value is shown.
> > >
> > > How can I obtain the proper values in the cells
> of
> > > 2nd
> > > sheet instead of "#VALUE!"?
> > >
> > > Thanking you all in advance.
> > >
> > >
> > >
> > >
> > >
> >
>
---------------------------------------------------------------------
> > > 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/
> >
> >
>
>
>
---------------------------------------------------------------------
> 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/
Re: Please help - getting '#VALUE!" in Cells of 2nd sheet
Posted by Rana Aich <ra...@yahoo.com>.
Thanks for your tips. Actually I went further and
found that the Formula that I'm using has COUNTIF. The
formula that gives me wrong result is as follows:
=COUNTIF(sheet1!E2:E183,"<=499")-COUNTIF(sheet1!E2:E183,"<200")
Whereas, I've tried a simple formula as below:
=SUM(sheet1!E2:E115) and Bingo! It is working. So it
seems I have done something wrong with the String
processing with "COUNTIF".
My "Calculation Tab" in "Tool>Option" is in Automatic
mode.
--- deshmol-lists@yahoo.com wrote:
> Check whether you have
>
> Tools > Options > [calculations_tab] > Calculation
>
> ...set to "Automatic".
>
>
> Alternatively, you can also use
> F9/Shift+F9/Ctrl+Alt+F9/Ctrl+Alt+Shift+F9 to
> evaluate
> all changed formulae in the sheet (look up the excel
> help for details/differences of each key
> combination.
>
>
> ~ amol
>
> --- Rana Aich <ra...@yahoo.com> wrote:
>
> > Hi,
> >
> > I'm generating an Excel File with two sheets :
> > Sheet1
> > and Sheet2. In Sheet1 I've calculated few formulas
> -
> > and they are working fine.
> >
> > In the Sheet2 I put formulas based on values on
> > Sheet1. But instead of getting the Proper Values
> I'm
> > getting "#VALUE!" in all the cells. Interstingly
> > after
> > clicking the individual cells in Sheet2 and
> pressing
> > the "Enter" key, the Cell value is shown.
> >
> > How can I obtain the proper values in the cells of
> > 2nd
> > sheet instead of "#VALUE!"?
> >
> > Thanking you all in advance.
> >
> >
> >
> >
> >
>
---------------------------------------------------------------------
> > 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/
>
>
---------------------------------------------------------------------
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/
Re: Please help - getting '#VALUE!" in Cells of 2nd sheet
Posted by de...@yahoo.com.
Check whether you have
Tools > Options > [calculations_tab] > Calculation
...set to "Automatic".
Alternatively, you can also use
F9/Shift+F9/Ctrl+Alt+F9/Ctrl+Alt+Shift+F9 to evaluate
all changed formulae in the sheet (look up the excel
help for details/differences of each key combination.
~ amol
--- Rana Aich <ra...@yahoo.com> wrote:
> Hi,
>
> I'm generating an Excel File with two sheets :
> Sheet1
> and Sheet2. In Sheet1 I've calculated few formulas -
> and they are working fine.
>
> In the Sheet2 I put formulas based on values on
> Sheet1. But instead of getting the Proper Values I'm
> getting "#VALUE!" in all the cells. Interstingly
> after
> clicking the individual cells in Sheet2 and pressing
> the "Enter" key, the Cell value is shown.
>
> How can I obtain the proper values in the cells of
> 2nd
> sheet instead of "#VALUE!"?
>
> Thanking you all in advance.
>
>
>
>
>
---------------------------------------------------------------------
> 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/