Properly Generating Excel Files in .NET

I've seen a lot of new web developers struggle with generating Excel files in their web applications. They often take one of two incorrect approaches.

1. Take a Gridview, render it to HTML, then send that to the client with an Excel MIME type and/or a .xls file extension.
2. Use Office Interop.

The first approach often looks something like this:

protected void ExportBtn_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xls");
    Response.ContentType = "application/vnd.xls";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    ResultGrid.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}

This results in an HTML file being served to the client pretending to be a file that contains Excel data. But it isn't. So when opening this file in Microsoft Office on a desktop computer, it will display a warning that the file may be corrupted. The user can click past the warning, but that's not very user friendly or professional.

Another downside to this approach is that any consuming application that doesn't know how to handle .xls files that contain HTML will report the file is corrupted and refuse to display anything at all, even if the application knows how to open HTML files with a .html extension. Often this approach is taken by someone who asks themselves "How can I export a GridView to Excel?" Which is not a good way of thinking about what you're trying to accomplish. Most of the time, the goal is to export the underlying data to Excel, not the GridView.

The Office Interop approach is even worse. Office Interop was not meant to be used on servers. It will cause plenty of strange errors. And the Office Interop libraries aren't friendly to work with. Don't use it. That's all I'm going to say about that approach.

The correct approach to providing Excel files to your user is to use a library capable of generating Open Office XML Spreadsheet (.xlsx) files natively. There's several libraries for this, such as Open Office XML SDK from Microsoft, NPOI (NuGet), or EPPlus (NuGet).

My favorite is EPPlus. Here's an example of creating a real .xlsx file from data in a DataTable.

protected void ExportBtn_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var package = new ExcelPackage(); //create a new package, this is the equivalent of an XLSX file.
    var sheet = package.Workbook.Worksheets.Add("My Data"); //Add a new sheet to the workbook
    var dt = GetDataTable(); //This retrieves a System.Data.DataTable, you'd likely get it from your database.
    sheet.Cells["A1"].LoadFromDataTable(dt, true); //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    Response.BinaryWrite(package.GetAsByteArray());
    Response.End();
}

You might be thinking "Okay, that got the data in there. But my Export GridView to HTML approach also included some styling info!". Which is true. Let's say we want to use an Excel table style.

protected void ExportBtn_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var package = new ExcelPackage(); //create a new package, this is the equivalent of an XLSX file.
    var sheet = package.Workbook.Worksheets.Add("My Data"); //Add a new sheet to the workbook
    var dt = GetDataTable(); //This retrieves a System.Data.DataTable, you'd likely get it from your database.
    sheet.Cells["A1"].LoadFromDataTable(dt, true); //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    var range = sheet.Cells[1, 1, dt.Rows.Count + 1, 2]; //We're getting a reference to all the cells that contain data
    var table = sheet.Tables.Add(range, "My Data Table"); //Creating an Excel table
    table.TableStyle = TableStyles.Medium8; //Setting the table style to something that looks nice
    range.AutoFitColumns(); //Auto fitting the column widths.
    Response.BinaryWrite(package.GetAsByteArray());
    Response.End();
}

Also, commonly among Web Forms developers (and less commonly among MVC developers), people generate their file directly in some code behind button event handler. You can take advantage of strongly typed models instead, and gain a bit more control over how your Excel file is generated.

public static ExcelPackage GenerateExcelFileForProducts(IEnumerable<Product> products)
{
    var package = new ExcelPackage();
    var sheet = package.Workbook.Worksheets.Add("Products");
    int rownum = 1;
    sheet.Cells["A" + rownum].Value = "Id";
    sheet.Cells["B" + rownum].Value = "Name";
    sheet.Cells["C" + rownum].Value = "Description";
    sheet.Cells["D" + rownum].Value = "Price";            
    foreach (var p in products)
        {
        rownum++;
        sheet.Cells["A" + rownum].Value = p.Id;
        sheet.Cells["B" + rownum].Value = p.Name;
        sheet.Cells["C" + rownum].Value = p.Description;
        sheet.Cells["D" + rownum].Value = p.Price;
        }
    var range = sheet.Cells[1, 1, rownum, 4];
    var table = sheet.Tables.Add(range, "Products Table");
    table.TableStyle = TableStyles.Medium8;
    range.AutoFitColumns();
    return package;
}

Some people don't like .xlsx files, because they believe that they're incompatible with Office 2003 and Office XP. That's partially true. Originally they did not support the Open Office XML formats. However, an available service pack provides support for the formats. You really have no excuse to be using .xls files anymore.

---