Project Description
Library for working Excel automation which ensures the automation object is released. Includes some ready to use functionality.

Working with large Excel files is problematic. After reading an article by Joel Spolsky which advocated using Excel to work with Excel files because nobody else has put in everything necessary to optimize the experience like the real thing, I decided to try it out. It turns out that the Excel automation object is difficult to work with, but does some amazing things. It was able to copy/paste columns of data in huge files (200K rows) around fast while not spiking memory at all. The same operation using EPPlus brought the server down.

Relative to running on the server, there are some caveats, but I believe if you test a process and it works, it will be fine. You can't run Excel automation under a service and do just anything.

This has never actually been in production, but has gone through a fair amount of testing - the main concern always being whether it's leaving the automation object behind (it seems to clean up consistently).

There is the obvious dependency on Excel being installed on the machine where executing.

You can use this as your wrapper for the automation object and do anything Excel does, but unfortunately I can't expose that directly, e.g., I can't expose a function that provides you with a live automation application object that will be closed when finished because you can't expose a COM object across library bounds. In order to build your own functionality you will have to descend your own class from ExcelApplicationBase, but built in are these functions:
  • ColumnReorder (as mentioned above): new ColumnReorderer().Reorder( newPath, <string list of columns the way you want ordered> )
  • Sort: new Sorter().SortByColumn( columnIndex, path )
  • CSV to XLSX: new CSVtoXLSXConverter().CSVto_XLSX( path )
  • XLSX to CSV: new XLSXtoCSVConverter().XLSXto_CSV( path )

Last edited Nov 23, 2015 at 2:40 PM by bradwood, version 2