As planned and promised here is send post of export to excel series…..
| # | Method / Technology | Pros | Cons |
| 1 | OLEDB | 1. Simple as Writing SQL queries
2. Free
3. Supports XLS (JET OLEDB 4.0) and XLSX ( ACE 12.0)
4. Can Create Multiple Sheets | 1. Length of title can only be up to 64 characters
2. To insert title as first row need extra columns also to maintain the structure
3. Does not support any styling / formatting of data
4. Cannot delete sheet with OLEDB
5. Sum how JET OLEDB does not work on our servers |
| 2 | EPPPlus / Office Open XML http://epplus.codeplex.com/ | 1. Open source / GPLv2
2. Supports insertion of Rows and columns just as done in excel by mouse
3. support margin of cells
4. supports styles / lookups / Data (Cell) Format etc. ( have not tried extensively)
5. Can Create Multiple Sheets | 1.does not support xls format i.e. File created via this will not work in |
| 3 | CSV | 1. Simple as writing text | 1. Cannot create multiple sheets
2. Cannot control styles / format
3. Writing / formatting row by row, cell by cell can take more time. |
| 4 | VSTO / Office.Interop | 1. Complete programming support to Excel Via C#
2. Huge documentation / support available
3. Integrated support to Visual studio | 1. Requires Interop DLL on server , this is shipped with MS office
2. Yet to explore |
