Using ExcelPacke to create Excel sheets on server

Posted by DigiMortal on ASP.net Weblogs See other posts from ASP.net Weblogs or by DigiMortal
Published on Sun, 30 May 2010 10:29:35 GMT Indexed on 2010/05/30 10:32 UTC
Read the original article Hit count: 616

Filed under:

In one of my community projects I needed to output some listings as Excel file. As installing Excel to server is non-sense that I was easily able to avoid I found simple solution for Excel 2007 files – open-source project called ExcelPackage. In this posting I will show you hot to create simple event attendees report in Excel 2007 format using ExcelPackage.

Cautions

Although ExcelPackage works well for me here are some things you should be aware of.

ExcelPackage needs file system access because compression library it uses is designed so.
There is only very old source code available and it is published under GPL. So if you are writing application to your customers then you cannot use this library unless you make your whole application open-source.
ExcelPackage has also some technical problems and it is not very easy to use in simple cases. Authors have not provided any new releases since the beginning of 2007 so I have good reason to consider this project as abandoned.
You may find the extensive package EPPlus also useful as there are new versions coming over time. EPPlus is also published under GPL (because ExcelPackage is under GPL), so you can use it only on very limited manner.

If you don’t afraid some s*itfight with technology and GPL is okay for your system then let’s go on.

Exporting event attendees list to Excel

Suppose we have list with event attendees and we want to export it to Excel. We are behaving normally and we don’t install Excel desktop software to our web server. Here is the code.

void ExportToExcel(Event evt)
{
    var fileInfo = new FileInfo(Path.GetTempPath() + "\\" +  
                                DateTime.Now.Ticks + ".xlsx");
 
    using (var xls = new ExcelPackage(fileInfo))
    {
        var sheet = xls.Workbook.Worksheets.Add(evt.Title);
 
        sheet.Cell(1, 1).Value = "First name";
        sheet.Cell(1, 2).Value = "Last name";
        sheet.Cell(1, 3).Value = "E-mail";
        sheet.Cell(1, 4).Value = "Phone";
        sheet.Cell(1, 5).Value = "Registered";
        sheet.Cell(1, 6).Value = "Live Meeting";
 
        var i = 1;
        foreach(var attendee in evt.Attendees)
        {
            i++;
 
            var profile = attendee.Profile;
            sheet.Cell(i, 1).Value = profile.FirstName;
            sheet.Cell(i, 2).Value = profile.LastName;
            sheet.Cell(i, 3).Value = profile.Email;
            sheet.Cell(i, 4).Value = profile.Phone;
            sheet.Cell(i, 5).Value = att.Created.ToString();
            sheet.Cell(i, 6).Value = att.LiveMeeting.ToString();
        }
 
        xls.Save();  
    }
 
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats";
    Response.AddHeader("Content-Disposition",       
                       "attachment; filename=" + fileInfo.Name);
    Response.WriteFile(fileInfo.FullName);
    Response.Flush();
 
    if (fileInfo.Exists)
        fileInfo.Delete();      
}

And here is the result.

Event attendees list

Although it is possible to make this list more effective and nice it works and users can start using it until all the nice bells and whistles are coming.

Conclusion

After some fighting with technology it was not very hard to get nice Excel 2007 sheets coming out from our server. We used ExcelPackage library to create list of event attendees and our event organizers can now simply download data to Excel if they need to contact with attendees or manage their data using Excel tools.

Developer IT