Office.js Excel: Improve performance when writing

I can write 25000 rows x 3 columns of plain text in under .3 seconds which is fantastic.

However, as soon as I add more than plain text (formatting, formulas, cell coloring, conditional formatting, columnWidth adjustments, etc) it takes dramatically longer to finish.

For example, I wrote the same 25000 rows (x 1 column) with range.getCell(i, 0).format.fill.color = 'yellow' and it took 31.223 seconds, even using suspendApiCalculationUntilNextSync().

What can I do to get better write performance when writing rows with formatting to Excel?

回答1:

The are two angles to answer this question from.

First, the longer-term

First, we are looking into making an API [no ETA yet, but it is being actively investigated] that should make it possible to feed a 2D array of formats into a Range (much as you can do with ".values" today, rather than having to individually create ranges and only set one cell at a time). That should make it much faster when you have a rectangular block of cells and want to set a bunch of formats on it at the same time.
If your cells are scattered through the grid, but they are conceptually grouped such that some of them share the same colors/formatting, another forthcoming API will allows you to create multi-area Ranges would also allow you to speed up processing. If memory serves from VBA/VSTO, the sweet-spot is creating a multi-area range object comprised of ~30 individual ranges (e.g., "A1, B7:B9, C11, A4, ..."), and setting their format in one go. This should also help.

Now, to the shorter-term workaround: if you are referencing the CDN and are on a newer-ish build of Office (probably just "current channel" should suffice), you should be able to see a dramatic improvement by disposing ranges that you don't need.

A bit of background first. When you do Excel.run(...), any Ranges that you make use of inside that callback get tracked for the duration of the run, so that they can be adjusted when new rows/columns are added/deleted. This process takes memory, and while it's not perceptable in most cases, it will dramatically slow Excel down if you create 1000s of Ranges. With formatting, if you're individually formatting different cells to be different colors, and if your starting range is sufficiently large, you might well get into the 1000s. Moreover, this includes any ranges you create, even intermediary ones (e.g., if you do var rangeXYZ = originalRange.getCell(0,0).getResizedRange(1,1).getIntersection(somethingElse), not only will you have created the rangeXYZ range, but you will also have created two nameless intermediary ranges, one for originalRange.getCell(0,0), and another for the resized range. These ranges will still persist throughout the whole duration of the Excel.run, even though you'll never access them again! Of course, the good news is that at the end, we will indeed garbage-collect all these uneeded ranges when the Excel.run is done, but your code will still be running slow within the Excel.run, which is exactly what your question is about.

So: as I mentioned, starting in newer builds (and no-op-ing on older ones), you can call .untrack() on the Range to indicate that you won't need it be tracked longer-term. For convenience, the call to ".untrack()" returns the object itself, so that you can chain it as follows:

var rangeXYZ = originalRange
    .getCell(0,0).untrack()
    .getResizedRange(1,1).untrack()
    .getIntersection(somethingElse).untrack();
rangeXYZ.format.fill.color = "yellow"

Note the .untrack() on each of the intermediary ranges, as well as on the "rangeXYZ" itself. Any range marked for untracking will be disposed at an opporunte moment during the processing of context.sync(). You can still keep using the Range before context.sync(), but trying to use it after the context.sync() will throw an error. Essentially, think of untrack() as marking the object for early garbage collection on the next context.sync().

Your mileage will vary, but on the example that I used, the performance improved from 13.7 seconds to 5.7 seconds, i.e., a 2.5x performance gain, when formatting a range of 100x100 cells You can see the code diff here: https://github.com/OfficeDev/office-js-snippets/pull/184/files#diff-4bec6e2366b688602d12011ad3b0f2ef. As you can see, it's very easy to sprinkle in the .untrack() calls, and the improvement can be dramatic.

Best!

~ Michael

回答2:

Try to create a range for as many cells as possible and then call below code once :