Excel.Table class
Represents an Excel table. To learn more about the table object model, read Work with tables using the Excel JavaScript API.
- Extends
Remarks
Properties
| auto |
Represents the |
| columns | Represents a collection of all the columns in the table. |
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| highlight |
Specifies if the first column contains special formatting. |
| highlight |
Specifies if the last column contains special formatting. |
| 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. |
| legacy |
Returns a numeric ID. |
| name | Name of the table. The set name of the table must follow the guidelines specified in the Rename an Excel table article. |
| rows | Represents a collection of all the rows in the table. |
| show |
Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier. |
| show |
Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier. |
| show |
Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row. |
| show |
Specifies if the header row is visible. This value can be set to show or remove the header row. |
| show |
Specifies if the total row is visible. This value can be set to show or remove the total row. |
| sort | Represents the sorting for the table. |
| style | Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified. |
| table |
The style applied to the table. |
| worksheet | The worksheet containing the current table. |
Methods
| clear |
Clears all the filters currently applied on the table. |
| clear |
Changes the table to use the default table style. |
| convert |
Converts the table into a normal range of cells. All data is preserved. |
| delete() | Deletes the table. |
| get |
Gets the range object associated with the data body of the table. |
| get |
Gets the range object associated with the header row of the table. |
| get |
Gets the range object associated with the entire table. |
| get |
Gets the range object associated with the totals row of the table. |
| load(options) | Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| reapply |
Reapplies all the filters currently on the table. |
| resize(new |
Resize the table to the new range. The new range must overlap with the original table range and the headers (or the top of the table) must be in the same row. |
| 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. |
| set |
Sets the style applied to the table. |
| toJSON() | Overrides the JavaScript |
Events
| on |
Occurs when data in cells changes on a specific table. |
| on |
Occurs when a filter is applied on a specific table. |
| on |
Occurs when the selection changes on a specific table. |
Property Details
autoFilter
Represents the AutoFilter object of the table.
readonly autoFilter: Excel.AutoFilter;Property Value
Remarks
columns
Represents a collection of all the columns in the table.
readonly columns: Excel.TableColumnCollection;Property Value
Remarks
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
Specifies if the first column contains special formatting.
highlightFirstColumn: boolean;Property Value
boolean
Remarks
highlightLastColumn
Specifies if the last column contains special formatting.
highlightLastColumn: boolean;Property Value
boolean
Remarks
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.
readonly id: string;Property Value
string
Remarks
legacyId
Returns a numeric ID.
readonly legacyId: string;Property Value
string
Remarks
name
Name of the table.
The set name of the table must follow the guidelines specified in the Rename an Excel table article.
name: string;Property Value
string
Remarks
rows
Represents a collection of all the rows in the table.
readonly rows: Excel.TableRowCollection;Property Value
Remarks
showBandedColumns
Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier.
showBandedColumns: boolean;Property Value
boolean
Remarks
showBandedRows
Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier.
showBandedRows: boolean;Property Value
boolean
Remarks
showFilterButton
Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.
showFilterButton: boolean;Property Value
boolean
Remarks
showHeaders
Specifies if the header row is visible. This value can be set to show or remove the header row.
showHeaders: boolean;Property Value
boolean
Remarks
showTotals
Specifies if the total row is visible. This value can be set to show or remove the total row.
showTotals: boolean;Property Value
boolean
Remarks
sort
Represents the sorting for the table.
readonly sort: Excel.TableSort;Property Value
Remarks
style
Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified.
style: string;Property Value
string
Remarks
Examples
// Set table style.
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
table.name = 'Table1-Renamed';
table.showTotals = false;
table.style = 'TableStyleMedium2';
table.load('tableStyle');
await context.sync();
console.log(table.style);
});
tableStyle
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.
The style applied to the table.
readonly tableStyle: Excel.TableStyle;Property Value
Remarks
worksheet
The worksheet containing the current table.
readonly worksheet: Excel.Worksheet;Property Value
Remarks
Method Details
clearFilters()
Clears all the filters currently applied on the table.
clearFilters(): void;Returns
void
Remarks
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.
clearStyle(): void;Returns
void
Remarks
convertToRange()
Converts the table into a normal range of cells. All data is preserved.
convertToRange(): Excel.Range;Returns
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
table.convertToRange();
await context.sync();
});
delete()
Deletes the table.
delete(): void;Returns
void
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
table.delete();
await context.sync();
});
getDataBodyRange()
Gets the range object associated with the data body of the table.
getDataBodyRange(): Excel.Range;Returns
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
const tableDataRange = table.getDataBodyRange();
tableDataRange.load('address')
await context.sync();
console.log(tableDataRange.address);
});
getHeaderRowRange()
Gets the range object associated with the header row of the table.
getHeaderRowRange(): Excel.Range;Returns
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
const tableHeaderRange = table.getHeaderRowRange();
tableHeaderRange.load('address');
await context.sync();
console.log(tableHeaderRange.address);
});
getRange()
Gets the range object associated with the entire table.
getRange(): Excel.Range;Returns
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
const tableRange = table.getRange();
tableRange.load('address');
await context.sync();
console.log(tableRange.address);
});
getTotalRowRange()
Gets the range object associated with the totals row of the table.
getTotalRowRange(): Excel.Range;Returns
Remarks
Examples
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
const tableTotalsRange = table.getTotalRowRange();
tableTotalsRange.load('address');
await context.sync();
console.log(tableTotalsRange.address);
});
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
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.
await Excel.run(async (context) => {
const tableName = 'Table1';
const table = context.workbook.tables.getItem(tableName);
table.load('id')
await context.sync();
console.log(table.id);
});
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.
reapplyFilters(): void;Returns
void
Remarks
resize(newRange)
Resize the table to the new range. The new range must overlap with the original table range and the headers (or the top of the table) must be in the same row.
resize(newRange: Range | string): void;Parameters
- newRange
-
Excel.Range | string
The range object or range address that will be used to determine the new size of the table.
Returns
void
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/46-table/resize-table.yaml
await Excel.run(async (context) => {
// Retrieve the worksheet and a table on that worksheet.
const sheet = context.workbook.worksheets.getItem("Sample");
const expensesTable = sheet.tables.getItem("ExpensesTable");
// Resize the table.
expensesTable.resize("A1:D20");
await context.sync();
});
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
- Excel.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
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
setStyle(style)
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.
Sets the style applied to the table.
setStyle(style: string | TableStyle | BuiltInTableStyle): void;Parameters
- style
-
string | Excel.TableStyle | Excel.BuiltInTableStyle
The style to apply to the table. An InvalidArgumentException is thrown if a string is provided that does not match the name of any style.
Returns
void
Remarks
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.
readonly onChanged: OfficeExtension.EventHandlers<Excel.TableChangedEventArgs>;Event Type
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-table-changed.yaml
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 a filter is applied on a specific table.
readonly onFiltered: OfficeExtension.EventHandlers<Excel.TableFilteredEventArgs>;Event Type
Remarks
onSelectionChanged
Occurs when the selection changes on a specific table.
readonly onSelectionChanged: OfficeExtension.EventHandlers<Excel.TableSelectionChangedEventArgs>;Event Type
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-table-changed.yaml
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");
});
Office Add-ins
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for