基于DateTime输入和班次时间在C#中从Excel提取姓名

背景 #

开发C#项目时，需根据输入的DateTime从Excel中提取对应时段的值班人员姓名。Excel表结构如下：

• 列B到H依次对应周一至周日
• 早班（7:00-13:30）姓名位于第4行（B4-H4）
• 晚班（13:30-21:00）姓名位于第5行（B5-H5）
• 表中每天对应两行记录：班次时间行、姓名行，时间采用24小时制，无特殊格式

现有实现代码 #

public static string GetName(Worksheet worksheet, DateTime dateTime)
{
    string Name = "";

    DateTime weekStartDate = dateTime.Date.AddDays(-(int)dateTime.DayOfWeek);

    var usedRange = worksheet.UsedRange;
    var rows = usedRange.Rows;
    int rowCount = rows.Count;
    for (int i = 1; i <= rowCount; i++)
    {
        var row = (Range)rows[i];
        if (row.Row == 1)
        {
            continue;
        }

        var dayOfWeekCell = (Range)row.Cells[1, 1];
        var dayOfWeekText = dayOfWeekCell.Text;
        if (!Enum.TryParse(dayOfWeekText, out DayOfWeek dayOfWeek))
        {
            continue;
        }

        var columnCount = usedRange.Columns.Count;
        for (int j = 1; j <= columnCount; j++)
        {
            var column = (Range)usedRange.Columns[j];
            var columnHeader = column.Cells[1, 1].Text;
            if (string.Equals(columnHeader, dayOfWeekText, StringComparison.OrdinalIgnoreCase))
            {
                var shiftTypeCell = (Range)row.Cells[2, j];
                string currentShiftType = shiftTypeCell.Text;
                if (dateTime.TimeOfDay >= new TimeSpan(7, 0, 0) && dateTime.TimeOfDay < new TimeSpan(13, 30, 0) && string.Equals(currentShiftType, "Früh", StringComparison.OrdinalIgnoreCase))
                {
                    Name = (row.Cells[4, j] as Range).Value;
                }
                else if (dateTime.TimeOfDay >= new TimeSpan(13, 30, 0) && dateTime.TimeOfDay < new TimeSpan(21, 0, 0) && string.Equals(currentShiftType, "Spät", StringComparison.OrdinalIgnoreCase))
                {
                    Name = (row.Cells[5, j] as Range).Value;
                }

                break;
            }
        }

    }

    return Name;
}

调用方式：

string Person = Helpers.GetName((Worksheet)shiftsWorksheet, zeitpunkt);

问题现象 #

传入DateTime参数04.03.2023 14:30（周六晚班）时，期望返回G5单元格的姓名，但当前代码无法正确匹配班次并提取结果。

问题分析与修正代码 #

现有代码逻辑冗余，存在索引混淆、不必要遍历的问题。直接根据DateTime的星期和时间定位对应单元格即可，简化后的代码如下：

public static string GetName(Worksheet worksheet, DateTime dateTime)
{
    // 映射DayOfWeek到Excel列：周一=2(B), 周二=3(C)...周六=7(G), 周日=8(H)
    int columnIndex = dateTime.DayOfWeek switch
    {
        DayOfWeek.Monday => 2,
        DayOfWeek.Tuesday => 3,
        DayOfWeek.Wednesday => 4,
        DayOfWeek.Thursday => 5,
        DayOfWeek.Friday => 6,
        DayOfWeek.Saturday => 7,
        DayOfWeek.Sunday => 8,
        _ => -1
    };

    if (columnIndex == -1)
        return string.Empty;

    // 根据时间判断班次行：早班=4，晚班=5
    int rowIndex = dateTime.TimeOfDay switch
    {
        var time when time >= new TimeSpan(7, 0, 0) && time < new TimeSpan(13, 30, 0) => 4,
        var time when time >= new TimeSpan(13, 30, 0) && time < new TimeSpan(21, 0, 0) => 5,
        _ => -1
    };

    if (rowIndex == -1)
        return string.Empty;

    // 提取对应单元格的值
    var targetCell = worksheet.Cells[rowIndex, columnIndex] as Range;
    return targetCell?.Value?.ToString() ?? string.Empty;
}

关键说明 #

1. 列映射：直接将DateTime的DayOfWeek转换为Excel列索引，避免遍历表头匹配，效率更高且不易出错。
2. 班次判断：通过TimeOfDay直接确定对应行（早班4、晚班5），无需依赖Excel中的班次文本（如"Früh"/"Spät"），减少耦合。
3. 直接取值：通过明确的行/列索引直接访问目标单元格，避免复杂的嵌套遍历逻辑。

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