1

I am using "@microsoft/office-js": "^1.1.43" within Angular CLI project ("@angular/common": "~10.2.0").

I was using straight forward way ie., dataTable.rows.add(rows); and this was working fine only for up to 10k records, but it was time consuming, but wasn't crashing. For the output with 50k records, addin was just crashing with message saying "Sorry, we had to restart because this add-in wasn't responding.".

Based on this post Office.js Excel: Improve performance when writing rows with formatting, I tried untrack() method.

Using that technique, excel addin now runs and works fine(without crashing) up to records of 57k (around 20 columns). This is the plain output that I get from an API. There is no formatting involved nor are there any excel styling or formulae related chores.

However, there are few reports which would go up to 150k. With same technique as above, I tried to execute 97k rows, unfortunately, addin crashes with the same message that it isn't responding.

I also came across this post: Long operations crash Office addin (JS) which makes use of webworker. However, in my case, I cannot execute Excel.run() inside a webworker.

I was just wondering if there are any updates or I need to try something different so that addin do not crash(it's ok if it takes time, but it shouldn't crash)

Following is the code I currently use:

createTable3() {
    let rows; //filled from API service length ranges from 5k to 150k
    let cols;//filled from API service length ranges from 3 to 25


    Excel.run(async (context) => {
      const sheet = context.workbook.worksheets.add('MiReport');
     
      console.log('Creating table...');
      const sheet = context.workbook.worksheets.getActiveWorksheet();
      sheet.getRange().clear();
      const range = sheet.getRange("A1").getResizedRange(0, cols.length - 1);
      sheet.tables.add(range, false).set({ name: "myTable" });
        const table = sheet.tables.getItem("myTable");
                table.getHeaderRowRange().values = [columns];
                const rangeToWriteTo = table.getDataBodyRange().untrack()
                    .getCell(0, 0).untrack().getResizedRange(rows.length - 1, columns.length - 1).untrack();
                const fullTableRange = table.getRange().untrack().getCell(0, 0).untrack().getBoundingRect(rangeToWriteTo).untrack();
                table.convertToRange();
                rangeToWriteTo.values = rows;
                sheet.tables.add(fullTableRange, true /*hasHeaders*/);

                console.log('Created table...');

  }).catch( er => console.log('Exce.run() error', er));
  }
Deepak
  • 1,038
  • 4
  • 18
  • 31
  • Thanks Deepak, is this issue happen on Excel online or Windows or Mac? I agree with you,it should not crash. team is doing investigation, meanwhile, I would like to share some information for your future reference. As there is payload limitation, so we are not able to send a big request for each context.sync(); therefore we suggest that you could separate the request into several context.sync(). https://learn.microsoft.com/en-us/office/dev/add-ins/concepts/resource-limits-and-performance-optimization#excel-add-ins – Raymond Lu Nov 12 '20 at 08:11
  • Hi Raymond, Yeah, at least it shouldn't crash. I am trying this on installed version of MS excel 365, windows system, with i7 2.2GHz CPU, 16 GB RAM. For time being, based on a suggestion from office-js' github discussion, I am not using a table and instead just creating a range and writing values directly to the sheet. This isn't crashing the add-in now and performance was also so much better compared to that of creating and adding bulk rows to a table. More details: https://github.com/OfficeDev/office-js/issues/12#issuecomment-725951321 – Deepak Nov 12 '20 at 10:32

0 Answers0