poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Evaluating expressions outside a cell value context
Date Thu, 02 Feb 2017 01:38:19 GMT
Oh, the primary class is o.a.p.ss.formula.DataValidationEvaluator

On Wed, Feb 1, 2017 at 5:37 PM Greg Woolsey <greg.woolsey@gmail.com> wrote:

> My GitHub branch now contains Data Validation code and unit tests.  The
> test file DataValidationEvaluations.xlsx contains a large set of validation
> examples, including one formula example that applies to a range of cells
> and uses a relative formula.  The evaluation code has corresponding logic
> to offset the relative formula Ptgs from the top left of the region.
>
> Every test is labeled in the file with column A as a description, column B
> as the cell with validation, and column C the expected result, TRUE =
> valid, FALSE = invalid.
>
> The unit test compares the POI validation result with the expected column,
> failing on boolean mismatches.
>
> Have not had time to run all tests yet, but this should only be code
> additions, not modifications.  I'll run them soon.
>
> I'm sure there are code style discussions to be had - for example I
> implemented some things as inner classes for now, but we may want them
> top-level instead.
>
> Comments welcome, this is early code but is built on top of the SS
> interfaces, so should be stable for HSSF and XSSF.
>
> Greg
>
> On Mon, Jan 30, 2017 at 9:55 AM Greg Woolsey <greg.woolsey@gmail.com>
> wrote:
>
> Also, I just found this sample workbook
> <http://download.microsoft.com/download/1/6/F/16F701E9-63BA-48D3-8B48-096F9288F443/AF010235700_en-us_cfsamples_af010235700.xlsx>
in
> the Excel online support docs.  If I have time to turn that into a unit
> test, it's about as complete as we could want.  Some parts are lost saving
> as HSSF, but we can then test that we evaluate what remains the same way as
> newer Excel when opening a legacy formatted file.
>
> On Mon, Jan 30, 2017 at 9:38 AM Greg Woolsey <greg.woolsey@gmail.com>
> wrote:
>
> 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 <apache@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message