在office.js中为excel设置条件格式

I need a way to set conditional formatting in an office.js add-in.

我需要一种在office.js加载项中设置条件格式的方法。

Currently thinking I might be able to use VBA to set it based on the cell value that the add-in sets - but it would be really nice to be able to do this directly from within office.js.

目前我认为我可以使用VBA根据加载项设置的单元格值来设置它 - 但是能够直接从office.js中执行此操作会非常好。

I am looking for some creative workarounds until Microsoft implements this natively within the add-in.

我正在寻找一些创造性的解决方法,直到Microsoft在加载项中本地实现这一点。

3 个解决方案

#1

Based on your (and many other developers' feedback), we're announcing the preview of Excel API 1.6 at Build 2017 right now, which includes support for conditional formatting. Office 365 Insider builds with support for Excel API 1.6 will be available later this month.

基于您(以及许多其他开发人员的反馈),我们现在宣布在Build 2017上预览Excel API 1.6,其中包括对条件格式的支持。支持Excel API 1.6的Office 365 Insider版本将于本月晚些时候推出。

I built a snippet that shows you the basics of those APIs, which you can look at in our new Script lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in, then choose "import" in the navigation menu and import the following gist: https://gist.github.com/TristanD-MSFT/80920d57bf587bd859ff62afb2b7c673

我构建了一个片段,向您展示了这些API的基础知识,您可以在我们的新脚本实验室(https://aka.ms/getscriptlab)中查看。只需安装Script Lab加载项,然后在导航菜单中选择“导入”并导入以下要点:https://gist.github.com/TristanD-MSFT/80920d57bf587bd859ff62afb2b7c673

#2

At this point in time we do not have support for setting conditional formatting via Office.js yet, but we will consider adding support for this for further updates we are making to the API.

目前我们还没有支持通过Office.js设置条件格式,但我们会考虑添加对此的支持,以便我们对API进行进一步的更新。

Thanks, Philip, Software Engineer on the Office Extensibility team

谢谢,Philip,Office可扩展性团队的软件工程师

#3

As Philip said, conditional formatting is not available out of the box (as of ExcelApi 1.2). As far as a creative workaround goes, depending on the size of your range, you could do something like this.

正如菲利普所说,条件格式不是开箱即用的(从ExcelApi 1.2开始)。就创意解决方法而言,根据您的范围大小,您可以执行类似的操作。

The basic idea is to create a binding over the desired range. Then add a data change listener and re-apply the formatting every time that a change has occurred.

基本思想是在所需范围内创建绑定。然后添加数据更改侦听器,并在每次发生更改时重新应用格式。

To create the binding, something as simple as this will do:

要创建绑定,只需要这样做:

Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Matrix, {id:'MyBinding'});

Now comes the interesting part, the eventing:

现在是有趣的部分,即事件:

var binding;

Office.context.document.bindings.getByIdAsync('MyBinding', function(result) {
    if (result.status === "succeeded") {
        binding = result.value;
        binding.addHandlerAsync(Office.EventType.BindingDataChanged, formatData);
    } else {
        console.log(result.error.message);
    }
});

function formatData() {
    // First, disable the binding that was previously created, or else
    // setting formatting will also trigger data changed event.
    binding.removeHandlerAsync(Office.EventType.BindingDataChanged);

    Excel.run(function(ctx) {
        var range = ctx.workbook.bindings.getItem('MyBinding').getRange();
        range.load("values");
        return ctx.sync().then(function() {
            for (var row = 0; row < range.values.length; row++) {
                for (var col = 0; col < range.values[0].length; col++) {
                    var cellValue = range.values[row][col];
                    var color;
                    if (cellValue < 30) {
                        color = "red";
                    } else if (cellValue < 65) {
                        color = "orange"; 
                    } else {
                        color = "green";
                    }
                    range.getCell(row, col).format.fill.color = color;
                }
            }
        })
    })
    .catch(function(error) {
        console.log(error);
    })
    .then(function() {
        // Re-enable the event:
        binding.addHandlerAsync(Office.EventType.BindingDataChanged, formatData);
    });
}

Hope this helps as a temporary workaround!

希望这有助于作为临时解决方案!

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

~Michael Zlatkovsky,Office Extensibility团队的开发人员,MSFT

#1

#2