CODEWITHSUNDEEP
exceloffice365office-addinsoffice-js
Ar Es
Ar Es

Reputation: 431

Add-Ins for Office365: inserting data into an excel sheet from a task-pane add-in

I must be missing something:

[problem]:

I have a 2-dimensional array of data, that i would like to insert into the current sheet starting from cell A1. I also have to format this data. The current document may be empty or not. I do not have control over this.

At a later point, i need to remove the data from the document and insert new data. This new set of data may have different dimensions.

This seems to be impossible to do using the Office JavaScript Api.

All the things I tried using TableBindings, etc. have failed. In many cases, functionality that should work according to MSDN failed, giving me cryptic error messages such as "internal error" (code 5001) or unsupported binding operation (3010). I have to use tablebindings, because i can't apply formatting to anything else according to the MSDN documentation.

The following solutions are unacceptable:

All of which are nightmarish solutions from a usability point-of-view.

I can create a binding from named item and construct the range by counting columns and rows and building a string like this "A1:C232" but this only works once, because:

I hope someone from Microsoft reads this and can point me in the right direction. I really hope! Because i'm starting to fear that this is actually by design. (i'm so frustrated by office.js after the last few weeks of struggle, i'm having a hard time not ranting, so i'll stop right here.. don't get me started on ui fabric)

Upvotes: 3

Views: 1549

Answers (1)

Drew Weymouth
Drew Weymouth

Reputation: 106

With the new Excel Javascript APIs introduced with Office 2016 (and available soon in Office Online), it is easy to manipulate worksheet data using the new Range object. The snippet below runs without requiring range selection or any other action on the part of the user.

var data = [
    [1,2,3],
    [2,6,7]
]

Excel.run(function (ctx) {
    // make space for the data to be inserted
    var sheet1 = ctx.workbook.worksheets.getItem("Sheet1");
    var firstCell = sheet1.getCell(0,0);
    var lastCell = sheet1.getCell(data.length - 1, data[0].length - 1);
    var range = firstCell.getBoundingRect(lastCell).insert('down');

    range.values = data; // insert data
    range.format.font.bold = true;

    range.delete('up'); // erase data, shift up
    return ctx.sync();
}).catch(function(error) {
    console.log(error);
})

Try it out in the Office JS Snippet explorer and see the blog post linked below for more info!

https://github.com/OfficeDev/office-js-snippet-explorer

https://dev.office.com/blogs/Office-js-Public-Preview

Upvotes: 7

Related Questions