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

Well, it seems to be working. You have to treat excel gently; it's much happier if you open the external workbook first, then the primary workbook. If you don't, it gives a message about unsafe links, but other than that, everything seems okay. The evaluator only works on single cells, so you will have to loop if you want to recalculate the entire spreadsheet. Also, the external reference has to have been already linked by excel, POI has not implemented that feature yet. Here is what I put into github:

    // Open workbooks
    Path pathBook1 = Paths.get("c:/users/karl/scsb/Vlookup/Book1.xlsx");
    InputStream is = Files.newInputStream(pathBook1);
    XSSFWorkbook book1 = new XSSFWorkbook(is);
    // Add Linked Cell
    // The workbook must already have been linked to Book2.xlsx by Excel
    // The linkExternalWorkbook has not yet been implemented: SEE BUG #57184
    Cell cell = book1.getSheetAt(0).createRow(2).createCell(0);
    cell.setCellFormula("A2+[Book2.xlsx]Sheet1!A1");
    // Create evaluator after the new cell has been added.
    FormulaEvaluator mainEvaluator = book1.getCreationHelper().createFormulaEvaluator();
    XSSFWorkbook book2 = new XSSFWorkbook("c:/users/karl/scsb/Vlookup/Book2.xlsx");
    Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
    workbooks.put("Book1.xlsx", mainEvaluator);
    workbooks.put("Book2.xlsx", book2.getCreationHelper().createFormulaEvaluator());
    mainEvaluator.setupReferencedWorkbooks(workbooks);
//  mainEvaluator.evaluateAll();                            // doesn't work.
//  XSSFFormulaEvaluator.evaluateAllFormulaCells(book1);    // doesn't work.
    mainEvaluator.evaluateFormulaCell(cell);
    System.out.println(cell.getNumericCellValue());
    book2.close();
    // Close and write workbook 1
    is.close();
    OutputStream os = Files.newOutputStream(pathBook1);
    book1.write(os);
    os.close();
    book1.close();