RangeFormat.columnWidth and RangeFormat.autofitColumns() for Officejs API doesn't work

Question

I'm having an issue with columnWidth not actually setting and showing in Excel 2016. Below is where my Excel.run function is:

        _columns = columns;
        var headers = [];
        var numCols = _columns.length;
        var letter = columnToLetter(numCols); //for perspective, this becomes "P"
        for (var i = 0; i < numCols; i++) {
            var header = [];
            header.push(_columns[i].header);
            headers.push(header); //basically an array of arrays, where each entry is ["some string"]
        }

        var ranges = [];
        Office.context.document.bindings.addFromNamedItemAsync(['Sheet1!A', letter].join(':'), 'table', { id: 'table' });

        Excel.run(function (context) {

            var sheet = context.workbook.worksheets.getItem("Sheet1");
            for (var i = 0; i < numCols; i++) {


                var range = sheet.getCell(0, i);
                range.load(['values', 'text', 'format/columnWidth']);
                ranges.push(range);

            }

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

                for (var i = 0; i < numCols; i++) {
                    ranges[i].values = [headers[i]]; //now each range, which is essentially one cell, has one value in values. values = [["some string"]]
                    headerFormatFn(ranges[i]);
                    context.sync();
                }



            })
            .catch(function (err) { console.error(err); })
        });

and the actual formatting happens in the headerFormatFn(ranges[i]):

    function headerFormatFn(cell) {


        cell.format.font.bold = true;
        cell.format.borders.getItem(1).style = 'Continuous';
        cell.format.columnWidth = 80;

    }

I've checked support for ExcelApi in my Excel 2016 and i know autofitColumns() isn't supported so I gather that's why the function doesn't work. I'll need to update Excel 2016 for that. However, columnWidth is a property that is supported in ExcelApi 1.1 and so it is supported in my version of Excel 2016.

It's odd because format.font.bold and format.borders works just fine. It appears that just setting the columnWidth is not working. See below for a view of the resulting worksheet:

Resulting Excel 2016 Worksheet

I can't tell if this has something to do with context, or if maybe we're setting up our Excel Add-in wrong. Any help would be greatly appreciated, and if any more information is needed let me know.

-------UPDATE 1-------

In response to answer by @Michael, I made these changes:

I changed my Excel.run to:

        Excel.run(function (context) {

            var sheet = context.workbook.worksheets.getItem("Sheet1");
            for (var i = 0; i < numCols; i++) {


                var range = sheet.getCell(0, i);
                range.load(['values', 'text', 'format/*']);
                ranges.push(range);

            }
            for (var i = 0; i < numCols; i++) {

                ranges[i].values = [headers[i]];
                headerFormatFn(ranges[i]);
            }
            return context.sync()
            .catch(function (err) { console.error(err); })
        });

and i'm using border.getItemAt(0).style = 'Continuous' now:

        try {
            console.log("1");
            cell.format.font.bold = true;
            console.log("2");
            cell.format.borders.getItemAt(0).style = 'Continuous';
            console.log("3");
            cell.format.columnWidth = 80;
        }
        catch(error)
        {
            console.log(error);
        }

The logs of 1, 2, and 3 show that the loop completes then gives an OfficeExtensionError of InvalidArgument after the loop on the context.sync() call.

Michael Zlatkovsky - Microsoft Michael Zlatkovsky - Microsoft · Accepted Answer · 2016-10-25T16:23:12

If I had to guess, I think it's the cell.format.borders.getItem(1).style line that is causing the issue. I don't think you can do a getItem(1), as 1 is not a valid key. You instead want something like InsideHorizontal, as in:

range.format.borders.getItem('InsideHorizontal').style = 'Continuous';

When something in a batch fails, no further queued-up changes get executed, which is why the columnWidth line is not triggering.

That being said, I think the real issue with the code is that you're silently swallowing errors and also "breaking the promise chain". The offending code is:

            for (var i = 0; i < numCols; i++) {
                ...
                context.sync();
            }

Two problems with this:

You are doing a context.sync() in each for loop. You are almost certainly not intending to do this (and this will fail after you have ~50 columns, as Excel can only handle X many in-parallel requests). What you want is to do the sync only once, beneath the for loop.
You are not doing a "return" of context.sync(). This means that for all intents and purposes, the Excel.run no longer awaits on context.sync, but instead finishes what it's doing and moves on. The context.sync() at that point becomes a separate workstream that is spawned off, but is not tracked by the parent. Which means that the catch on the parent isn't able to catch any issues that arise during that child sync.

I am in the process of creating a site/book to take some of the mystery out of Office.js and its internal workings. I can post here when I have the topic ready to view (I have one on "not breaking the promise chain" in particular).

RangeFormat.columnWidth and RangeFormat.autofitColumns() for Officejs API doesn't work

1 Answers