如何编写迟到早退标记脚本

本文目录导读：

1. 方案1：基于考勤数据的Python脚本
2. 方案2：Excel VBA宏脚本
3. 方案3：Shell脚本（适用于Linux/Mac）
4. 使用说明
5. 建议的扩展功能

我来为您提供一个完整的迟到早退标记脚本,以下是几个常见场景的实现方案：

方案1：基于考勤数据的Python脚本

import pandas as pd
from datetime import datetime, time
class AttendanceMarker:
    def __init__(self):
        # 设置上下班时间
        self.start_time = time(9, 0)  # 9:00上班
        self.end_time = time(18, 0)   # 18:00下班
        self.late_threshold = time(9, 30)  # 9:30算迟到
        self.early_threshold = time(17, 30) # 17:30算早退
    def parse_time(self, time_str):
        """解析时间字符串"""
        try:
            return datetime.strptime(time_str, '%H:%M').time()
        except:
            return datetime.strptime(time_str, '%H:%M:%S').time()
    def mark_attendance(self, date_time_str):
        """标记单次考勤记录"""
        try:
            dt = datetime.strptime(date_time_str, '%Y-%m-%d %H:%M')
            time_only = dt.time()
            status = 'normal'
            if time_only > self.start_time and time_only <= self.late_threshold:
                status = 'late'  # 迟到
            elif time_only > self.late_threshold:
                status = 'serious_late'  # 严重迟到
            elif time_only < self.end_time and time_only >= self.early_threshold:
                status = 'early'  # 早退
            elif time_only < self.early_threshold:
                status = 'serious_early'  # 严重早退
            return {
                'datetime': date_time_str,
                'status': status,
                'time': time_only
            }
        except Exception as e:
            return {'error': str(e)}
    def process_csv(self, csv_file):
        """处理CSV格式的考勤数据"""
        try:
            df = pd.read_csv(csv_file)
            results = []
            for idx, row in df.iterrows():
                date_time = f"{row['date']} {row['time']}"
                result = self.mark_attendance(date_time)
                results.append(result)
            df_result = pd.DataFrame(results)
            df_result.to_csv('attendance_result.csv', index=False)
            print(f"处理完成，结果已保存到 attendance_result.csv")
        except Exception as e:
            print(f"处理文件时出错: {e}")
    def process_excel(self, excel_file):
        """处理Excel格式的考勤数据"""
        try:
            df = pd.read_excel(excel_file)
            results = []
            for idx, row in df.iterrows():
                date_time = f"{row['日期']} {row['时间']}"
                result = self.mark_attendance(date_time)
                results.append(result)
            df_result = pd.DataFrame(results)
            df_result.to_excel('attendance_result.xlsx', index=False)
            print(f"处理完成，结果已保存到 attendance_result.xlsx")
        except Exception as e:
            print(f"处理文件时出错: {e}")
# 使用示例
def main():
    marker = AttendanceMarker()
    # 单次标记
    test_times = [
        '2024-01-15 08:55',  # normal
        '2024-01-15 09:15',  # late
        '2024-01-15 10:00',  # serious_late
        '2024-01-15 17:40',  # early
        '2024-01-15 16:00',  # serious_early
        '2024-01-15 18:00',  # normal
    ]
    for time_str in test_times:
        result = marker.mark_attendance(time_str)
        print(f"{result['datetime']}: {result['status']}")
    # 处理文件示例
    # marker.process_csv('attendance.csv')
    # marker.process_excel('attendance.xlsx')
if __name__ == "__main__":
    main()

方案2：Excel VBA宏脚本

Sub MarkLateEarly()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentTime As Date
    Dim status As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' 设置时间标准
    Dim startTime As Date
    Dim endTime As Date
    Dim lateThreshold As Date
    Dim earlyThreshold As Date
    startTime = TimeValue("09:00:00")
    endTime = TimeValue("18:00:00")
    lateThreshold = TimeValue("09:30:00")
    earlyThreshold = TimeValue("17:30:00")
    ' 假设数据在第1列为日期时间，第2列存放标记结果
    For i = 2 To lastRow
        currentTime = TimeValue(Format(ws.Cells(i, 1).Value, "HH:MM:SS"))
        If currentTime > startTime And currentTime <= lateThreshold Then
            status = "迟到"
        ElseIf currentTime > lateThreshold Then
            status = "严重迟到"
        ElseIf currentTime < endTime And currentTime >= earlyThreshold Then
            status = "早退"
        ElseIf currentTime < earlyThreshold Then
            status = "严重早退"
        Else
            status = "正常"
        End If
        ' 将状态写入第2列
        ws.Cells(i, 2).Value = status
        ' 添加颜色标记
        Select Case status
            Case "迟到"
                ws.Cells(i, 2).Interior.Color = vbYellow
            Case "严重迟到"
                ws.Cells(i, 2).Interior.Color = vbRed
            Case "早退"
                ws.Cells(i, 2).Interior.Color = RGB(255, 255, 0)
            Case "严重早退"
                ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
            Case Else
                ws.Cells(i, 2).Interior.Color = vbGreen
        End Select
    Next i
    MsgBox "标记完成！"
End Sub

方案3：Shell脚本（适用于Linux/Mac）

#!/bin/bash
# 考勤标记脚本
# 使用方法: ./attendance.sh 时间字符串 或 传入文件
# 配置参数
START_TIME="09:00"
END_TIME="18:00"
LATE_THRESHOLD="09:30"
EARLY_THRESHOLD="17:30"
# 检查单个时间
check_time() {
    local input_time=$1
    # 转换时间为时间戳比较
    input_ts=$(date -d "$input_time" +%s 2>/dev/null)
    if [ $? -ne 0 ]; then
        echo "时间格式错误: $input_time"
        return 1
    fi
    # 获取今天的时间
    today=$(date +%Y-%m-%d)
    # 转换为更细粒度的比较
    input_hour=$(date -d "$input_time" +%H:%M)
    # 进行比较
    if [[ "$input_hour" > "$START_TIME" && "$input_hour" <= "$LATE_THRESHOLD" ]]; then
        echo "$input_time -> 迟到"
    elif [[ "$input_hour" > "$LATE_THRESHOLD" ]]; then
        echo "$input_time -> 严重迟到"
    elif [[ "$input_hour" < "$END_TIME" && "$input_hour" >= "$EARLY_THRESHOLD" ]]; then
        echo "$input_time -> 早退"
    elif [[ "$input_hour" < "$EARLY_THRESHOLD" ]]; then
        echo "$input_time -> 严重早退"
    else
        echo "$input_time -> 正常"
    fi
}
# 处理文件
process_file() {
    local file=$1
    if [ ! -f "$file" ]; then
        echo "文件不存在: $file"
        return 1
    fi
    while IFS= read -r time_str; do
        if [ -n "$time_str" ]; then
            check_time "$time_str"
        fi
    done < "$file"
}
# 主程序
case $# in
    0)
        echo "使用方法:"
        echo "  $0 '时间字符串'    检查单个时间"
        echo "  $0 -f '文件路径'   批量检查文件中的时间"
        exit 1
        ;;
    1)
        if [ -f "$1" ]; then
            process_file "$1"
        else
            check_time "$1"
        fi
        ;;
    2)
        if [ "$1" = "-f" ]; then
            process_file "$2"
        else
            echo "参数错误"
            exit 1
        fi
        ;;
esac

使用说明

1. Python版本：适用于大规模数据处理，支持CSV、Excel格式
2. VBA版本：适用于Excel中的数据直接处理
3. Shell版本：适用于Linux/Mac环境

建议的扩展功能

# 添加更多高级功能
class AdvancedAttendanceMarker(AttendanceMarker):
    def __init__(self):
        super().__init__()
        self.working_days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']
    def is_working_day(self, date):
        """检查是否为工作日"""
        return date.strftime('%A') in self.working_days
    def calculate_late_minutes(self, time):
        """计算迟到分钟数"""
        if time > self.start_time:
            delta = datetime.combine(datetime.today(), time) - \
                    datetime.combine(datetime.today(), self.start_time)
            return delta.seconds // 60
        return 0
    def calculate_early_minutes(self, time):
        """计算早退分钟数"""
        if time < self.end_time:
            delta = datetime.combine(datetime.today(), self.end_time) - \
                    datetime.combine(datetime.today(), time)
            return delta.seconds // 60
        return 0
    def generate_report(self, attendance_list):
        """生成考勤报告"""
        report = {
            'total_days': len(attendance_list),
            'late_days': 0,
            'early_days': 0,
            'normal_days': 0,
            'total_late_minutes': 0,
            'total_early_minutes': 0
        }
        for record in attendance_list:
            if 'late' in record['status']:
                report['late_days'] += 1
                report['total_late_minutes'] += self.calculate_late_minutes(record['time'])
            elif 'early' in record['status']:
                report['early_days'] += 1
                report['total_early_minutes'] += self.calculate_early_minutes(record['time'])
            else:
                report['normal_days'] += 1
        return report

请根据您的具体需求选择合适的版本,并相应调整时间参数和阈值。