1

I have developed an Excel Javascript plugin using the React framework.

The add-in workflow is simple.

  1. Request data from Ui using APIs
  2. Passing entities' column names and authorized API keys
  3. Start writing data into an active Excel sheet by pulling the button.

Problem:

During the Excel data writing, the whole Excel workbook freezes, and I'm unable to use any other workbook until the data is written.

The maximum amount of data coming from APIs is 5-8K.

Below is the sample code.

try {
  var i = 0;
  // these data are coming from APIs
  var allCompanies =[12,32,33,43,45,66,12,32,10,12,21,90];
  allCompanies.forEach(async _company => {
    // calling APIs for each _docs and writing data for that companies
    await fetchData(_company).then(async (responseRows) => {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.load(["name"]);
        await context.sync();
        for (let i = 0; i < responseRows.length; i++) {
          // data operations into Exel sheet
          // creating table, table header, applying Css
          // writting value to each cells
        }
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
        await context.sync();
      });
    });
  });
} catch (error) {
  console.log("error on appending data into excel " + error);
}

Any solution will be helpful to me.

Thanks.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Kishan Vaishnani
  • 234
  • 1
  • 12

1 Answers1

2

If possible, move context.sync() out of the loop. Calling context.sync() multiple times within a loop can result in decreased performance and increased latency.

Avoid using the context.sync method in loops

Bingo
  • 141
  • 5
  • If I move context.sync() out of the loop then whatever data I dump into excel will not saw immediately in excel sheet. So that is the reason why I need to sync sheet for each companyId – Kishan Vaishnani May 09 '23 at 06:51
  • You usually have to split the loop into two loops, with a context.sync in between them. See the article that @Bingo linked to. – Rick Kirkham May 09 '23 at 20:36
  • Hi, @RickKirkham I have pushed the array into one variable and removed the loop. but it creating issues with writing 7k rows at the same time. is cause any problem if we add rows 7k at a time? – Kishan Vaishnani May 11 '23 at 05:22
  • 1
    Excel cannot read more than 5 MB of data in a given call of context.sync. – Bingo May 11 '23 at 22:59