Thursday, December 04, 2014

Programatically Reordering Excel Columns

My task is to reorder the columns in an Excel spreadsheet.  The files are generally quite large (75-100K rows, up to 200 columns).

My first inclination was, as usual, EPPlus.  It took a bit of doing, but I got it working.  Only it was slow.  Really slow.  It will stream properly and not overload memory, but too slow.

After reading this post that mentioned the benefits of using Excel automation, I thought I'd give that a try.  I was amazed.  Even though I'm not actually saving the resulting file until after I've copy/pasted all columns of data in, I never see the memory spike.  There's some really good memory management in there.  Yes, having a dependency on a COM object isn't optimal, but when we're talking Excel, I think it's acceptable.  It freakin rocks, and the code is very simple.

Anyway, I'm including both here (if you have a need for this and your files aren't large, you might want to use EPPlus).

In either case the call is like:
EPPlus version:

Helpers:
Main Class:
Excel Automation version: