Format tables in add-ins for Excel

This article explains the different features of the formatting API and outlines how to use them. In this release, you can programmatically specify cell formatting and some other options only for tables (not for Office.CoercionType.Text or Office.CoercionType.Matrix data structures) and only in Excel add-ins. To set formatting with your add-in:

• The user selects the table (or where to programmatically insert a table), and then your add-in can call the Document.setSelectedDataAsync method on that table to set formatting.

• Or, if the workbook already contains bound tables (or your add-in uses one of the "addFrom" methods of the Bindings object to create bound tables when it is initialized), your add-in can call the Binding.setDataAsync method on those bound tables to set formatting.

Important: To use these new and updated methods to format tables in Excel add-ins, your add-in project must use or be updated to use Office.js v1.1 or later.

Specifying formatting

To specify the formatting you want to set, you create a JavaScript object literal that contains one or more key-values pairs. You can combine a series of formatting settings in a list within the JavaScript object. For example:

var myFormat = {fontStyle:"bold", width:"autoFit", borderColor:"purple"};

To apply the formatting, pass the JavaScript object to one the methods that support formatting data and other features of the table.

You can work with formatting in two ways:

• The first time your add-in writes data to a selection or binding, by specifying the optional cellFormat or tableOptions parameters in the options object passed to the Document.setSelectedDataAysnc or Binding.setDataAsync methods.

• After you initially set formatting, you can clear or update formatting using one of the new methods dedicated to that purpose.

Using optional parameters with data setting methods

For table bindings, you can use the following optional parameters to specify formatting when setting data with either the Document.setSelectedData or Binding.setDataAsync methods: tableOptions and cellFormat.

The tableOptions optional parameter

Use the tableOptions optional parameter to specify default table styles and turn on and off certain table features, such as: Header Row, Total Row, and Banded Rows. The value you pass as the tableOptions parameter is a JavaScript object that contains a list of key-value pairs. For example,

tableOptions: {bandedRows: true, filterButton: false, style:"TableStyleMedium3"};

The cellFormat optional parameter

Use the cellFormat optional parameter to 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 JavaScript objects that specify which cells to target and the formats to apply to them. For example:

cellFormat: 
    [{cells: {row: 1}, format: {fontColor: "yellow"}}, 
        {cells: Office.Table.Headers, format: {fontColor: "yellow"}}, 
        {cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}]

You can combine multiple cells: and format: pairs in the cellFormat array to minimize the number of function calls required to apply formatting.

cells

Use cells: to specify the range of columns, rows, and cells you want to apply formatting to.

Supported ranges in cells values

format

Use format: to specify the formatting you want to apply to the range defined with cells: as list of JavaScript key-value pairs. For a list of supported values, see Supported formatting keys and values.

Limits specifying formatting for Excel Online

When setting formatting in 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. (In other words, everything specified in one of the cells: object literals in the array passed to cellFormat.) For example, the following call passes two formatting groups to cellFormat.

Office.context.document.setSelectedDataAsync(
    {cellFormat:[{cells: {row: 1}, format: {fontColor: "yellow"}}, 
        {cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}}]}, 
    function (asyncResult){});

Applying optional parameters

In this release, only the Document.setSelectedDataAsync and TableBinding.setDataAsync methods support writing data and setting formatting for tables in the same call using the tableOptions and cellFormat optional parameters. In the following examples, the tableData value passed to the first parameter of each method (the data parameter) must be a TableData object that contains the definition of the table and data to be written.

Document.setSelectedDataAsync example

Office.context.document.setSelectedDataAsync(tableData, 
    {tableOptions: {headerRow:false}, 
        cellFormat: [{cells: Office.Table.Headers, format: {fontColor: "yellow"}}]}, 
    function (asyncResult) {});

TableBinding.setDataAsync example

Office.select("bindings#myBinding").setDataAsync(tableData, 
    {tableOptions: {headerRow:false}, 
        cellFormat: [{cells: Office.Table.Headers, format: {fontColor: "yellow"}}]}, 
    function (asyncResult) {});

Note:: The call to Office.select("bindings#myBinding") assumes that a binding named myBinding already exists in the worksheet.

Updating and clearing formatting

When you set formatting with the cellFormat and tableOptions optional parameters of the Document.setSelectedDataAsync or TableBinding.setDataAsync methods, they will set formatting only the first time you call them. To update or clear formatting, you must use three new methods of the TableBinding object: setFormatsAsync, setTableOptionsAsync, and clearFormatsAsync.

Updating formatting

The TableBinding.setFormatsAsync method is only for updating cell formatting, such as width, height, font, background, and alignment. It takes cellFormat as the required parameter:

Office.select("bindings#myBinding").setFormatsAsync(
    [{cells: {row: 1}, format: {fontColor: "yellow"}}, 
        {cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}}], 
    function (asyncResult){});

The TableBinding.setTableOptionsAsync method is only for updating table options, such as banded rows and filter buttons. It takes tableOptions as the required parameter:

var tableOptions = {bandedRows: true, filterButton: false, style: "TableStyleMedium3"}; 

Office.select("bindings#myBinding").setTableOptionsAsync(tableOptions, function(asyncResult){});

Clearing formatting

The TableBinding.clearFormatsAsync method is for clearing all formatting in the table. It takes the asyncContext optional parameter and an optional callback function:

Office.select("bindings#myBinding").clearFormatsAsync();

Supported formatting keys and values

The following tables list the supported key-value pairs you can pass to the cellFormat or tableOptions parameters.

For format: values, available settings correspond to a subset of those in the Format Cells dialog box (right-click > Format Cells or Format > Format Cells on the Home tab of the ribbon). For tableOptions: values, settings correspond to those in the Table Style Options and Table Styles groups on the Table Tools |Design tab of the ribbon.

Important: The methods of the formatting API support only the options and values summarized below. If you specify formatting options or values other than these, the handling behavior is undefined. These undefined handling behaviors aren't necessarily consistent across supported platforms; you shouldn't develop your add-ins based on any of the side effects of these undefined behaviors for any specific platform. However, the undefined handling behaviors shouldn't harm the state and UI of your add-in or the documents they interact with.

Alignment

Background

Border

Cell width, height and wrapping

Font

Indent

Number formatting

Table options