Formula Evaluator in Apache POI

Question

I have a program in which it pulls data from a workbook. One of the sheets has a cell in it that gets its data from a formula that involves another sheet from the same workbook. The workbook is called "week 11.xls" and the sheet is called Sat AP. I'm trying to use this code below:

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(myWorkbook);
    HSSFCell cell = myWorkbook.getSheetAt(11).getRow(10).getCell(3); 
    CellValue cv = fe.evaluate(cell);

I get the error that it cannot resolve the external workbook week 11 sat.xls It seems as if it is trying to open week 11 sat.xls when it should be trying to open week 11.xls. myWorkbook is used it other functions and it works fine so I know that it is not pointing to the wrong file. Any help would be much appreciated. Thanks!

Solution

There are two ways to handle formula cells in POI, depending on your needs. For performance reasons, when Excel writes a formula cell, it calculates the current value and stores that with the formula too.

If you're not making changes to the file, then you can simply retrieve the cached value from the last time Excel worked on the file, and use that. You'll get the same value that shows in Excel, and you won't need to do any evaluation. Your code would look something like

if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
 switch(cell.getCachedFormulaResultType()) {
  case HSSFCell.CELL_TYPE_STRING:
     HSSFRichTextString str = cell.getRichStringCellValue();
     if(str != null && str.length() > 0) {
        text.append(str.toString());
     }
     break;
  case HSSFCell.CELL_TYPE_NUMERIC:
     HSSFCellStyle style = cell.getCellStyle();
     if(style == null) {
        text.append( cell.getNumericCellValue() );
     } else {
        text.append(
              _formatter.formatRawCellContents(
                    cell.getNumericCellValue(),
                    style.getDataFormat(),
                    style.getDataFormatString()
              )
        );
     }
     break;
  case HSSFCell.CELL_TYPE_BOOLEAN:
     text.append(cell.getBooleanCellValue());
     break;
  case HSSFCell.CELL_TYPE_ERROR:
     text.append(ErrorEval.getText(cell.getErrorCellValue()));
     break;
 }

(This code is taken from the Excel Text Extractor code in POI, ExcelExtractor, if you want to see an example of it in use)

Otherwise, if you are changing the value of cells first, you will need to re-evaluate the formula values. This is largely covered in the formula evaluation page on the POI site. If you have external workbook references, as it looks like do from your error message, don't forget to tell the POI Formula Evaluator how to find the underlying files for each of your external workbooks.

Comments

[Gagravarr]

Do you really need to evaluate the cell though? Are you making any changes to the file, or just reading the file? (If you're not making changes, then there's a much easier way to do it)

[GNic24]

Thanks for the response, I'm not making any changes to the cell but when I try to get the cell value through other methods it returns the wrong amount, The formula in the cell is "H7 + 'Fri AP'!D11" but when I try to get that value it only returns the value from Fri AP D11.

[Gangnus]

Use clearAllCachedResultValues(); Look stackoverflow.com/a/60559846/715269