2

When using the Excel JavaScript API (ExcelApi1.1) to create a table and add data via a web Excel add-in, using an Excel document in desktop office, the table failed to be created when attempting to add 400k-600k or more rows, returning the error “GeneralException: An internal error has occurred.”

Note: When using an Excel document in online office the failure occurs at 20k – 40k or more rows, returning the error “GeneralException: An internal error has occurred.”

Initially the implementation of the web Excel Add-in was identical to that provided in the tutorial ‘Create an Excel task pane add-in’. But the performance of adding rows (all rows or one at a time) directly to the table was significantly worse. https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/excel-tutorial

Following Microsoft advice on ‘Performance optimization using the Excel JavaScript API’ the implementation was changed to create a range then add this range to a new table, before call context.sync(). https://github.com/OfficeDev/office-js-docs-pr/blob/master/docs/excel/performance.md

The core of the web Excel Add-in is as follows:

Excel.run(function(context) {

// Temporally suspend the auto calcatuion when you try to do large dataset read/wirte operation
if (Office.context.requirements.isSetSupported('ExcelApi', 1.6)) {
    // Only supported in 1.6 onwards
    context.application.suspendApiCalculationUntilNextSync();
}

const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();

var rowCount = dataValueArray.length;
console.log('data.length=' + rowCount);

var range = currentWorksheet.getRange("A2:I" + (rowCount + 1));
range.values = dataValueArray;

const expensesTable = currentWorksheet.tables.add("A1:I" + (rowCount + 1), true /*hasHeaders*/);
expensesTable.name = "ExpensesTable";

expensesTable.getHeaderRowRange().values = [["Timestamp", "NanoSeconds", "OPCServer", "Source", "Severity", "Message", "Condition", "Category", "SequenceNo"]];

var ret = context.sync().then(function() {
    console.log('staticDataAlarms: context.sync() complete:(s)', + (performance.now() - start) / 1000);
});

return ret;
})

A large number of rows cannot be added, our expectations is for Excel to render tables containing at least 1 million rows, similar to using the ‘Get Data’ function.

Tests were performed on the following environments:

Excel - Office 2019 32 bit, Version 1903 (Build 11425.20244)

Excel – Office 2016 32 bit, Version 16.0.4738.1000

Excel – Office online, Build 16.0.11615.35054

Also see related Issue: Performance of Excel JavaScript API (ExcelApi1.1) when creating a table/range with a large data set (+50k rows) is slow (5 seconds per 50k rows)

Trevor McAlister
  • 33
  • 6
  • might need to be set in batches if it is out of memory issue – Slai May 01 '19 at 16:41
  • I’ve tried using a chunk size of 10k rows, and it has occasionally managed to create a table with 600k & 800k rows. But it is only successful about 20% of the time (limited testing though), and when it fails I don’t even get any error massage, it does some of the context.sync() but doesn’t do the final one or render the table. – Trevor McAlister May 02 '19 at 08:47
  • I’ve not yet been able to create a table with 1 million rows, event using chunk sizes of (20k, 10k, 1k 500) Also there is nothing in the ExcelApi documentation indicating array size limits for the range.value function, or any memory limits. So in theory the max row limit of Excel and 64bit process memory limit are the only constraints. – Trevor McAlister May 02 '19 at 08:47
  • there is an `.untrack()` suggestion here https://stackoverflow.com/questions/51735674/office-js-excel-improve-performance-when-writing-rows-with-formatting to free some memory. It is from this issue https://github.com/OfficeDev/office-js/issues/12 – Slai May 02 '19 at 09:35
  • I’ve just tried using the .untrack() on the range when braking it down into chunks of 10k, but it didn’t have any noticeable effect on performance, memory usage, and it still fails to create 800k rows. – Trevor McAlister May 03 '19 at 10:06

0 Answers0