By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,493 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,493 IT Pros & Developers. It's quick & easy.

How to evaluate logical expression / formula cell in excel using POI?

P: 1
Hi Expert,

I am trying to get the value from excel formula cell using POI. My code works fine for less complex formula cells, but fails or returns error code for complex formula cells.
Following is the code I am using to get values:-
Expand|Select|Wrap|Line Numbers
  1. public class POIMain{
  2.     public POIMain() {
  3.     }
  4.     public static void main(String[] args) {
  5.  
  6.        try{
  7.         InputStream myxls = new FileInputStream("C:\\Workspace\\AMR TAX_ASIC_v1.xls");
  8.         HSSFWorkbook wb     = new HSSFWorkbook(myxls);
  9.         HSSFSheet sheet = wb.getSheet("Cat-Prop Mapping");       // 4th shEET
  10.         HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
  11.  
  12.           CellReference cellReferenceSource = new CellReference("H1"); 
  13.  
  14.           HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
  15.  
  16.       for(Iterator rowIter = sheet.rowIterator();rowIter.hasNext();){
  17.  
  18.  
  19.           HSSFRow  row = (HSSFRow)rowIter.next();
  20.           HSSFCell cellSource = row.getCell(cellReferenceSource.getCol()); 
  21.  
  22.            evaluator.setCurrentRow(row);
  23.  
  24.        if (cellSource!=null)
  25.           {
  26.               //HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
  27.               CellValue cellValue= evaluator.evaluate(cellSource);
  28.  
  29.               //CellValue cellValueTarget= evaluator.evaluate(cellTarget);
  30.  
  31.  
  32.               if(cellValue!=null){
  33.                    switch (cellValue.getCellType()) {
  34.                        case HSSFCell.CELL_TYPE_BOOLEAN:
  35.                        System.out.println(cellValue.getBooleanValue());                      
  36.                        break;
  37.                    case HSSFCell.CELL_TYPE_NUMERIC:
  38.                        System.out.println(cellValue.getNumberValue());
  39.  
  40.                        break;
  41.                    case HSSFCell.CELL_TYPE_STRING:
  42.                        System.out.println(cellValue.getStringValue());                       
  43.                        break;
  44.  
  45.                    case HSSFCell.CELL_TYPE_ERROR:
  46.                        System.out.println(cellValue.getErrorValue());                      
  47.                        break;
  48.                    case HSSFCell.CELL_TYPE_BLANK:
  49.                     System.out.println("");                       
  50.                    break;                  
  51.                    // CELL_TYPE_FORMULA will never happen
  52.                    case HSSFCell.CELL_TYPE_FORMULA: 
  53.                     System.out.println("CELL_TYPE_FORMULA");
  54.                        break;
  55.                    default:
  56.                         System.out.println("null");
  57.  
  58.                    }
  59.  
  60.               }
  61.           }else{
  62.  
  63.               System.out.println("null");
  64.           }
  65.  
  66.  
  67.  
  68.       }
  69.       }catch(Exception e){
  70.             System.out.print("Exception in main "+e);
  71.       }
  72.  
  73.     }
And PFA excel file on which I am working.
Please help me to resolve this problem.
For example formula in cell is: =IF(ISNA(VLOOKUP($AF4,ruut3,2,FALSE)),"",VLOOKUP($ AF4,ruut3,2,FALSE))
Oct 24 '08 #1
Share this Question
Share on Google+
1 Reply


Dököll
Expert 100+
P: 2,364
I am not sure how to help you with this one, but what is the error code. Could you Google the error code?

Have you also searched the forum on POI, have not yet worked with excel, you're ehead of me there.

In a bit, do Google the error code though...

Dököll
Nov 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.