XL reading data using Apache Poi

[Gajendra Jain]

Hi, 

I am getting an error while reading the data from the xlsx which has a value from the external reference. 

Could you please let me know how to read the value from the excel? 

external reference formula is 

='C:\XL Date\[Datainsert.xlsx]dateReferences'!$C$9 

Exception in thread "main" java.lang.RuntimeException: not implemented yet 
        at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:127) 
        at org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:615) 
        at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:510) 
        at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268) 
        at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119) 
        at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079) 
        at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066) 
        at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426) 
        at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526) 
        at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510) 
        at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467) 
        at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447) 
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568) 
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176) 
        at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:148) 
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286) 
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230) 
        at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264) 
        at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:179) 
        at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateInCell(XSSFFormulaEvaluator.java:44) 
        at com.fleetmgmt.test.DateReferenceTest.main(DateReferenceTest.java:38) 




Code Iam using is 

 Workbook workbook = WorkbookFactory.create(new FileInputStream("C:/XL Date/TestData.xlsx")); // or sample.xls 
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
        System.out.println("Number Of Sheets" + workbook.getNumberOfSheets()); 
        Sheet sheet = workbook.getSheetAt(0); 
        System.out.println(sheet.getSheetName()); 
        System.out.println("Number Of Rows:" + sheet.getLastRowNum()); 

        Row row = sheet.getRow(13); 
      //  System.out.println("Cell Value:" + row.getCell(0).getStringCellValue()); 
        
        //for(int col = 0; col<20;col++){ 
            Cell cell = row.getCell(0);   
            if (cell != null) { 
                
                switch (evaluator.evaluateInCell(cell).getCellType()) { 
                case Cell.CELL_TYPE_BOOLEAN: 
                    System.out.println(cell.getBooleanCellValue()); 
                    break; 
                case Cell.CELL_TYPE_NUMERIC: 
                    System.out.println(cell.getNumericCellValue()); 
                    break; 
                case Cell.CELL_TYPE_STRING: 
                    System.out.println(cell.getStringCellValue()); 
                    break; 
                case Cell.CELL_TYPE_BLANK: 
                    break; 
                case Cell.CELL_TYPE_ERROR: 
                    System.out.println(cell.getErrorCellValue()); 
                    break; 

                // CELL_TYPE_FORMULA will never occur 
                case Cell.CELL_TYPE_FORMULA: 
                    System.out.println("Formala "); 
                    break; 
                } 
            }

[Shawn Knight]

You know, your issue has nothing to do with selenium -- however, I would start looking at, "com.fleetmgmt.test.DateReferenceTest.main(DateReferenceTest.java:38)"