office excel addin reloads/hangs/not responcive when there is more around 5000 data points to print using range value

Question

i am developing office addin using officejs. i am using the range value property to show the value on excel as shown in example here https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/range.md#property-access-examples

some detail about my code. i have an ajax call which gives me at one point only 5000 data points and i convert that into a format required for range.values property and assigned to it and then call context sync, i noticed that my addins hangs or show reload addin on excel client on desktop machine, it works fine in chrome, how to best handle this case when there is more data point to write. after one ajax call end i will have another call which will also give 5000 points

my typescript code look like this.

public addData(rangeAddress: string, header: any[][], rows: any[][]): JQueryPromise<string> {
            let me = this;
            let defer = $.Deferred();

            Excel.run(function (ctx: Excel.RequestContext) {
                let addressParts = rangeAddress.split("!");
                let oldRange: Excel.Range = ctx.workbook.worksheets.getItem(addressParts[0]).getRange(rangeAddress);
                oldRange.unmerge();
                oldRange.format.autofitColumns();
                oldRange.format.autofitRows();
                oldRange.clear();

                return ctx.sync().then(function () {
                    let startCell: Excel.Range = oldRange.getCell(0, 0), headerBoundingRange: Excel.Range, dataRangeStartCell: Excel.Range,
                        completeDataBoundingRange: Excel.Range, headerLength = 0, dataRangeEndCell: Excel.Range;

                    if (header.length > 0 && header[0].length > 0) {
                        headerLength = header[0].length;
                        let headerEndCell = startCell.getOffsetRange(0, header[0].length - 1);
                        headerBoundingRange = startCell.getBoundingRect(headerEndCell);
                        dataRangeStartCell = startCell.getOffsetRange(1, 0);
                        headerBoundingRange.load(["address", "columnCount"]);
                        completeDataBoundingRange = headerBoundingRange;

                    } else {
                        dataRangeStartCell = startCell;
                        completeDataBoundingRange = startCell;
                    }

                    dataRangeEndCell = dataRangeStartCell.getOffsetRange(rows.length > 0 ? rows.length - 1 : 0,
                        rows.length > 0 ? rows[0].length - 1 : 0);

                    let dataBoundingRange = dataRangeStartCell.getBoundingRect(dataRangeEndCell);
                    dataBoundingRange.load(["address", "rowCount", "columnCount"]);
                    completeDataBoundingRange = completeDataBoundingRange.getBoundingRect(dataBoundingRange);
                    completeDataBoundingRange.load("address");
                    return ctx.sync().then(function () {
                        dataBoundingRange.values = rows;

                        if (headerLength > 0) {
                            headerBoundingRange.values = [header[0]];
                        }

                        me.formatTable(headerBoundingRange, dataBoundingRange, header.length === 2 ? header[1] : [],
                            Utility.tagDataTableName);

                        defer.resolve(completeDataBoundingRange.address);
                    });
                });
            }).catch(function (error: any) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
                defer.reject();
            });

            return defer.promise();
        }

GCSDC GCSDC · Accepted Answer · 2016-09-15T17:10:41

You may find useful information on performance, limits and optimization here: Resource limits and performance optimization for Office Add-ins (specially on section Design and implementation techniques). This shall help you solving the issue.

Also, it is worth to mention that on Windows, Office Add-ins will use Internet Explorer components for loading and hosting the add-in, and so, for a more specific test and comparison, it would be interesting to test the add-in using Office Online on Internet Explorer, rather than Chrome.

Also, if you're using Windows 10, you may find it helpful to debug your add-in on office desktop, as explained on Debug add-ins using F12 developer tools on Windows 10.

office excel addin reloads/hangs/not responcive when there is more around 5000 data points to print using range value

1 Answers