Tip to make AdjustToContents() a lot faster.

May 13, 2011 at 6:39 PM

Here's a tip to make the AdjustToContents() a lot faster. Let's say you have a sheet with 9999 rows. If you do ws.Columns().AdjustToContents() it has to calculate the width of all 9999 rows to determine the right width. That takes a long time. Chances are pretty good that the data in the first 99 rows is not that different than the data in the remaining 9900 rows. So by calling ws.Columns().AdjustToContents(1,99) you could save a significant amount of time. In my case I saved 20 seconds of real-time processing by only looking at the first 75 rows out of 9761.

This is not a bug. In fact, I saw this recommendation related to Excel itself. I would assume that since AdjustToContents() with no arguments is supposed to check all the rows, then that's what it should do. However, when writing an application, you can make it run a lot faster by calling AdjustToContents() and specifying that it only examine a small number of rows rather than the entire file.

P.S. I added this tip as a comment on the documentation page.