I have been looking at the best ways in which Lipper can work with Excel in our next-generation application and took a look at ExcelPackage - an Office Open XML file creation library. I was quite impressed with how easy it is to create Excel documents on the fly with our latest financial information and then to delivery that file to the end user via a web service or something.

The nice thing is that you can create exciting looking Excel documents (using Office 2007) on the server with very little coding. ExcelPackage makes use of the .NET Framework 3.0's new System.IO.Packaging namespace in the Office Open XML creation. This does mean that you are unable to create dynamic Office 2003 or earlier files to deliver (tried that as a test myself).

What makes it so fast is that you can make use of a templated Excel file on your server instead of building all the styles of document in code. This then means that you can just deal with just populating the values into the Excel document as needed and the values that you place in specific ranges or cells then can be used to drive other parts of the document - such as charts. An example I quickly put together with dummy data is presented here:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using OfficeOpenXml;

namespace ConsoleApplication1
{
    class Program
    {
           static void Main()
          {
               FileInfo newFile = new FileInfo(@"C:\LipperSelectedAsset.xlsx");
               FileInfo template = new FileInfo(@"C:\AssetOverview.xlsx");

               using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
               {
                     ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
                     worksheet.Cell(1, 2).Value = "ABC Global Select Equity B USD";
                     worksheet.Cell(5, 4).Value = "Equity Fund";
                     worksheet.Cell(1, 2).Comment = "New Fund";
            
                     ExcelWorksheet worksheet2 = xlPackage.Workbook.Worksheets[2];
                     worksheet2.Cell(1, 1).Value = "23";
                     worksheet2.Cell(2, 1).Value = "32";
                     worksheet2.Cell(3, 1).Value = "25";
                     worksheet2.Cell(4, 1).Value = "22";
                     worksheet2.Cell(5, 1).Value = "23";
                     worksheet2.Cell(6, 1).Value = "231";
                     worksheet2.Cell(7, 1).Value = "232";

                     ExcelWorksheet worksheet3 = xlPackage.Workbook.Worksheets[3];
                     worksheet3.Cell(1, 1).Value = "";

                     xlPackage.Save();
                 }
         }
    }
}

In the first line of code - I created an instance of the file that I will be creating on the server. The second line specifies the template that is to be utilized. Then using the ExcelPackage object, you make an association between the new file and the template. Then using the ExcelWorksheet object, you create values on the three default worksheets of the Excel document. It is important to realize what I am doing in the 3rd worksheet of the file. You can see from my bit of code that I am populating a blank value in the first cell and doing nothing more. The problem is that the class library expects a value on each of the three default worksheets otherwise you will get an error.

Give it a try - this is pretty simple.