使用Python从excel中抽取数据并生成工资台账

【一、提出问题】

公司人事部门每年6月都要对去年每个员工的工资进行统计合算，计算每个员工去年的工资合计，用于确定每名员工今年的社保及公积金基数，数据表格格式如下：

而在实际工作中，工资表是每月生成的，即每月的工资都在一个单独的excel表中，每个员工的工资放在1-12月的12个月excel文件中。且每个员工都在不同的sheet中，用于区别所在部门。如下表所示：

从全年12个月的excel中去复制粘贴不仅工作量大，而且在员工有离职、新增或部门变动的情况下，很容易出现错行的情况。如XX部的XX员工在7月份调动到其它部门，我们在复制8月份的表时，就很有可能把下一面XXX员的数据复制后粘贴到XX员工那一行，造成数据错误。
使用python的openyxl组件，可以轻松的对excel表中的数据进行分析查询和提取，并在新的excel中生成我们想的合并数据。

【二、关于openyxl组件】

openpyxl是一个强大的Python库，用于处理Excel文件，可以方便的读取、编辑和创建Excel工作簿和工作表。无论是需要自动化处理大量数据，还是创建漂亮的报告，openyxl都能轻松实现。
第一次使用openpyxl,需要先安装。在命令行中运行：

pip install openpyxl
Openpyxl的基本使用方法：

import openpyxl#导入组件

#打开指定excel文件
hzb = openpyxl.load_workbook('e:/2023年工资表/XXX公司2023年度工资汇总表.xlsx')
#打开指定的工作簿
hzb_sheet=hzb.worksheets[0]
#修改指定工作簿中的某一行列的内容
hzb_sheet.cell(row=4,column=1).value='111111'
# 保存修改后的文件
hzb.save(filename='文件名.xlsx')

【三、实现思路】

1、遍历12个月的工资表
2、在每月的工资表中遍历每个工作簿，也就是每个部门
3、在每个工作捕中，根据员工的工号，提取姓名和当月应发工资（计算社保是以应发工资为基准的）
(1)遍历汇总表中的工号
①如果某个工号已经汇总表中，则把应发工资数添加到员工所在一行的所在月度列中。
②如果这个工号不在汇总表中（说明是本月入职员工），则在已存在员工的后面添加上一行，则把应发工资数添加到所在月度列中。

【四、实现代码】

import openpyxl#导入组件

#打开指定excel文件
hzb = openpyxl.load_workbook('e:/2023年工资表/XXX公司2023年度工资汇总表.xlsx')
#打开指定的工作簿
hzb_sheet=hzb.worksheets[0]
hzb_curmaxrow=4
hzb_maxrow=200#设置员工最大数


def getHzbRow(_gh):
    res=hzb_curmaxrow
    for r in range(4,hzb_maxrow+1):
        hzb_gh = hzb_sheet.cell(row=r, column=1).value
        if hzb_gh==_gh:
            return r
    return res



for i in range(1,13):#遍历1月至12月
    #打开某月的工资表，参数1为文件名，参数2为读取的试，如data_only=true,表示表格中有计算公式时，取结果值，而不是公式
    #这个参数默认为false,如不加这个参数，对一些通过在excel表中计算的值显示的是计算公式，如=sum(A2:E2)等
    cur_gzb = openpyxl.load_workbook(f'e:\\2023年工资表\\XXX公司2023年{i}月工资表.xlsx',data_only=True)
    for sht in cur_gzb.sheetnames:  # 因为不同部门的员工在不同的sheet的，故要每月工资表中的对所有sheet进行遍历
        cur_sht = cur_gzb[sht]  # 取得sheet
        cur_row = 5  # 每个sheet的前4行为表头，从第5行开始取工号、姓名及应发工资
        while cur_sht.cell(row=cur_row, column=3).value != "合计":  # 循环每行，直到取得的姓名为合计说明本sheet数据结束
            cur_gh=cur_sht.cell(row=cur_row, column=1).value
            cur_xm = cur_sht.cell(row=cur_row, column=3).value
            cur_yfs = cur_sht.cell(row=cur_row, column=15).value
            irow=getHzbRow(cur_gh)
            if irow==hzb_curmaxrow:
                hzb_curmaxrow+=1
            hzb_sheet.cell(row=irow, column=1).value = cur_gh  # 添加工号
            hzb_sheet.cell(row=irow, column=2).value = cur_xm  # 添加姓名
            hzb_sheet.cell(row=irow, column=i + 2).value = cur_yfs  # 添加应发数，1月在第三列,即n月在n+2列

            cur_row+=1


# 保存修改后的文件
hzb.save(filename='e:/2023年工资表/XXX公司2023年度工资汇总表.xlsx')

【五、运行效果】

运行项目后，数据会自动汇总到e:/2023年工资表/XXX公司2023年度工资汇总表.xlsx中，打开这个excel表，会看到数据已经汇总好了：

从表中可以看也，实现了我们对每名员工的工号、姓名及每月应发工资的自动提取。对年中离职的员工，其工资也只统计到其离职的一月，对于年中新入职的员工，其工资也只从入职时显示，避免的从excel表手工复制、粘贴的错误发生。
其实利用VBA，也可以实现对上述功能的实现，有兴趣的朋友也可以在excel表中添加宏，用VBA来实现数据汇总的功能，原理上是一样的。