Excel Add-in Row Index and Values are returned as undefined

Question

I am trying to remove some rows in a table via Excel add-in. The code that I am using is below:

            var table = ctx.workbook.tables.getItem('TableName');

            if (Office.context.requirements.isSetSupported('ExcelApi', 1.2) === true) {
                table.clearFilters();
            }

            var tableRows = table.rows.load('items');

            ctx.sync().then(function () {
                for (var i = (tableRows.count - 1); i >= 0; i -= 1) {
                    var row = tableRows.getItemAt(tableRows.items[i].index);
                    row.delete();
                }
            });

This works fine in Excel online including Internet Explorer 11. Also it works with version 1601 (Build 6741.2088) and later. However, it doesn't work with version 1509 (Build 4266.1001). In this version I get the values and indexes of row items as undefined. How can I resolve this issue?

Michael Zlatkovsky · Accepted Answer · 2017-04-26 23:40:28Z

1

I am not sure what difference the build numbers would make -- but I will say that the TableRow API is the one API that is brittle (and we're looking into how to fix it). Essentially, when you do a getItemAt, you're creating a reference based on the point-in-time index, and this index does not get adjusted when new rows are added or deleted.

In other words, for deletion/insertion, I would treat it like you need to invalidate the table rows collection, and/or would use Range objects instead. Again, note that this statement applies only to the TableRow/TableRowCollection objects -- other collections, like the worksheet collection, adjust correctly.

answered Apr 26, 2017 at 23:40

Michael Zlatkovsky

8,7301 gold badge35 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Add-in Row Index and Values are returned as undefined

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related