Edit

What's new in Excel JavaScript API 1.6

  • Article

Conditional formatting

Introduces conditional formatting of a range. Allows the following types of conditional formatting.

  • Color scale
  • Data bar
  • Icon set
  • Custom

In addition:

  • Returns the range the conditional format is applied to.
  • Removal of conditional formatting.
  • Provides priority and stopifTrue capability.
  • Get collection of all conditional formatting on a given range.
  • Clears all conditional formats active on the current specified range.

API list

The following table lists the APIs in Excel JavaScript API requirement set 1.6. To view API reference documentation for all APIs supported by Excel JavaScript API requirement set 1.6 or earlier, see Excel APIs in requirement set 1.6 or earlier.

Class Fields Description
Application suspendApiCalculationUntilNextSync() Suspends calculation until the next context.sync() is called.
CellValueConditionalFormat format Returns a format object, encapsulating the conditional formats font, fill, borders, and other properties.
rule Specifies the rule object on this conditional format.
ColorScaleConditionalFormat criteria The criteria of the color scale.
threeColorScale If true, the color scale will have three points (minimum, midpoint, maximum), otherwise it will have two (minimum, maximum).
ConditionalCellValueRule formula1 The formula, if required, on which to evaluate the conditional format rule.
formula2 The formula, if required, on which to evaluate the conditional format rule.
operator The operator of the cell value conditional format.
ConditionalColorScaleCriteria maximum The maximum point of the color scale criterion.
midpoint The midpoint of the color scale criterion, if the color scale is a 3-color scale.
minimum The minimum point of the color scale criterion.
ConditionalColorScaleCriterion color HTML color code representation of the color scale color (e.g., #FF0000 represents Red).
formula A number, a formula, or null (if type is lowestValue).
type What the criterion conditional formula should be based on.
ConditionalDataBarNegativeFormat borderColor HTML color code representing the color of the border line, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
fillColor HTML color code representing the fill color, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
matchPositiveBorderColor Specifies if the negative data bar has the same border color as the positive data bar.
matchPositiveFillColor Specifies if the negative data bar has the same fill color as the positive data bar.
ConditionalDataBarPositiveFormat borderColor HTML color code representing the color of the border line, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
fillColor HTML color code representing the fill color, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
gradientFill Specifies if the data bar has a gradient.
ConditionalDataBarRule formula The formula, if required, on which to evaluate the data bar rule.
type The type of rule for the data bar.
ConditionalFormat cellValue Returns the cell value conditional format properties if the current conditional format is a CellValue type.
cellValueOrNullObject Returns the cell value conditional format properties if the current conditional format is a CellValue type.
colorScale Returns the color scale conditional format properties if the current conditional format is a ColorScale type.
colorScaleOrNullObject Returns the color scale conditional format properties if the current conditional format is a ColorScale type.
custom Returns the custom conditional format properties if the current conditional format is a custom type.
customOrNullObject Returns the custom conditional format properties if the current conditional format is a custom type.
dataBar Returns the data bar properties if the current conditional format is a data bar.
dataBarOrNullObject Returns the data bar properties if the current conditional format is a data bar.
delete() Deletes this conditional format.
getRange() Returns the range the conditonal format is applied to.
getRangeOrNullObject() Returns the range to which the conditonal format is applied.
iconSet Returns the icon set conditional format properties if the current conditional format is an IconSet type.
iconSetOrNullObject Returns the icon set conditional format properties if the current conditional format is an IconSet type.
id The priority of the conditional format in the current ConditionalFormatCollection.
preset Returns the preset criteria conditional format.
presetOrNullObject Returns the preset criteria conditional format.
priority The priority (or index) within the conditional format collection that this conditional format currently exists in.
stopIfTrue If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell.
textComparison Returns the specific text conditional format properties if the current conditional format is a text type.
textComparisonOrNullObject Returns the specific text conditional format properties if the current conditional format is a text type.
topBottom Returns the top/bottom conditional format properties if the current conditional format is a TopBottom type.
topBottomOrNullObject Returns the top/bottom conditional format properties if the current conditional format is a TopBottom type.
type A type of conditional format.
ConditionalFormatCollection add(type: Excel.ConditionalFormatType) Adds a new conditional format to the collection at the first/top priority.
clearAll() Clears all conditional formats active on the current specified range.
getCount() Returns the number of conditional formats in the workbook.
getItem(id: string) Returns a conditional format for the given ID.
getItemAt(index: number) Returns a conditional format at the given index.
items Gets the loaded child items in this collection.
ConditionalFormatRule formula The formula, if required, on which to evaluate the conditional format rule.
formulaLocal The formula, if required, on which to evaluate the conditional format rule in the user's language.
formulaR1C1 The formula, if required, on which to evaluate the conditional format rule in R1C1-style notation.
ConditionalIconCriterion customIcon The custom icon for the current criterion, if different from the default icon set, else null will be returned.
formula A number or a formula depending on the type.
operator greaterThan or greaterThanOrEqual for each of the rule types for the icon conditional format.
type What the icon conditional formula should be based on.
ConditionalPresetCriteriaRule criterion The criterion of the conditional format.
ConditionalRangeBorder color HTML color code representing the color of the border line, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
sideIndex Constant value that indicates the specific side of the border.
style One of the constants of line style specifying the line style for the border.
ConditionalRangeBorderCollection bottom Gets the bottom border.
count Number of border objects in the collection.
getItem(index: Excel.ConditionalRangeBorderIndex) Gets a border object using its name.
getItemAt(index: number) Gets a border object using its index.
items Gets the loaded child items in this collection.
left Gets the left border.
right Gets the right border.
top Gets the top border.
ConditionalRangeFill clear() Resets the fill.
color HTML color code representing the color of the fill, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
ConditionalRangeFont bold Specifies if the font is bold.
clear() Resets the font formats.
color HTML color code representation of the text color (e.g., #FF0000 represents Red).
italic Specifies if the font is italic.
strikethrough Specifies the strikethrough status of the font.
underline The type of underline applied to the font.
ConditionalRangeFormat borders Collection of border objects that apply to the overall conditional format range.
fill Returns the fill object defined on the overall conditional format range.
font Returns the font object defined on the overall conditional format range.
numberFormat Represents Excel's number format code for the given range.
ConditionalTextComparisonRule operator The operator of the text conditional format.
text The text value of the conditional format.
ConditionalTopBottomRule rank The rank between 1 and 1000 for numeric ranks or 1 and 100 for percent ranks.
type Format values based on the top or bottom rank.
CustomConditionalFormat format Returns a format object, encapsulating the conditional formats font, fill, borders, and other properties.
rule Specifies the Rule object on this conditional format.
DataBarConditionalFormat axisColor HTML color code representing the color of the Axis line, in the form #RRGGBB (e.g., "FFA500") or as a named HTML color (e.g., "orange").
axisFormat Representation of how the axis is determined for an Excel data bar.
barDirection Specifies the direction that the data bar graphic should be based on.
lowerBoundRule The rule for what constitutes the lower bound (and how to calculate it, if applicable) for a data bar.
negativeFormat Representation of all values to the left of the axis in an Excel data bar.
positiveFormat Representation of all values to the right of the axis in an Excel data bar.
showDataBarOnly If true, hides the values from the cells where the data bar is applied.
upperBoundRule The rule for what constitutes the upper bound (and how to calculate it, if applicable) for a data bar.
IconSetConditionalFormat criteria An array of criteria and icon sets for the rules and potential custom icons for conditional icons.
reverseIconOrder If true, reverses the icon orders for the icon set.
showIconOnly If true, hides the values and only shows icons.
style If set, displays the icon set option for the conditional format.
PresetCriteriaConditionalFormat format Returns a format object, encapsulating the conditional formats font, fill, borders, and other properties.
rule The rule of the conditional format.
Range calculate() Calculates a range of cells on a worksheet.
conditionalFormats The collection of ConditionalFormats that intersect the range.
TextConditionalFormat format Returns a format object, encapsulating the conditional format's font, fill, borders, and other properties.
rule The rule of the conditional format.
TopBottomConditionalFormat format Returns a format object, encapsulating the conditional format's font, fill, borders, and other properties.
rule The criteria of the top/bottom conditional format.
Worksheet calculate(markAllDirty: boolean) Calculates all cells on a worksheet.

See also