Excel.Table class

Represents an Excel table. To learn more about the table object model, read Work with tables using the Excel JavaScript API.

[ API set: ExcelApi 1.1 ]

Extends
OfficeExtension.ClientObject

Properties

autoFilter

Represents the AutoFilter object of the table. Read-Only.

[ API set: ExcelApi 1.9 ]

columns

Represents a collection of all the columns in the table. Read-only.

[ API set: ExcelApi 1.1 ]

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

highlightFirstColumn

Indicates whether the first column contains special formatting.

[ API set: ExcelApi 1.3 ]

highlightLastColumn

Indicates whether the last column contains special formatting.

[ API set: ExcelApi 1.3 ]

id

Returns a value that uniquely identifies the table in a given workbook. The value of the identifier remains the same even when the table is renamed. Read-only.

[ API set: ExcelApi 1.1 ]

legacyId

Returns a numeric id.

[ API set: ExcelApi 1.8 ]

name

Name of the table.

[ API set: ExcelApi 1.1 ]

rows

Represents a collection of all the rows in the table. Read-only.

[ API set: ExcelApi 1.1 ]

showBandedColumns

Indicates whether the columns show banded formatting in which odd columns are highlighted differently from even ones to make reading the table easier.

[ API set: ExcelApi 1.3 ]

showBandedRows

Indicates whether the rows show banded formatting in which odd rows are highlighted differently from even ones to make reading the table easier.

[ API set: ExcelApi 1.3 ]

showFilterButton

Indicates whether the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

[ API set: ExcelApi 1.3 ]

showHeaders

Indicates whether the header row is visible or not. This value can be set to show or remove the header row.

[ API set: ExcelApi 1.1 ]

showTotals

Indicates whether the total row is visible or not. This value can be set to show or remove the total row.

[ API set: ExcelApi 1.1 ]

sort

Represents the sorting for the table. Read-only.

[ API set: ExcelApi 1.2 ]

style

Constant value that represents the Table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleStyleDark1" through "TableStyleStyleDark11". A custom user-defined style present in the workbook can also be specified.

[ API set: ExcelApi 1.1 ]

worksheet

The worksheet containing the current table. Read-only.

[ API set: ExcelApi 1.2 ]

Methods

clearFilters()

Clears all the filters currently applied on the table.

[ API set: ExcelApi 1.2 ]

clearStyle()

Changes the table to use the default table style.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

convertToRange()

Converts the table into a normal range of cells. All data is preserved.

[ API set: ExcelApi 1.2 ]

delete()

Deletes the table.

[ API set: ExcelApi 1.1 ]

getDataBodyRange()

Gets the range object associated with the data body of the table.

[ API set: ExcelApi 1.1 ]

getHeaderRowRange()

Gets the range object associated with header row of the table.

[ API set: ExcelApi 1.1 ]

getRange()

Gets the range object associated with the entire table.

[ API set: ExcelApi 1.1 ]

getTotalRowRange()

Gets the range object associated with totals row of the table.

[ API set: ExcelApi 1.1 ]

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

reapplyFilters()

Reapplies all the filters currently on the table.

[ API set: ExcelApi 1.2 ]

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Table object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.TableData) that contains shallow copies of any loaded child properties from the original object.

Events

onChanged

Occurs when data in cells changes on a specific table.

[ API set: ExcelApi 1.7 ]

onFiltered

Occurs when filter is applied on a specific table.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

onSelectionChanged

Occurs when the selection changes on a specific table.

[ API set: ExcelApi 1.7 ]

Property Details

autoFilter

Represents the AutoFilter object of the table. Read-Only.

[ API set: ExcelApi 1.9 ]

readonly autoFilter: Excel.AutoFilter;

Property Value

columns

Represents a collection of all the columns in the table. Read-only.

[ API set: ExcelApi 1.1 ]

readonly columns: Excel.TableColumnCollection;

Property Value

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

highlightFirstColumn

Indicates whether the first column contains special formatting.

[ API set: ExcelApi 1.3 ]

highlightFirstColumn: boolean;

Property Value

boolean

highlightLastColumn

Indicates whether the last column contains special formatting.

[ API set: ExcelApi 1.3 ]

highlightLastColumn: boolean;

Property Value

boolean

id

Returns a value that uniquely identifies the table in a given workbook. The value of the identifier remains the same even when the table is renamed. Read-only.

[ API set: ExcelApi 1.1 ]

readonly id: string;

Property Value

string

legacyId

Returns a numeric id.

[ API set: ExcelApi 1.8 ]

readonly legacyId: string;

Property Value

string

name

Name of the table.

[ API set: ExcelApi 1.1 ]

name: string;

Property Value

string

rows

Represents a collection of all the rows in the table. Read-only.

[ API set: ExcelApi 1.1 ]

readonly rows: Excel.TableRowCollection;

Property Value

showBandedColumns

Indicates whether the columns show banded formatting in which odd columns are highlighted differently from even ones to make reading the table easier.

[ API set: ExcelApi 1.3 ]

showBandedColumns: boolean;

Property Value

boolean

showBandedRows

Indicates whether the rows show banded formatting in which odd rows are highlighted differently from even ones to make reading the table easier.

[ API set: ExcelApi 1.3 ]

showBandedRows: boolean;

Property Value

boolean

showFilterButton

Indicates whether the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

[ API set: ExcelApi 1.3 ]

showFilterButton: boolean;

Property Value

boolean

showHeaders

Indicates whether the header row is visible or not. This value can be set to show or remove the header row.

[ API set: ExcelApi 1.1 ]

showHeaders: boolean;

Property Value

boolean

showTotals

Indicates whether the total row is visible or not. This value can be set to show or remove the total row.

[ API set: ExcelApi 1.1 ]

showTotals: boolean;

Property Value

boolean

sort

Represents the sorting for the table. Read-only.

[ API set: ExcelApi 1.2 ]

readonly sort: Excel.TableSort;

Property Value

style

Constant value that represents the Table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleStyleDark1" through "TableStyleStyleDark11". A custom user-defined style present in the workbook can also be specified.

[ API set: ExcelApi 1.1 ]

style: string;

Property Value

string

worksheet

The worksheet containing the current table. Read-only.

[ API set: ExcelApi 1.2 ]

readonly worksheet: Excel.Worksheet;

Property Value

Method Details

clearFilters()

Clears all the filters currently applied on the table.

[ API set: ExcelApi 1.2 ]

clearFilters(): void;

Returns

void

clearStyle()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Changes the table to use the default table style.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

clearStyle(): void;

Returns

void

convertToRange()

Converts the table into a normal range of cells. All data is preserved.

[ API set: ExcelApi 1.2 ]

convertToRange(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.convertToRange();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

delete()

Deletes the table.

[ API set: ExcelApi 1.1 ]

delete(): void;

Returns

void

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.delete();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getDataBodyRange()

Gets the range object associated with the data body of the table.

[ API set: ExcelApi 1.1 ]

getDataBodyRange(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableDataRange = table.getDataBodyRange();
    tableDataRange.load('address')
    return ctx.sync().then(function() {
            console.log(tableDataRange.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getHeaderRowRange()

Gets the range object associated with header row of the table.

[ API set: ExcelApi 1.1 ]

getHeaderRowRange(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableHeaderRange = table.getHeaderRowRange();
    tableHeaderRange.load('address');
    return ctx.sync().then(function() {
        console.log(tableHeaderRange.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getRange()

Gets the range object associated with the entire table.

[ API set: ExcelApi 1.1 ]

getRange(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableRange = table.getRange();
    tableRange.load('address');    
    return ctx.sync().then(function() {
            console.log(tableRange.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getTotalRowRange()

Gets the range object associated with totals row of the table.

[ API set: ExcelApi 1.1 ]

getTotalRowRange(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableTotalsRange = table.getTotalRowRange();
    tableTotalsRange.load('address');    
    return ctx.sync().then(function() {
            console.log(tableTotalsRange.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.TableLoadOptions): Excel.Table;

Parameters

options
Excel.Interfaces.TableLoadOptions

Provides options for which properties of the object to load.

Returns

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.Table;

Parameters

propertyNames
string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Examples

// Get a table by name. 
Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.load('index')
    return ctx.sync().then(function() {
            console.log(table.index);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Get a table by index.
Excel.run(function (ctx) { 
    var index = 0;
    var table = ctx.workbook.tables.getItemAt(0);
    table.load('id')
    return ctx.sync().then(function() {
            console.log(table.id);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Set table style. 
Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.name = 'Table1-Renamed';
    table.showTotals = false;
    table.style = 'TableStyleMedium2';
    table.load('tableStyle');
    return ctx.sync().then(function() {
            console.log(table.style);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.Table;

Parameters

propertyNamesAndPaths
{ select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

reapplyFilters()

Reapplies all the filters currently on the table.

[ API set: ExcelApi 1.2 ]

reapplyFilters(): void;

Returns

void

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.TableUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties
Interfaces.TableUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options
OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

Remarks

This method has the following additional signature:

set(properties: Excel.Table): void

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Table): void;

Parameters

properties
Excel.Table

Returns

void

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Table object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.TableData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.TableData;

Returns

Event Details

onChanged

Occurs when data in cells changes on a specific table.

[ API set: ExcelApi 1.7 ]

readonly onChanged: OfficeExtension.EventHandlers<Excel.TableChangedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.TableChangedEventArgs>

Examples

await Excel.run(async (context) => {
    let table = context.workbook.tables.getItemAt(0);
    table.onChanged.add(onChange);

    await context.sync();
    console.log("A handler has been registered for the onChanged event");
});

onFiltered

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Occurs when filter is applied on a specific table.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly onFiltered: OfficeExtension.EventHandlers<Excel.TableFilteredEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.TableFilteredEventArgs>

onSelectionChanged

Occurs when the selection changes on a specific table.

[ API set: ExcelApi 1.7 ]

readonly onSelectionChanged: OfficeExtension.EventHandlers<Excel.TableSelectionChangedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.TableSelectionChangedEventArgs>

Examples

await Excel.run(async (context) => {
    let table = context.workbook.tables.getItemAt(0);
    table.onSelectionChanged.add(onSelectionChange);

    await context.sync();
    console.log("A handler has been registered for table onSelectionChanged event");
});