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 XML | 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 | No | Yes | Yes | No | Yes | Yes |
| ExcelMapper for tabular data | No | ACE & JET | No | Yes | Yes | Yes | Yes | Yes |
| CarlosAg Excel Xml Writer Library – uses old xml spreadsheet format | No | No | Yes | Yes | No | Yes | No | No (only .xml) |
| GemBox (for small files it is free) | No | No? | No? | Yes | Yes | Yes | Yes | Yes |
| Koogra | No | No | No | Yes? | Yes | No? | Yes? | Yes |
| MyXLS | No | Yes | No | Yes | Yes | Yes | Yes | No |
| ExtremeML | No | No | Yes | Yes | Yes | Yes | No? | Yes |
| ClosedXML | No | No | Yes | Yes | Yes | Yes | No | Yes |
Using Excel PIA Articles:
Loading and reading the Microsoft Excel file content using C#
How to export database to Excel file
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 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
SpreadsheetLight – Not free, but no hassle license and it a reasonable price and looks very simple to use.
Posts
0 comments:
Post a Comment