Thanks, that makes sense wrt custom implementations of FormulaEvaluator - I hadn't thought about anyone rolling their own, but it's an interface, so quite possible. Too bad we can't require Java 8 yet and use default methods.
I can work with the new *Evaluator class idea. And the HSSF limitations will just mean more unit tests :) I have Excel 2016 available so I can create test workbooks, save them as both XLSX and XLS, and compare evaluations. I can then write unit tests based on them that expect the results seen in Excel. That should give us reference points for confidence in our replication of their logic, especially around rule priority/order and XLS HSSF files. On Fri, Jan 27, 2017 at 11:05 PM Nick Burch <apa...@gagravarr.org> wrote: > On Sat, 28 Jan 2017, Greg Woolsey wrote: > > As noted in one of the method JavaDocs, we also need to expose and make > use > > of the ConditionalFormattingRule "priority" attribute. That's key to > > matching the right rule when more than one rule applies to a cell. Only > > the first match in priority order is applied. > > Your slight challenge is that not all Conditional Formatting rules have a > priority... XLSX ones do, and newer XLS ones based on CFRule12Record (sid > = 0x087A) do, but the older XLS ones (CFRuleRecord / 0x01B1) don't. I'm > not sure what Excel does for those, but my hunch (based on our API) is > that it uses their order as a priority. > > > > I've created a fork in GitHub for this, and committed a stab at > > high-level API methods that could be added to the FormulaEvaluator > > interface: > > > https://github.com/WoozyG/poi/commit/d44fee7bd03ed450af589467ec90e2581b9f2b16$ > > FormulaEvaluator is an interface, which we have 4 implementations of in > our codebabse, and I'd guess that other complex users of POI will have > dozens more. I'm not sure, therefore, that we want to be putting all of > the CF and DV logic methods on there, especially as it'll be common to all > implementations > > The HSSF classes for CF all use org.apache.poi.ss.formula.Formula which is > PTG based. The HSSF classes for DV seem to store the raw PTGs. > > If we added two new SS usermodel classes, eg > ConditionalFormattingEvaluator and DataValidationEvaluator, these could be > classes (not interfaces) with your proposed new methods on. They could > hold the logic (once) for all formats (as it's basically the same on all) > for priority, checking etc > > Doing that would also mean that "our" new classes could call out to our > existing low-level ones to evaluate formulas. That would mean we wouldn't > have to make a breaking change to the FormulaEvaluator interface too > > Might that work for you? > > > No implementations have been done yet, and the Vaadin comments indicate > > HSSF doesn't parse conditional formatting properly or something, and > can't > > be evaluated correctly currently. I don't know exactly what they found > > wrong, and it's rather annoying they didn't file any bugs. > > I think that comment is out of date, from before the CF work in 3.13 > > Nick > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org > For additional commands, e-mail: dev-h...@poi.apache.org > >
