TableCollection Object (JavaScript API for Excel)

Represents a collection of all the tables that are part of the workbook or worksheet, depending on how it was reached.

Properties

Property Type Description Req. Set
count int Returns the number of tables in the workbook. Read-only. 1.1
items Table[] A collection of table objects. Read-only. 1.1

See property access examples.

Relationships

None

Methods

Method Return Type Description Req. Set
add(address: object, hasHeaders: bool) Table Create a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown. 1.1
getCount() int Gets the number of tables in the collection. 1.4
getItem(key: number or string) Table Gets a table by Name or ID. 1.1
getItemAt(index: number) Table Gets a table based on its position in the collection. 1.1
getItemOrNullObject(key: number or string) Table Gets a table by Name or ID. If the table does not exist, will return a null object. 1.4

Method Details

add(address: object, hasHeaders: bool)

Create a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown.

Syntax

tableCollectionObject.add(address, hasHeaders);

Parameters

Parameter Type Description
address object A Range object, or a string address or name of the range representing the data source. If the address does not contain a sheet name, the currently-active sheet is used.
hasHeaders bool Boolean value that indicates whether the data being imported has column labels. If the source does not contain headers (i.e,. when this property set to false), Excel will automatically generate header shifting the data down by one row.

Returns

Table

Examples

Excel.run(function (ctx) { 
    var table = ctx.workbook.tables.add('Sheet1!A1:E7', true);
    table.load('name');
    return ctx.sync().then(function() {
        console.log(table.name);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getCount()

Gets the number of tables in the collection.

Syntax

tableCollectionObject.getCount();

Parameters

None

Returns

int

getItem(key: number or string)

Gets a table by Name or ID.

Syntax

tableCollectionObject.getItem(key);

Parameters

Parameter Type Description
key number or string Name or ID of the table to be retrieved.

Returns

Table

Examples

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

Examples

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

getItemAt(index: number)

Gets a table based on its position in the collection.

Syntax

tableCollectionObject.getItemAt(index);

Parameters

Parameter Type Description
index number Index value of the object to be retrieved. Zero-indexed.

Returns

Table

Examples

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

getItemOrNullObject(key: number or string)

Gets a table by Name or ID. If the table does not exist, will return a null object.

Syntax

tableCollectionObject.getItemOrNullObject(key);

Parameters

Parameter Type Description
key number or string Name or ID of the table to be retrieved.

Returns

Table

Property access examples

Excel.run(function (ctx) { 
    var tables = ctx.workbook.tables;
    tables.load();
    return ctx.sync().then(function() {
        console.log("tables Count: " + tables.count);
        for (var i = 0; i < tables.items.length; i++)
        {
            console.log(tables.items[i].name);
        }
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Get the number of tables

Excel.run(function (ctx) { 
    var tables = ctx.workbook.tables;
    tables.load('count');
    return ctx.sync().then(function() {
        console.log(tables.count);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});
; if (error instanceof OfficeExtension.Error) { console.log("Debug info: " + JSON.stringify(error.debugInfo)); } });
or.debugInfo)); } });

Get the number of tables

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