1. Development background

During this year's epidemic, the teaching methods of many colleges and universities have changed from offline to online. With this move, the attendance mode of class committee has also changed greatly.
In the traditional teaching method, the class committee only needs to call the roll before the class starts and summarize it every week to complete the attendance work. However, limited to online teaching, the attendance class committee can only ask for the attendance form from the teacher after each class and compare the attendance list to complete the attendance task of a class. In addition, due to the disunity of naming, the workload of the attendance class committee is increased indirectly.

Name column in attendance table	Name column in attendance sheet
10001 + Zhang San	Zhang San
10002 Li Si	Li Si
10003 - Wang Wu	Wang Wu
10004_ Zhao Liu	Zhao Liu
10005 Li Qi	Li Qi

After receiving the attendance form from the teacher, the attendance class committee will first use Excel to separate the name column of the attendance table, and then copy the name column of the attendance sheet into the attendance table, and highlight it with duplicate values. However, due to the different format of the name column of attendance table, excel only supports fixed length or fixed symbol segmentation, and often one third of the content needs to be manually sorted.

On average, it takes at least 5-8 minutes for a course to complete the statistics. There are at least two courses a day, and a maximum of five courses. It takes 15-30 minutes a day and about 2-3 hours a week on the necessary attendance tasks, and two-thirds of the time is spent on manual sorting. Is there a faster and more convenient way to complete this mechanical work?

Note: this background may be different from that of some colleges and universities. Some netizens may have a better online course platform or attendance mode. This paper only talks about technology, not other meaningless discussions

2. Implementation process

-Data reading

Import related Library

from os import listdir
from numpy import array
from numpy import loadtxt
from pandas import read_excel
from re import split
import tkinter as tk
from tkinter.filedialog import askdirectory

read file

def getFileName(totalFileDir,out2):#'D: \ "online class attendance record \ \ tmp '
    attenFileName = 'Attendance Sheet'  # 
    listFile = listdir(totalFileDir)  # os.listdir : output all files and folders under the specified file path, type: list
    xlsxList = []
    csvList = []
    leaveTxt = []
    for file in listFile:
        if attenFileName in  file:  #This judgment is to find out the attendance sheet. Note: attendance sheet! =Attendance sheet
            attenFileName = totalFileDir + '\\' + file
            out2.delete(0.0, tk.END)  # TK: clear the original content
            out2.insert('end',totalFileDir + '\\' + file+'Attendance sheet read successfully!')
        elif '.xlsx' in file and 'Attendance Sheet' not in file:
            xlsxList.append(totalFileDir + '\\' + file)  #Put all xlsx in the list container
            out2.insert('end',totalFileDir + '\\' + file+'Attendance table read successfully!')
        else:
            out2.insert('end','%s Not supported".csv"perhaps".xlsx"format!'%(file))
    return attenFileName,xlsxList,leaveTxt  #In the future, open the interface of csvList and support the format of csv

-Data slicing and comparison matching

Get the student column of attendance table and slice with regular expression. At first, I wanted to use the built-in functions such as strip or replace, which are provided by python, but only support a single character, so the split function of regular expression is used.

def getStudName(attenFileName,xlsxList,leaveTxt,out2):
    out2.delete(0.0, tk.END)  # TK: clear content

    #Read attendance table to get students' names
    stuTable = read_excel(attenFileName,index_col=0)
    dataFroam = stuTable.iloc[2:,1]
    stuNameList = list(array(dataFroam)) #Get student list

    #Leave list read:
    loadLeave = str(loadtxt(leaveTxt,encoding='utf-8',dtype='str',delimiter=','))
    todayLeave = split(r'2020/5/9(.*?)', loadLeave)[-1]

    outDict = {}  # Used to store {table name: truant name}

    for xlsx in xlsxList:
        className = xlsx.split('\\')[-1]
        tmpNameList = stuNameList.copy()

        teachTable = read_excel(xlsx)
        arrTable = array(teachTable).reshape(1,-1)
        strTable = str(arrTable)
        splitTable = split(r'(?:-|－|\s|\+|\d|_)\s*',strTable)

        for name in stuNameList:
            if any(name in s for s in splitTable):
                tmpNameList.remove(name)
                outDict[className] = tmpNameList

    out2.insert('end',outDict,todayLeave)

-Graphic development

Instantiate TK object and set basic properties

#instantiation 
root = tk.Tk()
root.title('Easy attendance    V 1.0.0')
root.geometry('800x500')  #Set size title

lab1 = tk.Label(root,text = 'Please select attendance path:',font = ('Song style',12),width = 15,height = 2)  #assembly
lab1.place(x=3,y=2)  #Place the label,

Set the file read directory, which can be changed manually

def selectPath():
    # Select the file path_ Receiving file address
    path_ = askdirectory()
    # Replace / in the absolute file address by the replace function to make the file readable by the program
    path_ = path_.replace("/", "\\\\")
    # Path set path_ Value of
    path.set(path_)

Set the interface button size and other properties

path = tk.StringVar()
ent1 = tk.Entry(root,textvariable = path,bd = 3,xscrollcommand = 100,font=('Song style',15),width = 57)  #Get user input component to box
ent1.place(x=125,y=11)

but1 = tk.Button(root,text = 'Choose the path',font = ('Song style',12),width = 10,height = 1,command = selectPath)  #Click component
but1.place(x=700,y=11)

out2 = tk.Text(root,width=86,height=22,font=('Song style',13))
out2.place(x=10,y=102)

Bind click event to receive function output

def onClick1():#Click events for but1
    s,k,l= getFileName(ent1.get(), out2)
    return s,k,l
but1 = tk.Button(root,text = 'Read data',font = ('Song style',20),command=onClick1,width = 10,height = 1)
but1.place(x=220,y=40)

def onClick2():#Click events for but2
    s, k, l = getFileName(ent1.get(), out2)
    getStudName(s,k,l,out2)

but2 = tk.Button(root,text = 'Start execution',font = ('Song style',20),command=onClick2,width = 10,height = 1)
but2.place(x=420,y=40)

root.mainloop()

3. Operation effect

-Interface

-Choose the path

It is recommended to create a special directory in which the attendance sheets and attendance tables are placed, and the results will be moved out.

-Read data

**Select the path and click read data

-Final effect

4. Precautions

1. The key word for reading attendance sheet is "attendance sheet", as long as the file name contains these three words; however, it should be noted that other file names other than attendance sheet should not contain this keyword.

attenFileName = 'Attendance Sheet'
    for file in listFile:
        if attenFileName in  file:

1. There are other ways to use the dead list.

    loadLeave = str(loadtxt(leaveTxt,encoding='utf-8',dtype='str',delimiter=','))
    todayLeave = split(r'2020/5/9(.*?)', loadLeave)[-1]

1. For more convenience, I directly package this into. exe (it is really large, 300+MB). After creating the shortcut, you can directly double-click to run it (you need to install the pyinstaller library to compile).
   Knowledge is shallow, talent is shallow, there is still a lot to be improved, I hope you will give me your advice, thank you.