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 .

Get description for enums

Did you ever had this need to add descriptions to enums ?

Let’s say you have the following enum :

public enum ProcessResult
{
   CompleteSuccess = 1,
   SuccessButDidNotSentEmail = 2,
   SomeItemsFailed = 3
}

And you want to show the result to the user in some GUI application.
Now – of course you may have a LUT/LOV
table in the DB with descriptions for each value ,and you can retrieve the description from the DB , but sometimes it’s not really needed .

All you have to do is add the “description” attribute to each value like this :

public enum ProcessResult
{
   [Description("Everything completed. No errors occurred")]
   CompleteSuccess = 1,
   [Description("All items were processed , but failed to send the email message")]
   SuccessButDidNotSentEmail = 2,
   [Description("Processing of some items failed")]
   SomeItemsFailed = 3
}

Now to retrieve the description at run-time just use the following code :

private string GetDescription(ProcessResult result)
        {
            FieldInfo field = result.GetType().GetField(result.ToString());

            DescriptionAttribute attribute
                    = Attribute.GetCustomAttribute(field, typeof(DescriptionAttribute))
                        as DescriptionAttribute;

            return attribute == null ? result.ToString() : attribute.Description;
        }

This is also can be very useful when the description you providing is in other language.

First post

Well – this is my first post in this blog.

You are going to see here mainly posts about development and related issues .

And hopefully ideas and proceedings of my study of internet marketing field.