Java POI XSSF VLookup formula

Question

I am trying to put simple VLookup formula in my ".xlsx" file using Java and Apache POI.
This formula is having the external reference and that is NOT working for me.

So to give you more details I am using poi and poi-ooxml version 3.13 and excel 2007.
I am placing the formula into cell this way (where cell is a Cell):

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("StringContainingFormula");

And then evaluate the formula, I have tried three different ways but with NO luck. (wb is XSSFWorkbook).

1

FormulaEvaluator mainWorkbookEvaluator = wb.getCreationHelper().createFormulaEvaluator();
Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
workbooks.put("SpreadsheetName.xlsx", mainWorkbookEvaluator);
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
mainWorkbookEvaluator.evaluateAll();

2

XSSFEvaluationWorkbook.create(wb);
Workbook nwb = wb;
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (Sheet sheet : nwb) {
    for (Row r : sheet) {
        for (Cell c : r) {
            if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                try {
                    //evaluator.evaluateFormulaCell(c);
                    evaluator.evaluate(c);
                } catch (Exception e) {
                    System.out.println("Error occured in 'EvaluateFormulas' : " + e);
                }
            }
        }
    }
}

3

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);  

The problem is, it writes to Excel file and then while evaluating it throws error:

java.lang.IllegalArgumentException: Invalid sheetIndex: -1

Now if I open the Excel file, it gives me warning:

Automatic update of links has been disabled

If I enable the content, formula shows the result properly and if I do not do anything than formula resulting in #N/A.
Now if I select the cell with formula in it and click formula bar and hit enter than formula shows the result.

Update:

So, I disabled the warning message by going into Excel options and it started giving me the formula inside the cell.
BUT, when I tried to get the result value from it using

if (cell.getCachedFormulaResultType == Cell.CELL_TYPE_STRING) {
    System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
}

It never gave me the getCachedFormulaResultType as CELL_TYPE_STRING, it always return CELL_TYPE_NUMERIC. It supposed to return string value. I am expacting URL and someother value (words seperated by "|" - "cat|dog|bird").

I appreciate any help/suggestion.

Answer 1

So, as I don't have any other option, I had to copy the referenced data to main workbook (I know it's not good idea, in case if the data is huge) to make things working.
So what I did is:

1. Copy the referenced data from external workbook.
2. Create new worksheet on main workbook(in which you need to write your formula) and paste the referenced data.
3. Now write the formula and this time instead of external workbook use newly created worksheet(with pasted data).
   Note*: You need to write all the data (including formulas) first before heading to evaluate the formulas.
4. Then open workbook for reading if you have closed already and evaluate all the formulas like this (wb is XSSFWorkbook):

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);