#31 Formula Tag does not recalculate before saving

[Mark Vejvoda]

While doing so automated testing for our JETT reports, we discovered a weird situation.

A cell uses the formula tag:

<jt:formula text="${periodTotal.amount}/3600"/>

Running the output file (xlsx) through Apache TIKA (to convert it to XML to compare results with expected) I see that this field which uses the formula tag is empty (it is the only field using formula and ending with an empty cell in the XML file)!

Next When i open the output xlsx file in Excel.. i in fact do see the correct formula and values in the cells (4 rows in that column). Now if i do nothing except click saveas and save to another filename, then compare the file created by JETT vs the file resaved in Excel, then DIFF the xlsx files using WinMerge with a plugin which shows MS Office document file diffs (http://freemind.s57.xrea.com/xdocdiffPlugin/en/index.html) then i see in the original JETT output file the cells with the JETT formula TAG appear empty, while the resaved file shows the correct values. Somehow JETT is setting the cell value in a way that is not consistent with how Excel saves it when using the formula tag.

*UPDATE: If i run the unit test for the formula tag, and compare the output file: FormulaTagResult.xlsx with the same file resaved inside excel as a different filename, i see the same results for the formula tag cells.. empty values in winmerge (and tika) but i DO see values in the fields inside excel.

This is a JETT defect. POI states in this link: http://poi.apache.org/spreadsheet/eval.html that we need to recalc all formulas before we save. Doing that fixes the files when i diff them. Attached is my patched excel transformer.

[Mark Vejvoda]

You can compare Excel documents using WinMerge + a plugin for it:

http://winmerge.org/

http://freemind.s57.xrea.com/xdocdiffPlugin/en/index.html

Then you see when comparing the output from JETT and the same file resaved in excel are different without my fix attached.

[Randy Gettman]

I will include your code in ExcelTransformer. I'll also include a new method to set formula evaluation on, so that this processing is only executed when needed/desired.

I may also include another new method to force formula recalculation.

Thanks!

[Randy Gettman]

I have checked in new code to ExcelTransformer. I have added the setEvaluateFormulas and setForceRecalculationOnOpening methods. The setEvaluateFormulas method controls whether JETT will call Apache POI evaluation of all formulas after transformation. The setForceRecalculationOnOpening method controls a flag in the workbook telling Excel whether to recalculate formulas when it's opened. (It doesn't touch the flag if this method isn't called.)

This will be in the next JETT build.

[Randy Gettman]

This change is in JETT 0.8.0, which was released on July 31, 2014.