Generating an excel file with EPPlus is failing

Question

When I try to generate an Excel file using EPPlus, Excel give me the following error message:

Excel cannot open the file 'myfilename.xlsx' because the file format or file extension is not valid. Verify the the file has not been corrupted and that the file extension matches the format of the file.

Here's my code:

public ActionResult Index()
{
    using (ExcelPackage package = new ExcelPackage())
    {
        // I populate the worksheet here.  I'm 90% sure this is fine
        // because the stream file size changes based on what I pass to it.

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.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());
        return File(stream, contentType, fileName);
    }
}

Any idea what I'm doing wrong?

The error says `myfilename.xslx` while your code shows `myfilename.xlsx`. Which one are you really using? — M.Babcock, Mar 07 '12 at 20:29
Answered [yesterday](http://stackoverflow.com/a/9574414/284240). I assume that this is the same issue. — Tim Schmelter, Mar 07 '12 at 20:44
I found this code worked when I set the stream position to 0 and I found it cleaner than the accepted solution. — stimms, Jun 10 '12 at 17:47

score 31 · Accepted Answer · answered Sep 03 '12 at 18:57

All you need to do is reset the stream position. stream.Position = 0;

You shouldn't write directly to the Response, it's not the MVC way. It doesn't follow the correct MVC pipeline and it tightly couples your controller action code to the Response object.

When you add a file name as the 3rd parameter in File(), MVC automatically adds the correct Content-Disposition header... so you shouldn't need to add it manually.

The short of it is, this is what you want:

public ActionResult Index()
{
    using (ExcelPackage package = new ExcelPackage())
    {
        // I populate the worksheet here.  I'm 90% sure this is fine
        // because the stream file size changes based on what I pass to it.

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

So question about this, I am using a similar method although I am currently returning a FileStreamResponse instead of a file object. The problem I am running into though is that if the user makes more than one request while the first file is being build, they all seem to get locked up and the saveas method never completes. Have you ran into this with EPPlus? — Shawn, Feb 11 '14 at 02:39
Can yo post the ajax success method that handles this return? — Christian Doulos, Feb 24 '21 at 21:04

score 10 · Answer 2 · answered Mar 07 '12 at 20:37

10

Your code doesn't show stream being written to the HttpResponse - presumably being done in the File method which you haven't posted.

One way that does work is the following:

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader(
            "content-disposition", String.Format(CultureInfo.InvariantCulture, "attachment; filename={0}", fileName));
Response.BinaryWrite(package.GetAsByteArray());
Response.End();

answered Mar 07 '12 at 20:37

Joe

122,218
32
205
338

The `File` method is part of `ASP.Net MVC`. I'll try this way! – Matt Grande Mar 07 '12 at 20:52
Strange, it now says "Excel found unreadable content in 'myfilename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." If I click Yes, it seems to work, but I have no idea what's giving that error. – Matt Grande Mar 07 '12 at 20:59
I have seen that error message before, IIRC it happened when Response.Clear() was omitted. Another possibility is that an EPPlus bug results in invalid data in the file. You can test which it is by saving the file on the server, and seeing if you get the same error when opening it on the server. – Joe Mar 07 '12 at 21:31
Also when using the `File` method, you may need to first position your MemoryStream to the start of the stream (`stream.Position = 0`). – Joe Mar 07 '12 at 21:35
Ahh, the "Excel found unreadable content" was my fault. I was trying to set a width on Column 0. – Matt Grande Mar 07 '12 at 21:41
-1 this isn't the way you should do it... it's not the MVC way and it tightly couples your code to the response object. **All you need to do is reset the stream position**. – Charlino Sep 03 '12 at 19:02

score 2 · Answer 3 · answered Feb 11 '14 at 07:55

Similar to Joe's answer, I still had to call Response.ClearHeaders():

   protected void btnDownload_Click(object sender, EventArgs e)
   {

       ExcelPackage pck = new ExcelPackage();
       var ws = pck.Workbook.Worksheets.Add("Sample2");

       ws.Cells["A1"].Value = "Sample 2";
       ws.Cells["A1"].Style.Font.Bold = true;
       var shape = ws.Drawings.AddShape("Shape1", eShapeStyle.Rect);
       shape.SetPosition(50, 200);
       shape.SetSize(200, 100);
       shape.Text = "Sample 2 outputs the sheet using the Response.BinaryWrite method";
       Response.Clear();    
       Response.ClearHeaders();
       Response.BinaryWrite(pck.GetAsByteArray());
       Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
       Response.AddHeader("content-disposition", "attachment;  filename=Sample2.xlsx");
       Response.End();
  }

Generating an excel file with EPPlus is failing

3 Answers3

Linked