poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MonuSonu <lalwani.moh...@gmail.com>
Subject How to evaluate logical expression in excel using POI?
Date Fri, 24 Oct 2008 01:54:02 GMT

Hi,

I am trying to get the value from cell which has value calculated by using
logical formula. I am getting value as -30 for all the cells of that column.
But same code works for less complicated formula's cells.
Following is the code I am using to retreive value:

public class POIMain{
    public POIMain() {
    }

    public static void main(String[] args) {
       
       try{
        InputStream myxls = new FileInputStream("C:\\Workspace\\AMR
TAX_ASIC_v1.xls");
        HSSFWorkbook wb     = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheet("Cat-Prop Mapping");       // 4th
sheet
        
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
wb);

                                       
          CellReference cellReferenceSource = new CellReference("H1"); 
          
          HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
           
      for(Iterator rowIter = sheet.rowIterator();rowIter.hasNext();){
      
           
          HSSFRow  row = (HSSFRow)rowIter.next();
          HSSFCell cellSource = row.getCell(cellReferenceSource.getCol()); 
         
           evaluator.setCurrentRow(row);
           
       if (cellSource!=null)
          {
              //HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
              CellValue cellValue= evaluator.evaluate(cellSource);
             
              //CellValue cellValueTarget= evaluator.evaluate(cellTarget);
              
                           
              if(cellValue!=null){
                   switch (cellValue.getCellType()) {
                       case HSSFCell.CELL_TYPE_BOOLEAN:
                       System.out.println(cellValue.getBooleanValue());                  
   
                       break;
                   case HSSFCell.CELL_TYPE_NUMERIC:
                       System.out.println(cellValue.getNumberValue());
                      
                       break;
                   case HSSFCell.CELL_TYPE_STRING:
                       System.out.println(cellValue.getStringValue());                   
   
                       break;
                   
                   case HSSFCell.CELL_TYPE_ERROR:
                       System.out.println(cellValue.getErrorValue());                    
 
                       break;
                   case HSSFCell.CELL_TYPE_BLANK:
                    System.out.println("");                       
                   break;                  
                   // CELL_TYPE_FORMULA will never happen
                   case HSSFCell.CELL_TYPE_FORMULA: 
                    System.out.println("CELL_TYPE_FORMULA");
                       break;
                   default:
                        System.out.println("null");
                       
                   }
                
              }
          }else{
          
              System.out.println("null");
          }
          
      }
      }catch(Exception e){
            System.out.print("Exception in main "+e);
      }
    
    }
}

And PFA excel file.
Please let me know in case you know how to get value of complex formula
cells in excel using POI.

Thanks

http://www.nabble.com/file/p20142924/AMR%2BTAX_ASIC_v1.xls
AMR+TAX_ASIC_v1.xls 



-- 
View this message in context: http://www.nabble.com/How-to-evaluate-logical-expression-in-excel-using-POI--tp20142924p20142924.html
Sent from the POI - User mailing list archive at Nabble.com.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message