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();

Answer 2

I has the same problem recently. The problem was that with current POI, one can't write document, add the formula to specific field and perform the formula evaluation on a, at this moment, still not existing document. The trick I used to solve it was:

1. Write all data and formulas into a workbook
2. add workbook.setForceFormulaRecalculation(true); before closing this document
3. Write and close the document
4. Open the same document again and only do FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll();
5. Write and close again

Answer 3

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);