CODEWITHSUNDEEP
javascriptexceloffice365office-js
JayChase
JayChase

Reputation: 11525

Adding rows to table very slow in Excel online

I have a JavaScript import add-in in which I am adding rows to a table of 20 columns using the TableBinding.addRowsAsync function. In the desktop client this works very well when adding a large number of rows but in Excel online the performance it terrible. For 100 rows the average time to add is 1500ms. For 300 it goes up to around 2100ms. Here is the actual code from the performance test:

//vm.binding is a tableBinding
//vm.testData is an array of twenty string values
vm.binding.addRowsAsync(vm.testData, function (result) {
   //...
    });

I have a requirement to support importing a large number of rows (avg 20000). At the moment this import is being added in pages of 100 to keep the add-in from becoming unresponsive after 5 seconds but this takes a long time to complete.

Is there anything I am missing which I can use to improve the in browser performance?

Upvotes: 0

Views: 525

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

I am following up on why adding rows to the table binding using that API is slow. Stay tuned (though also realize that any bug fix would not be instantaneous...)

One thing you can try -- I'm not sure whether it'll be faster or not -- is to use our host-specific Excel APIs outlined here: https://github.com/OfficeDev/office-js-docs/tree/master/excel

In your case, the code would be something like:

Excel.run(function (ctx) { 
      var expenseTable = ctx.workbook.tables.getItem("ExpenseTable");
      expenseTable.rows.add(null,[['Rent','$600','Housing']]);
      expenseTable.rows.add(null, [["Movie Club", "$75", "Entertainment"]]);
      expenseTable.rows.add(null, [["Food", "$450", "Food"]]);
      expenseTable.rows.add(null, [["Car", "$150", "Transportation"]]);
      expenseTable.rows.add(null, [["Tuition", "$800", "School costs"]]);
      expenseTable.rows.add(null, [["Books", "$150", "School costs"]]);
      expenseTable.rows.add(null, [["Gift", "$100", "Other"]]);
      expenseTable.rows.add(null, [["Loan", "$250", "Loans/Payments"]]);

    return ctx.sync();
})
.catch(function (error) {
        console.log(JSON.stringify(error));
});

The APIs only work in Excel Online or Excel 2016 (not 2013). If that's a problem for you, though, you can still use your current approach on the desktop, and use the new one (assuming it's faster) online. Just do

if (Office.context.requirements.isSetSupported("ExcelApi")) {
    Excel.run(...)
} else {
    bindings.addRowsAsync(...)
}

Hope this helps!

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Upvotes: 2

Related Questions