Data Validation: Does this value conform?

[Blake Watson <bl...@pnmac.com>]

I'm assuming the answer to this question is "No" but I wanted to make sure
before I embarked on creating my own functionality.

Is there any way to say:

1) Can I put value x into cell c without violating any data validations the
cell has? e.g.

c.setCellValueWithValidation(v); //only replaces value if v conforms,
otherwise error

2) Does value x conform to specific data validation v? e.g.

v.valueConforms(x); //true if it would be okay to put this value in, false
otherwise

3) Or anything similar like this?

As I say, I'm guessing not since POI seems to be geared toward =creating=
Excel workbooks and thisi s more about =interpreting= Excel workbooks.

-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com <me...@pnmac.com>
www.PennyMacUSA.com <http://www.pennymacusa.com/>

Re: Data Validation: Does this value conform?

[Blake Watson <bl...@pnmac.com>]

>
>
>
> Hmm, sounds like we need an extra method on DataValidationEvaluator or
> similar class to handle all these other cases then!
>
>
​
I think so. I'm hoping I can get to a point so that late spring/early
summer I can start pushing some stuff back. POI has been tremendously
helpful for us.
​

Re: Data Validation: Does this value conform?

[Nick Burch <ap...@gagravarr.org>]

On Thu, 16 Mar 2017, Blake Watson wrote:
> Arrays.asList(
>>    DataValidationEvaluator.getValidationForCell(ref).
>>         getValidationConstraint().getExplicitListValues()
>> ).contains(value)
>>
>> should get you almost all the way there, I think?
>
> \u200bI think you may be overestimating getExplicitListValues(). =) It handles
> one precise situation: When the Excel creator has typed in a list. If he's
> referencing a list by range or name, getExplicitListValues doesn't handle
> it. If he's referencing a list by range, and the range isn't on the same
> page, it's not even parsed out of the XML!

Hmm, sounds like we need an extra method on DataValidationEvaluator or 
similar class to handle all these other cases then!

Nick

Re: Data Validation: Does this value conform?

[Blake Watson <bl...@pnmac.com>]

Nick--

Thanks!

Arrays.asList(
>    DataValidationEvaluator.getValidationForCell(ref).
>         getValidationConstraint().getExplicitListValues()
> ).contains(value)
>
> should get you almost all the way there, I think?


​I think you may be overestimating getExplicitListValues(). =) It handles
one precise situation: When the Excel creator has typed in a list. If he's
referencing a list by range or name, getExplicitListValues doesn't handle
it. If he's referencing a list by range, and the range isn't on the same
page, it's not even parsed out of the XML!

I have handlings for lists, however, and now I want to handle cases where
the constraints are: whole/decimal/date <,<=,=,=>,> or between one or more
other values (which may, themselves, be literal, cell references or other
formulae).

​===Blake===​

Re: Data Validation: Does this value conform?

[Nick Burch <ap...@gagravarr.org>]

On Thu, 16 Mar 2017, Blake Watson wrote:
> Is there any way to say:
> 1) Can I put value x into cell c without violating any data validations the
> cell has? e.g.
>
> c.setCellValueWithValidation(v); //only replaces value if v conforms,
> otherwise error

I don't think so. CellUtil would probably be where I'd expect to see that 
sort of method (it has similar helpful wrappers), but no DV stuff yet

> 2) Does value x conform to specific data validation v? e.g.
>
> v.valueConforms(x); //true if it would be okay to put this value in, false
> otherwise

Arrays.asList(
    DataValidationEvaluator.getValidationForCell(ref).
  	getValidationConstraint().getExplicitListValues()
).contains(value)

should get you almost all the way there, I think?

(Handy helper method on DataValidationEvaluator or similar might be good 
though!)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org