如何使用Excel JavaScript API office加载项最大程度地提高表行添加50K +行的性能 [英] How to maximise performance for table row addition of 50K+ rows using Excel JavaScript API office Add-IN

问题描述

我正在尝试向表中添加大量行. 我的项目要求添加大表.

I m trying to add large number of rows to table. My Project has requirement for large table addition.

请让我知道是否有更好的选择来最大化性能. 我应该使用Range对象API吗?

Please let me know if there is a better alternative to maximize performance. Should I use Range object API?

代码如下所示.

   function createSampleSheet(numberOfTimes) {

    startTime = performance.now();

    Excel.run(function (context) {
        var sheets = context.workbook.worksheets;
        var sheet = context.workbook.worksheets.getActiveWorksheet();
        var expensesTable = sheet.tables.add("A1:H1", true /*hasHeaders*/);
        expensesTable.name = "ExpensesTable";


     expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Category Type", "Class", "NHID", "Collab ID", "WBID"]];

      expensesTable.rows.add(null /*add rows to the end of the table*/, [
["1/1/2017", "The Phone Company", "Communications", "BCD","Distinction", "3", "45", "1000036"]

            ]);


            for (i = 1; i <= numberOfTimes; i++) {

expensesTable.rows.add(null /*add rows to the end of the table*/, [
               ["1/2/2017", "The Mobile Company", "Corporations", "BSD", "First", "2", "36", "1000026"] ]);}

      return context.sync()
            .then(function () {
                endTime = performance.now();

 log.logOutput(" " + numberOfTimes + " rows++ " + (endTime - startTime) + " milliseconds OR " + ((endTime - startTime) / 1000) + " seconds")

            })
            .then(context.sync);

    }).catch(function (error) {
        console.log("Error: " + error);
        BWUI.errorHandler("Upload tables failed : " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    });

} 

推荐答案

将行添加到尽可能大的块(数组数组)中，而不是一次添加一行，效率会更高.
另外，如果工作簿中有任何公式，则需要处于手动计算"模式(不幸的是，由于某些原因，您无法从Office-JS API设置计算模式")
或尝试使用最接近的等式 suspendedCalculationUntilNextSync()

It will be more efficient to add your rows in as large a block (array of arrays) as possible rather than a single row at a time.
Also if you have any formulas in your workbook you need to be in Manual Calculation mode (unfortunately for some reason you cannot set Calculation Mode from the Office-JS API)
or try to use the nearest equivalent which is suspendCalculationUntilNextSync()

有关更多详细信息，请参见我有关EXcel JS读写性能的博客文章: Excel JS读写性能

See my blog post on EXcel JS Read-Write performance for more details: Excel JS Read-Write Performance

这篇关于如何使用Excel JavaScript API office加载项最大程度地提高表行添加50K +行的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！