'Apache POI : delete the cached results from the file

I am trying to update an existing Excel file with many formulas.

I can't use evaluate-function ( Excel-file contains a function not supported by Apache POI )

How can I delete the cached results from the file (after update), before I save new file?

I want to try:

“For each cell of type formula → temporarily set the cell type to something different than Cell.CELL_TYPE_FORMULA and then back to the original cell type” Is this possible? An example of such code?

excelapacheapache-poi


Solution 1:[1]

This isn't an answer, but it's too much to fit into a bunch of comments and should help. I ran into the same problem.

  • It looks like the XLS(X) file format allows storing cached calculations for each formula cell. POI does not update them. I'm not sure if POI is capable of marking them as 'invalidated' either; though I think it sets a flag that is a global: "Do not trust ANY of the cached values anymore".

  • That flag appears to get completely ignored by LibreOffice.

  • Excel itself also doesn't listen to this flag unless you confirm that you want to 'enable editing' (I'm not sure if thats the right term; a yellow bar with a button that shows up for most xls you download from the web - I have a dutch i18ned excel, its "Bewerken Inschakelen" on that.

This bug on the POI project's tracker is relevant, and even offers a workaround:

wb.getCreationHelper().createFormulaEvaluator().evaluateAll();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 rzwitserloot

Related Questions