Office.TableBinding interface

Represents a binding in two dimensions of rows and columns, optionally with headers.

Extends
Binding

Remarks

Requirement SetsTableBindings

The TableBinding object inherits the id property, type property, getDataAsync method, and setDataAsync method from the Office.Binding object.

For Excel, note that after you establish a table binding, each new row a user adds to the table is automatically included in the binding and rowCount increases.

Properties

columnCount

Gets the number of columns in the TableBinding, as an integer value.

hasHeaders

True, if the table has headers; otherwise false.

rowCount

Gets the number of rows in the TableBinding, as an integer value.

Methods

addColumnsAsync(tableData, options, callback)

Adds the specified data to the table as additional columns.

addRowsAsync(rows, options, callback)

Adds the specified data to the table as additional rows.

clearFormatsAsync(options, callback)

Clears formatting on the bound table.

deleteAllDataValuesAsync(options, callback)

Deletes all non-header rows and their values in the table, shifting appropriately for the host application.

getFormatsAsync(cellReference, formats, options, callback)

Gets the formatting on specified items in the table.

setFormatsAsync(cellFormat, options, callback)

Sets formatting on specified items and data in the table.

setTableOptionsAsync(tableOptions, options, callback)

Updates table formatting options on the bound table.

Property Details

columnCount

Gets the number of columns in the TableBinding, as an integer value.

columnCount: number;

Property Value

number

Remarks

Support details

A capital Y in the following matrix indicates that this property is supported in the corresponding Office host application. An empty cell indicates that the Office host application doesn't support this property.

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
Access Y
Excel Y Y Y
Word Y Y Y

Examples

function showBindingColumnCount() {
    Office.context.document.bindings.getByIdAsync("myBinding", function (asyncResult) {
        write("Column: " + asyncResult.value.columnCount);
    });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

hasHeaders

True, if the table has headers; otherwise false.

hasHeaders: boolean;

Property Value

boolean

Remarks

Support details

A capital Y in the following matrix indicates that this property is supported in the corresponding Office host application. An empty cell indicates that the Office host application doesn't support this property.

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
Access Y
Excel Y Y Y
Word Y Y Y

Examples

function showBindingHasHeaders() {
    Office.context.document.bindings.getByIdAsync("myBinding", function (asyncResult) {
        write("Binding has headers: " + asyncResult.value.hasHeaders);
    });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

rowCount

Gets the number of rows in the TableBinding, as an integer value.

rowCount: number;

Property Value

number

Remarks

When you insert an empty table by selecting a single row in Excel 2013 and Excel Online (using Table on the Insert tab), both Office host applications create a single row of headers followed by a single blank row. However, if your add-in's script creates a binding for this newly inserted table (for example, by using the Office.Bindings.addFromSelectionAsync method), and then checks the value of the rowCount property, the value returned will differ depending whether the spreadsheet is open in Excel 2013 or Excel Online.

  • In Excel on the desktop, rowCount will return 0 (the blank row following the headers is not counted).

  • In Excel Online, rowCount will return 1 (the blank row following the headers is counted).

You can work around this difference in your script by checking if rowCount == 1, and if so, then checking if the row contains all empty strings.

In content add-ins for Access, for performance reasons the rowCount property always returns -1.

Support details

A capital Y in the following matrix indicates that this property is supported in the corresponding Office host application. An empty cell indicates that the Office host application doesn't support this property.

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
Access Y
Excel Y Y Y
Word Y Y Y

Examples

function showBindingRowCount() {
    Office.context.document.bindings.getByIdAsync("myBinding", function (asyncResult) {
        write("Rows: " + asyncResult.value.rowCount);
    });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

Method Details

addColumnsAsync(tableData, options, callback)

Adds the specified data to the table as additional columns.

addColumnsAsync(tableData: TableData | any[][], options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

tableData
TableData | any[][]

An array of arrays ("matrix") or a TableData object that contains one or more columns of data to add to the table. Required.

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

Remarks

To add one or more columns specifying the values of the data and headers, pass a TableData object as the data parameter. To add one or more columns specifying only the data, pass an array of arrays ("matrix") as the data parameter.

The success or failure of an addColumnsAsync operation is atomic. That is, the entire add columns operation must succeed, or it will be completely rolled back (and the AsyncResult.status property returned to the callback will report failure):

  • Each row in the array you pass as the data argument must have the same number of rows as the table being updated. If not, the entire operation will fail.

  • Each row and cell in the array must successfully add that row or cell to the table in the newly added column(s). If any row or cell fails to be set for any reason, the entire operation will fail.

  • If you pass a TableData object as the data argument, the number of header rows must match that of the table being updated.

Additional remark for Excel Online: The total number of cells in the TableData object passed to the data parameter can't exceed 20,000 in a single call to this method.

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
Word Y Y Y

Examples

// The following example adds a single column with three rows to a bound table with the id "myTable"
// by passing a TableData object as the data argument of the addColumnsAsync method. To succeed,
// the table being updated must have three rows.

// Add a column to a binding of type table by passing a TableData object.
function addColumns() {
    var myTable = new Office.TableData();
    myTable.headers = [["Cities"]];
    myTable.rows = [["Berlin"], ["Roma"], ["Tokyo"]];

    Office.context.document.bindings.getByIdAsync("myTable", function (result) {
        result.value.addColumnsAsync(myTable);
    });
}

// The following example adds a single column with three rows to a bound table with the id myTable
// by passing an array of arrays ("matrix") as the data argument of the addColumnsAsync method.
// To succeed, the table being updated must have three rows.

// Add a column to a binding of type table by passing an array of arrays.
function addColumns() {
    var myTable = [["Berlin"], ["Roma"], ["Tokyo"]];

    Office.context.document.bindings.getByIdAsync("myTable", function (result) {
        result.value.addColumnsAsync(myTable);
    });
}

addRowsAsync(rows, options, callback)

Adds the specified data to the table as additional rows.

addRowsAsync(rows: TableData | any[][], options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

rows
TableData | any[][]

An array of arrays ("matrix") or a TableData object that contains one or more rows of data to add to the table. Required.

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

Remarks

The success or failure of an addRowsAsync operation is atomic. That is, the entire add columns operation must succeed, or it will be completely rolled back (and the AsyncResult.status property returned to the callback will report failure):

  • Each row in the array you pass as the data argument must have the same number of columns as the table being updated. If not, the entire operation will fail.

  • Each column and cell in the array must successfully add that column or cell to the table in the newly added rows(s). If any column or cell fails to be set for any reason, the entire operation will fail.

  • If you pass a TableData object as the data argument, the number of header rows must match that of the table being updated.

Additional remark for Excel Online: The total number of cells in the TableData object passed to the data parameter can't exceed 20,000 in a single call to this method.

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
Access Y
Excel Y Y Y
Word Y Y Y

Examples

function addRowsToTable() {
    Office.context.document.bindings.getByIdAsync("myBinding", function (asyncResult) {
        var binding = asyncResult.value;
        binding.addRowsAsync([["6", "k"], ["7", "j"]]);
    });
}

clearFormatsAsync(options, callback)

Clears formatting on the bound table.

clearFormatsAsync(options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

Remarks

See Format tables in add-ins for Excel for more information.

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

Examples

// The following example shows how to clear the formatting of the bound table with an ID of "myBinding":
Office.select("bindings#myBinding").clearFormatsAsync();

deleteAllDataValuesAsync(options, callback)

Deletes all non-header rows and their values in the table, shifting appropriately for the host application.

deleteAllDataValuesAsync(options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

Remarks

In Excel, if the table has no header row, this method will delete the table itself.

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
Access Y
Excel Y Y Y
Word Y Y Y

Examples

function deleteAllRowsFromTable() {
    Office.context.document.bindings.getByIdAsync("myBinding", function (asyncResult) {
        var binding = asyncResult.value;
        binding.deleteAllDataValuesAsync();
    });
}

getFormatsAsync(cellReference, formats, options, callback)

Gets the formatting on specified items in the table.

getFormatsAsync(cellReference?: any, formats?: any[], options?: Office.AsyncContextOptions, callback?: (result: AsyncResult< ({ cells: any, format: any})[]>) => void): void;

Parameters

cellReference
any

An object literal containing name-value pairs that specify the range of cells to get formatting from.

formats
any[]

An array specifying the format properties to get.

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult< ({ cells: any, format: any})>) => void[]

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult. The value property of the result is an array containing one or more JavaScript objects specifying the formatting of their corresponding cells.

Returns

void

Remarks

Returned format structure

Each JavaScript object in the return value 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: n} Specifies the range that is the zero-based nth row of data in the table.
{column: n} Specifies the range that is the zero-based nth column of data in the table.
{row: i, column: j} Specifies the single cell that is the ith row and jth column of 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).

setFormatsAsync(cellFormat, options, callback)

Sets formatting on specified items and data in the table.

setFormatsAsync(cellFormat: any[], options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

cellFormat
any[]

An array that contains one or more JavaScript objects that specify which cells to target and the formatting to apply to them.

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

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: n} Specifies the range that is the zero-based nth row of data in the table.
{column: n} Specifies the range that is the zero-based nth column of data in the table.
{row: i, column: j} Specifies the single cell that is the ith row and jth column of 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.

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.

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

Examples

// 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){});

// 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){});

setTableOptionsAsync(tableOptions, options, callback)

Updates table formatting options on the bound table.

setTableOptionsAsync(tableOptions: any, options?: Office.AsyncContextOptions, callback?: (result: AsyncResult<void>) => void): void;

Parameters

tableOptions
any

An object literal containing a list of property name-value pairs that define the table options to apply.

options
Office.AsyncContextOptions

Provides an option for preserving context data of any type, unchanged, for use in a callback.

callback
(result: AsyncResult<void>) => void

Optional. A function that is invoked when the callback returns, whose only parameter is of type Office.AsyncResult.

Returns

void

Remarks

HostsExcel
Requirement SetsNot in a set

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 A user-defined item of any type that is returned in the AsyncResult object without being altered.

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

Examples

// The following example shows how to:
// 1. Create an object literal that specifies the table formatting options to update on the bound table.
// 2. Call setTableOptions on a previously bound table (with an id of myBinding) passing the object
//    with formatting setting as the tableOptions parameter.
function updateTableFormatting(){
    var tableOptions = {bandedRows: true, filterButton: false, style: "TableStyleMedium3"}; 

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