Help reading formula in a spreadsheet..

[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: Help reading formula in a spreadsheet..

[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/