2.4.23 BrtBeginCFRule
The BrtBeginCFRule 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 and the Macro Sheet (section 2.1.7.32) part ABNF. The collection of records specifies a conditional formatting rule for a range.
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
3 |
|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
iType |
|||||||||||||||||||||||||||||||
|
iTemplate |
|||||||||||||||||||||||||||||||
|
dxfId |
|||||||||||||||||||||||||||||||
|
iPri |
|||||||||||||||||||||||||||||||
|
iParam |
|||||||||||||||||||||||||||||||
|
reserved1 |
|||||||||||||||||||||||||||||||
|
reserved2 |
|||||||||||||||||||||||||||||||
|
A |
B |
C |
D |
E |
reserved4 |
cbFmla1 |
|||||||||||||||||||||||||
|
... |
cbFmla2 |
||||||||||||||||||||||||||||||
|
... |
cbFmla3 |
||||||||||||||||||||||||||||||
|
... |
strParam (variable) |
||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
rgce1 (variable) |
|||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
rgce2 (variable) |
|||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
|
rgce3 (variable) |
|||||||||||||||||||||||||||||||
|
... |
|||||||||||||||||||||||||||||||
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 BrtBeginColorScale (section 2.4.28) record in the collection of conditional formatting records following this record.
-
When iType is CF_TYPE_DATABAR, there MUST be one BrtBeginDatabar (section 2.4.43) record in the collection of conditional formatting records following this record.
-
When iType is CF_TYPE_MULTISTATE, there MUST be one BrtBeginIconSet (section 2.4.91) 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_FILTER
CF_TEMPLATE_FILTER
CF_TYPE_MULTISTATE
CF_TEMPLATE_MULTISTATE
dxfId (4 bytes): A DXFId (section 2.5.38) 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, dxfId MUST be 0xFFFFFFFF and MUST be ignored.
iPri (4 bytes): A signed integer that specifies the relative priority of this rule compared to the other rules in this sheet. Rules are applied in order from the smallest iPri to the largest iPri. This value MUST be greater than 0 and MUST NOT duplicate an iPri value in any other BrtBeginCFRule (section 2.4.23) or BrtBeginCFRule14 (section 2.4.24) record that exists in the same worksheet (section 2.1.7.62) or macro sheet (section 2.1.7.32) part.
iParam (4 bytes): This structure specifies a parameter for the 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 (section 2.5.15) 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. The value of 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. The value of 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 the rule evaluates to TRUE for that cell. This value MUST be 0 when iType is CF_TYPE_DATABAR, CF_TYPE_GRADIENT, or CF_TYPE_MULTISTATE.
-
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. If this value is equal to 0x00000000, rgce1 MUST NOT exist. If this value is nonzero, rgce1 MUST exist and the value of cbFmla1 MUST be ignored.
cbFmla2 (4 bytes): An unsigned integer. If this value is equal to 0x00000000, rgce2 MUST NOT exist. If nonzero, rgce2 MUST exist and the value of cbFmla2 MUST be ignored.
cbFmla3 (4 bytes): An unsigned integer. If this value is equal to 0x00000000, rgce3 MUST NOT exist. If nonzero, rgce3 MUST exist and the value of cbFmla3 MUST be ignored.
strParam (variable): An XLNullableWideString (section 2.5.167) that specifies a value used in this 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.
rgce1 (variable): A CFParsedFormula (section 2.5.98.6) that specifies the first formula (section 2.2.2) used in this rule. The following table specifies the conditions for which rgce1 MUST contain a formula. For any conditions not specified in the following table, cbFmla1 MUST be 0x00000000 and rgce1 MUST be omitted.
-
Condition
Formula in rgce1
iType = CF_TYPE_CELLIS
A CFParsedFormula that specifies the formula (section 2.2.2), numeric value, or cell reference that specifies the first operand for the CFOper specified by iParam.
iType = CF_TYPE_EXPRIS and iTemplate=CF_TEMPLATE_FMLA
A CFParsedFormula. 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 CFParsedFormula that implements the text comparison operation specified by iParam.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSBLANKS
A CFParsedFormula that returns a nonzero value when the cell is blank.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSNOBLANKS
A CFParsedFormula that returns a nonzero value when the cell is not blank.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSERRORS
A CFParsedFormula that returns a nonzero value when the cell contains an error.
iType = CF_TYPE_EXPRIS and iTemplate = CF_TEMPLATE_CONTAINSNOERRORS
A CFParsedFormula 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 CFParsedFormula that implements the date comparison operation specified by iParam.
rgce2 (variable): A CFParsedFormula that specifies the second formula (section 2.2.2) used in this rule. The following table specifies the conditions for which rgce2 MUST contain a formula. For any conditions not specified in the following table, cbFmla2 MUST be 0x00000000 and rgce2 MUST be omitted.
-
Condition
Formula in rgce2
iType = CF_TYPE_CELLIS, and iParam=CF_OPER_BN or CF_OPER_NB
A CFParsedFormula that specifies the formula (section 2.2.2), numeric value, or cell reference that specifies the second operand for the CFOper specified by iParam.
rgce3 (variable): A CFParsedFormula that specifies the third formula (section 2.2.2) used in this rule. If iType is not CF_TYPE_GRADIENT, CF_TYPE_DATABAR, or CF_TYPE_MULTISTATE, cbFmla3 MUST be 0x00000000 and rgce3 MUST be omitted. Otherwise, rgce3 can contain a CFParsedFormula. When this formula returns zero, conditional formatting is not displayed. When the formula returns a nonzero value, or when rgce3 is omitted, conditional formatting is displayed.