- MS Excel Primary Interop Assembly (PIA) Based: These require MS Excel be installed and licensed. Not a good option of server environment since it basically spins up Excel.
- OleDB Based: These are very fast a light weight solutions generally that are server friendly. No license or installation of Excel required. I highly recommend the ACE driver instead of JET driver. You can do read, write, update with this option. See OLE DB Article section below for details.
- Other: This means they have coded their own solution typically using some kind of XML format, but sometimes using binary format.
I would love to add other solutions to the list so please let me know your favorite solutions.
|Solution||Requires Excel / Uses PIA||Uses OleDB||Uses |
|Server Friendly||Supports Read||Supports Write||Supports |
|MS Excel Interop (PIA)||Yes||No||No||Yes||Yes||Yes||Yes|
|.NET Framework Data Provider for OLE DB (using ACE OLEDB 12.0)||No||ACE or JET||No||Yes||Yes||Yes||Yes|
|Open XML SDK 2.0 for MS Office||No||No||Yes||Yes||Yes||Yes||No||Yes|
|Excel Data Reader||No||No||?||Yes||Yes||No||Yes||Yes|
for tabular data
|No||ACE & JET||?||Yes||Yes||Yes||Yes||Yes|
|CarlosAg Excel Xml Writer Library – uses old xml spreadsheet format||No||No||No||Yes||No||Yes||No||No (only .xml)|
|GemBox (for small files it is free)||No||No?||?||Yes||Yes||Yes||Yes||Yes|
My thoughts (Feb 2015)If you are doing anything server side such as ASP.NET, I highly suggest staying away from anything that uses the MS Excel Interop (PIA). It is nice it supports .xls format though.
If you need to read data VERY fast OleDB will be WAY faster than any api.
If you need to write many MB of data I suggest you use a product that uses the OpenXmlWriter.
I have used and like ClosedXML alot. It is reasonably fast and easy to use.
I have read about SpreadSheetLight and it looks very easy to program because it is designed to work like you are using Excel. It appears that it uses the OpenXmlWriter which is as fast as you can get. It appears to be very efficient at writing large amounts of data, but less efficient for editing existing sheets. It has excellent documentation. I think this would be my choice if I were to start a new project again and didn't know the OpenXml API.
EPPlus also look good and has like 4 times the downloads of ClosedXML now. It does do Data Validations and pivot tables which is nice. The documentation seems lacking. If you want to be close to OpenXml and be able to interact with it, you may like this product.
Using Excel PIA Articles:Loading and reading the Microsoft Excel file content using C#
How to export database to Excel file
C# Excel Tutorial
OLE DB ArticlesOLE DB can use either JET or ACE. ACE is MUCH better than JET because it doesn’t have the same pitfalls with guessing data that JET does, etc. So when using OLE DB change the connection string to use ACE instead of JET driver for OleDB.
Connection strings for Excel 2007 - Connection Strings for Excel / ACE OLE DB 12.0
How to read from an Excel file using OLEDB
How to insert data to Excel file using OLEDB
How to update data in Excel file using OLEDB
My favorite Option for very large amounts of data is to use the Open XML format
Writing Large Excel File with the Open XML SDK
How to create a stylesheet in Excel Open XML
Other Useful LinksFileHelpers – import/export data from fixed length or delimited records using strongly type values