Thanks for the feedback. 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#diff-7038cb0544a92793c214095bf721a1ff Comments and better ideas welcome, of course. I think these methods could serve UI projects well, centralizing the logic needed to replicate Excel's behavior in these areas. 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. 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. Greg On Fri, Jan 27, 2017 at 3:18 AM Nick Burch <apa...@gagravarr.org> wrote: > On Fri, 27 Jan 2017, Greg Woolsey wrote: > > This can be done today by accessing the evaluate* package-scoped methods > in > > o.a.p.ss.formula.WorkbookEvaluator from a class defined in that package. > > This requires some understanding of formula parsing and the resulting Ptg > > array, and uses a method explicitly noted to not be for public use. > > > > A general-purpose public method would look something like the other > > evaluate* methods: > > > > public ValueEval evaluate(String formula, CellReference ref, FormulaType > > formulaType) { > > We have some evaluation related interfaces, maybe we could put one of > those onto the Conditional Formatting and Data Validation classes, then > add a evaluate method on WorkbookEvaluator based on that? (Add / expose) > > > However, we may also want a more semantic way to handle evaluating data > > validations and conditional formatting. Using them has several > > complexities and wrinkles that make it hard to do right in all cases > > I'd lean towards adding ConditionalFormattingEvaluator and > DataValidationEvaluator classes, which do the hard work of working out > what applies, which of two competing things win etc. Put user-facing > methods on those. Have those delegate down to the new WorkbookEvaluator to > check if a given formula is true / false / etc > > > This is definitely a candidate for a branch/discuss/refine/merge > workflow, > > which I'm willing to pursue. I haven't done SVN branches and merges in > > about 7 years, but how bad can it be? :D > > You can always branch it in github if needed! > > > One wrinkle is that I've seen the Vaadin code that does the conditional > > formatting, because I'm a licensed customer, but it is using their > > commercial license. That means I can't do the POI work on the more > complex > > and complete steps above. > > I think it's publically available source, just not under a license we can > use. I've certainly found the source when tracking down some odd bugs! You > won't be able to re-use their code, but I don't think that having seen it > precludes you writing the POI one > > > I'm also willing to get my boss to contact Vaadin and see if they will > > release the relevant bits of conditional formatting code to us for use in > > POI with the Apache 2 license. She's very good at that sort of thing. I > > wouldn't want it directly, but the patterns it follows likely mirror some > > of what we need, and I don't want to cause a problem where there doesn't > > need to be one. > > Maybe the way to sell it is not as "can you give" or "can you relicense", > but "would you like to make someone else take over the development and > maintenance for you" :) > > Nick > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org > For additional commands, e-mail: dev-h...@poi.apache.org > >
