Office.TableBinding interface

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

columnCount: number;

Property Value

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; 
}

True, if the table has headers; otherwise false.

hasHeaders: boolean;

Property Value

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; 
}

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

rowCount: number;

Property Value

Remarks

When you insert an empty table by selecting a single row in Excel 2013 and Excel on the web (using Table on the Insert tab), both Office 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 on the web.

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

• In Excel on the web, 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.

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; 
}

Adds the specified data to the table as additional columns.

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

Parameters

Returns

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 columns. 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 on the web: 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.

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() {
    const 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() {
    const myTable = [["Berlin"], ["Roma"], ["Tokyo"]];

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

Adds the specified data to the table as additional columns.

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

Parameters

Returns

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 columns. 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 on the web: 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.

Adds the specified data to the table as additional rows.

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

Parameters

Returns

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. 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 on the web: 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.

Examples

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

Adds the specified data to the table as additional rows.

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

Parameters

Returns

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. 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 on the web: 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.

Clears formatting on the bound table.

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

Parameters

Returns

Remarks

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

Examples

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

Clears formatting on the bound table.

clearFormatsAsync(callback?: (result: AsyncResult<void>) => void): void;

Parameters

Returns

Remarks

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

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

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

Parameters

Returns

Remarks

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

Examples

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

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

deleteAllDataValuesAsync(callback?: (result: AsyncResult<void>) => void): void;

Parameters

Returns

Remarks

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

Gets the formatting on specified items in the table.

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

Parameters

Returns

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

The format: property specifies values that correspond to a subset of the settings available in the Format Cells dialog box in Excel (Right-click then select Format Cells, or Home > Format > Format Cells).

Gets the formatting on specified items in the table.

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

Parameters

Returns

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

The format: property specifies values that correspond to a subset of the settings available in the Format Cells dialog box in Excel (Right-click then select Format Cells, or Home > Format > Format Cells).

Sets formatting on specified items and data in the table.

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

Parameters

Returns

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

The format: property specifies values that correspond to a subset of the settings available in the Format Cells dialog box in Excel (Right-click then select 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.

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

Sets formatting on specified items and data in the table.

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

Parameters

Returns

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

The format: property specifies values that correspond to a subset of the settings available in the Format Cells dialog box in Excel (Right-click then select 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.

Updates table formatting options on the bound table.

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

Parameters

Returns

Remarks

Requirement set: Not 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.

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(){
    const tableOptions = {bandedRows: true, filterButton: false, style: "TableStyleMedium3"}; 

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

Updates table formatting options on the bound table.

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

Parameters

Returns

Remarks

Requirement set: Not 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.