用C# Open XML生成的Excel在AWS部署后无法打开求助

问题根源分析 #

本地正常但AWS部署后文件损坏，通常由以下原因导致：

• Open XML 对象未显式保存，文档结构不完整
• 内存流处理不当，输出数据不完整
• AWS环境的响应压缩破坏了二进制Excel文件结构
• 响应头设置不规范

具体解决方案 #

1. 显式保存Workbook对象 #

Open XML的Workbook需显式调用Save()确保内容完整写入流，仅依赖Close()在部分AWS环境（如Lambda）可能无法完成完整写入。

在spreadsheetDocument.Close();前添加：

workbookPart.Workbook.Save();

2. 禁用该接口的响应压缩 #

AWS负载均衡或Lambda默认可能开启Gzip压缩，二进制Excel文件被压缩后会损坏。在Action上添加[DisableResponseCompression]特性：

[HttpGet("GenerateExcel")]
[DisableResponseCompression] // 新增此特性
public IActionResult GenerateExcel()
{
    // ... 原有代码
}

若全局配置了响应压缩，也可在Program.cs中排除Excel的MIME类型：

builder.Services.AddResponseCompression(options =>
{
    options.ExcludedMimeTypes = new List<string>
    {
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    };
});

3. 规范Content-Disposition头设置 #

手动添加头可能存在格式问题，使用ContentDispositionHeaderValue生成标准头：

var contentDisposition = new ContentDispositionHeaderValue("attachment")
{
    FileName = "ExcelFile.xlsx"
};
Response.Headers.ContentDisposition = contentDisposition.ToString();

修改后的完整代码 #

using Microsoft.AspNetCore.Mvc;
using Microsoft.Net.Http.Headers;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

[ApiController]
[Route("[controller]")]
public class ExcelController : ControllerBase
{
    [HttpGet("GenerateExcel")]
    [DisableResponseCompression]
    public IActionResult GenerateExcel()
    {
        using var memoryStream = new MemoryStream();
        using (var spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = spreadsheetDocument.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
            var sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Sheet1"
            };
            sheets.Append(sheet);

            // 显式保存Workbook
            workbookPart.Workbook.Save();
            spreadsheetDocument.Close();
        }

        // 规范设置响应头
        var contentDisposition = new ContentDispositionHeaderValue("attachment")
        {
            FileName = "ExcelFile.xlsx"
        };
        Response.Headers.ContentDisposition = contentDisposition.ToString();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        memoryStream.Position = 0;
        return File(memoryStream, Response.ContentType);
    }
}

内容的提问来源于stack exchange，提问作者Lewis