Converting an Excel Range to a Table

Question

I'm trying to use Office.js API to build an Angular Excel add-in, but I'm having a problem converting an Excel.Range to an Excel.Table.

I have a code like the example below binded to a button click, but although I manage to get the documentLines in the Excel Sheet, no table is created.

getExampleTable() {

    const documentLinesHeader = [["Period 1", "Period 2", "Period 3"]];

    const documentLines = [
        ["Line 1.1", "Line 1.2", "Line 1.3"],
        ["Line 2.1", "Line 2.2", "Line 2.3"],
        ["Line 3.1", "Line 3.2", "Line 3.3"],
    ];

    const documentLinesCount = documentLines.length;
    const columnCount = documentLines[0].length;

    Excel.run(async (ctx) => {        

        let sheet = ctx.workbook.worksheets.getActiveWorksheet();

        let cell = sheet.getCell(0,0);

        let tableRange = cell.getResizedRange(documentLinesCount - 1, columnCount - 1);

        tableRange.load("address");

        await ctx.sync();

        console.log("Table Range is:", tableRange.address);
        // Outputs Table Range is: Sheet1!A1:C3

        tableRange.values = documentLines;

        let exampleTable = sheet.tables.add(tableRange, true);
        exampleTable.getHeaderRowRange().values = documentLinesHeader;
        exampleTable.name = "ExampleTable";

        return await ctx.sync();
    })
    .catch(this.errorHandler);
}

If I look at the console, I have the error:

Debug info: {"code":"InvalidArgument","message":"The argument is invalid or 
missing or has an incorrect format.","errorLocation":"TableCollection.add"}

My Office Version is: 16.0.4639.1000

Kim Brandl · Accepted Answer · 2018-01-19 20:16:26Z

2

I'm unable to reproduce the issue that you've described. According to the docs, TableCollection.add is part of requirement set 1.1 and your version of Office does support that requirement set.

The following function (a somewhat streamlined/simplified version of your function) successfully populates a range with data and then creates a table from that range.

async function getExampleTable_2() {
    await Excel.run(async (context) => {

        const values = [
            ["Period 1", "Period 2", "Period 3"],
            ["Line 1.1", "Line 1.2", "Line 1.3"],
            ["Line 2.1", "Line 2.2", "Line 2.3"],
            ["Line 3.1", "Line 3.2", "Line 3.3"]
        ];

        // Get active sheet.
        let sheet = context.workbook.worksheets.getActiveWorksheet();

        // Get Range object that encompasses table data.
        let tableRange = sheet.getCell(0, 0).getResizedRange(values.length - 1, values[0].length - 1);

        // Write values to the range.
        tableRange.values = values;

        // Create a table from the range.
        let exampleTable = sheet.tables.add(tableRange, true);

        exampleTable.name = "ExpensesTable";

        await context.sync();
    }).catch(errorHandler);
}

You can try this snippet yourself by using Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following Gist URL: https://gist.github.com/kbrandl/fcb894084eb764098156965aeefb5bf2.

answered Jan 19, 2018 at 20:16

Kim Brandl

13.5k2 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Silva Over a year ago

Just tried your suggestion, and both buttons fail with the same error... Could it be some Edge version issue? (I believe the add-ins use the Edge engine, correct?)

Kim Brandl Over a year ago

Add-ins on the Desktop currently use Internet Explorer 11 (or lower if the computer doesn't have IE 11). In Office Online, Add-ins simply run in an iFrame. Did you try my running my snippet in Script Lab or did you copy/paste the function into your Angular app?

Silva Over a year ago

I've run the script lab with your gist. So, I tried the online versions, both on IE11 and Edge, and both worked. This must be some kind of problem with my desktop Office version...

Silva Over a year ago

I found the problem (some kind of bug in office maybe?). Will answer bellow. Thank you for help @Kim Brand.

Silva · Accepted Answer · 2018-01-22 16:38:34Z

2

So, I found that there is some kind of problem with my version of Office that makes it impossible to "add" a Range object to the TableCollection. Although the documentation clearly states that the parameter can be "A Range object, or a string address or name of the range representing the data source."

The workaround to this problem, is to load the address of the Range, and pass that instead to the add method. Using my snippet above, you have to load the address, and then, instead of passing the Range object, pass the range address, like this:

let exampleTable = sheet.tables.add(tableRange.address, true);

answered Jan 22, 2018 at 16:38

Silva

1611 silver badge13 bronze badges

3 Comments

Kim Brandl Over a year ago

Glad to hear that you figured it out and thanks for sharing this info. I've logged an issue in the office-js repo (github.com/OfficeDev/office-js/issues/52) to report this to the product team.

Michael Zlatkovsky Over a year ago

Please see my comment on github.com/OfficeDev/office-js/issues/52#issuecomment-359530509. Even though the method is a 1.1 one, the ability to pass a Range object is part of 1.3. The IntelliSense comment notes this, but it looks like our documentation does not. My apologies, we'll get it fixed. See the comment on GitHub for more details.

Kim Brandl Over a year ago

Thanks, @Michael Zlatkovsky. The docs have been updated with this info: dev.office.com/reference/add-ins/excel/tablecollection.

Collectives™ on Stack Overflow

Converting an Excel Range to a Table

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related