I am working on importing a data from a Excel Sheet when a user tries to upload (.xlsx) file. For one particular field Field1 – 2233 its using a Named Range to get the actual value and not the named range.

NamedRange:

“IF(AND(‘Sheet 1′!NbFCbag <>”Weight including baggage”,’Sheet 1’!NbCCbag <> “Weight including baggage”),(‘Sheet 1′!NbFC*’Sheet 1’!WFC + ‘Sheet 1′!NbFCbag*’Sheet 1’!WFCbag) + (‘Sheet 1′!NbCC*’Sheet 1’!WCC + ‘Sheet 1′!NbCCbag*’Sheet 1’!WCCbag),IF(AND(‘Sheet 1′!NbFCbag<>”Weight including baggage”,’Sheet 1’!NbCCbag = “Weight including baggage”),(‘Sheet 1′!NbFC*’Sheet 1’!WFC + ‘Sheet 1′!NbFCbag*’Sheet 1’!WFCbag) + ‘Sheet 1′!NbCC*’Sheet 1’!WCC,IF(AND(‘Sheet 1′!NbFCbag=”Weight including baggage”,’Sheet 1′!NbCCbag <> “Weight including baggage”),’Sheet 1′!NbFC*’Sheet 1’!WFC + (‘Sheet 1′!NbCC*’Sheet 1’!WCC + ‘Sheet 1′!NbCCbag*’Sheet 1′!WCCbag),’Sheet 1′!NbFC*’Sheet 1’!WFC+ ‘Sheet 1′!NbCC*’Sheet 1’!WCC)))”

`
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

 for (Name namedRange : workbook.getAllNames()) {
                    String rangeName = namedRange.getNameName();
                    try {
                        if (NAMED_RANGE.contains(rangeName)) {
                            AreaReference areaReference = new AreaReference(namedRange.getRefersToFormula(), workbook.getSpreadsheetVersion());
                            CellReference[] cellReferences = areaReference.getAllReferencedCells();
                            for (CellReference cellReference : cellReferences) {
                                Sheet namedSheet = workbook.getSheet(cellReference.getSheetName());
                                Row row = namedSheet.getRow(cellReference.getRow());
                                Cell cell = row.getCell(cellReference.getCol());
                                if (cell != null) {
                                    namedRangeValues.put(rangeName, getFormulaCellValueAsString(cell,evaluator));
                                }
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }


 private String getFormulaCellValueAsString(Cell cell, FormulaEvaluator evaluator) {
        CellValue cellValue = evaluator.evaluate(cell);
        logger.info("cellValue:");
        switch (cellValue.getCellType()) {
            case STRING:
                return cellValue.getStringValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return Double.toString(cellValue.getNumberValue());
                }
            case BOOLEAN:
                return Boolean.toString(cellValue.getBooleanValue());
            default:
                return "";
        }
    }

Error:
org.apache.poi.ss.formula.FormulaParseException: Second part of cell reference expected after sheet name at index 35.
    at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:579)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:327)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1556)
    at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1514)
    at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1501)
    at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1875)
    at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:2018)
    at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1986)
    at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1943)
    at org.apache.poi.ss.formula.FormulaParser.intersectionExpression(FormulaParser.java:1916)
    at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1897)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:2044)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:175)
    at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:85)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:61) at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:109) `   




How do I decode this to get the exact value?

I tried using Formula Evaluator but it fails with the error: Second part of cell reference expected after sheet name at index.