dataValidations (Data Validations)

This collection expresses all data validation information for cells in a sheet which have data validation features applied.

Data validation is used to specify constaints on the type of data that can be entered into a cell. Additional UI can be provided to help the user select valid values (e.g., a dropdown control on the cell or hover text when the cell is active), and to help the user understand why a particular entry was considered invalid (e.g., alerts and messages).

Various data types can be selected, and logical operators (e.g., greater than, less than, equal to, etc) can be used. Additionally, instead of specifying an explicit set of values that are valid, a cell or range reference may be used.

An input message can be specified to help the user know what kind of value is expected, and a warning message (and warning type) can be specified to alert the user when they've entered invalid data.

[Example:

<dataValidations count="1">
<dataValidation type="whole" errorStyle="warning" operator="greaterThan"
showInputMessage="1" showErrorMessage="1" errorTitle="Invalid Data"
error="The value must be a whole number greater than 0."
promptTitle="Whole Number"
prompt="Please enter a whole number greater than 0." sqref="A1">
<formula1>0</formula1>
</dataValidation>
</dataValidations>

end example]

Parent Elements

worksheet (§3.3.1.96)

 

Child Elements	Subclause
dataValidation (Data Validation)	§3.3.1.30

 

Attributes	Description
count (Data Validation Item Count)	The expected number of data validation items for this worksheet.   The possible values for this attribute are defined by the XML Schema unsignedInt datatype.
disablePrompts (Disable Prompts)	A boolean value indicating whether all input prompts for the worksheet are disabled.   The possible values for this attribute are defined by the XML Schema boolean datatype.
xWindow (Top Left Corner (X Coodrinate))	The x-coordinate (relative to window) of top-left corner of the data validation input prompt (textbox). This is per sheet, not per cell. Units in pixels.   The possible values for this attribute are defined by the XML Schema unsignedInt datatype.
yWindow (Top Left Corner (Y Coordinate))	The y-coordinate (relative to window) of top-left corner of the data validation input prompt (textbox). This is per sheet, not per cell. Units in pixels.   The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_DataValidations">

   <sequence>

   <element name="dataValidation" type="CT_DataValidation" minOccurs="1" maxOccurs="unbounded"/>

   </sequence>

   <attribute name="disablePrompts" type="xsd:boolean" use="optional" default="false"/>

   <attribute name="xWindow" type="xsd:unsignedInt" use="optional"/>

   <attribute name="yWindow" type="xsd:unsignedInt" use="optional"/>

   <attribute name="count" type="xsd:unsignedInt" use="optional"/>

</complexType>