Geeks With Blogs

News

  
Bill Evjen's Blog Code, Life and Community

ExcelPackage

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.

Posted on Monday, April 9, 2007 2:24 PM Microsoft , Reviews , HOW-TOs | Back to top


Comments on this post: Server-Side Excel Generation

# ExcelPackage : génération de fichiers Excel 2007 (SpreadsheetML)
Requesting Gravatar...
Ceux qui suivent ce qui se passe sur OpenXML Developer doivent connaitre depuis un bout de temps, mais
Left by CoqBlog on Apr 15, 2007 8:17 AM

# re: Server-Side Excel Generation
Requesting Gravatar...
I trying to do same thing i have added references of windowsbase.dll but still i m getting error "OfficeOpenXml could not found"

Please help me how to resolve it.
Left by sabir on Aug 19, 2008 4:30 AM

# re: Server-Side Excel Generation
Requesting Gravatar...
There is a new free Web Service out there called Web-Sheets that will handle server side excel creation and return you the URL link to your Excel file.

Works really good and simple to setup. You just send the web service your data in a XML format and then it turns that into an excel document server side and kicks back the URL linking the excel file. Has formatting options and supports formulas.

See the link below for examples and more info:

Check it out
http://webreliance.com/ContentPages/Web-Sheets_How_It_Works.aspx
Left by Jack on Apr 02, 2009 8:37 AM

Your comment:
 (will show your gravatar)


Copyright © Bill Evjen | Powered by: GeeksWithBlogs.net