Office.js:我可以设置二维数组中一系列单元格的填充吗? [英] Office.js: Can I set the fill of a range of cells from a 2d array?

问题描述

到目前为止，对于office.js，我已经习惯了这种模式，在这种模式下，您将数据数组分配给相应的excel范围:

So far, with office.js, I'm getting used to this pattern, where you assign an array of data to a corresponding excel range:

 range.values = [["Eurasia", "29.96", "0.25", "15-Feb" ]];
 range.numberFormat = [[null, null, null, "m/d/yyyy;@"]];

但是，我正在尝试在不同的单元格中使用不同的颜色来更改一系列单元格的背景色，并且我已经拥有一个与每个单元格有关的2d颜色数组.

据我所知，看起来您可以通过逐个循环遍历每个单个单元格来分配填充颜色:

As far as I can tell, it looks like you can assign the fill color by looping over every single cell one by one:

    var rowCount = selectionRange.values.length;
    var columnCount = selectionRange.values[0].length;
    for (var row = 0; row < rowCount; row++) {
        for (var column = 0; column < columnCount; column ++) {
            if (selectionRange.values[row][column] > 50) {
                selectionRange.getCell(row, column)
                    .format.fill.color = "yellow";
            }
        }
    }

或通过将单色分配到整个范围:

Or by assinging a single color to an entire range:

range.format.fill.color = "yellow";  

但是我正在寻找类似的东西:

But I'm looking for something like:

range.fill = [["yellow", "red", "red", "white"]];

似乎有一个类似于您设置value或numberFormat的选项，但我一直找不到.

It seems like there'd be an option similar to how you set values or numberFormat, but I haven't been able to find it.

是否存在，或者我需要一个一个地设置它?

推荐答案

对于格式设置属性(基本上是非数组)，您必须一次设置一个单元格.

For the formatting properties (and basically, non-arrays), you do have to set one cell at a time.

我们在待办事项列表上有一个项目，可以对这些属性进行类似数组的读取和设置，但还没有.我会再次将其提交给团队，但并不是排在最前列.

We have an item on the backlog to allow array-like reading and setting of these properties, but we don't have them yet. I'll bring it up again to the team, but it's not at the forefront of the queue.

取决于数组的大小(本质上，在一个Excel.run调用中要创建多少范围)，可能会有一些性能方面的影响.如果您在那里遇到问题，请告诉我，我可以提供更多信息(不过，我建议为此打开一个单独的线程).

Depending on the size of your array (and essentially, how many ranges you are creating inside of the one Excel.run call), there may be some perf implications. If you run into issues there, let me know, and I can provide more info (I recommend opening a separate thread for it, though).

顺便说一句，在脚本实验室.

这篇关于Office.js:我可以设置二维数组中一系列单元格的填充吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！