While doing so automated testing for our JETT reports, we discovered a weird situation.
A cell uses the formula tag:
<jt:formulatext="${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.
Last edit: Mark Vejvoda 2014-06-19
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
While doing so automated testing for our JETT reports, we discovered a weird situation.
A cell uses the formula tag:
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.
Last edit: Mark Vejvoda 2014-06-19
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.
I'll make further comments on Ticket #31 that you've filed for this issue.