Office.js Excel:使用格式编写行时提高性能 [英] Office.js Excel: Improve performance when writing rows with formatting

问题描述

我可以在0.3秒内写出25000行x 3列纯文本，这太棒了.

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

但是，一旦我添加的内容超过纯文本(格式，公式，单元格颜色，条件格式，columnWidth调整等)，则需要花费更长的时间才能完成.

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.

例如，我使用range.getCell(i, 0).format.fill.color = 'yellow'编写了相同的25000行(x 1列)，即使使用suspendApiCalculationUntilNextSync()，也花费了31.223秒.

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().

在将行格式化为Excel时如何做才能获得更好的写入性能?

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

推荐答案

从两个角度回答这个问题.

The are two angles to answer this question from.

首先，长期

1. 首先，我们正在研究制作一个API(尚无ETA，但正在积极研究它的 )，该API应该可以将2D格式的数组输入Range(与您现在可以使用".values"，而不必分别创建范围并一次只设置一个单元格.如果您有一个矩形的单元格块并且想要同时在其上设置一堆格式，那应该会更快.
2. 如果您的单元格分散在网格中，但是在概念上将它们分组，以使其中一些共享相同的颜色/格式，那么另一个即将发布的API将允许您创建多区域范围，还可以加快处理速度.如果内存是由VBA/VSTO提供的，则最佳点将创建一个包含约30个单独范围(例如，"A1，B7:B9，C11，A4，...")的多区域范围对象，并设置其格式一气呵成.这也应该有所帮助.

1. 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.
2. 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.

现在，使用短期解决方法:如果您引用的是CDN，并且使用的是较新版本的Office(可能只需当前频道"就足够了)，那么您应该能够通过配置不需要的范围来看到巨大的进步.

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.

首先要有一些背景知识.当您执行Excel.run(...)时，会在run的持续时间内跟踪您在该回调中使用的任何范围，以便在添加/删除新行/列时可以对其进行调整.此过程会占用内存，虽然在大多数情况下是不容易察觉的，但是如果您创建1000个Ranges，则会大大减慢Excel的运行速度.使用格式化时，如果您要分别将不同的单元格格式化为不同的颜色，并且起始范围足够大，则很可能会达到1000.此外，这包括您创建的 any 范围，甚至包括中间范围(例如，如果您执行var rangeXYZ = originalRange.getCell(0,0).getResizedRange(1,1).getIntersection(somethingElse)，不仅将创建rangeXYZ范围，而且还将创建两个无名的中间域范围，一个用于originalRange.getCell(0,0)，另一个用于调整大小的范围，即使您再也不会访问它们，这些范围仍将在Excel.run的整个持续时间内持续存在！ >最后，在Excel.run完成后，我们的确会垃圾收集所有这些未使用的范围，但是您的代码在Excel.run内仍将运行缓慢，这正是您所要解决的问题.

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.

所以:正如我提到的，从较新的版本开始(对较旧的版本不进行操作)，您可以在Range上调用.untrack()，以表明您不需要长期跟踪它.为了方便起见，对".untrack()"的调用将返回对象本身，以便您可以按以下方式进行链接:

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"

请注意每个中间范围以及"rangeXYZ"本身上的.untrack().在context.sync()的处理过程中，将在适当的时候处理标记为未跟踪的任何范围.您仍然可以继续使用范围之前 context.sync()，但是尝试在context.sync()之后使用它会引发错误.本质上，将untrack()标记为下一个context.sync()上的早期垃圾回收对象.

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().

您的行驶里程会有所不同，但是在我使用的示例中，在格式化100x100单元格范围时，性能从13.7秒提高到5.7秒，即性能提高了2.5倍.您可以在此处看到代码差异: https://github.com/OfficeDev/office-js -snippets/pull/184/files#diff-4bec6e2366b688602d12011ad3b0f2ef .如您所见，将.untrack()调用非常容易，并且改善非常显着.

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.

最好！

〜迈克尔

这篇关于Office.js Excel:使用格式编写行时提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！