As a developer, you definitely need to read or manipulate Excel files at some point.
There are a lot of ways to do this:
- The best known method is to make use of the Excel Interop library.
This method has some big disadvantages if you want to use it in a web development environment:
Excel needs to be installed on your web server and the Excel process needs to run.
Each user who needs Excel interaction on your website needs his own Excel process
and sometimes these processes just don’t close.
Using the Interop library is a good solution for single user Windows applications.
- If you only need data from your Excel file, you can use OLEDB or just download a LINQ to Excel library.
The last one uses OLEDB too, but using LINQ is so much more fun. Off course this system only works for well structured and simple tabular data.
Another problem that pops out is the lack of a 64 bit OLEDB provider for Excel, so you must compile your code for 32 bit machines.
Too bad, you can’t use the full power of that 64 bit processor…
Bringing in count all the disadvantages of the methods above, I started looking for a .NET library which does the job for me.
The library needed to have the following requirements: read values and cell properties (background color,..) of both Excel 2007 and the old Excel format.
There are some paid one’s that will do the job quite well, but I have found 2 open source (and free) libraries that fitted my needs:
- For the old Excel format: NPOI. This CodePlex project is the .NET version of the POI Java project. This is a very mature product which offers a lot of possibilites.
Support for the Open XML format would be a nice feature!
- For the Open XML Excel format I’ve chosen ExcelPackage, which can be found on CodePlex too.
As we know the Open XML format is in fact a ZIP-package which includes separate XML definition files for styles and data.
The standard version of the library only parses the data in the file, but if you look a little bit further, you will find an extended version which parses the styles.xml file too. Pay attention with this because the package includes some XML parsing bugs!
Now you can combine these two libraries, using a common interface, plugging in the right implementation according to the Excel format you have to process.
This is another fine example of the importance of CodePlex.
The Microsoft open source project has a big added value for developers these days!