Use ExcelPackage to Manipulate Open XML Excel Files : CSharp Feeds

Use ExcelPackage to Manipulate Open XML Excel Files

by Keyvan Nayyeri via Keyvan Nayyeri on 12/6/2008 8:50:37 PM

Office document formats are one of the most common file types among users, especially Word and Excel files that play a very important role in today’s usage of software. I think you rarely can find someone who doesn’t use one of these formats in his daily life.

The importance of these document types has had its impact on software development, and it’s been a common task to deal with such documents in different types of desktop, web, or embedded device applications.

By the way, here I want to focus on Excel files. You would know that working with Excel documents is an easy task especially for desktop applications because a rich set of APIs are provided by Office for developers; however, when it comes to online scenarios, there are some difficulties especially in the area of deployment and installation of your web applications on production. To resolve the problems in this field, many .NET component vendors have built their own libraries to work with Word and Excel files on the server without installing anything onto the server. One of these components is Aspose.Cells for .NET and Java.

Fortunately Excel 2007 and its fundamental usage of Open XML has simplified the process of working with Excel documents to a great extent but still there are some difficulties for online scenarios.

Yesterday I ran into a situation where I had to read data from an Excel file on the server. Looking for the best possible solution, I found an open source component hosted on CodePlex called ExcelPackage which is actually a wrapper around the .NET 3.0 System.IO.Packaging API to simplify the process of working with Excel 2007 files. This component, that is provided as a DLL file, can be registered to GAC to provide a rich set of APIs to work with Open XML data.

Here I just want to give a quick overview of this component, but you can find good information about this component from its documentation file and also from this article on OpenXML Developer community.

The first step before starting using the component is, registering the assembly in the GAC. You can download the binary package which comes with a BAT file to automate the registration, however, it’s built for VS 2005 and you need to run your own command for VS 2008. By the way, I had some difficulties to add a reference to my registered assembly in VS 2008, so finally I added that manually.

Having the reference, you can start developing applications that apply this library. Here I want to write two sample scenarios in which I read the content of an existing Excel file, and build another file on fly respectively.

In the first sample I load the content of an Excel file in my ASP.NET application and bind a GridView to this control. Here is the Excel file that I want to use that contains a list of people along with their blog URLs.

People Worksheet

First I define a Person class to represent each person and make my job easier.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace ExcelPackageSample.Code
{
    public class Person
    {
        public string Name { get; set; }
 
        public Uri Url { get; set; }
    }
}

Now I create a page that contains a GridView to display the list of people.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" 
Inherits="ExcelPackageSample._Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ExcelPackage Sample</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gridPeople" runat="server" AutoGenerateColumns="False" BackColor="White"
            BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black"
            GridLines="Vertical">
            <RowStyle BackColor="#F7F7DE" />
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name">
                    <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                </asp:BoundField>
                <asp:HyperLinkField DataNavigateUrlFields="Url" DataNavigateUrlFormatString="{0}"
                    DataTextField="Url" HeaderText="URL" />
            </Columns>
            <FooterStyle BackColor="#CCCC99" />
            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
            <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

In the main step I write my logic code to load the data from Excel file and bind my grid to the data.

private void LoadPeople()
{
    var peopleList = new List<Person>();
 
    var file = new FileInfo(Server.MapPath("~/files/people.xlsx"));
 
    using (var excelPackage = new ExcelPackage(file))
    {
        var worksheet = excelPackage.Workbook.Worksheets[1];
 
        for (var i = 2; i < 10; i++)
        {
            if (!string.IsNullOrEmpty(worksheet.Cell(i, 1).Value))
            {
                var person = new Person();
                person.Name = worksheet.Cell(i, 1).Value;
                person.Url = new Uri(worksheet.Cell(i, 2).Value);
 
                peopleList.Add(person);
            }
        }
    }
 
    this.gridPeople.DataSource = peopleList;
    this.gridPeople.DataBind();
}

Thank to ExcelPackage, this is a pretty simple and readable code. I pass the information for my Excel file to the ExcelPackage class and load its first worksheet. Note that in ExcelPackage API are indexes start from 1 and are completely compatible with Excel itself. Having the worksheet in hand, I can iterate over all the cells and load their data to add Person objects to my list. ExcelPackage returns the content of a cell as string data and you may need to cast them to your desire type.

Running the code, I get the output that I expected to get.

ExcelPackage Sample

But in the second sample I want to create an Excel file on fly and to do this, I add a button control to my page that generates an Excel file which contains a list of a few cities in Kurdistan.

protected void btnGenerate_Click(object sender, EventArgs e)
{
    var file = new FileInfo(Server.MapPath("~/files/generated.xlsx"));
 
    using (ExcelPackage excelPackage = new ExcelPackage(file))
    {
        var worksheet = excelPackage.Workbook.Worksheets.Add("Kurdistan");
 
        worksheet.Column(1).Width = 20;
 
        worksheet.Cell(1, 1).Value = "Kermanshah";
        worksheet.Cell(2, 1).Value = "Sanandaj";
        worksheet.Cell(3, 1).Value = "Hawraman";
        worksheet.Cell(4, 1).Value = "Paveh";
 
        excelPackage.Workbook.Properties.Title = "Some Cities in Kurdistan";
        excelPackage.Workbook.Properties.Author = "Keyvan Nayyeri";
 
        excelPackage.Save();
    }
}

As you see, the main structure of the code is constant and relies on the creation of an instance of the ExcelPackage object, and then adding a new worksheet to the collection. After that, I have set the width of my first column and the values of my cells which is followed by setting some properties of the Excel file, and finally storing the changes.

Result