Bug 58131

Summary:	Conditional Formatting Evaluation
Product:	POI	Reporter:	Nick Burch <apache>
Component:	SS Common	Assignee:	POI Developers List <dev>
Status:	NEW ---
Severity:	enhancement
Priority:	P2
Version:	3.16-dev
Target Milestone:	---
Hardware:	All
OS:	All

Description Nick Burch 2015-07-12 15:38:59 UTC

We've had quite a few discussions about it on the mailing list, such as <http://mail-archives.apache.org/mod_mbox/poi-user/201103.mbox/%3CAANLkTi=mP_bQ7O+T34szv7eWq6nq1u187+gtb=Sjcnmt@mail.gmail.com%3E>, but I don't believe we have any code yet which can tell you which (if any) conditional formatting rules apply to a given cell. For some Excel -> Other use cases, that's something you'd like to be able to know

It would therefore be good to have an evaluator, which can tell you which rules apply to a given cell (including taking account of priorities / formulas / percentages), and which state within that applies (eg for a red/yellow/green format, which one it would be)

It looks like the ASFv2 licensed Vaadin has some support for this already:
http://demo.vaadin.com/javadoc/com.vaadin.addon/vaadin-spreadsheet/1.0.0.beta1/com/vaadin/addon/spreadsheet/ConditionalFormatter.html - we might be able to interest them in contributing that back to speed the development effort involved!

Comment 1 Greg Woolsey 2017-02-24 19:23:19 UTC

I implemented most of this with r1782894, see 

https://poi.apache.org/apidocs/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.html

This does not currently implement any assistance in finding the applicable format bucket from partitioned formats (color, icon, etc.), but does evaluate whether that format applies to a cell or not.  Implementations can then take the applied format rule and determine which bucket the current value belongs to.

Along with this, WorkbookEvaluator has new evaluate(...) methods for non-cell formula evaluations like these.