CODEWITHSUNDEEP
javascriptoffice-js
hyperdrive
hyperdrive

Reputation: 1846

Office Excel JS - Change Range values of table object

I am working on an Excel add-in using the Office JS API. I am experiencing an issue when updating the values of a binding type of "range" when a table object has been inserted inside the bounds of the range.

For example I have a binding on A1:C3, and a table has been inserted in the Excel UI using. Insert -> Table at B2:C3 with My table has headers checked on.

Using the below function to update the raw values of that range seems to remove the table object.

Alternatively, If the binding range is smaller then the table object, ie. binding inside the bounds of the table object, the table and formatting remains.

var bindingId = "123";
var newValue = [["123","123","123"], ["123","123","123"], ["123","123","123"]];

return Excel.run(function (ctx) {

    var binding = ctx.workbook.bindings.getItem(bindingId);
    var range = binding.getRange().load('values, address');

    return ctx.sync().then(function () {

        range.values = newValue;

        return ctx.sync();

    });

});

Upvotes: 1

Views: 954

Answers (1)

Marc LaFleur
Marc LaFleur

Reputation: 33094

To return a table object from your binding, should use bindingObject.getTable() rather than bindingObject.getRange().

Once you have a table object, you can use the table.rows.add() method to add one or more rows while preserving the existing table.

Upvotes: 1

Related Questions