ASP.NET Core MVC中读取单行重复列的Excel并转DataTable存入数据库

解决方案 #

你现在需要先把宽格式的Excel表转成符合你表结构的长格式，核心逻辑是先解析表头的重复分组，再把每行用户的多组日期数据拆分成多条对应单条日期的记录，具体修改代码如下：

// 先定义和你目标表结构匹配的实体类，方便后续批量插入数据库
public class EmployeeScore
{
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public int Score { get; set; }
    public int CutOf { get; set; }
}

// 后续逻辑替换你原来读取Excel的部分
var resultList = new List<EmployeeScore>();

if (file != null && file.ContentType.Length > 0 && System.IO.Path.GetExtension(file.FileName).ToLower() == ".xlsx")
{
    string path = Path.Combine(hostingEnv.WebRootPath, "Uploads");
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    string fileName = Path.GetFileName(file.FileName);
    string filePath = Path.Combine(path, fileName);
    using (var stream = new FileStream(filePath, FileMode.Create))
    {
        file.CopyTo(stream);
    }

    using (var workbook = new XLWorkbook(filePath))
    {
        IXLWorksheet worksheet = workbook.Worksheet(1);
        var rows = worksheet.RowsUsed().ToList();
        if(rows.Count <= 1)
        {
            ViewBag.Message = "Empty Excel File!";
            return;
        }

        // 第一步：解析表头，确定分组位置
        var headerRow = rows[0];
        int totalColumns = headerRow.LastCellUsed().Address.ColumnNumber;
        // 存储每个日期分组的列索引：key是分组序号，value是(日期列索引, Score列索引, CutOf列索引)
        var groupColumns = new List<(int DateCol, int ScoreCol, int CutOfCol)>();
        // 第一列固定是姓名，所以从第二列开始遍历
        for(int col = 2; col <= totalColumns; col += 3)
        {
            // 你的表头每组是固定3列：日期、Score、CutOf，所以每3列一个分组
            groupColumns.Add((col, col+1, col+2));
        }

        // 第二步：遍历数据行，拆分成多条记录
        // 跳过表头，从第二行开始遍历数据
        for(int rowIndex = 1; rowIndex < rows.Count; rowIndex++)
        {
            var currentRow = rows[rowIndex];
            // 第一列是姓名
            string empName = currentRow.Cell(1).Value.ToString().Trim();
            if(string.IsNullOrEmpty(empName)) continue;

            // 遍历每个日期分组，生成对应记录
            foreach(var group in groupColumns)
            {
                // 读取当前分组的三个字段值，空值可以根据业务处理跳过或者设默认值
                var dateCell = currentRow.Cell(group.DateCol);
                var scoreCell = currentRow.Cell(group.ScoreCol);
                var cutOfCell = currentRow.Cell(group.CutOfCol);

                if(!dateCell.TryGetDateTime(out DateTime date) || !scoreCell.TryGetInt(out int score) || !cutOfCell.TryGetInt(out int cutOf))
                {
                    // 无效数据可以自行加日志或者跳过
                    continue;
                }

                resultList.Add(new EmployeeScore
                {
                    Name = empName,
                    Date = date,
                    Score = score,
                    CutOf = cutOf
                });
            }
        }
    }

    // 第三步：把resultList直接批量插入你的数据库即可，比DataTable效率更高
    // 示例用EF Core的话直接：dbContext.EmployeeScores.AddRange(resultList); dbContext.SaveChanges();
}

核心逻辑说明

• 你原来的逻辑是1:1映射Excel结构到DataTable，不适合这种重复列的宽表，所以先按照表头的固定分组规则（每3列一组对应一个日期的Score和CutOf）先定位每组的列位置
• 遍历每个用户行的时候，把每个分组的数据单独拆成一条符合目标表结构的记录，最终得到的集合就是你要的标准按人员+日期分行的格式
• 直接用实体类接收数据后续插入数据库更方便，不需要再转DataTable

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