Conditional formatting uses old value for formula cell

[ianscriven]

This is applicable to the current master branch, not a released version - specifically pull request #648

See the attached file. Both cells have a conditional formatting rule (red with red text) if the value is > 5. With the latest build using POI 3.17, changing A1 to 6 causes only A1 to be highlighted red, even though B1 ("=A1") is also 6. Subsequently changing A1 back to 5 causes B1 to be highlighted red, even though it's value is 5 again.

Seems to me that the conditional formatting is being evaluated before the formula is - so it's using the old value of the cell.

Tagging @alexberazouski and @WoozyG as I'm not sure if this is a Vaadin Spreadsheet issue or a POI issue.

conditional formatting - formula cells.xlsx

[ianscriven]

I've done some investigating on this, and have found the cause.

Line 270 of ConditionalFormatter:
// calculate for cells to the right and below first, so this can have the proper border IDs if needed

So, in the previously attached spreadsheet, conditional formatting for B1 was being evaluated as part of the evaluation of A1 (as it's to the right, and so potentially sets A1's border), which happens before the value of B1 is updated. Then, when the value of B1 does get updated, its conditional formatting is not re-recalculated as it is now in the set cellsEvaluatedInThisRun.