ASP.NET Core 6 MVC中ClosedXML读取Excel内存占用过高问题

核心问题分析 #

1. 重复读取流：同时使用ClosedXML和ExcelDataReader读取同一个流，两份内存对象叠加，直接翻倍内存占用
2. 线程不安全的并行访问：ClosedXML的Worksheet、Row、Cell对象并非线程安全，多线程并发访问会导致内部缓存错乱，触发内存泄漏
3. 不合理的并行度：MaxDegreeOfParallelism=150远超过CPU核心数，大量线程上下文切换会额外消耗内存和CPU资源
4. 冗余内存分配：初始化大量未使用的数组（如_TypeOfPay、_TempCheck），同时持有DataSet和ClosedXML的内存对象，内存占用翻倍
5. 资源释放不规范：未用using自动管理可释放资源，导致Workbook、Stream等资源无法及时回收

优化步骤 #

1. 移除重复读取逻辑 #

只保留ClosedXML或ExcelDataReader其中一种读取方式，避免重复加载整个Excel文件到内存。

2. 修正并行逻辑：先读取数据再并行处理 #

ClosedXML不支持多线程直接访问文档对象，应先单线程读取原始数据到内存集合，再对集合进行并行业务处理，彻底脱离ClosedXML的内存上下文。

3. 合理控制并行度 #

将MaxDegreeOfParallelism设置为Environment.ProcessorCount或Environment.ProcessorCount * 2，避免创建过多线程导致资源浪费。

4. 用using自动管理资源 #

所有实现IDisposable的对象（Stream、XLWorkbook等）都用using包裹，确保资源在使用完毕后自动释放，无需手动调用Dispose或Close。

5. 减少冗余内存分配 #

• 删除未使用的数组变量
• 改用List<T>替代固定大小数组，避免预分配过多内存
• 不要同时持有DataSet和ClosedXML的内存对象，二选一即可

优化后的示例代码 #

public void ReadFile(IFormFile MyFileCollection)
{
    // 提取文件名逻辑
    if (MyFileCollection.FileName.Contains('~'))
    {
        NameOfUpload = MyFileCollection.FileName.Split('~')[1].Split('.')[0];
    }

    // Using自动管理流和Workbook资源
    using var fileStream = MyFileCollection.OpenReadStream();
    using var workbook = new XLWorkbook(fileStream, XLEventTracking.Disabled);
    var worksheet = workbook.Worksheet(1);

    bool isHeaderFound = false;
    int startRow = 1;

    // 单线程遍历找到表头位置，避免多线程冲突
    foreach (var row in worksheet.RowsUsed())
    {
        var cellValue = row.Cell(1).GetValue<string>()?.Trim() ?? string.Empty;
        if (string.IsNullOrEmpty(cellValue))
            continue;

        if (cellValue.ToLower() == "номер посылки")
        {
            isHeaderFound = true;
            startRow = row.RowNumber() + 1;
            break;
        }
    }

    if (!isHeaderFound)
        return;

    // 单线程读取原始数据到集合，脱离ClosedXML上下文
    var rawExcelRows = new List<RawExcelRow>();
    int lastRow = worksheet.LastRowUsed().RowNumber();
    for (int i = startRow; i <= lastRow; i++)
    {
        var row = worksheet.Row(i);
        var nakladnoi = row.Cell(1).GetValue<string>()?.Trim() ?? string.Empty;
        if (string.IsNullOrEmpty(nakladnoi))
            continue;

        rawExcelRows.Add(new RawExcelRow
        {
            NumberNakladnoi = nakladnoi,
            NumberOrderOfSender = row.Cell(2).GetValue<string>()?.Trim() ?? string.Empty,
            Partiya = row.Cell(4).CachedValue?.ToString() ?? string.Empty,
            NomerRZ = row.Cell(5).GetValue<string>()?.Trim() ?? string.Empty,
            PlaceCount = string.IsNullOrEmpty(row.Cell(6).GetValue<string>()) ? 0 : row.Cell(6).GetValue<int>(),
            MethodDelivery = row.Cell(7).GetValue<string>()?.Trim() ?? string.Empty,
            TypeOfDelivery = row.Cell(8).GetValue<string>()?.Trim() ?? string.Empty,
            CityRaw = row.Cell(10).GetValue<string>()?.Trim() ?? string.Empty,
            PVZTarget = row.Cell(11).GetValue<string>()?.Trim() ?? string.Empty,
            KladrPointDelivery = row.Cell(12).GetValue<string>()?.Trim() ?? string.Empty,
            Date15 = row.Cell(15).GetValue<string>()?.Trim() ?? string.Empty,
            Date13 = row.Cell(13).GetValue<string>()?.Trim() ?? string.Empty,
            CargoState = row.Cell(18).GetValue<string>()?.Trim() ?? string.Empty,
            ReasonDontArrive = row.Cell(19).GetValue<string>()?.Trim() ?? string.Empty,
            WeightBySizeStr = row.Cell(20).GetValue<string>() ?? string.Empty,
            WeightFaktStr = row.Cell(21).GetValue<string>() ?? string.Empty,
            CODStr = row.Cell(23).GetValue<string>() ?? string.Empty
        });
    }

    // 并行处理业务逻辑，此时操作的是内存集合，线程安全
    var parallelOptions = new ParallelOptions
    {
        MaxDegreeOfParallelism = Environment.ProcessorCount * 2
    };

    var resultCollection = new ConcurrentBag<DataFromFile>();
    Parallel.ForEach(rawExcelRows, parallelOptions, rawRow =>
    {
        var data = new DataFromFile
        {
            _Number_Nakladnoi = rawRow.NumberNakladnoi,
            _NumberOrderOf_Sender = rawRow.NumberOrderOfSender,
            _Partiya = rawRow.Partiya,
            _NomerRZ = rawRow.NomerRZ,
            _PlaceCount = rawRow.PlaceCount,
            _MethodDelivery = rawRow.MethodDelivery,
            _TypeOf_delivery = rawRow.TypeOfDelivery,
            _CityDelivery = addresses.GetCorrectCityName(rawRow.CityRaw),
            _PVZ_Target = rawRow.PVZTarget,
            _Kladr_PointDelivery = rawRow.KladrPointDelivery,
            _DateFaktDelivery = !string.IsNullOrEmpty(rawRow.Date15) 
                ? SystemClass.GetDateTimeFromString(rawRow.Date15) 
                : SystemClass.GetDateTimeFromString(rawRow.Date13),
            _CargoState = rawRow.CargoState,
            _ReasonDontArrive = rawRow.ReasonDontArrive,
            _WeightBySize = string.IsNullOrEmpty(rawRow.WeightBySizeStr) ? 0 : double.Parse(rawRow.WeightBySizeStr),
            _WeightFakt = string.IsNullOrEmpty(rawRow.WeightFaktStr) ? 0 : double.Parse(rawRow.WeightFaktStr),
            _COD = string.IsNullOrEmpty(rawRow.CODStr) ? 0 : double.Parse(rawRow.CODStr)
        };

        resultCollection.Add(AddNewElement(data));
    });
}

// 辅助类：暂存Excel原始数据，避免直接操作ClosedXML对象
private class RawExcelRow
{
    public string NumberNakladnoi { get; set; }
    public string NumberOrderOfSender { get; set; }
    public string Partiya { get; set; }
    public string NomerRZ { get; set; }
    public int PlaceCount { get; set; }
    public string MethodDelivery { get; set; }
    public string TypeOfDelivery { get; set; }
    public string CityRaw { get; set; }
    public string PVZTarget { get; set; }
    public string KladrPointDelivery { get; set; }
    public string Date15 { get; set; }
    public string Date13 { get; set; }
    public string CargoState { get; set; }
    public string ReasonDontArrive { get; set; }
    public string WeightBySizeStr { get; set; }
    public string WeightFaktStr { get; set; }
    public string CODStr { get; set; }
}

额外优化建议 #

• 流式读取大文件：若Excel文件超过500MB，ClosedXML的内存加载方式会占用大量内存，建议改用支持流式读取的库（如EPPLus的流式API、ExcelDataReader），逐行读取处理，避免一次性加载整个文件
• 异步并行处理IO操作：如果addresses.GetCorrectCityName是数据库或API调用等IO操作，应使用Parallel.ForEachAsync替代Parallel.ForEach，提升IO利用率的同时减少线程开销
• 内存诊断：用Visual Studio诊断工具或dotMemory分析内存快照，定位具体的内存泄漏点，确认是否有未释放的对象引用

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