2.4.24 BrtBeginCFRule14
The BrtBeginCFRule14 record specifies the properties of a conditional formatting rule and specifies the beginning of a collection of records as defined by the Worksheet (section 2.1.7.62) part ABNF. The collection of records specifies a conditional formatting rule for a range. This record is equivalent to BrtBeginCFRule (section 2.4.23) but this record allows a GUID to be specified for the associated BrtCFRuleExt (section 2.4.330) record.
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
3 |
|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
FRTHeader (variable) |
|||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
iType |
|||||||||||||||||||||||||||||||
|
iTemplate |
|||||||||||||||||||||||||||||||
|
dxfId |
|||||||||||||||||||||||||||||||
|
iPri |
|||||||||||||||||||||||||||||||
|
iParam |
|||||||||||||||||||||||||||||||
|
reserved1 |
|||||||||||||||||||||||||||||||
|
reserved2 |
|||||||||||||||||||||||||||||||
|
A |
B |
C |
D |
E |
reserved4 |
cbFmla1 |
|||||||||||||||||||||||||
|
... |
cbFmla2 |
||||||||||||||||||||||||||||||
|
... |
cbFmla3 |
||||||||||||||||||||||||||||||
|
... |
unused1 |
||||||||||||||||||||||||||||||
|
... |
guid (16 bytes) |
||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
... |
fGuid |
||||||||||||||||||||||||||||||
|
... |
strParam (variable) |
||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
FRTHeader (variable): An FRTHeader (section 2.5.61) that specifies the future record (section 2.1.6) information for this record. The FRTHeader fields MUST have the values listed in the following table.
-
Field
Value
FRTHeader.fRef
0
FRTHeader.fSqref
0
FRTHeader.fFormulas
0 or 1
FRTHeader.fRelID
0
-
FRTHeader.fFormulas MUST be 1 if any of the fields cbFmla1, cbFmla2, or cbFmla3 are nonzero. Otherwise, it MUST be 0.
-
FRTHeader.rgFormulas contains an array of one or two formulas (section 2.2.2) in the conditional formatting rule. The number of formulas in this array MUST be equal to the number of fields (cbFmla1, cbFmla2, and cbFmla3) that are nonzero. cbFmla1, cbFmla2, and cbFmla3 MUST NOT all be nonzero.
-
The following table explains how to interpret the first formula in the array, if it is present.
-
Condition
Interpretation of first formula in FRTHeader.rgFormulas
iType = CF_TYPE_CELLIS
A FRTCFParsedFormula14 (section 2.5.56) that specifies the formula (section 2.2.2), numeric value, or cell reference that specifies the first operand for the CFOper (section 2.5.15) specified by iParam.
iType = CF_TYPE_EXPRIS and iTemplate=CF_TEMPLATE_FMLA
A FRTCFParsedFormula14. When the formula (section 2.2.2) returns 0, conditional formatting is not displayed. When the formula returns a nonzero value, conditional formatting is displayed.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSTEXT
A FRTCFParsedFormula14 that implements the text comparison operation specified by iParam.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSBLANKS
A FRTCFParsedFormula14 that returns a nonzero value when the cell is blank.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSNOBLANKS
A FRTCFParsedFormula14 that returns a nonzero value when the cell is not blank.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSERRORS
A FRTCFParsedFormula14 that returns a nonzero value when the cell contains an error.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSNOERRORS
A FRTCFParsedFormula14 that returns a nonzero value when the cell does not contain an error.
iType = CF_TYPE_EXPRIS, and iTemplate greater than or equal to 0x0F and less than or equal to 0x18
A FRTCFParsedFormula14 that implements the date comparison operation specified by iParam.
iType = CF_TYPE_GRADIENT, CF_TYPE_DATABAR, or CF_TYPE_MULTISTATE
A FRTCFParsedFormula14 that specifies whether to display conditional formatting. When this formula (section 2.2.2) returns zero, conditional formatting is not displayed. When the formula returns a nonzero value, or is not present, conditional formatting is displayed.
-
If none of the preceding conditions hold, FRTHeader.fFormulas MUST be 0.
-
The following table explains how to interpret the second formula (section 2.2.2) in the array, if it is present.
-
Condition
Interpretation of second formula in FRTHeader.rgFormulas
iType = CF_TYPE_CELLIS, and iParam=CF_OPER_BN or CF_OPER_NB
A FRTCFParsedFormula14 (section 2.5.56) that specifies the formula (section 2.2.2), numeric value, or cell reference that specifies the second operand for the CFOper specified by iParam.
-
If the preceding condition does not hold, FRTHeader.rgFormulas MUST contain fewer than two formulas.
iType (4 bytes): A CFType (section 2.5.18) that specifies the way conditional formatting is displayed in the range.
-
When iType is CF_TYPE_GRADIENT, there MUST be one BrtBeginColorScale14 (section 2.4.29) record in the collection of conditional formatting records following this record.
-
When iType is CF_TYPE_DATABAR, there MUST be one BrtBeginDatabar14 (section 2.4.44) record in the collection of conditional formatting records following this record.
-
When iType is CF_TYPE_MULTISTATE, there MUST be one BrtBeginIconSet14 (section 2.4.92) record in the collection of conditional formatting records following this record.
iTemplate (4 bytes): A CFTemp (section 2.5.16) that specifies the type of logical condition that determines when conditional formatting is shown. The following table specifies the allowable combinations of iType and iTemplate. Other combinations MUST NOT be used.
-
Value of iType
Value of iTemplate
CF_TYPE_CELLIS
CF_TEMPLATE_EXPR
CF_TYPE_EXPRIS
CF_TEMPLATE_FMLA
CF_TYPE_EXPRIS
CF_TEMPLATE_UNIQUEVALUES
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSTEXT
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSBLANKS
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSNOBLANKS
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSERRORS
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSNOERRORS
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTODAY
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTOMORROW
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODYESTERDAY
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLAST7DAYS
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLASTMONTH
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODNEXTMONTH
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTHISWEEK
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODNEXTWEEK
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLASTWEEK
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTHISMONTH
CF_TYPE_EXPRIS
CF_TEMPLATE_ABOVEAVERAGE
CF_TYPE_EXPRIS
CF_TEMPLATE_BELOWAVERAGE
CF_TYPE_EXPRIS
CF_TEMPLATE_DUPLICATEVALUES
CF_TYPE_EXPRIS
CF_TEMPLATE_EQUALABOVEAVERAGE
CF_TYPE_EXPRIS
CF_TEMPLATE_EQUALBELOWAVERAGE
CF_TYPE_GRADIENT
CF_TEMPLATE_GRADIENT
CF_TYPE_DATABAR
CF_TEMPLATE_DATABAR
CF_TYPE_DATABAR
CF_TEMPLATE_EXPR
CF_TYPE_FILTER
CF_TEMPLATE_FILTER
CF_TYPE_MULTISTATE
CF_TEMPLATE_MULTISTATE
-
dxfId (4 bytes): A DXFId14<8> (section 2.5.39) that specifies the differential formatting (section 2.2.6.2) applied to the range. If iType is CF_TYPE_GRADIENT, CF_TYPE_DATABAR, or CF_TYPE_MULTISTATE, and iPri is greater than 0, dxfId MUST be 0xFFFFFFFF and MUST be ignored.
-
If iPri is equal to -1, this field MUST be 0, and MUST be ignored.
iPri (4 bytes): A signed integer that specifies the relative priority of this rule compared to the other rules in the sheet, or whether this BrtBeginCFRule14 (section 2.4.24) specifies extension information for a conditional formatting data bar rule specified by a BrtBeginCFRule (section 2.4.23) record. MUST be greater than 0 or equal to -1.
-
Rules with iPri field greater than -1 are applied in order from the smallest iPri to the largest iPri. If iPri is greater than 0, it MUST NOT duplicate an iPri value in any other BrtBeginCFRule or BrtBeginCFRule14 record that exists in the same worksheet (section 2.1.7.62) part.
-
If iPri is equal to -1, this record specifies extension information for a conditional formatting data bar rule, and MUST be followed by a BrtBeginDatabar14 (section 2.4.44) record describing this additional information. The value of the guid field MUST equal the value of the guid field of the associated BrtCFRuleExt (section 2.4.330) record. The iPri field of the BrtBeginCFRule record associated with this BrtCFRuleExt record specifies the relative priority of this rule. If iPri is equal to -1 and fGuid is 0x00, or if fGuid is 0x01 but there exists no BrtCFRuleExt record containing a matching GUID, then this record and the succeeding BrtBeginDatabar14 (section 2.4.44) record MUST be ignored. If iPri is not equal to -1 fGuid and guid MUST be ignored.
iParam (4 bytes): This structure specifies a parameter for this conditional formatting rule. The value, type, and meaning of iParam depend on the value of iType and iTemplate, as specified in the following table:
-
Value of iType
Value of iTemplate
Meaning of iParam
CF_TYPE_CELLIS
CF_TEMPLATE_EXPR
A CFOper that specifies the type of value comparison used.
CF_TYPE_EXPRIS
CF_TEMPLATE_CONTAINSTEXT
A CFTextOper (section 2.5.17) that specifies the type of text comparison operation used.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTODAY
A CFDateOper (section 2.5.12) that MUST be CF_TIMEPERIOD_TODAY.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTOMORROW
A CFDateOper that MUST be CF_TIMEPERIOD_TOMORROW.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODYESTERDAY
A CFDateOper that MUST be CF_TIMEPERIOD_YESTERDAY.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLAST7DAYS
A CFDateOper that MUST be CF_TIMEPERIOD_LAST7DAYS.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLASTMONTH
A CFDateOper that MUST be CF_TIMEPERIOD_LASTMONTH.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODNEXTMONTH
A CFDateOper that MUST be CF_TIMEPERIOD_NEXTMONTH.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTHISWEEK
A CFDateOper that MUST be CF_TIMEPERIOD_THISWEEK.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODNEXTWEEK
A CFDateOper that MUST be CF_TIMEPERIOD_NEXTWEEK.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODLASTWEEK
A CFDateOper that MUST be CF_TIMEPERIOD_LASTWEEK.
CF_TYPE_EXPRIS
CF_TEMPLATE_TIMEPERIODTHISMONTH
A CFDateOper that MUST be CF_TIMEPERIOD_THISMONTH.
CF_TYPE_EXPRIS
CF_TEMPLATE_ABOVEAVERAGE
An unsigned integer that specifies the number of standard deviations above the average to format. iParam MUST be greater than or equal to 0 and less than 4. If iParam is 0, conditional formatting applies to all values greater than the average.
CF_TYPE_EXPRIS
CF_TEMPLATE_BELOWAVERAGE
An unsigned integer that specifies the number of standard deviations below the average to format. iParam MUST be greater than or equal to 0 and less than 4. If iParam is 0, conditional formatting applies to all values below the average.
CF_TYPE_EXPRIS
CF_TEMPLATE_EQUALABOVEAVERAGE
MUST be 0x00000000.
CF_TYPE_EXPRIS
CF_TEMPLATE_EQUALBELOWAVERAGE
MUST be 0x00000000.
CF_TYPE_FILTER
CF_TEMPLATE_FILTER
An unsigned integer that specifies how many cells are formatted by this rule. The value of fPercent specifies whether iParam is a percentage or a quantity of cells. When fPercent is 1, iParam MUST be greater than or equal to 0 and less than or equal to 100. Otherwise iParam MUST be greater than or equal to 1 and less than or equal to 1000.
-
For all combinations of iType and iTemplate that are not specified in this table, iParam MUST be 0x00000000.
reserved1 (4 bytes): This value MUST be 0 and MUST be ignored.
reserved2 (4 bytes): This value MUST be 0 and MUST be ignored.
A - reserved3 (1 bit): This value MUST be 0 and MUST be ignored.
B - fStopTrue (1 bit): A bit that specifies whether evaluation of additional conditional formatting rules is skipped for a cell if this rule evaluates to TRUE for that cell. The value MUST be 0 when iType is CF_TYPE_DATABAR, CF_TYPE_GRADIENT, or CF_TYPE_MULTISTATE. The following table explains the meanings of each value for fStopTrue.
-
Value
Meaning
0
After this rule has been evaluated, the rule with the next largest iPri value is evaluated normally.
1
If this rule evaluates to TRUE for a cell, rules with a larger iPri value are not evaluated for that cell. If this rule evaluates to FALSE for a cell, the rule with the next largest iPri value is evaluated normally.
-
C - fAbove (1 bit): A bit that specifies whether conditional formatting is applied to cells with values above or below the average value of other cells in the range. If iTemplate is CF_TEMPLATE_ABOVEAVERAGE or CF_TEMPLATE_EQUALABOVEAVERAGE, fAbove MUST be 1. Otherwise, fAbove MUST be 0.
D - fBottom (1 bit): A bit that specifies how cells are formatted as specified by the following table.
-
Value
Meaning
0
Conditional formatting is applied to cells whose value is in the top end of the range specified by iParam and fPercent.
1
Conditional formatting is applied to cells whose value is in the bottom end of the range specified by iParam and fPercent.
-
If iType is not CF_TYPE_FILTER, fBottom is unused and MUST be 0.
E - fPercent (1 bit): A bit that specifies how cells are formatted as specified by the following table.
-
Value
Meaning
0
Conditional formatting is applied to the number of cells specified by iParam.
1
iParam specifies the percentage of cells in the range to which conditional formatting is applied.
-
If iType is not CF_TYPE_FILTER, fPercent is unused and MUST be 0.
reserved4 (11 bits): This value MUST be 0 and MUST be ignored.
cbFmla1 (4 bytes): An unsigned integer that MUST equal either the cce of the first formula (section 2.2.2) in FRTHeader.rgFormulas or 0x00000000. If iType is CF_TYPE_GRADIENT, CF_TYPE_DATABAR, or CF_TYPE_MULTISTATE, cbFmla1 MUST be 0x00000000. If cbFmla1 is nonzero, cbFmla3 MUST be 0x00000000.
cbFmla2 (4 bytes): If cbFmla1 is nonzero, this value is an unsigned integer that MUST equal the cce of the second formula (section 2.2.2) in FRTHeader.rgFormulas, or 0x00000000. When cbFmla1 is 0x00000000, cbFmla2 MUST be 0x00000000. If cbFmla2 is nonzero, cbFmla3 MUST be 0x00000000.
cbFmla3 (4 bytes): An unsigned integer that MUST equal either the cce of the first formula (section 2.2.2) in FRTHeader.rgFormulas or 0x00000000. If cbFmla3 is nonzero, both cbFmla1 and cbFmla2 MUST be 0x00000000. If iType is not equal to CF_TYPE_GRADIENT, CF_TYPE_DATABAR, or CF_TYPE_MULTISTATE, cbFmla3 MUST be 0x00000000.
unused1 (4 bytes): Undefined, and MUST be ignored.
guid (16 bytes): If fGuid is 1, this value is a GUID as specified in [MS-DTYP] section 2.3.4 that identifies the conditional formatting rule. If fGuid is 0, this field is unused and MUST be ignored.
fGuid (4 bytes): A Boolean (section 2.5.98.3) that specifies whether the conditional formatting rule contains a GUID. This value MUST be a value from the following table.
-
Value
Meaning
0x00000000
The conditional formatting rule does not contain a GUID. guid is unused and MUST be ignored.
0x00000001
The conditional formatting rule contains a GUID.
-
If iPri is -1 and fGuid is 0x00000001, the value of the guid field MUST equal the value of the guid field of the associated BrtCFRuleExt (section 2.4.330) record. If iPri is -1 and fGuid is 0x00000000, this BrtBeginCFRule14 (section 2.4.24) and the succeeding BrtBeginDatabar14 (section 2.4.44) record MUST be ignored.
strParam (variable): An XLNullableWideString (section 2.5.167) that specifies a value used in the conditional formatting rule. If iTemplate is not CF_TEMPLATE_CONTAINSTEXT, strParam MUST be NULL and MUST be ignored. Otherwise, strParam MUST contain a string that specifies the value that is searched for, and strParam MUST be greater than 0 and less than 256 characters long.