How to save a simple tabular Excel file from C# using EPPlus

An application I wrote was exporting a couple of different sets of objects as CSV files, but the people using it wanted Excel files. Although Microsoft designed C# and everybody involved assumed that such formats are native to dot Net, they are not.

I used a class library called EPPlus, which seems to be the most popular way of doing this. Unfortunately the examples on their website are not useful, showing things like how to set some arbitrary cell on an existing spread sheet to an image or bar chart. The library also supports functions and all sorts of shit, but really, the only thing I care about is exporting a damn table because the users don’t know how to import CSV into their proprietary system.

All I want is to take a list of objects, that I was exporting to CSV before, and write it to a new XLSX file. I found a solution on StackOverflow here, but here is a more complete implementation. (Assuming that you have an instance of a class called Response. This should be general enough for you to modify it according to whatever your objects and fields are. I’ve simplified this so that it doesn’t look anything like the actual objects I work with.)

The EPPlus Worksheet class has a handy method to load a worksheet from a table, and if you load it in the very first cell of a spread sheet, it gives you what you want. (Why didn’t they document that? There is nothing standard or intuitive about it at all. Like, I’d expect a newly instantiated work sheet to have no cells, and have an empty collection by default, and would never have guessed that I can just load a table into a magically accessed cell A1.) So all I needed to do was create a temporary DataTable instance to hold the data, and then it was easy… I don’t already have the objects in a table, because I’m a couple of levels of abstraction away from that. So this code does everything it needs to do, including creating a temporary in-memory table, a new file to contain the data, and then writes the data to the file.

public void SaveToExcel(List<Response> responses)
{
    string responsePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Responses";

    DataTable table = new DataTable();
    table.Columns.Add("Response", typeof(string));
    table.Columns.Add("ResponseDate", typeof(DateTime));
    table.Columns.Add("CellNumber", typeof(string));

    foreach (var response in responses)
        table.Rows.Add(response.Response, response.ResponseDate, response.CellNumber);

    string filename = DateTime.Now.ToString("yyyy-MM-dd HHmmssfff") + ".xlsx";
    filename = Path.Combine(responsePath, filename);
    Directory.CreateDirectory(responsePath);

    using (var stream = new FileStream(filename, FileMode.Create, FileAccess.Write, FileShare.None, 0x2000, false))
    {
        using (ExcelPackage pck = new ExcelPackage(stream))
        {
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Responses");
            ws.Cells["A1"].LoadFromDataTable(table, true);
            pck.Save();
        }
    }
}
Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Export XLSX file and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s