ASP.NET Core MVC导出Excel文件损坏问题技术求助

背景 #

开发了一个ASP.NET Core MVC Web应用工具，用于从数据库导出数据到多工作表的Excel工作簿。

问题描述 #

应用可导出Excel工作簿，但用户打开下载的文件时，会提示文件已损坏，不过文件内实际包含部分数据。

前端代码 #

$(document).on('click', '#btnDataExport', function () {
    var zId = $("#deZone").val();
    var fId = $("#deFactory").val();
    var mId = $('#deMarket').val();
    var selectedCheckboxes = $('input.chk:checked');
    var selectedIds = [];
    selectedCheckboxes.each(function () {
        selectedIds.push($(this).attr('id'));
    });
    var zoneIdsStr = zId.join(',');
    var marketIdsStr = mId.join(',');
    var factoryIdsStr = fId.join(',');
    $.ajax({
        cache: false,
        type: 'POST',
        url: '/DataExport/DataExport',
        data: {
            entities: selectedIds,
            zoneIds: zoneIdsStr,
            marketIds: marketIdsStr,
            factoryIds: factoryIdsStr
        },
        success: function (data) {
            var blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
            var link = document.createElement('a');
            link.href = window.URL.createObjectURL(blob);
            link.download = 'Export.xlsx';
            link.click();
        },
        error: function () {
            alert("Error while loading the page");
        }    
    });
});

后端代码 #

[HttpPost]
public async Task<IActionResult> DataExport(List<string> entities, List<int> zoneIds, List<int>marketIds,List<int> factoryIds)
{
    // byte[] fileContents;
    using (var package = new ExcelPackage())
    {
        foreach (var entity in entities)
        {
            if (string.Compare(entity, "YearlyZoneKPIs", false) == 0)
            {
                var yearlyZoneKPIs = _dataexportservice.GetYearlyZoneKpis(zoneIds, marketIds);
                ExcelWorksheet worksheetyearlyZoneKPIs = package.Workbook.Worksheets.Add("YearlyZoneKPIs");
                DataExportExcelFactory.CreateExcelForYearlyZoneKPIs(ref worksheetyearlyZoneKPIs, yearlyZoneKPIs);
             }
         }
    }

    using (MemoryStream mstream = new MemoryStream())
    {
        package.SaveAs(mstream);
        return File(mstream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExportedData.xlsx");
    }
}    

注：DataExportExcelFactory.CreateExcelForYearlyZoneKPIs是静态方法，用于通过引用将数据填充到工作表中。

问题根源及修复方案 #

1. 后端资源释放时机错误 #

第一个using块结束后，ExcelPackage对象已被Dispose，后续调用package.SaveAs(mstream)会操作已释放的资源，导致生成的文件不完整。

修复后后端代码：

[HttpPost]
public async Task<IActionResult> DataExport(List<string> entities, List<int> zoneIds, List<int> marketIds, List<int> factoryIds)
{
    using (var package = new ExcelPackage())
    {
        foreach (var entity in entities)
        {
            if (string.Compare(entity, "YearlyZoneKPIs", false) == 0)
            {
                var yearlyZoneKPIs = _dataexportservice.GetYearlyZoneKpis(zoneIds, marketIds);
                ExcelWorksheet worksheetyearlyZoneKPIs = package.Workbook.Worksheets.Add("YearlyZoneKPIs");
                DataExportExcelFactory.CreateExcelForYearlyZoneKPIs(ref worksheetyearlyZoneKPIs, yearlyZoneKPIs);
             }
         }

        using (MemoryStream mstream = new MemoryStream())
        {
            package.SaveAs(mstream);
            mstream.Position = 0; // 重置流指针到起始位置
            return File(mstream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExportedData.xlsx");
        }
    }
}    

将MemoryStream的操作移至ExcelPackage的using块内部，确保保存操作时资源未被释放，同时添加流指针重置操作保证数据完整读取。

2. 前端二进制数据处理不当 #

jQuery AJAX默认将响应解析为文本，会破坏Excel二进制文件的字节结构，导致文件损坏。

修复后前端代码：

$(document).on('click', '#btnDataExport', function () {
    var zId = $("#deZone").val();
    var fId = $("#deFactory").val();
    var mId = $('#deMarket').val();
    var selectedCheckboxes = $('input.chk:checked');
    var selectedIds = [];
    selectedCheckboxes.each(function () {
        selectedIds.push($(this).attr('id'));
    });
    var zoneIdsStr = zId.join(',');
    var marketIdsStr = mId.join(',');
    var factoryIdsStr = fId.join(',');
    
    $.ajax({
        cache: false,
        type: 'POST',
        url: '/DataExport/DataExport',
        data: {
            entities: selectedIds,
            zoneIds: zoneIdsStr,
            marketIds: marketIdsStr,
            factoryIds: factoryIdsStr
        },
        xhrFields: {
            responseType: 'blob' // 指定响应为二进制blob类型
        },
        success: function (data) {
            var link = document.createElement('a');
            link.href = window.URL.createObjectURL(data);
            link.download = 'ExportedData.xlsx';
            link.click();
            window.URL.revokeObjectURL(link.href); // 释放URL对象，避免内存泄漏
        },
        error: function () {
            alert("导出过程出错");
        }    
    });
});

添加xhrFields: { responseType: 'blob' }直接获取二进制响应，避免文本解析导致的字节损坏，同时添加资源释放操作优化内存使用。

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