ASP.NET Core导出带计划的委托报表至Excel时遇空值异常求助

核心问题分析 #

1. System.InvalidOperationException: 可为空对象必须具有值：代码中直接访问了可为空类型（如int?/DateTime?）的.Value属性，但该字段实际为null，需先判断是否有值再访问。
2. Skip/Take未使用OrderBy警告：Linq查询中使用分页操作前必须先排序，否则每次查询结果顺序可能不一致，导致分页数据混乱。

1. 仓储层代码（IAdminMandateRepository & AdminMandateRepository） #

using Microsoft.EntityFrameworkCore;
using YourProjectName.Models;
using YourProjectName.Filters;

public interface IAdminMandateRepository
{
    Task<IQueryable<Mandate>> GetMandatesWithDetailsAsync(PagingFilter filter);
}

public class AdminMandateRepository : IAdminMandateRepository
{
    private readonly AppDbContext _dbContext;

    public AdminMandateRepository(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<IQueryable<Mandate>> GetMandatesWithDetailsAsync(PagingFilter filter)
    {
        var query = _dbContext.Mandates
            .Include(m => m.MandateDetails) // 关联子报表数据
            .Include(m => m.Schedule) // 关联计划数据
            .AsQueryable();

        // 搜索筛选逻辑示例
        if (!string.IsNullOrEmpty(filter.SearchKeyword))
        {
            query = query.Where(m => m.MandateNumber.Contains(filter.SearchKeyword) 
                                 || m.ClientName.Contains(filter.SearchKeyword));
        }

        // 必须先排序，解决Skip/Take警告
        query = query.OrderBy(m => m.CreatedDate); // 按创建时间排序，可根据业务调整字段

        // 分页处理
        if (filter.PageSize > 0 && filter.PageNumber > 0)
        {
            query = query.Skip((filter.PageNumber - 1) * filter.PageSize).Take(filter.PageSize);
        }

        return query;
    }
}

2. 服务层代码（AdminMandatesService） #

using OfficeOpenXml;
using YourProjectName.Models;
using YourProjectName.Filters;
using YourProjectName.Repositories;
using System.IO;

public class AdminMandatesService : IAdminMandatesService
{
    private readonly IAdminMandateRepository _mandateRepository;

    public AdminMandatesService(IAdminMandateRepository mandateRepository)
    {
        _mandateRepository = mandateRepository;
    }

    public async Task<byte[]> ExportMandateWithDetailListToExcelAsync(PagingFilter filter)
    {
        var mandates = await _mandateRepository.GetMandatesWithDetailsAsync(filter).ToListAsync();

        using var package = new ExcelPackage();
        var mainWorksheet = package.Workbook.Worksheets.Add("委托主报表");
        var detailWorksheet = package.Workbook.Worksheets.Add("委托子报表");

        // 主报表表头
        mainWorksheet.Cells[1, 1].Value = "委托编号";
        mainWorksheet.Cells[1, 2].Value = "客户名称";
        mainWorksheet.Cells[1, 3].Value = "创建时间";
        mainWorksheet.Cells[1, 4].Value = "计划状态";

        // 主报表数据填充
        int mainRow = 2;
        foreach (var mandate in mandates)
        {
            mainWorksheet.Cells[mainRow, 1].Value = mandate.MandateNumber;
            mainWorksheet.Cells[mainRow, 2].Value = mandate.ClientName;
            // 处理可为空的DateTime字段，避免访问.Value时出错
            mainWorksheet.Cells[mainRow, 3].Value = mandate.CreatedDate.HasValue ? mandate.CreatedDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : string.Empty;
            // 处理可为空的计划状态字段
            mainWorksheet.Cells[mainRow, 4].Value = mandate.Schedule?.Status ?? "无计划";
            mainRow++;
        }

        // 子报表表头
        detailWorksheet.Cells[1, 1].Value = "委托编号";
        detailWorksheet.Cells[1, 2].Value = "明细项名称";
        detailWorksheet.Cells[1, 3].Value = "数量";
        detailWorksheet.Cells[1, 4].Value = "单价";

        // 子报表数据填充
        int detailRow = 2;
        foreach (var mandate in mandates)
        {
            foreach (var detail in mandate.MandateDetails)
            {
                detailWorksheet.Cells[detailRow, 1].Value = mandate.MandateNumber;
                detailWorksheet.Cells[detailRow, 2].Value = detail.ItemName;
                // 处理可为空的数值字段
                detailWorksheet.Cells[detailRow, 3].Value = detail.Quantity.HasValue ? detail.Quantity.Value : 0;
                detailWorksheet.Cells[detailRow, 4].Value = detail.UnitPrice.HasValue ? detail.UnitPrice.Value.ToString("0.00") : "0.00";
                detailRow++;
            }
        }

        // 自动调整列宽
        mainWorksheet.Cells.AutoFitColumns();
        detailWorksheet.Cells.AutoFitColumns();

        return package.GetAsByteArray();
    }
}

3. 控制器代码（AdminMandatesController） #

using Microsoft.AspNetCore.Mvc;
using YourProjectName.Filters;
using YourProjectName.Services;

[Route("api/admin/mandates")]
[ApiController]
public class AdminMandatesController : ControllerBase
{
    private readonly IAdminMandatesService _mandatesService;

    public AdminMandatesController(IAdminMandatesService mandatesService)
    {
        _mandatesService = mandatesService;
    }

    [HttpGet("export-excel")]
    public async Task<IActionResult> ExportMandateWithDetailToExcel([FromQuery] PagingFilter filter)
    {
        try
        {
            var excelBytes = await _mandatesService.ExportMandateWithDetailListToExcelAsync(filter);
            return File(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"委托报表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        }
        catch (Exception ex)
        {
            return StatusCode(StatusCodes.Status500InternalServerError, $"导出失败：{ex.Message}");
        }
    }
}

4. 辅助类（PagingFilter） #

public class PagingFilter
{
    public int PageNumber { get; set; } = 1;
    public int PageSize { get; set; } = 10;
    public string? SearchKeyword { get; set; }
}

关键修复点 #

• 可为空字段处理：所有可为空类型均先通过.HasValue判断，或使用??空合并运算符提供默认值，避免直接访问.Value导致空引用错误。
• OrderBy前置：在Linq查询的Skip/Take操作前添加OrderBy，确保分页结果顺序稳定，消除警告。
• EPPlus操作优化：对Excel列进行自动调整，提升报表可读性。

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