Create Excel from .NET code – new Office Open XML file format

A few days ago I needed to add a feature to some application which let
the user to create an Excel file based on some data that is retrieved from the DB .

The user can sometime create the file on the winforms client app , and sometimes
on server side inside WCF service.

There are few options to perform this kind of task

1. Use OLE Automation . Means – create instance of Excel inside your code  , use it’s API ( which is pretty rich I must admit ) , save the file to some location and you’re done.
   Well.. almost  .The problem with this method is that Excel object for some reason is a “Die Hard” buddy.He sometimes refuses to die and remains in memory for some reason.In other scenarios he present you with headache when you want to use it in multiple threads ( and this scenario happens when you have for example ASP.NET application that creates excel files on the server ).

   And the obvious problem – you must have Excel installed on the running machine.
   If the running machine is the client – then you may say – “Ok. All my client machines have Excel installed” . But what if you need to generate he files on the server ?
   You then have to install Excel on the server – and pay for the license of course.

2. The second option is mostly used on web sites that need to export html tables to Excel.
   What you basically do is to render the web page which contains data ( tables mostly ) ,
   with a special content type which will tells the browser to treat the response as Excel file. Then depending on the browser and client machine configuration – the client can save/open the retrieved data in excel application.

   The main problem with this approach ( besides the fact that it mostly web oriented)
   is that you have almost no control over the Excel file, and you have to figure out how your HTML table will look like in Excel by tweaking the HTML again and again until it looks as something you can live with .You cannot access the excel file in code , go over spreadsheets , cells , check the values , use formulas and etc.

3. A new option we have if we’re using Excel 2007  (and beyond ).
   This option involves using the new Office Open XML file format.What this basically means that you don’t have to have Excel installed , since you
   don’t use the Excel instance in your code . Instead you create a file using the Open Xml API, you save the file , and then you can open it on any machine which have Excel 2007 installed.To create the file you may use the .NET 3.0 System.IO.Packaging API , or use some wrapper for it .
   I’ve used ExcelPackage and in 95% it satisfied my current needs .
   There is a good post on CodeProject about it.
   This library is still not perfect ( read the FAQ on CodePlex page ) , but it does the basic stuff and little beyond just fine .

As I’ve said I’ve taken the third approach  , and it seems as the right choice right now in my specific situation . I cannot tell you this is always the right solution , since as you know there is no such thing as the “right solution” . You always have to test you situation and decide what you need for that .