2

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 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
  • Rewrite your code so that `context.sync` is not called inside a loop. Look carefully at the patterns in https://learn.microsoft.com/office/dev/add-ins/concepts/correlated-objects-pattern – Rick Kirkham May 09 '23 at 20:39
  • Can you show how you write data? I tend to do all my manipulations in JS then write once to the worksheet. I have a few suggestions, but I'd like to see your `write` statement as it stands. Add as much code in the loop as you can please to show. – FreeSoftwareServers May 18 '23 at 22:53

1 Answers1

1

I see a number of things that can be improved.

  • Don't use Tables, or at least, don't write to tables, if you can. Write to a range, then convert to a table (if needed) at the end
  • Don't call Excel.Run inside each loop.
  • Don't write to the worksheet that is being displayed, switch to another worksheet so you aren't updating the display while writing
  • Don't context.sync until done looping
FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57