在 JavaScript API for Office 中使用承诺 (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);
        });
    });
}

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

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

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