CODEWITHSUNDEEP
angulartypescriptoffice-js
Silva
Silva

Reputation: 161

Converting an Excel Range to a Table

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

Upvotes: 1

Views: 1252

Answers (2)

Silva
Silva

Reputation: 161

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);

Upvotes: 2

Kim Brandl
Kim Brandl

Reputation: 13490

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.

Upvotes: 2

Related Questions