ASP.NET Core Web API导出Excel仅导出当前页问题求助

你的导出方法ExportMandateToExcelAsync调用了GetMandateAsync，而GetMandateAsync内部会执行分页逻辑（通过PaginationAsync方法），所以不管搜索结果有多少条，只会返回当前页的最多10条数据，导致Excel仅导出当前页内容。

需要修改导出逻辑，直接获取所有符合过滤条件的数据，跳过分页步骤。以下两种方式均可实现：

方式1：新增全量数据获取方法（推荐） #

步骤1：在仓储层新增方法 #

在仓储中添加方法，返回经过过滤和排序后的所有数据：

public async Task<IEnumerable<Mandate>> GetAllMandatesAsync(PagingFilter filter)
{
    var query = await GetMandateQueryAsync(filter);
    return await query.ToListAsync();
}

步骤2：修改服务层导出方法 #

修改ExportMandateToExcelAsync，跳过分页逻辑直接获取全量数据：

public async Task<Response<byte[]>> ExportMandateToExcelAsync(PagingFilter filter)
{
    try
    {
        // 获取所有符合过滤条件的数据，跳过分页
        var mandates = await _unitOfWork.MerchantMandates.GetAllMandatesAsync(filter);
        // 映射为MandateListDto
        var auditReports = _mapper.Map<IEnumerable<MandateListDto>>(mandates);

        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        var excelPackage = new ExcelPackage();
        var worksheet = excelPackage.Workbook.Worksheets.Add("Mandate Reports");

        // 标题设置
        var title = "Reports";
        var titleCell = worksheet.Cells["A1:K1"];
        titleCell.Merge = true;
        titleCell.Value = title;
        titleCell.Style.Font.Size = 16;
        titleCell.Style.Font.Bold = true;
        titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        titleCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
        titleCell.Style.Fill.BackgroundColor.SetColor(Color.LightGray);

        // 表头设置
        var headers = new List<string>
        {
            "S.No.",
            "Merchant Name",
            "Reference Number",
            "Customer Account No.",
            "Branch",
            "Payment Frequency",
            "Start Date",
            "End Date",
            "Amount",
            "Approval Status",
            "Action Date"
        };
        for (int i = 0; i < headers.Count; i++)
        {
            worksheet.Cells[2, i + 1].Value = headers[i];
            worksheet.Cells[2, i + 1].Style.Font.Bold = true;
            worksheet.Cells[2, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells[2, i + 1].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
        }

        // 填充数据行
        var row = 3;
        var serialNumber = 1;
        foreach (var report in auditReports)
        {
            worksheet.Cells[row, 1].Value = serialNumber;
            worksheet.Cells[row, 2].Value = report.Merchant.MerchantName;
            worksheet.Cells[row, 3].Value = report.ReferenceNumber;
            worksheet.Cells[row, 4].Value = report.DrAccountNumber;
            worksheet.Cells[row, 5].Value = report.BankBranch.BranchName;
            worksheet.Cells[row, 6].Value = report.PaymentFrequency;
            worksheet.Cells[row, 7].Value = report.StartDate.Date.ToString("dd-MMM-yyyy");
            worksheet.Cells[row, 8].Value = report.EndDate.Date.ToString("dd-MMM-yyyy");
            worksheet.Cells[row, 9].Value = report.Amount.ToString("N2");
            worksheet.Cells[row, 10].Value = report.MandateApprovalStatus;
            worksheet.Cells[row, 11].Value = report.CreatedAt?.ToString("dd-MMM-yyyy") ?? string.Empty;

            row++;
            serialNumber++;
        }

        // 自动适配列宽
        worksheet.Cells.AutoFitColumns();

        var excelBytes = excelPackage.GetAsByteArray();

        return new Response<byte[]>()
        {
            StatusCode = (int)HttpStatusCode.OK,
            Successful = true,
            Data = excelBytes,
            Message = "Successful.",
            ResponseCode = EnumResponseCodes.SUCCESS.GetStringValue(),
            ResponseDescription = EnumResponseCodes.SUCCESS.ToString()
        };
    }
    catch (Exception ex)
    {
        _logger.Error(ex, "An error occurred.");
        return new Response<byte[]>()
        {
            StatusCode = (int)HttpStatusCode.InternalServerError,
            Successful = false,
            Message = "An error occurred.",
            ResponseCode = EnumResponseCodes.FAILED.GetStringValue(),
            ResponseDescription = EnumResponseCodes.FAILED.ToString()
        };
    }
}

方式2：临时修改分页参数（快速实现） #

如果不想新增仓储方法，可以在导出时临时将分页参数设置为总记录数，强制获取所有数据：

public async Task<Response<byte[]>> ExportMandateToExcelAsync(PagingFilter filter)
{
    try
    {
        // 先获取符合条件的总记录数
        var query = await _unitOfWork.MerchantMandates.GetMandateQueryAsync(filter);
        var totalRecords = await query.CountAsync();
        
        // 构建导出专用的过滤参数，设置PageSize为总记录数
        var exportFilter = new PagingFilter
        {
            SearchQuery = filter.SearchQuery,
            StartDate = filter.StartDate,
            EndDate = filter.EndDate,
            SortBy = filter.SortBy,
            IsSortAscending = filter.IsSortAscending,
            PageNumber = 1,
            PageSize = totalRecords
        };
        
        var auditReportsResponse = await GetMandateAsync(exportFilter);
        var auditReports = auditReportsResponse.Data.PageItems;

        // 后续Excel生成逻辑保持不变...
    }
    catch (Exception ex)
    {
        // 异常处理逻辑保持不变...
    }
}

• 如果数据量极大（如上万条），直接导出全量数据可能引发内存过高问题，建议添加数据量限制，或实现异步分批导出逻辑。
• 确保_mapper.Map能正确完成Mandate到MandateListDto的对象映射。

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