Get individual cell formats in Excel using office.js?

Question

I'm just starting to look at the new office js API with regard to converting existing Excel add-ins to use this new tech.

I can easily get an array of values from an entire range by queuing a single load on the context, but there doesn't seem to be an equivalent way to get cell formatting. Unless all cells in the range are formatted identically, the value returned for the range is 'undefined'.

The solution I've come up with is to queue a load operation on each individual cell in the range. For example, this function gets the fill color for each cell in a range:

function readFormats() {
    Excel.run(function (ctx) {
        var cells = [];

        //First get the size of the range for use in the loop below
        var myRange = ctx.workbook.getSelectedRange().load(["rowCount", "columnCount"]);

        return ctx.sync()
        .then(function () {
            //Loop though every cell and queue a load on the context for fill colour
            for (var r = 0; r < myRange.rowCount; ++r)
                for (var c = 0; c < myRange.columnCount; ++c)
                    cells.push(myRange.getCell(r, c).load("format/fill"));
        })
        .then(ctx.sync)
        .then(function () {
            //Do something useful with the fill color of cells in array here
        })
    })
    .then(function () {
        console.log("Formats done");
    })
    .catch(function (error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    });
}

This code works as expected, but is extremely slow. For example, a 10,000 cell range takes approximately 12 seconds and a 20k cell range somewhere around 45 seconds to run. When I tried it on a range containing 50k cells, my async callback was never called at all.

Is there a better and more efficient way to do this?

Answer 1

There currently isn't a better way, but we do have it on our backlog to expose cell-level properties. I will be sure to share your question with the team.