Bug 58227 - Excel Data table not evaluated
Summary: Excel Data table not evaluated
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 57840
  Show dependency tree
Reported: 2015-08-09 20:43 UTC by nirzohar
Modified: 2016-06-15 10:29 UTC (History)
0 users

Excel file with example (14.74 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-08-10 13:56 UTC, nirzohar
Java file example (1.96 KB, text/plain)
2015-08-10 13:57 UTC, nirzohar

Note You need to log in before you can comment on or make changes to this bug.
Description nirzohar 2015-08-09 20:43:22 UTC
If you'll go in the Excel application, and click on "Recalculate All", it will evaluate all formulas and Data Tables at the workbook. But if you'll run FormulaEvaluator.evaluateAll() (from Java code) it's calculate only formulas without Data Tables.

I was looking for alternative solution to run and calculate Data Table, cannot find any.

Comment 1 Nick Burch 2015-08-10 06:01:11 UTC
Currently, the Formula Evaluator only iterates over sheets, rows and cells

Any chance you could create a very small test file with a data table in it along with some regular formulas, and a junit test case that tweaks some values in the spreadsheet and table, then does a recalculate, and finally asserts the updated values? That should pass for the sheets, but fail for the data table, but will be a good start for an eventual fix
Comment 2 nirzohar 2015-08-10 13:56:48 UTC
Created attachment 32986 [details]
Excel file with example
Comment 3 nirzohar 2015-08-10 13:57:48 UTC
Created attachment 32987 [details]
Java file example
Comment 4 nirzohar 2015-08-10 14:03:16 UTC
The attached excel file contains data table (M4:P52).
If you'll change the input 3 for the data table, and run "Recalculate All", you'll see the values in the data tables changes. (Only after click on recalculate all).

The same logic in the attached java file. I change the 3rd input (cell C4), and recalculate all cells, but data tables values do not effected.

Note, I use private method evaluateAllCells which run over all cells and calculate the formula. I use this method since FormulaEvaluator.evaluateAll() throw an exception.

Comment 5 Nick Burch 2015-08-10 14:04:33 UTC
Thanks for that. However, the test java program doesn't seem to be doing any checks on the data table part, on the main sheet part. Any chance you could add that check in too?
Comment 6 nirzohar 2015-08-10 19:40:44 UTC
The java program (line 42) get the value from M4 cell. This value should be updated after recalculate with data table.

To manually simulate this, open the file with excel, change the value in cell C4. Look at the value at M4 (just random cell in the data table), this value in cell M4 will be change only after you'll run "recalculate all".

The java app do the same logic.