office-js - Office.js Excel : Improve performance when writing rows with formatting

Question

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

但是，一旦我添加的不仅仅是纯文本(格式、公式、单元格着色、条件格式、列宽调整等)，它需要更长的时间才能完成。

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

将带有格式的行写入 Excel 时，如何才能获得更好的写入性能？

Answer 1

从两个角度来回答这个问题。

一、长期

首先，我们正在研究制作一个 API [还没有 ETA，但正在积极调查中] 应该可以将二维格式数组输入到一个范围中(就像你今天可以用“.values”做的那样，而不是而不是必须单独创建范围并且一次只设置一个单元格)。当您有一个矩形单元格块并希望同时在其上设置一堆格式时，这应该会使其更快。

如果您的单元格分散在网格中，但它们在概念上分组，以便其中一些共享相同的颜色/格式，另一个即将推出的 API 将允许您创建多区域范围，也将允许您加快处理速度。如果内存来自 VBA/VSTO，最佳点是创建一个包含约 30 个单独范围(例如，“A1、B7:B9、C11、A4、...”)的多区域范围对象，并设置它们的格式一气呵成。这也应该有所帮助。

现在，到 短期解决方法 :如果您正在引用 CDN 并且使用的是较新版本的 Office(可能只是“当前 channel ”就足够了)，您应该能够通过 看到显着的改进。处理不需要的范围 .

首先介绍一下背景。当您这样做时 Excel.run(...) ，在 run 的持续时间内，您在该回调中使用的任何范围都会被跟踪。 ，以便在添加/删除新行/列时可以调整它们。此过程会占用内存，虽然在大多数情况下无法察觉，但如果您创建 1000 个范围，它会显着降低 Excel 的速度。对于格式化，如果您将不同的单元格单独格式化为不同的颜色，并且如果您的起始范围足够大，则很可能会进入 1000 秒。此外，这包括您创建的任何范围，甚至中间范围(例如，如果您执行 var rangeXYZ = originalRange.getCell(0,0).getResizedRange(1,1).getIntersection(somethingElse) ，您不仅会创建 rangeXYZ 范围，而且您还将创建两个无名的中间范围，一个用于 originalRange.getCell(0,0) , 另一个用于调整大小的范围。这些范围在 Excel.run 的整个持续时间内仍然存在，即使您永远不会再次访问它们!当然，好消息是最后，我们确实会垃圾-当 Excel.run 完成时收集所有这些不需要的范围，但您的代码在 Excel.run 内仍然运行缓慢，这正是您的问题所在。

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

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

请注意 .untrack()在每个中间范围以及“rangeXYZ”本身上。在处理 context.sync() 的过程中，任何标记为取消跟踪的范围都将适时处理。 .您仍然可以在 context.sync() 之前继续使用 Range ，但试图在 context.sync() 之后使用它会抛出错误。基本上，想想 untrack()作为在下一个 context.sync() 上标记早期垃圾收集的对象.

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

最好的事物!

~迈克尔