使用ClosedXML生成XLSX文件报错：格式或扩展名无效求助

技术栈：

• C# .NET Durable Function（Timer Trigger）
• Azure Blob Storage（备份工作簿）
• SendGrid（发送邮件附件）
• ClosedXML（含Report组件）

我遇到的问题：无法生成可正常打开的XLSX格式Excel文件，打开时Excel报错：

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

奇怪的是，XLS格式的文件完全正常——不管是从Azure Blob容器下载，还是通过SendGrid邮件打开都能正常使用，但XLS格式不符合客户需求。

更新模板变量的方法（XLS格式生成正常） #

该方法用于更新Azure存储中的模板变量，生成带指定单元格内容的XLS文件无问题：

public async Task<MemoryStream> UpdateVariables(MemoryStream streamTemplate, List<DealDocumentFlattenedForCsv> data)
{
    var template = new XLTemplate(streamTemplate);
    template.AddVariable("Value1", data[0].Value1);
    template.AddVariable("Value2", data[0].Value2);
    template.AddVariable("Value3", data[0].Value3);
    template.AddVariable("Value4", data[0].Value4);
    template.Generate();
    
    MemoryStream xlsStream = new();
    template.SaveAs(xlsStream);
    await xlsStream.FlushAsync();
    xlsStream.Position = 0;
    return xlsStream;
}

填充Excel表格的方法 #

该方法可成功填充模板中的表格：

public async Task<MemoryStream> GetMemoryStreamForExcelDocument(List<DealDocumentFlattenedForCsv> data, MemoryStream stream, string sheetName, string tableName)
{
    using var workBook = new XLWorkbook(stream);
    
    var table = workBook.Table(tableName);
    table.ReplaceData(data, propagateExtraColumns: true);

    workBook.SaveAs(stream);

    await stream.FlushAsync();

    stream.Position = 0;

    return stream;
}

SendGrid邮件构建代码 #

我尝试过两种Content-Type：

1. application/vnd.ms-excel（对应XLS格式）
2. application/vnd.openxmlformats-officedocument.spreadsheetml.sheet（对应XLSX格式）
   也试过emailMessage.Attachments和email.AddAttachment两种添加附件的方式，代码如下：

private static void BuildEmailMessage(SendGridMessage emailMessage, byte[] csvByteArray)
{
    emailMessage.AddTo(new EmailAddress("email@email.com"));
    emailMessage.AddContent("text/html", "This is the attachment <b>email</b>");

    emailMessage.Attachments = new List<Attachment>
    {
        new Attachment()
        {
            Content = Convert.ToBase64String(csvByteArray),
            Filename = "test.xlsx",
            Type = "application/vnd.ms-excel",
            //Type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            Disposition = "attachment"
        }
    };
    //emailMessage.AddAttachment("test.xlsx", Convert.ToBase64String(csvByteArray));
    emailMessage.From = new EmailAddress("email@email.com");
    emailMessage.SetSubject("test msg");
}

Azure Blob Storage读写方法 #

public MemoryStream ReadFileFromBlobStorage(string fileName, string containerName)
{
    var blobContainerClient = _blobServiceClient.GetBlobContainerClient(containerName);
    var blob = blobContainerClient.GetBlobClient(fileName);

    var memoryStream = new MemoryStream();
    
    blob.DownloadTo(memoryStream);

    return memoryStream;
}

public async Task SaveFileToBlobStorageAsync(string containerName, string fileName, Stream stream)
{
    stream.Position = 0;
    var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
    
    var blobClient = containerClient.GetBlobClient(fileName);

    var blobHttpHeaders = new BlobHttpHeaders()
    {
        ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    };
   
    await blobClient.UploadAsync(stream, blobHttpHeaders, conditions: null);
    stream.Close();
}

下载XLSX文件后打开，会弹出上述格式无效的错误提示。恳请各位帮忙排查问题，谢谢！

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