本文目录导读：
我来为您提供一个完整的迟到早退标记脚本,以下是几个常见场景的实现方案：
方案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
使用说明
- Python版本：适用于大规模数据处理，支持CSV、Excel格式
- VBA版本：适用于Excel中的数据直接处理
- 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
请根据您的具体需求选择合适的版本,并相应调整时间参数和阈值。
