用Python读取考勤机卡式报表并处理成常规的行式报表格式

article/2025/6/28 22:54:04

1. 场景：

在做公司的临时工考勤工资表时，考勤机导出的excel为卡式报表，看很直观，但处理起来麻烦。所以做了一个odoo模块用于将卡式报表处理成更常规的适合财务统计的普通报表。语言Python,用到的模块为xlrd和xlwt。

2.先看效果

在这里插入图片描述
如图，左为卡式报表，右为用python处理后生成的常规财务报表。

3.使用的工具 xlrd,xlwt

xlrd为Python常用的读取excel表格的模块，xlwt为常用的写excel表格的模块，都具体较多的对excel格式，样式，行列数处理的函数。

关于xlrd和xlwt常用的方法介绍，可以看以下两篇博客：
1. https://www.jianshu.com/p/93a1a9a8749b
2. https://blog.csdn.net/zc666ying/article/details/105617072

4.xlrd读取卡式报表主要代码

        excel = xlrd.open_workbook(file_contents=base64.decodestring(self.excel))sheet_name_list = excel.sheet_names()sheet_obj = excel.sheet_by_name(sheet_name_list[0])nrows = sheet_obj.nrows # 总行数ncols = sheet_obj.ncols # 总列数# 逐行读取for line_num in range(0,nrows):start_row = -1name = Nonerow_values = sheet_obj.row_values(rowx=line_num) # listfor cell_value in row_values:if u'姓名' in str(cell_value):start_row = line_numif start_row < 0:continue# line_len = sheet_obj.row_len(rowx=start_row) # 行长度# 从卡式报表获取打卡信息cell_value = sheet_obj.cell_value(rowx=start_row, colx=1)for laborer in all_laborers:if laborer in cell_value:name = laborerif name == None:raise UserError('报表中出现临时工信息表中不存在的名字%s'%str(cell_value))check_records = []for l in range(1, 17):date = sheet_obj.cell_value(rowx=start_row+1, colx=l)check = sheet_obj.cell_value(rowx=start_row+2, colx=l)if date:check_records.append([int(date), check.split(' ')])else:check_records.append([date, check.split(' ')])for l in range(1, 17):date = sheet_obj.cell_value(rowx=start_row+3, colx=l)check = sheet_obj.cell_value(rowx=start_row+4, colx=l)if date:check_records.append([int(date), check.split(' ')])else:check_records.append([date, check.split(' ')])final_data[name] = check_records

5.xlwt写成常规行式报表主要代码

        """ step 4: 根据信息维护数据，做工资表excel """# 先画基本框架book = xlwt.Workbook()style = xlwt.XFStyle() # 创建一个样式对象font = xlwt.Font() # 为样式初始化字体font.bold = True # 粗体font.height = 20*8al = xlwt.Alignment()al.horz = 0x02 # 设置水平居中al.vert = 0x01 # 设置垂直居中style.alignment = alstyle.font = fontsheet = book.add_sheet('邻寻')headers = ['序号','姓名','联系电话','日期','上班','下班','上班','下班','工时','计薪方式','时薪','金额','金额小计','领款登记','复核']line_cr = 0 # 初始化行标# 1. 生成第一行标题,并写入列名sheet.write_merge(0, 0, 0, len(headers)-1, f'{self.month}月深圳仓邻寻临时工工时工资表', style)line_cr += 1for p in range(0,len(headers)):sheet.write(1, p, headers[p], style)line_cr += 1# 2. 生成数据num = 0for name,monthly_data in new_final_data.items():num += 1length = len(monthly_data)phone = ''hourly_wages,work_hours,wages_subtotal = 0,0,0# phone,hourly_wages,work_hours = '',0,0for d in casual_laborer:if name in d:phone,hourly_wages = d[1],d[3]sheet.write_merge(line_cr, line_cr+length-1, 0, 0, num, style)sheet.write_merge(line_cr, line_cr+length-1, 1, 1, name, style)sheet.write_merge(line_cr, line_cr+length-1, 2, 2, phone, style)sheet.write_merge(line_cr, line_cr+length-1, 13, 13, '', style)sheet.write_merge(line_cr, line_cr+length-1, 12, 12, '', style)# 格式转换和写入每行的数据for i in monthly_data:date = f"{self.year}/{self.month}/{str(i[0])}"sheet.write(line_cr, 3, date, style)if len(i[1]) not in (0,2,4):# sheet.write_merge(line_cr, line_cr+length, 4, 7,'打卡异常,请手动确认', style)sheet.write_merge(line_cr, line_cr, 4, 7,'打卡异常,请手动确认', style)elif len(i[1]) == 0:sheet.write(line_cr, 8, 0, style)sheet.write(line_cr, 14, 0, style)elif len(i[1]) == 2:sheet.write(line_cr, 4, i[1][0], style)sheet.write(line_cr, 5, i[1][1], style)work_hours = self.calc_work_hours(i[1][0],i[1][1])sheet.write(line_cr, 8, work_hours, style)sheet.write(line_cr, 14, work_hours, style)else:sheet.write(line_cr, 4, i[1][0], style)sheet.write(line_cr, 5, i[1][1], style)sheet.write(line_cr, 6, i[1][2], style)sheet.write(line_cr, 7, i[1][3], style)work_hours = self.calc_work_hours(i[1][0],i[1][1],i[1][2],i[1][3])sheet.write(line_cr, 8, work_hours, style)sheet.write(line_cr, 14, work_hours, style)sheet.write(line_cr, 9, '时薪', style)sheet.write(line_cr, 10, hourly_wages, style)sheet.write(line_cr, 11, work_hours*hourly_wages, style)line_cr += 1