Python使用简易的xlrd和xlwt库实现会议考勤文档匹配

前言导出腾讯会议参会名单制作花名册代码实现导入库定义init使用filedialog获取文件路径将所有参会人员以及其在会时间以dict格式保存在list里将所有学生姓名与序号同上保存至list中的dict里同时返回记录的列数剔除在会时间少于输入值的学生，进行判断是否考勤并输出文档 全部代码

前言

最近有一个会议需要我进行考勤登记，而且预期在以后这个会议会经常召开，一场会下来大几十个人。所以就给自己写了个将腾讯会议导出的参会人员名单的内容与自己手上的花名册进行比对并记录考勤的代码，理论上这些功能都能通过excel的vlookup函数实现，但如果可以更方便那当然还是写代码了。本文仅仅做一个记录作用。

导出腾讯会议参会名单

制作花名册

代码实现

导入库

我为了自己方便使用，导入了tkinter库的filedialog函数，实际使用可以不用这个

from tkinter import filedialog import xlrd import xlwt import json from xlutils.copy import copy

定义init

def __init__(self): self.name_list = [] self.column = "" self.all_list = []

使用filedialog获取文件路径

def choose_files(self): global interview_path global name_list_path print('请选择腾讯会议参会人员表') interview_path = filedialog.askopenfilename(filetypes=[('excels', '*.xls;*.xlsx')]) print('请选择记名册') name_list_path = filedialog.askopenfilename(filetypes=[('excels', '*.xls;*.xlsx')]) self.interview_book(interview_path) self.column = self.namelist_book(name_list_path)

将所有参会人员以及其在会时间以dict格式保存在list里

def interview_book(self,interview_path): global interview interview_workbook = xlrd.open_workbook(interview_path) interview = interview_workbook.sheets()[0] if interview.cell_value(0,2) == '会议主题': print('正在导入会议：' + interview.cell_value(1,2)) print('会议号：' + interview.cell_value(1,3)) else: print("您选择的文件有误，不是腾讯会议参会人员表") return nrows = interview.nrows x = 1 while x < nrows: name_dict = {} name = interview.cell_value(x,4) time = interview.cell_value(x,7) if len(time) != 0: time2 = time.split(":") time3 = int(time2[0]) * 3600 + int(time2[1]) * 60 + int(time2[2]) else: time3 = 0 name_dict['name'] = name name_dict['time'] = int(time3) self.name_list.append(name_dict) x +=1

将所有学生姓名与序号同上保存至list中的dict里同时返回记录的列数

def namelist_book(self,namelist_path): global namelist global pages_num namelist_workbook = xlrd.open_workbook(namelist_path) sheet_names = namelist_workbook.sheet_names() i=0 for item in sheet_names: print("{}、{}".format(i+1,item)) i+=1 pages_num = int(input("将此次参会记录记录至哪一页")) pages_num = pages_num - 1 namelist = namelist_workbook.sheets()[pages_num] column = 0 while True: try: cell_content = namelist.cell_value(0, i) except: break i += 1 lennames = int(namelist.nrows) t = 1 while t < lennames: all_list = {} name = namelist.cell_value(t,0) all_list['name'] = name all_list['num'] = t self.all_list.append(all_list) t+=1 return i

剔除在会时间少于输入值的学生，进行判断是否考勤并输出文档

def analysis(self): old_excel = xlrd.open_workbook(name_list_path) new_excel = copy(old_excel) ws = new_excel.get_sheet(pages_num) ws.write(0,int(self.column),label='interview.cell_value(1,2)') ddl = int(input("请输入参会的最少及格时间（秒）")) for item in self.name_list: if item['time'] < ddl: self.name_list.remove(item) i = 0 while i < len(self.name_list): status = 0 for item in self.all_list: if item['name'] in self.name_list[i]['name']: ws.write(int(item['num']), int(self.column), label='是') status = 1 if status == 0: print("{}没有找到".format(self.name_list[i]['name'])) i+=1 try: new_excel.save('new_file.xls') except: print("保存失败，请检查是否已关闭该表格")

全部代码

from tkinter import filedialog import xlrd import xlwt import json from xlutils.copy import copy class Imp(): def __init__(self): self.name_list = [] self.column = "" self.all_list = [] def choose_files(self): global interview_path global name_list_path print('请选择腾讯会议参会人员表') interview_path = filedialog.askopenfilename(filetypes=[('excels', '*.xls;*.xlsx')]) print('请选择记名册') name_list_path = filedialog.askopenfilename(filetypes=[('excels', '*.xls;*.xlsx')]) self.interview_book(interview_path) self.column = self.namelist_book(name_list_path) def interview_book(self,interview_path): global interview interview_workbook = xlrd.open_workbook(interview_path) interview = interview_workbook.sheets()[0] if interview.cell_value(0,2) == '会议主题': print('正在导入会议：' + interview.cell_value(1,2)) print('会议号：' + interview.cell_value(1,3)) else: print("您选择的文件有误，不是腾讯会议参会人员表") return nrows = interview.nrows x = 1 while x < nrows: name_dict = {} name = interview.cell_value(x,4) time = interview.cell_value(x,7) if len(time) != 0: time2 = time.split(":") time3 = int(time2[0]) * 3600 + int(time2[1]) * 60 + int(time2[2]) else: time3 = 0 name_dict['name'] = name name_dict['time'] = int(time3) self.name_list.append(name_dict) x +=1 def namelist_book(self,namelist_path): global namelist global pages_num namelist_workbook = xlrd.open_workbook(namelist_path) sheet_names = namelist_workbook.sheet_names() i=0 for item in sheet_names: print("{}、{}".format(i+1,item)) i+=1 pages_num = int(input("将此次参会记录记录至哪一页")) pages_num = pages_num - 1 namelist = namelist_workbook.sheets()[pages_num] column = 0 while True: try: cell_content = namelist.cell_value(0, i) except: break i += 1 lennames = int(namelist.nrows) t = 1 while t < lennames: all_list = {} name = namelist.cell_value(t,0) all_list['name'] = name all_list['num'] = t self.all_list.append(all_list) t+=1 return i def analysis(self): old_excel = xlrd.open_workbook(name_list_path) new_excel = copy(old_excel) ws = new_excel.get_sheet(pages_num) ws.write(0,int(self.column),label='interview.cell_value(1,2)') ddl = int(input("请输入参会的最少及格时间（秒）")) for item in self.name_list: if item['time'] < ddl: self.name_list.remove(item) i = 0 while i < len(self.name_list): status = 0 for item in self.all_list: if item['name'] in self.name_list[i]['name']: ws.write(int(item['num']), int(self.column), label='是') status = 1 if status == 0: print("{}没有找到".format(self.name_list[i]['name'])) i+=1 try: new_excel.save('new_file.xls') except: print("保存失败，请检查是否已关闭该表格") f = open('fail.txt','w') for item in export_fail: f.write(item+"\n") f.close() course = Imp() course.choose_files() course.analysis()