What's new in Excel JavaScript API 1.6
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
stopifTruecapability. - 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. |