Thursday, August 25, 2011

Free Options for reading and writing Excel Files

In general there are three main categories of tools that allow you to interact with Excel files.
  • 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 have not tested many of these solutions other than using PIA or OLE DB. The exception to that is CarlosAg Excel Xml Writer Library which I do like, but it is only for Writing Excel files, not reading them. It has good performance and works well for tabular and non-tabular spreadsheets.
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
OpenXmlWriter
Server Friendly Supports Read Supports Write Supports
.XLS
Supports
.XLSX
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
ExcelMapper
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
Koogra No No ? Yes? Yes No? Yes? Yes
MyXLS No Yes ? Yes Yes Yes Yes No
ExtremeML No No No Yes Yes Yes No? Yes
ClosedXML No No No Yes Yes Yes No Yes
SpreadSheetLight No No Yes Yes Yes Yes No Yes
EPPlus No No ? Yes Yes Yes No 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 Articles

OLE 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 Links

FileHelpers – import/export data from fixed length or delimited records using strongly type values
Web Spreadsheet
OpenXMLDeveloper.org

1 comment:

Mike said...

I have to say that I really like this post as I get to about these interesting and commonly used shortcut methods. I applied them immediately. Before this I was using method which long and take too much time for performing the same task.

electronic signature software