Data Validation: Does this value conform?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Data Validation: Does this value conform?

Blake Watson
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
[hidden email] <[hidden email]>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Data Validation: Does this value conform?

Nick Burch-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Data Validation: Does this value conform?

Blake Watson
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===​
Reply | Threaded
Open this post in threaded view
|

Re: Data Validation: Does this value conform?

Nick Burch-2
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?
>
> ​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!
Hmm, sounds like we need an extra method on DataValidationEvaluator or
similar class to handle all these other cases then!

Nick


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Data Validation: Does this value conform?

Blake Watson
>
>
>
> 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.