Excel - Slow performance when hiding rows

[Danwakeem]

Summary

I am trying to hide a lot of disjoint rows in an Excel spreadsheet and it takes a while to hide all of the rows. (It is totally possible there is a better way to hide all of these I am just not sure what that would be.)

Expected Behavior

I wouldn't have expected it to take so long or maybe I would expect a way to group a set of ranges into one action?

Current Behavior

I loop through a list of rows I need to hide and wait for it to finish. Maybe if there were a way to specify a set of ranges to hide all at once?

Steps to Reproduce, or Live Example

I created a gist with an example for an actual set of hidden rows my application attempted to hide.

The part in question is this bit of code here

return Excel.run(function (ctx) {

  var worksheet = ctx.workbook.worksheets.getActiveWorksheet();
  _.forEach(hiddenRows, function (rowData) {
    var range = worksheet.getRange(rowData.range);
    range.rowHidden = true;
  });

  return ctx.sync()
  .then(function () {
  }).catch(function (err) {
});

Context

My application is trying to pull some accounting data from a oracle database into a spreadsheet for our accounting team. We are trying to auto hide some of the data so they can immediately see the important parts right when the data loads. You can see by the sample data there are a lot of records being inserted into the spreadsheet so hiding some of the data is very important for us.

Also the data is ordered by groupings so combining these all into one hidden section probably isn't an option.

Environment

• PC desktop, Mac, Office Online: We have accountants that use this application on a wide variety of devices
• Excel
• Excel 2016 1705
• MacOS, Windows 10, Windows 7
• IE 11, Chrome, Safari, Firefox, Edge

Useful logs

If you run the gist I provided as an Excel Office addin then it will tell you how long a set of hide operations took. On average it is about 2 minutes and 45 seconds.

[sameera]

Just a thought, would adding an extra "priority" column and applying data filter instead of hiding be a workaround in this case?

[Danwakeem]

Yeah I could give that a shot. Currently I am not using a table I am just inserting the data directly into the excel spreadsheet rows so I will probably have to switch to using a table for that to work right @sameera?

[Danwakeem]

Wow, I really appreciate the suggestion on this @sameera. Using tables with a data filter rather than the raw excel rows is about 10,000 times easier than trying to this the way I was doing this before. Thank you!

[sameera]

Glad it helped!