TableBinding.setFormatsAsync method
Sets or updates formatting on specified items and data in the bound table.
| Hosts: | Excel |
| Available in Requirement set | Not in a set |
| Added in | 1.1 |
bindingObj.setFormatsAsync(cellFormat [,options] , callback);
Parameters
| Name | Type | Description | Support notes |
|---|---|---|---|
| cellFormat | array | An array that contains one or more JavaScript objects that specify which cells to target and the formatting to apply to them. Required. | |
| options | object | Specifies any of the following optional parameters | |
| asyncContext | array, boolean, null, number, object, string, or undefined | A user-defined item of any type that is returned in the AsyncResult object without being altered. | |
| callback | object | A function that is invoked when the callback returns, whose only parameter is of type AsyncResult. |
Callback Value
When the function you passed to the callback parameter executes, it receives an AsyncResult object that you can access from the callback function's only parameter.
In the callback function passed to the goToByIdAsync method, you can use the properties of the AsyncResult object to return the following information.
| Property | Use to... |
|---|---|
| AsyncResult.value | Always returns undefined because there is no data or object to retrieve when setting formats. |
| AsyncResult.status | Determine the success or failure of the operation. |
| AsyncResult.error | Access an Error object that provides error information if the operation failed. |
| AsyncResult.asyncContext | Access your user-defined object or value, if you passed one as the asyncContext parameter. |
Remarks
Specifying the cellFormat parameter
Use the cellFormat parameter to set or change cell formatting values, such as width, height, font, background, alignment, and so on. The value you pass as the cellFormat parameter is an array that contains a list of one or more JavaScript objects that specify which cells to target ( cells:) and the formats ( format:) to apply to them.
Each JavaScript object in the cellFormat array has this form:
{cells:{ cell_range }, format:{ format_definition }}
The cells: property specifies the range you want format using one of the following values:
Supported ranges in cells property
| cells range settings | Description |
|---|---|
{row: i} |
Specifies the range that extends to the ith row of data in the table. |
{column: i} |
Specifies the range that extends to ith column of data in the table. |
{row: i, column: j} |
Specifies the range of cells from the ith row to the jth column of data in the table. |
Office.Table.All |
Specifies the entire table, including column headers, data, and totals (if any). |
Office.Table.Data |
Specifies only the data in the table (no headers and totals). |
Office.Table.Headers |
Specifies only the header row. |
The format: property specifies values that correspond to a subset of the settings available in the Format Cells dialog box in Excel (Right-click > Format Cells or Home > Format > Format Cells).
You specify the value of the format: property as a list of one or more property name - value pairs in a JavaScript object literal. The property name specifies the name of the formatting property to set, and value specifies the property value. You can specify multiple values for a given format, such as both a font's color and size. Here's three format: property value examples:
//Set cells: font color to green and size to 15 points.
format: {fontColor : "green", fontSize : 15}
//Set cells: border to dotted blue.
format: {borderStyle: "dotted", borderColor: "blue"}
//Set cells: background to red and alignment to centered.
format: {backgroundColor: "red", alignHorizontal: "center"}
You can specify number formats by specifying the number formatting "code" string in the numberFormat: property. The number format strings you can specify correspond to those you can set in Excel using the Custom category on the Number tab of the Format Cells dialog box. This example shows how to format a number as a percentage with two decimal places:
format: {numberFormat:"0.00%"}
For more detail, see how to create a custom number format.
Specifying a single target
The following example shows a cellFormat value that sets the font color of the header row to red.
Office.select("bindings#myBinding).setFormatsAsync(
[{cells: Office.Table.Headers, format: {fontColor: "red"}}],
function (asyncResult){});
Specifying multiple targets
The setFormatsAsync method can support formatting multiple targets within the bound table in a single function call. To do that, you pass a list of objects in the cellFormat array for each target that you want to format. For example, the following line of code will set the font color of the first row yellow, and the fourth cell in the third row to have a white border and bold text.
Office.select("bindings#myBinding).setFormatsAsync(
[{cells: {row: 1}, format: {fontColor: "yellow"}},
{cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}}],
function (asyncResult){});
To set formatting on tables when writing data, use the tableOptions and cellFormat optional parameters of the Document.setSelectedDataAsync or TableBinding.setDataAsync methods.
Setting formatting with the optional parameters of the Document.setSelectedDataAsync and TableBinding.setDataAsync methods only works to set formatting when writing data the first time. To make formatting changes after writing data, use the following methods:
-
To update cell formatting, such as font color and style, use the TableBinding.setFormatsAsync method (this method).
-
To update table options, such as banded rows and filter buttons, use the TableBinding.setTableOptions method.
-
To clear formatting, use the TableBinding.clearFormats method.
Additional remarks for Excel Online
The number of formatting groups passed to the cellFormat parameter can't exceed 100. A single formatting group consists of a set of formatting applied to a specified range of cells. For example, the following call passes two formatting groups to cellFormat.
Office.select("bindings#myBinding).setFormatsAsync(
[{cells: {row: 1}, format: {fontColor: "yellow"}},
{cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}}],
function (asyncResult){});
For more details and examples, see How to format tables in add-ins for Excel.
Support details
A capital Y in the following matrix indicates that this method is supported in the corresponding Office host application. An empty cell indicates that the Office host application doesn't support this method.
For more information about Office host application and server requirements, see Requirements for running Office Add-ins.
Supported hosts, by platform
| Office for Windows desktop | Office Online (in browser) | Office for iPad | ||
|---|---|---|---|---|
| Excel | Y | Y | Y |
| Available in requirement sets | Not in a set. |
| Minimum permission level | WriteDocument |
| Add-in types | Content, task pane |
| Library | Office.js |
| Namespace | Office |
Support history
| Version | Changes |
|---|---|
| 1.1 | Added support for Excel in Office for iPad. |
| 1.1 | Introduced |