Table Object (JavaScript API for Excel)
Represents an Excel table.
Properties
| Property | Type | Description | Req. Set |
|---|---|---|---|
| highlightFirstColumn | bool | Indicates whether the first column contains special formatting. | 1.3 |
| highlightLastColumn | bool | Indicates whether the last column contains special formatting. | 1.3 |
| id | int | 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. | 1.1 |
| name | string | Name of the table. | 1.1 |
| showBandedColumns | bool | Indicates whether the columns show banded formatting in which odd columns are highlighted differently from even ones to make reading the table easier. | 1.3 |
| showBandedRows | bool | Indicates whether the rows show banded formatting in which odd rows are highlighted differently from even ones to make reading the table easier. | 1.3 |
| showFilterButton | bool | 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. | 1.3 |
| showHeaders | bool | Indicates whether the header row is visible or not. This value can be set to show or remove the header row. | 1.1 |
| showTotals | bool | Indicates whether the total row is visible or not. This value can be set to show or remove the total row. | 1.1 |
| style | string | Constant value that represents the Table style. Possible values are: TableStyleLight1 thru TableStyleLight21, TableStyleMedium1 thru TableStyleMedium28, TableStyleStyleDark1 thru TableStyleStyleDark11. A custom user-defined style present in the workbook can also be specified. | 1.1 |
See property access examples.
Relationships
| Relationship | Type | Description | Req. Set |
|---|---|---|---|
| columns | TableColumnCollection | Represents a collection of all the columns in the table. Read-only. | 1.1 |
| rows | TableRowCollection | Represents a collection of all the rows in the table. Read-only. | 1.1 |
| sort | TableSort | Represents the sorting for the table. Read-only. | 1.2 |
| worksheet | Worksheet | The worksheet containing the current table. Read-only. | 1.2 |
Methods
| Method | Return Type | Description | Req. Set |
|---|---|---|---|
| clearFilters() | void | Clears all the filters currently applied on the table. | 1.2 |
| convertToRange() | Range | Converts the table into a normal range of cells. All data is preserved. | 1.2 |
| delete() | void | Deletes the table. | 1.1 |
| getDataBodyRange() | Range | Gets the range object associated with the data body of the table. | 1.1 |
| getHeaderRowRange() | Range | Gets the range object associated with header row of the table. | 1.1 |
| getRange() | Range | Gets the range object associated with the entire table. | 1.1 |
| getTotalRowRange() | Range | Gets the range object associated with totals row of the table. | 1.1 |
| reapplyFilters() | void | Reapplies all the filters currently on the table. | 1.2 |
Method Details
clearFilters()
Clears all the filters currently applied on the table.
Syntax
tableObject.clearFilters();
Parameters
None
Returns
void
convertToRange()
Converts the table into a normal range of cells. All data is preserved.
Syntax
tableObject.convertToRange();
Parameters
None
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.
Syntax
tableObject.delete();
Parameters
None
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.
Syntax
tableObject.getDataBodyRange();
Parameters
None
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.
Syntax
tableObject.getHeaderRowRange();
Parameters
None
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.
Syntax
tableObject.getRange();
Parameters
None
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.
Syntax
tableObject.getTotalRowRange();
Parameters
None
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));
}
});
reapplyFilters()
Reapplies all the filters currently on the table.
Syntax
tableObject.reapplyFilters();
Parameters
None
Returns
void
Property access 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));
}
});