POI does not implement all functions and there are bugs reported regularly. We have little hope of getting close to total coverage. This is the method and javadoc that I plan to change: /** * <p> * Returns the formatted value of a cell as a {@code String} regardless * of the cell type. If the Excel format pattern cannot be parsed then the * cell value will be formatted using a default format. * </p> * <p>When passed a null or blank cell, this method will return an empty * String (""). Formulas in formula type cells will not be evaluated. * </p> * * @param cell The cell * @return the formatted cell value as a String */ public String formatCellValue(Cell cell) { return formatCellValue(cell, null); }
The excel file will usually have a cached value stored on the cell. We can still fail if this cached value is missing.
The current code returns the formula if the formula evaluator is not set - so I don't want to break backward compatibility. Maybe better to add a boolean param which says whether to prefer the cached values.
See bug #45404 for the original contribution, which is where the formula logic first came from I'd lean towards a boolean when you create the DataFormatter, similar to how we do for CSV stuff and locales, probably with a getter and setter for later changes. That's probably easier to use than overriding the format method If there's no cached value, I guess we return something like #N/A (error type 0x2a)?
I've added DataFormatter setUseCachedValuesForFormulaCells - if there is no cached value, it returns the cell formula (which is the legacy behaviour). - r1895699
I will follow up with extra test coverage over the coming days.
This issue was raised based on comments in https://stackoverflow.com/questions/70260410/generic-way-to-parse-excel-files