Writing an Excel file in EPPlus

Question

I have been stuck on this for days and despite all of the help out there, none of these solutions have been working for me. What I want to do is create an excel file using the EPPlus library with some basic data in it that I am pulling from a stored procedure. This is the code that I have in my ExportDocument.cs file:

public static ExcelPackage CreateExcelDocument(int [] arr)
{
    String path = @"D:\temp\testsheet3.xlsx";
    //FileInfo newFile = null;
    /*if (!File.Exists(path + "\\testsheet2.xlsx"))
    newFile = new FileInfo(path + "\\testsheet2.xlsx");
    else
        return newFile;*/
    using (ExcelPackage package = new ExcelPackage())
    {
        ExcelWorksheet ws = package.Workbook.Worksheets.Add("testsheet");
        ws.Cells["B1"].Value = "Number of Used Agencies";
        ws.Cells["C1"].Value = "Active Agencies";
        ws.Cells["D1"].Value = "Inactive Agencies";
        ws.Cells["E1"].Value = "Total Hours Volunteered";
        ws.Cells["B1:E1"].Style.Font.Bold = true;

        int x = 2;
        char pos = 'B';
        foreach (object o in arr)
        {
            String str = pos + x.ToString();
            ws.Cells[str].Value = o.ToString();
            if (pos > 'E')
            {
                pos = 'B';
                x++;
            }
            pos++;
        }
        package.Save();
        return package;
    }
}

All the commented code is different things that I have found on the internet to try. Note that this a school organization and we are not using MVC. I am then using the code behind file to pull this method like this:

protected void GenerateReport(Object o, EventArgs e)
{
    Session["reportSession"] = ddReport.SelectedItem.Value.ToString();
    int [] arr = new int [ReportRepository.GetAgencyCounts().Count];
    ReportRepository.GetAgencyCounts().CopyTo(arr, 0);

    ExcelPackage pck = ExportDocument.CreateExcelDocument(arr);
    /*try
    {
        byte [] data = ExportDocument.CreateExcelDocument(arr).GetAsByteArray();
        Response.Clear();
        Response.Buffer = true;
        Response.BinaryWrite(data);
        Response.AddHeader("content-length", data.Length.ToString());
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.Flush();
        Response.Close();
        Response.End();
    }
    catch (Exception ex) { }*/

    /*var stream = new MemoryStream();
    pck.SaveAs(stream);

    String filename = "myfile.xlsx";
    String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var cd = new System.Net.Mime.ContentDisposition
    {
        Inline = false,
        FileName = filename
    };
    Response.AppendHeader("Content-Disposition", cd.ToString());
    stream.Position = 0;

    return File(stream, contentType, filename);*/

    /*Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);
    Response.TransmitFile(Path.GetFullPath(file.Name));
    Response.Flush();
    Response.Close();*/

    /*Response.ClearHeaders();
    Response.BinaryWrite(pck.GetAsByteArray());
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=Sample2.xlsx");
    Response.Flush();
    Response.Close();*/
}

Again note that all the commented code is things that I have found from various sources that have not worked.

So I am not getting any errors but when I click the button on my application to execute the code behind method, nothing is happening. It is loading and it runs through but there are no files created, nothing is opening up. This is the first time I have ever used EPPlus and I am not wholly familiar with exporting things to excel programmatically so I feel lost here.

Are there any suggestions that you guys have? I would be happy to clarify any points that I have not hit upon fully as well.

Answer 1

Have you looked at the samples provided with EPPlus?

This one shows you how to create a file http://epplus.codeplex.com/wikipage?title=ContentSheetExample

This one shows you how to use it to stream back a file http://epplus.codeplex.com/wikipage?title=WebapplicationExample

This is how we use the package to generate a file.

var newFile = new FileInfo(ExportFileName);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{                       
    // do work here                            
    xlPackage.Save();
}

Answer 2

It's best if you worked with DataSets and/or DataTables. Once you have that, ideally straight from your stored procedure with proper column names for headers, you can use the following method:

ws.Cells.LoadFromDataTable(<DATATABLE HERE>, true, OfficeOpenXml.Table.TableStyles.Light8);

.. which will produce a beautiful excelsheet with a nice table!

Now to serve your file, assuming you have an ExcelPackage object as in your code above called pck..

Response.Clear();

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment;filename=" + sFilename);

Response.BinaryWrite(pck.GetAsByteArray());
Response.End();

Answer 3

If you have a collection of objects that you load using stored procedure you can also use LoadFromCollection.

using (ExcelPackage package = new ExcelPackage(file))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");

    worksheet.Cells["A1"].LoadFromCollection(myColl, true, OfficeOpenXml.Table.TableStyles.Medium1);

    package.Save();
}