ASP.NET Core Web API：EPPlus Excel上传之成功记录插入与失败信息展示

1. 定义校验结果DTO #

创建用于区分成功/失败记录的DTO，确保返回结果结构清晰：

// 存储单条校验失败的记录及错误原因
public class FailureItem<T>
{
    public T Record { get; set; }
    public List<string> ErrorMessages { get; set; } = new();
}

// 整体导入结果返回模型
public class ImportResult<T>
{
    public List<T> SuccessRecords { get; set; } = new();
    public List<FailureItem<T>> FailedRecords { get; set; } = new();
}

2. 抽离业务校验逻辑 #

单独编写校验方法，聚焦业务规则，避免与导入逻辑耦合：

private List<string> ValidateLeaveApplication(LeaveApplication leaveApp)
{
    var errors = new List<string>();

    // 必填字段校验
    if (string.IsNullOrWhiteSpace(leaveApp.EmployeeId))
    {
        errors.Add("员工ID不能为空");
    }
    // 日期合法性校验
    if (leaveApp.StartDate == DateTime.MinValue || leaveApp.EndDate == DateTime.MinValue)
    {
        errors.Add("日期格式无效，请输入正确的日期");
    }
    else if (leaveApp.StartDate >= leaveApp.EndDate)
    {
        errors.Add("开始日期必须早于结束日期");
    }
    // 枚举合法性校验
    if (!Enum.IsDefined(typeof(LeaveType), leaveApp.LeaveType))
    {
        errors.Add($"请假类型无效，可选值：{string.Join(", ", Enum.GetNames(typeof(LeaveType)))}");
    }
    // 可扩展其他业务规则，比如请假时长限制、员工状态校验等

    return errors;
}

3. 改造Excel导入方法 #

使用EPPlus读取Excel内容，逐条校验后拆分成功/失败记录，仅将通过校验的记录插入数据库：

[HttpPost("import-leaves")]
public async Task<IActionResult> ImportLeaveApplications(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest("请选择有效的Excel文件");
    }

    var importResult = new ImportResult<LeaveApplication>();

    using var package = new ExcelPackage(file.OpenReadStream());
    var worksheet = package.Workbook.Worksheets.First();
    var totalRows = worksheet.Dimension?.Rows ?? 0;

    // 从第2行开始读取（跳过表头行）
    for (int row = 2; row <= totalRows; row++)
    {
        // 映射Excel行数据到实体类
        var leaveApp = new LeaveApplication
        {
            EmployeeId = worksheet.Cells[row, 1].Text.Trim(),
            StartDate = DateTime.TryParse(worksheet.Cells[row, 2].Text, out var startDate) ? startDate : DateTime.MinValue,
            EndDate = DateTime.TryParse(worksheet.Cells[row, 3].Text, out var endDate) ? endDate : DateTime.MinValue,
            LeaveType = Enum.TryParse<LeaveType>(worksheet.Cells[row, 4].Text.Trim(), out var leaveType) ? leaveType : default,
            Reason = worksheet.Cells[row, 5].Text.Trim()
        };

        // 执行校验
        var validationErrors = ValidateLeaveApplication(leaveApp);
        if (!validationErrors.Any())
        {
            importResult.SuccessRecords.Add(leaveApp);
        }
        else
        {
            importResult.FailedRecords.Add(new FailureItem<LeaveApplication>
            {
                Record = leaveApp,
                ErrorMessages = validationErrors
            });
        }
    }

    // 批量插入校验通过的记录
    if (importResult.SuccessRecords.Any())
    {
        _dbContext.LeaveApplications.AddRange(importResult.SuccessRecords);
        await _dbContext.SaveChangesAsync();
    }

    // 返回结构化的导入结果
    return Ok(new
    {
        TotalSuccess = importResult.SuccessRecords.Count,
        TotalFailed = importResult.FailedRecords.Count,
        SuccessData = importResult.SuccessRecords,
        FailedData = importResult.FailedRecords.Select(item => new
        {
            item.Record,
            ErrorDetails = item.ErrorMessages
        })
    });
}

4. 额外配置说明 #

如果使用EPPlus 5及以上版本，需在Program.cs中配置许可上下文（非商用场景）：

builder.Services.AddSingleton(new ExcelPackage.LicenseContext(LicenseContext.NonCommercial));

前端展示建议 #

前端接收到返回结果后，可分别渲染成功列表和失败列表：

• 成功列表展示已插入数据库的请假申请信息
• 失败列表展示每条记录的字段内容及对应的错误原因，方便用户修改后重新上传

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