ConditionalFormattingRule Class
Defines the ConditionalFormattingRule Class.This class is only available in Office2010.When the object is serialized out as xml, its qualified name is x14:cfRule.
Inheritance Hierarchy
System.Object
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlCompositeElement
DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattingRule
Namespace: DocumentFormat.OpenXml.Office2010.Excel
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
Syntax
'Declaration
<OfficeAvailabilityAttribute(FileFormatVersions.Office2010)> _
<ChildElementInfoAttribute(GetType(Formula))> _
<ChildElementInfoAttribute(GetType(ColorScale), FileFormatVersions.Office2010)> _
<ChildElementInfoAttribute(GetType(DataBar), FileFormatVersions.Office2010)> _
<ChildElementInfoAttribute(GetType(IconSet), FileFormatVersions.Office2010)> _
<ChildElementInfoAttribute(GetType(DifferentialType), FileFormatVersions.Office2010)> _
<ChildElementInfoAttribute(GetType(ExtensionList), FileFormatVersions.Office2010)> _
Public Class ConditionalFormattingRule _
Inherits OpenXmlCompositeElement
'Usage
Dim instance As ConditionalFormattingRule
[OfficeAvailabilityAttribute(FileFormatVersions.Office2010)]
[ChildElementInfoAttribute(typeof(Formula))]
[ChildElementInfoAttribute(typeof(ColorScale), FileFormatVersions.Office2010)]
[ChildElementInfoAttribute(typeof(DataBar), FileFormatVersions.Office2010)]
[ChildElementInfoAttribute(typeof(IconSet), FileFormatVersions.Office2010)]
[ChildElementInfoAttribute(typeof(DifferentialType), FileFormatVersions.Office2010)]
[ChildElementInfoAttribute(typeof(ExtensionList), FileFormatVersions.Office2010)]
public class ConditionalFormattingRule : OpenXmlCompositeElement
Remarks
The following table lists the possible child types:
DocumentFormat.OpenXml.Office.Excel.Formula <xne:f>
ColorScale <x14:colorScale>
DataBar <x14:dataBar>
IconSet <x14:iconSet>
DifferentialType <x14:dxf>
ExtensionList <x14:extLst>
2.6.27 CT_CfRule
Target namespace: https://schemas.microsoft.com/office/spreadsheetml/2010/main
Referenced by: CT_ConditionalFormatting
This complex type specifies a conditional formatting rule for a range.
Child Elements:
xm:f : f elements that specify the formulas in the conditional formatting rule. The formulas MUST adhere to the grammar specified in Formulas, with the following restrictions:
The formula MUST NOT conform to the ref-infix-operator, book-prefix, local-cell-reference, array-constant, bang-reference, bang-name, sheet-range-reference, or structure-reference production rules.<26>
The formula MUST NOT match the single-sheet-area production rule.
The following table explains how to interpret the formulas:
Value of type |
Interpretation of the formulas in f |
cellIs |
f elements that specifies the formulas, numeric values, or cell references that specify the operands for the ST_ConditionalFormattingOperator ([ISO/IEC-29500-1] section 18.18.15) specified by operator. If operator is “between” or “notBetween”, f MUST contain two formulas, otherwise f MUST contain one formula. |
expression |
An f element that specifies a formula. When the formula returns zero, conditional formatting is not displayed. When the formula returns a nonzero value, conditional formatting is displayed. |
colorScale, dataBar, iconSet |
An f element that specifies a formula. When the formula returns zero, conditional formatting is not displayed. When the formula returns a nonzero value, or is not present, conditional formatting is displayed. |
containsText, notContainsText, beginsWith, endsWith, containsBlanks, notContainsBlanks, containsErrors, notContainsErrors |
An f element that specifies a formula that implements the operation specified by type. When the formula returns zero, conditional formatting is not displayed. When the formula returns a nonzero value, conditional formatting is displayed. |
colorScale : A CT_ColorScale element that specifies a color scale*.*
dataBar : A CT_DataBar element that specifies a data bar*.*
iconSet : A CT_IconSet element that specifies an icon set.
dxf : A CT_Dxf ([ISO/IEC-29500-4] section A.2) element that specifies the differential formatting ([ISO/IEC-29500-1] section M.2.7.3.8) applied to the range. If type is “colorScale”, “dataBar”, or “iconSet” or the priority attribute does not exist, this element MUST NOT exist.
extLst : A CT_ExtensionList ([ISO/IEC-29500-4] section A.2) element that specifies future extensibility for this element.
Attributes:
type : An ST_CfType ([ISO/IEC-29500-1] section 18.18.12) attribute that specifies the way conditional formatting is displayed in the range.
If and only if type is “colorScale”, a colorScale child element MUST exist in this element.
If and only if type is “dataBar”, a dataBar child element MUST exist in this element.
If and only if type is “iconSet”, an iconSet child element MUST exist in this element.
priority : An int ([XMLSCHEMA2] section 3.3.17) attribute that specifies the relative priority of this rule compared to the other rules in this sheet, or whether this CT_CfRule specifies extension information for a conditional formatting data bar rule as specified by the associated CT_CfRule ([ISO/IEC-29500-4] section A.2) element. MUST be greater than zero.
If priority exists, rules are applied in order from the smallest priority to the largest priority and it MUST NOT duplicate a priority value in any other CT_CfRule or CT_CfRule ([ISO/IEC-29500-4] section A.2) element that exists in the same worksheet part.
If priority does not exist, this CT_CfRule specifies extension information for a conditional formatting data bar rule, and the dataBar child element MUST exist and describe this additional information. The attribute id is used to identify the associated CT_CfRule ([ISO/IEC-29500-4] section A.2) and the priority attribute of this CT_CfRule ([ISO/IEC-29500-4] section A.2) specifies the relative priority of this rule. If neither the priority attribute nor the id attribute exist in this element, or if id exists but there exists no CT_CfRule ([ISO/IEC-29500-4] section A.2) element containing a matching GUID, then this record and the succeeding dataBar child element MUST be ignored. If the priority attribute exists in this element, id MUST be ignored.
formatRow : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that MUST NOT exist.
stopIfTrue : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether evaluation of additional conditional formatting rules is skipped for a cell if this rule evaluates to TRUE for that cell.
aboveAverage : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether the conditional formatting rule is applied to cells with values above or below the average value of other cells in the range as specified by the following table. This attribute MUST NOT exist if type is not equal to “aboveAverage”.
Value |
Meaning |
TRUE |
The conditional formatting rule is applied to cells with values above the average value of all cells in the range. |
FALSE |
The conditional formatting rule is applied to cells with values below the average value of all cells in the range. |
percent : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether the conditional formatting rule is applied to a percentage of cells as specified by the following table. This attribute MUST NOT exist if type is not equal to “top10”.
Value |
Meaning |
TRUE |
rank specifies the percentage of cells in the range to which conditional formatting is applied. |
FALSE |
The conditional formatting rule is applied to the number of cells specified by rank. |
bottom : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies how the conditional formatting rule is applied as specified by the following table. This attribute MUST NOT exist if type is not equal to “top10”.
Value |
Meaning |
TRUE |
Conditional formatting is applied to cells whose value is in the bottom end of the range specified by percent and rank. |
FALSE |
Conditional formatting is applied to cells whose value is in the top end of the range specified by percent and rank. |
operator : An ST_ConditionalFormattingOperator ([ISO/IEC-29500-1] section 18.18.15) attribute that specifies the type of value comparison used for this conditional formatting rule. This attribute MUST NOT exist if type is not equal to “cellIs”.
text : A string ([XMLSCHEMA2] section 3.2.1) attribute that specifies a text value used for this conditional formatting rule. This attribute MUST NOT exist if type is not equal to “beginsWith”, “containsText”, “endsWith”, or “notContainsText”.
timePeriod : An ST_TimePeriod ([ISO/IEC-29500-1] section 18.18.82) attribute that specifies the time period used for this conditional formatting rule. This attribute MUST NOT exist if type is not equal to “timePeriod”.
rank : An unsignedInt ([XMLSCHEMA2] section 3.3.22) attribute that specifies how many cells are formatted by this conditional formatting rule. The value of percent specifies whether rank is a percentage or a quantity of cells. When percent is TRUE, rank MUST be greater than or equal to 0 and less than or equal to 100. Otherwise, rank MUST be greater than or equal to 1 and less than or equal to 1000. This attribute MUST NOT exist if type is not equal to “top10”.
stdDev : An int ([XMLSCHEMA2] section 3.3.17) attribute that specifies the number of standard deviations above or below the average to format in the conditional formatting rule. This attribute MUST NOT exist if type is not equal to “aboveAverage” or if equalAverage is TRUE.
equalAverage : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies, together with aboveAverage, how the conditional formatting rule is applied as specified by the following table. This attribute MUST NOT exist if type is not equal to “aboveAverage”.
Value of equalAverage |
Value of aboveAverage |
Meaning |
TRUE |
TRUE |
Conditional formatting is applied to cells whose value is equal or above the average value of cells in the range. |
TRUE |
FALSE |
Conditional formatting is applied to cells whose value is equal or below the average value of cells in the range. |
FALSE |
TRUE |
Conditional formatting is applied to cells whose value is above the average value of all cells in the range plus stdDev times the standard deviation of all cells in the range. |
FALSE |
FALSE |
Conditional formatting is applied to cells whose value is below the average value of all cells in the range minus stdDev times the standard deviation of all cells in the range. |
activePresent : A boolean ([XMLSCHEMA2] section 3.2.2) attribute that MUST be TRUE if and only if a formula is present in f and type is “colorScale”, “dataBar”, or “iconSet”.
id : An ST_Guid ([ISO/IEC-29500-1] section 22.9.2.4) attribute that that identifies this conditional formatting rule. If the priority attribute does not exist, and this attribute exists, this attribute is used to match this CT_CfRule element to the corresponding CT_CfRule ([ISO/IEC-29500-4] section A.2) element. If neither the priority attribute nor this attribute exists, this CT_CfRule and its child CT_DataBar element MUST be ignored.
The following W3C XML Schema ([XMLSCHEMA1] section 2.1) fragment specifies the contents of this complex type.
<xsd:complexType name="CT_CfRule">
<xsd:sequence>
<xsd:element ref="xm:f" minOccurs="0" maxOccurs="3"/>
<xsd:element name="colorScale" type="CT_ColorScale" minOccurs="0" maxOccurs="1"/>
<xsd:element name="dataBar" type="CT_DataBar" minOccurs="0" maxOccurs="1"/>
<xsd:element name="iconSet" type="CT_IconSet" minOccurs="0" maxOccurs="1"/>
<xsd:element name="dxf" type="x:CT_Dxf" minOccurs="0" maxOccurs="1"/>
<xsd:element name="extLst" type="x:CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
</xsd:sequence>
<xsd:attribute name="type" type="x:ST_CfType" use="optional"/>
<xsd:attribute name="priority" type="xsd:int" use="optional"/>
<xsd:attribute name="formatRow" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="stopIfTrue" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="aboveAverage" type="xsd:boolean" use="optional" default="true"/>
<xsd:attribute name="percent" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="bottom" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="operator" type="x:ST_ConditionalFormattingOperator" use="optional"/>
<xsd:attribute name="text" type="xsd:string" use="optional"/>
<xsd:attribute name="timePeriod" type="x:ST_TimePeriod" use="optional"/>
<xsd:attribute name="rank" type="xsd:unsignedInt" use="optional"/>
<xsd:attribute name="stdDev" type="xsd:int" use="optional"/>
<xsd:attribute name="equalAverage" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="activePresent" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="id" type="x:ST_Guid" use="optional"/>
</xsd:complexType>
See section 5.3 for the full W3C XML Schema ([XMLSCHEMA1] section 2.1).
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.