.Net WCF导出EPPlus生成的Excel遇乱码及工作表名异常问题

问题现象 #

通过WCF服务流式输出EPPlus生成的Excel文件时，下载的文件充满PK�UU�H��3这类乱码字符，且工作表名称不是代码中设置的Summary，而是Content-Disposition头里的文件名；但将生成的文件直接保存到本地目录时，文件可读、数据正确，工作表名称也符合预期。

WCF服务契约 #

[WebInvoke(UriTemplate = "getengagementfeedfinalizedata?id={workspaceId}&s={startDate}&e={endDate}", Method = "POST", BodyStyle = WebMessageBodyStyle.Bare, ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json)]
Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data);

服务方法实现 #

public Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data)
{
    try
    {
        string contentType = "application/vnd.ms-excel; charset=utf-8;";
        string extension = "xls";
        string fileName = "report-" + DateTime.Now.Ticks.ToString();
        string contentDisposition = string.Format("attachment; filename={0}.{1}", fileName, extension);

        WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
        WebOperationContext.Current.OutgoingResponse.Headers.Set("Content-Disposition", contentDisposition);
        
        // 业务逻辑：从数据库获取数据并填充到feedFinalizeDataTable

        feedFinalizeDataTable.TableName = "Summary";

        DataSet dataSet = new DataSet();
        dataSet.Tables.Add(feedFinalizeDataTable);

        using (ExcelPackage excelPackage = new ExcelPackage())
        {
            foreach (DataTable dt in dataSet.Tables)
            {
                ExcelWorksheet sheet = excelPackage.Workbook.Worksheets.Add(dt.TableName);
                sheet.Cells["A1"].LoadFromDataTable(dt, true);

                var cellFont = sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Font;
                cellFont.Bold = true;
            }

            excelPackage.Save();
            var bin = excelPackage.GetAsByteArray();

            return new MemoryStream(bin);
        }
    }
    catch (Exception ex)
    {
        Logger.Instance.Write(LogLevel.ERROR, "DashboardService.GetEngagementFeedFinalizeData", ex);
        throw;
    }
}

已尝试操作 #

• 修改Content-Type为octet-stream并移除charset=utf-8，问题未解决
• 本地保存文件验证生成逻辑正常，代码如下：

using (ExcelPackage excel = new ExcelPackage())
{
    foreach (DataTable dt in dataset.Tables)
    {
        ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(dt.TableName);
        sheet.Cells["A1"].LoadFromDataTable(dt, true);

        var cellFont = sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Font;
        cellFont.Bold = true;
    }

    var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory);
    var filePath = path + "\\" + "New.xls";
    
    excel.SaveAs(new System.IO.FileInfo(filePath));
}

解决方案 #

1. 修正Content-Type和文件扩展名 #

EPPlus生成的是Open XML格式的Excel（.xlsx），而非旧版二进制格式的.xls，需调整配置：

• 将contentType改为application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
• 将extension改为xlsx

2. 移除ResponseFormat配置 #

契约中ResponseFormat = WebMessageFormat.Json会强制WCF将返回的二进制流按JSON格式处理，导致编码错误，需删除该配置：

[WebInvoke(UriTemplate = "getengagementfeedfinalizedata?id={workspaceId}&s={startDate}&e={endDate}", Method = "POST", BodyStyle = WebMessageBodyStyle.Bare, RequestFormat = WebMessageFormat.Json)]
Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data);

3. 重置MemoryStream指针 #

返回MemoryStream前需将流指针重置到起始位置，否则读取会从流的末尾开始，导致无效数据：

var bin = excelPackage.GetAsByteArray();
var ms = new MemoryStream(bin);
ms.Position = 0; // 重置流指针到起始位置
return ms;

4. 优化文件名编码（可选） #

为避免文件名包含特殊字符时出现下载异常，建议对文件名进行URL编码：

string encodedFileName = Uri.EscapeDataString(fileName);
string contentDisposition = string.Format("attachment; filename=\"{0}.{1}\"", encodedFileName, extension);

修正后的完整实现 #

public Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data)
{
    try
    {
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        string extension = "xlsx";
        string fileName = "report-" + DateTime.Now.Ticks.ToString();
        string encodedFileName = Uri.EscapeDataString(fileName);
        string contentDisposition = string.Format("attachment; filename=\"{0}.{1}\"", encodedFileName, extension);

        WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
        WebOperationContext.Current.OutgoingResponse.Headers.Set("Content-Disposition", contentDisposition);
        
        // 业务逻辑：从数据库获取数据并填充到feedFinalizeDataTable

        feedFinalizeDataTable.TableName = "Summary";

        DataSet dataSet = new DataSet();
        dataSet.Tables.Add(feedFinalizeDataTable);

        using (ExcelPackage excelPackage = new ExcelPackage())
        {
            foreach (DataTable dt in dataSet.Tables)
            {
                ExcelWorksheet sheet = excelPackage.Workbook.Worksheets.Add(dt.TableName);
                sheet.Cells["A1"].LoadFromDataTable(dt, true);

                var cellFont = sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Font;
                cellFont.Bold = true;
            }

            var bin = excelPackage.GetAsByteArray();
            var ms = new MemoryStream(bin);
            ms.Position = 0;
            return ms;
        }
    }
    catch (Exception ex)
    {
        Logger.Instance.Write(LogLevel.ERROR, "DashboardService.GetEngagementFeedFinalizeData", ex);
        throw;
    }
}

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