2

We are working on an excel workbook(~ 13 MBs in size) having 15 sheets. We are pasting data on a single sheet and all the other sheets have a moderate amount of formulas linked to this data dump sheet. We need to paste approx. 10,000 rows and 30 columns in chunks of 2500 rows each time. We are syncing the context of the workbook using context.sync() after every 4th iteration (i.e. after pasting 10,000 rows). This whole process is taking approx. 70 seconds in total. We have tried using context.sync() after pasting every 2500 rows but that slows down the process even more.

await Excel.run(async function main(context) {
var totalrowcount = result.totalrows;
iterations = Math.ceil(totalrowcount / 2500);
for (i = 1; i <= iterations; i++) {
    let names = context.workbook.names;
    var rng;
    data_arr = result.data;
    data_arr.splice(0, 1);

    var rowOffset = (i - 1) * 2500;
    rng = names.getItem(rangeName).getRange().getOffsetRange(1 + rowOffset, 3);
    rng.getAbsoluteResizedRange(data_arr.length, data_arr[0].length).values = data_arr;
    rng.untrack();

    if(i % 4 == 0){
        await context.sync();
    }
    else if(i == iterations){
        await context.sync();
    }
}});
Muskan Jain
  • 21
  • 1
  • Does the 70 seconds include the time to download the `result` or only the time to paste it to Excel? How long does it take to import the same amount of data manually? – Rick Kirkham Jun 21 '21 at 18:14
  • @RickKirkham The 70 seconds only includes the time to paste data to excel workbook via office.js. This time usually varies between 60-70 seconds depending on the internet speed. And it takes approx. 23-25 seconds to paste same amount of data manually in the data dump sheet. As the number of iterations increases, despite of the fact that chunk size is same, the time to paste chunks in excel keeps on reducing. – Muskan Jain Jun 22 '21 at 05:33
  • what if you do it manually from UI, how long will it take? could you try to application.calculationMode = "Manual" , and change it back after the paste is finished. – Raymond Lu Jun 22 '21 at 06:59
  • @RaymondLu We are already doing this, we are setting the calculation mode to Manual first and after pasting the data, we are again changing it to Automatic. – Muskan Jain Jun 22 '21 at 11:15

1 Answers1

0

have you also try to add suspendScreenUpdatingUntilNextSync pauses visual updates to Excel until the add-in calls context.sync(), or until Excel.run ends (implicitly calling context.sync) like https://learn.microsoft.com/en-us/office/dev/add-ins/excel/performance? As we cannot repro the issue locally, so if you can share session Id to let us take a look the detail log that would be helpful. Thanks.

ginger jiang
  • 69
  • 6