4

Tried the following code that I pieced together from various sources but it doesn't seem to be working. Do I need to go through each individual property and assign them one by one? 

Excel.run(function (ctx) {
        var worksheet = ctx.workbook.worksheets.getItem(worksheetName);
        var range = worksheet.getUsedRange();
        range.load(["formulasLocal", "address", "format/*", "format/fill", "format/borders", "format/font"]);

        var newWorksheet = ctx.workbook.worksheets.add(worksheetName + " -Copy");
        return ctx.sync().then(function () {
            var newAddress = range.address.substring(range.address.indexOf("!") + 1);
            newWorksheet.getRange(newAddress).values = range.formulasLocal;
            newWorksheet.getRange(newAddress).format = range.format;
        }).then(ctx.sync);
Mr1159pm
  • 774
  • 1
  • 10
  • 21

2 Answers2

3

Unfortunately, the scenario is not currently supported (though it is on our backlog). You can get Values and Text and Formulas as arrays, but not the formatting properties.

When you do access something like range.format.fill.color, it will return a value for you if the range is identically formatted; and "null" if the range has multiple colors, and hence there is no single answer to return.

So for now, you would need to go through each cell (range.getCell(i,j)) and put them into your own 2D array, load each range's value individually, then sync, and then use that information to individually apply formatting back. Should be possible with a reasonably small range, but we do realize that there are better solutions possible with more targeted APIs,

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • Thanks for the answer. The more I explore the new API, the more things turn out to be unavailable. No adding workbooks, no duplicating worksheets, can't even copy formatting. These are very fundamental operations, it's really puzzling that they would not be included. – Mr1159pm Apr 19 '16 at 05:58
  • For adding workbooks, this is a known gap, but something that is tricky to do in a cross-platform way. We have some ideas, and it's certainly on the backlog, but I'll admit it's not at the very top. The other two that you mention -- applying formatting and duplicating sheets -- are towards the top of the backlog as we continue to expand our API surface area. Feel free to suggest or vote on ideas at https://officespdev.uservoice.com/forums/224641-general/category/163563-add-in-excel – Michael Zlatkovsky - Microsoft Apr 19 '16 at 18:49
  • 1
    Any updates on this front? Do we have copy formats and/or duplicate worksheets working as yet? – sidnc86 Feb 28 '17 at 07:47
  • 1
    No updates yet. I cannot comment/ commit to when they will be done, but both are important scenarios that we plan to address soon – Michael Zlatkovsky - Microsoft Feb 28 '17 at 16:37
  • Any updates on this? The new api is becoming better and better, but I'm still waiting for multiple range formatting ... – Đức Thanh Nguyễn Mar 14 '19 at 14:27
  • @ĐứcThanhNguyễn,there is a `getCellProperties`/`getRowProperties`/`getColumnProperties` API (and corresponding `setCellProperties` etc.) that is, I believe, in the beta/preview stage now. I am not sure if it's ready for public consumption yet, though. Let me check on it and get back to you... – Michael Zlatkovsky - Microsoft Mar 15 '19 at 01:19
  • @MichaelZlatkovsky-Microsoft Thank you, I will play around with it. For those, who are looking, here is the link https://github.com/OfficeDev/office-js-docs-pr/blob/master/docs/reference/requirement-sets/excel-api-requirement-sets.md – Đức Thanh Nguyễn Mar 18 '19 at 10:23
2

There is a group of APIs for doing a batch of set/get Range format. But they are still in preview. You can work with them on build#16.0.11328.20158(win32) or Excel online if you reference the Beta CDN. Here is a simple sample for get a format of a range and set to another range.

var actualData = rangeSrc.getCellProperties({
 format: {
    font: {
           bold: true,
           color: true,
           italic: true,
           name: true,
           underline: true,
           size: true,
           strikethrough: true,
           subscript: true,
           superscript: true,
           tintAndShade: true
    }
 }

});

if you want to see a complete sample, you can import the following gist into Script Lab

PS: please ignore the @odata.type property, it might be temporary. But the rest of the API is expected to be unchanged when it gets released.

Jin
  • 21
  • 1