Office.js Word Add-In: Performance Issue with Updating Values in Large Tables

[hengchengfei]

Message from office-js bot: We’re closing this issue because it has been inactive for a long time. We’re doing this to keep the issues list manageable and useful for everyone. If this issue is still relevant for you, please create a new issue. Thank you for your understanding and continued feedback.

• Updating values in large Word tables (larger than 10 by 10) is very slow. Performance gets exponentially worse with table size.
• I'm using myTable.values = arrNewValues. I've also tried myTable.addRows("end", rows, arrNewValues). Where arrNewValues is a 2D array.
• I've also tried using updating via getOoxml() and insertOoxml(), but ran into other issues I haven't been able to resolve, but has good performance.
• Slow performance seems to be caused by "ScreenUpdating" (same issue exists in VBA and is solved via ScreenUpdating=false). I believe it is critically important to add the ability to temporarily turn off ScreenUpdating.
• Is there another way to improve table updating performance?

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop__
• Host [Excel, Word, PowerPoint, etc.]: Word__
• Office version number: Office2016__
• Operating System: Windows 10
• Browser (if using Office on the web): Edge__

Expected behavior

My add-in (https://analysisplace.com/Solutions/Document-Automation) performs document automation (updates content in a variety of Word docs). Many customers want to be able to update text in largish tables. Some documents have dozens of tables (appendices). I have run into the issue where updating these documents is unacceptably slow (well over a minute) due to the table updates.

Update time by table size:

• 2 rows by 10 columns: .33 seconds
• 4 rows by 10 columns: .52 seconds
• 8 rows by 10 columns: 1.5 seconds
• 16 rows by 10 columns: 5.5 seconds
• 32 rows by 10 columns: 20.8 seconds
• 64 rows by 10 columns: 88 seconds

function updateTableCells() {
    Word.run(function (context) {   
        var arrValues = context.document.body.tables.getFirst().load("values");
        return context.sync().then(
            function () {
                var rows = arrValues.values.length;
                var cols = arrValues.values[0].length;
                console.log(getTimeElapsed() + "rows " + rows + "cols " + cols);
                var arrNewValues = [];
                for (var row = 0; row < rows; row++) {
                    arrNewValues[row] = [];
                    for (var col = 0; col < cols; col++) {
                        arrNewValues[row][col] = 'r' + row + ':c' + col;
                    }
                }
                console.log(getTimeElapsed() + 'Before setValues ') ;
                context.document.body.tables.getFirst().values = arrNewValues;
                return context.sync().then(
                    function () {
                        console.log(getTimeElapsed() + "Done");
                });
            });
    })
        .catch(OfficeHelpers.Utilities.log);
}

[grangeryy]

@AbidRahman-MSFT do you have any suggestion on this?

[AbidRahman-MSFT]

Thanks @hengchengfei for reporting the issue here. I can reproduce the same issue on a Mac as well - I will discuss with our engineers and get back to you to see if there is any way we can improve the performance for updating larger tables.

[AbidRahman-MSFT]

Hey @hengchengfei - this is currently a limitation of the API. Please try the following workaround to populate table values using the 1.0 API set, this should greatly improve performance:

    console.log(new Date().getMilliseconds() - start.getMilliseconds() + "ms Before setValues ");
    //table.values = arrNewValues;
    table.select()
    await context.sync();
    Office.context.document.setSelectedDataAsync(arrNewValues, function (asyncResult) {
      if (asyncResult.status == Office.AsyncResultStatus.Succeeded) {
        console.log(new Date().getMilliseconds() - start.getMilliseconds() + "ms Done");
      }
    }); 

[hengchengfei]

@AbidRahman-MSFT Thank you.

I tried but it taked more time.

[4tti]

hello, we are facing very similar issue on desktop version (windows, however I guess it will be similar on Mac) In our case, the performance is depending on the visible area of the page, see the behavior in attached video.

https://user-images.githubusercontent.com/19533927/174286878-8abc5b8d-06de-40bb-91a8-79a8abc69656.mp4

Word version:

Gist: https://gist.github.com/jakuboppelt/039ae39ca0d4a056ca310a9143fbf729

[4tti]

@gergzk Why was this closed? It is real issue (or at least was). @wangyun-microsoft, @grangeryy, @AbidRahman-MSFT

[wangyun-microsoft]

Hi @4tti,

The issue was closed by Bot because it has been inactive for a long time. It did this to keep the issues list manageable and useful for everyone.

Is this still a problem for you?

[4tti]

No problem, just verified it is much faster now. Although there is small delay before it starts it is better than before. Thanks :)

[wangyun-microsoft]

Great to hear that it has improved. If this continues to be a problem, please open a new issue to track the new ask.

Thanks.