2.6.5 CT_DataValidation

  • Article

Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2009/9/main

Referenced by: CT_DataValidations

CT_DataValidation is a complex type that specifies data validation for a range on this sheet.

Child Elements:

formula1: A CT_DataValidationFormula element (section 2.6.4) that specifies the first formula for the data validation.

  • If operator is "between" or "notBetween" and type is not "custom", "list", or "none", this formula is used as the lesser of two bounding values and MUST exist.

  • If operator is not "between" or "notBetween", or type is "custom", this formula is the only formula and MUST exist.

  • If operator is not "between" or "notBetween", or type is "custom" or "list", either formula is the only formula and MUST exist or CT_DataValidation MUST have a list (section 2.4.66) child element.

  • If the type is "none", this formula MUST NOT exist.

formula2: A CT_DataValidationFormula element that specifies the second formula for the data validation.

  • If operator is "between" or "notBetween" and type is not "custom", "list", or "none", this formula is used as the greater of two bounding values and MUST exist.

  • If operator is not "between" or "notBetween", or type is "custom", "list", or "none", this formula MUST NOT exist.

xm:sqref: A sqref element (section 2.4.5) that specifies ranges to which data validation is applied.

Attributes:

type: An ST_DataValidationType ([ISO/IEC29500-1:2016] section 18.18.21) attribute that specifies the type of data validation.

errorStyle: An ST_DataValidationErrorStyle ([ISO/IEC29500-1:2016] section 18.18.18) attribute that specifies the style of error alert used for this data validation.

imeMode: An ST_DataValidationImeMode ([ISO/IEC29500-1:2016] section 18.18.19) attribute that specifies the Input Method Editor (IME) mode enforced by this data validation.

operator: An ST_DataValidationOperator ([ISO/IEC29500-1:2016] section 18.18.20) attribute that specifies the relational operator used with this data validation. If type is "custom", "list", or "none", the value of the operator attribute is undefined and MUST be ignored.

allowBlank: A Boolean ([XMLSCHEMA2/2] section 3.2.2) attribute that specifies whether the data validation treats empty or blank entries as valid.

Value

Meaning

"true"

The data validation treats empty or blank entries as valid.

"false"

The data validation treats empty or blank entries as invalid.

showDropDown: A Boolean ([XMLSCHEMA2/2] section 3.2.2) attribute that specifies whether to display the drop-down combo box for a list type data validation.

Value of showDropDown

Value of type

Meaning

"true"

list

Displays the drop-down combo box.

"false"

list

Suppresses the drop-down combo box.

showInputMessage: A Boolean ([XMLSCHEMA2/2] section 3.2.2) attribute that specifies whether to display the input prompt message.

Value

Meaning

"true"

Display the input prompt message.

"false"

Suppress the input prompt message.

showErrorMessage: A Boolean ([XMLSCHEMA2/2]  section 3.2.2) attribute that specifies whether to display the error alert message.

Value

Meaning

"true"

Display the error alert message.

"false"

Suppress the error alert message.

errorTitle: An ST_Xstring ([ISO/IEC29500-1:2016] section 22.9.2.19) attribute that specifies the text of the title bar of the error alert. The length of this string MUST be less than or equal to 32 characters.

error: An ST_Xstring attribute that specifies the message text of the error alert. The length of this string MUST be less than or equal to 225 characters.

promptTitle: An ST_Xstring attribute that specifies the text of the title bar of the input prompt. The length of this string MUST be less than or equal to 32 characters.

prompt: An ST_Xstring attribute that specifies the message text of the input prompt. This string MUST be less than or equal to 255 characters.

xr:uid: An ST_Guid ([ISO/IEC29500-1:2016] section 22.9.2.4) attribute that specifies a unique identifier associated with this data validation rule

The following W3C XML Schema ([XMLSCHEMA1/2] section 2.1) fragment specifies the contents of this complex type.

 <xsd:complexType name="CT_DataValidation">
   <xsd:sequence>
     <xsd:element name="formula1" type="CT_DataValidationFormula" minOccurs="0" maxOccurs="1"/>
     <xsd:element name="formula2" type="CT_DataValidationFormula" minOccurs="0" maxOccurs="1"/>
     <xsd:element ref="xm:sqref" minOccurs="1" maxOccurs="1"/>
   </xsd:sequence>
   <xsd:attribute name="type" type="x:ST_DataValidationType" use="optional" default="none"/>
   <xsd:attribute name="errorStyle" type="x:ST_DataValidationErrorStyle" use="optional" default="stop"/>
   <xsd:attribute name="imeMode" type="x:ST_DataValidationImeMode" use="optional" default="noControl"/>
   <xsd:attribute name="operator" type="x:ST_DataValidationOperator" use="optional" default="between"/>
   <xsd:attribute name="allowBlank" type="xsd:boolean" use="optional" default="false"/>
   <xsd:attribute name="showDropDown" type="xsd:boolean" use="optional" default="false"/>
   <xsd:attribute name="showInputMessage" type="xsd:boolean" use="optional" default="false"/>
   <xsd:attribute name="showErrorMessage" type="xsd:boolean" use="optional" default="false"/>
   <xsd:attribute name="errorTitle" type="x:ST_Xstring" use="optional"/>
   <xsd:attribute name="error" type="x:ST_Xstring" use="optional"/>
   <xsd:attribute name="promptTitle" type="x:ST_Xstring" use="optional"/>
   <xsd:attribute name="prompt" type="x:ST_Xstring" use="optional"/>
   <xsd:attribute ref="xr:uid"/>
 </xsd:complexType>

See section 5.4 for the full W3C XML Schema ([XMLSCHEMA1/2] section 2.1).