考勤统计

main.py

from typing import List

import pandas as pd

student_number_conversion = {}


def change_number(pre: str) -> str:
    if pre in student_number_conversion.keys():
        return student_number_conversion[pre]
    else:
        return ""


def check_in(stu: str, all_students: List[str], status: List[int]) -> bool:
    for i, s in enumerate(all_students):
        if stu == s:
            status[i] = 1
            return True
    return False


stu_excel_file = ''
attendance_excel_file = ''
# attendance_excel_file2 = ''
# attendance_excel_file3 = ''
use_column_stu = 'A:D'
use_column_att = 'A'
skip_rows_stu = 1
skip_rows_att = 2
cur_day = '9.1'

students = pd.read_excel(stu_excel_file, header=None, skiprows=skip_rows_stu, usecols=use_column_stu)
students.columns = ["序号", "学号", "姓名", "班级"]
# print(students.head())

attendance = pd.read_excel(attendance_excel_file, header=None, skiprows=skip_rows_att, usecols=use_column_att,
                           sheet_name=4, dtype=str)
attendance.columns = ["学号"]
attendance2 = pd.read_excel(attendance_excel_file, header=None, skiprows=skip_rows_att, usecols=use_column_att,
                            sheet_name=5, dtype=str)
attendance2.columns = ["学号"]
# attendance3 = pd.read_excel(attendance_excel_file3, header=None, skiprows=skip_rows_att, usecols=use_column_att)
# attendance3.columns = ["学号"]
attendance_all = pd.concat([attendance, attendance2])
# print(attendance_all)

print(students.shape)
students.insert(students.shape[1], cur_day, 0 * (students.shape[0]), True)

for a in attendance_all["学号"].astype(str):
    if not (check_in(a, students["学号"].astype(str), students[cur_day])
            or check_in(change_number(a), students["学号"].astype(str), students[cur_day])):
        print(a)

print(students.head())

students.to_excel("output.xlsx", index=False)