C# 使用 EPPlus 生成 excel 文件失败

Generating an excel file with EPPlus is failing

提问by Matt Grande

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

当我尝试使用 EPPlus 生成 Excel 文件时，Excel 给我以下错误消息：

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.

Excel 无法打开文件“myfilename.xlsx”，因为文件格式或文件扩展名无效。验证文件未损坏并且文件扩展名与文件格式匹配。

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?

知道我做错了什么吗？

采纳答案by Charlino

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

您需要做的就是重置流位置。 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.

您不应该直接写入 Response，这不是 MVC 方式。它不遵循正确的 MVC 管道，并且将您的控制器操作代码与 Response 对象紧密耦合。

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

当您在 中添加文件名作为第三个参数时File()，MVC 会自动添加正确的Content-Disposition标头...因此您不需要手动添加它。

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);
    }
}

回答by Joe

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

您的代码没有显示stream正在写入HttpResponse- 大概是在File您尚未发布的方法中完成的。

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();

回答by Ewald Stieger

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

类似于乔的回答，我仍然不得不打电话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();
  }