闲着无聊整个统计钉钉员工正常出勤天数的小程序。

闲着无聊整个统计钉钉员工正常出勤天数的小程序。

废话不多说，直接上干货！

首先用QT设计师设计以下简单的页面，生成ui文件，再使用pyuic命令转换成py文件。

然后，就是敲代码的时间，以下是全部代码。。。。。

import sys
from PyQt5.QtWidgets import (
    QApplication,QFileDialog,QMessageBox,QDialog,QTableWidgetItem
)
import pandas as pd
import os
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter,column_index_from_string
from collections import Counter
from untitled import Ui_Dialog

class Attendance_Statttistics(Ui_Dialog,QDialog):
    def __init__(self,set_string) -> None:
        super().__init__()
        self.set_string = set_string
        self.setupUi(self)
        self.show()

        self.pushButton_select_file.clicked.connect(self.get_file)
        self.pushButton_count.clicked.connect(self.count)
        self.pushButton_depart_query.clicked.connect(self.depart_query)
        self.pushButton_close.clicked.connect(self.close)

    def show_msgs(self,msg):
        msgs = QMessageBox.warning(self,"信息提示",msg)
        return msgs

    def get_file(self):
        #选择文件
        self.file = QFileDialog.getOpenFileName(self,"选择文件",os.getcwd(),"xlsx(*.xlsx)")[0]
        self.lineEdit_select_file.setText(self.file)

    def count(self):
        try:
            df = pd.read_excel(self.file,sheet_name="原始记录",header=2)
            df = df[["姓名","考勤组","考勤日期","打卡结果"]]
            #删除包含特定内容的所有行
            for i in self.set_string:
                df = df[-df['打卡结果'].isin([i])]
            df = df[-df['考勤组'].isin(['未加入考勤组'])]
            df['打卡结果'] = df['打卡结果'].replace('补卡审批通过','正常')
            #删除表格中的重复行
            df = df.drop_duplicates()
            df_1 = df.copy()
            df_1 = df_1[["姓名","考勤组"]].drop_duplicates()
            df_1.to_excel("./kaoqin.xlsx",index=False)
            #考勤组去重并转为列表类型
            depart = df["考勤组"].drop_duplicates().tolist()
            #print(depart)
            self.comboBox_department.addItems(depart)
            #统计出勤天数转成字典类型
            name_list = []
            [name_list.append(i) for i in df['姓名']]
            self.dir_list = dict(Counter(name_list))
            #print(self.dir_list)
            df_kaoqin = pd.read_excel("./kaoqin.xlsx")
            names = []
            days = []
            for i,j in self.dir_list.items():
                names.append(i)
                days.append(j)
            zip_tuples = zip(names,days)
            df_data = pd.DataFrame(zip_tuples,columns=["姓名","出勤天数"])
            self.df_merge = pd.merge(left=df_kaoqin,right=df_data,left_on="姓名",right_on="姓名")
            #print(self.df_merge)
            self.tableWidget_show_datas.setRowCount(len(self.df_merge))
            row_num = 0
            for idx,row in self.df_merge.iterrows():
                for col,value in enumerate(["姓名","考勤组","出勤天数"]):
                    self.tableWidget_show_datas.setItem(
                        row_num,col,
                        QTableWidgetItem(str(row[value]))
                    )
                row_num += 1
        except Exception as e:
            self.show_msgs(f"请正确选择文件！{e}")

    def depart_query(self):
        try:
            depart_text = self.comboBox_department.currentText()
            #print(depart_text)
            df_select = self.df_merge.loc[self.df_merge["考勤组"] == depart_text]
            self.tableWidget_show_datas.setRowCount(len(df_select))
            row_num = 0
            for idx,row in df_select.iterrows():
                for col,value in enumerate(["姓名","考勤组","出勤天数"]):
                    self.tableWidget_show_datas.setItem(
                        row_num,col,
                        QTableWidgetItem(str(row[value]))
                    )
                row_num += 1
        except Exception as e:
            self.show_msgs(f"请先执行全表统计后再尝试部门查询操作！{e}")

    def close(self):
        path = ["./kaoqin.xlsx"]
        for i in path:  
            if os.path.exists(i):
                os.remove(i)
        sys.exit()

if __name__ == "__main__":

    #定义需要删除行内所包含的字符内容set_string列表
    set_string = ['打卡无效:管理员设置上班后1小时才能打下班卡',
    '打卡无效:已经打过卡了，上班时间以最早打卡时间为准','打卡无效:今日休息，打卡需申请',
    '打卡无效:已经打过卡了，下班时间以最晚打卡时间为准','打卡无效:当前不在可打卡的时间范围，请和管理员联系',
    '打卡无效:此记录已被更新','打卡无效:未到最早上班时间，请在01:00后打卡','打卡无效:下班打卡时间已过',
    '迟到','早退','请假','加班','打卡无效:管理员设置上班后8小时才能打下班卡']

    app = QApplication(sys.argv)
    job = Attendance_Statttistics(set_string)
    sys.exit(app.exec_())