在Office JavaScript API中用承诺(ctx.sync)编写循环的最佳方法 [英] Best way to write loops with promises (ctx.sync) in JavaScript API for Office

问题描述

有许多线程讨论有关如何保证循环中promise的执行顺序.我想知道Office加载项的JavaScript API的最佳实践是什么.在大多数情况下，有问题的承诺是ctx.sync().

There are many threads that discuss about guaranteeing execution order of promises in loops. I would like to know what is the best practice in JavaScript API for Office Add-ins. Most of the time, the promise in question is ctx.sync().

这是一个片段，用于一张一张地打印Excel范围列表的地址.测试表明，它很好地遵守了Excel范围的顺序.但是问题是是否以及如何保证执行顺序?

Here is a snippet to print the address of a list of Excel ranges one by one. The test shows that it respects well the order of Excel ranges. But the question is whether and how to guarantee the execution order?

function loadAll () {
   var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
   var sheet = "Sheet1";
   for (var i = 0; i < ranges.length; i++) {
       loadRange(ranges[i], sheet);        
   }
}

function loadRange (range, sheet) {
   Excel.run(function (ctx) { 
       var r = ctx.workbook.worksheets.getItem(sheet).getRange(range);
       r.load('address');
       return ctx.sync().then(function() {
          console.log(r.address);
       });
   });
}

有人可以帮忙吗?

推荐答案

由于Excel.run返回一个Promise，因此可以将其与.then链接起来并保证顺序.即

Because Excel.run returns a Promise, you can chain it with a .then and guarantee order. I.e.,

Excel.run(function(ctx) { ... return ctx.sync(); ... })
    .then(function() {
        return Excel.run(function(ctx) { ... return ctx.sync(); ... })
    })
    .then(function() {
        return Excel.run(function(ctx) { ... return ctx.sync(); ... })
    });

话虽如此...这将是相当低效的.更好的方法是分批加载所有需要的对象，仅创建一个网络往返(对于Excel Online尤其重要，但是在桌面上也很明显):

That being said... this would be pretty dang inefficient. A much better approach would be to load all the objects you need in one batch, creating only one network roundtrip (especially important with Excel Online... but noticeable even on the Desktop):

function loadAll () {
    Excel.run(function(ctx) {
        var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
        var sheet = "Sheet1";

        var loadedRanges = [];
        for (var i = 0; i < ranges.length; i++) {
            var r = ctx.workbook.worksheets.getItem(sheet).getRange(ranges[i]);
            r.load('address');
            loadedRange.push(r);
        }

        return ctx.sync()
            .then(function() {
                for (var i = 0; i < loadedRanges.length; i++) {
                    console.log(loadedRanges[i].address);
                }
            });
    });
}

更新

如果根据评论，您最终确实需要执行彼此依赖的单独任务，并且每个任务都需要往返，因此确实需要通过链接Excel.run进行排序，那么我建议采取以下措施:

If, as per comment, you do end up needing to do separate tasks that depend on each other and that each require a roundtrip, and hence do need to be sequenced via chaining Excel.run, I would recommend something as follows:

function loadAll () {
    var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
    var sheet = "Sheet1";

    // Create a starter promise object
    var promise = new OfficeExtension.Promise(function(resolve, reject) { resolve (null); });

    for (var i = 0; i < ranges.length; i++) {
        // Create a closure over i, since it's used inside a function that won't be immediately executed.
        (function(i) {
            // Chain the promise by appending to it:
            promise = promise.then(function() {
                return loadRange(ranges[i], sheet);
            })
        })(i);       
    }
}

function loadRange (range, sheet) {
    return Excel.run(function (ctx) { 
        var r = ctx.workbook.worksheets.getItem(sheet).getRange(range);
        r.load('address');
        return ctx.sync().then(function() {
            console.log(r.address);
        });
    });
}

〜MSFT Office可扩展性团队的开发人员Michael Zlatkovsky

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

这篇关于在Office JavaScript API中用承诺(ctx.sync)编写循环的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！