Python进行Excel数据统计

前言

貌似大家中使用到Excel数据统计和处理比较多。前两天GF发来指示，帮忙统计2019年的全年（1-12月）、全体员工（每月可能有新入职员工）的养老保险、医疗保险、失业保险、工伤和生育共五项中的公司缴纳部分。需要进行全年累加，最后放到区域公司发来的汇总表中。貌似不难，但也需要理一理思路。

示例数据

示例的原始数据如下所示（12张表格）。

需填的表格如下

前期思路

（1）python Excel的读写操作
（2）全体员工的数据提取统计操作，此处适合使用字典类型（字典为可变数据类型）。字典的key为员工的名字，对应的value为一个含有5个元素的列表（后续需要累加更改）
（3）python写Excel的时候，需要进行人员的判断匹配，因为最终的汇总表人员众多

实操

遇到的问题和解决方案

（1）如何提取数据？ 原始数据中，公司含有不通的部门，每个部门又有相应的汇总栏目，影响数据提取。通过观察，A列数据的序号，只在对应员工栏目有数值的序号标识，而汇总栏目为空值。因此可以通过判断A列是否是数值来提取员工信息。
（2）存在None值。 对应的员工信息中，存在空值，例如吴三的医疗保险部分。为不影响后续的累加运算，需要进行数据的处理，把空值转换为数字0。
（3）数据提取。 由于GF的示例数据表格中养老保险、医疗保险、失业保险、工伤和生育是使用公式计算的，字典的键值对的值（value）提取的结果为元素为 '=ROUND(D4*19%,2)'的字符串列表，无法进行数值的累加操作。
（a）解决方案A。同样提取对应列的数据，从而计算出对应的保险的数据，由于计算结果为浮点数，最终的12个月累加结果存在小尾数问题，并且结果与手动结果不匹配，失败告终。
（b）解决方案B。先对Excel表格进行处理，清除掉所有的公式，只保留数据。使用的方法为Excel的VBA编程，具体参考文献[1] [2].
（4）列表对应项相加。 提取的结果每个sheet提取出一个字典。由于字典的值（value）为列表，操作时需要对value列表累加求和。python中列表的加法（+）为拼接操作，不像MATLAB中的矩阵操作。此时需要可以使用Numpy库进行数组操作，或者使用循环进行对应项相加。在此本文使用循环。

最终结果

（1）测试输出
张一: [20900, 840, 8400, 65, 600]
（2）写表格数据

实现代码

#By WDL 2020-4-27
import openpyxl

#单元格数据判断函数，剔除空单元格中读取的空值
def valuechange(sth): #判断是否为None
    if sth==None:
        sth=0
    return sth

#Excel表格读取函数
def excelread(wb,sheetname):#wb工作簿 sheetname表格名称
    ws=wb[sheetname] #打一个工作表
    # 读取数据，把excel中的一个sheet按行读取出来，存入一个二维的list
    total_list=[]
    for row in ws.rows:
        row_list=[]
        for cell in row:
            row_list.append(cell.value)
        total_list.append(row_list)
    # 返回读取的数据列表，每个元素为sheet中的一行
    return total_list

#表格数据处理函数,数据提取
def procSheet(wb,sheetname): #wb工作簿 sheetname表格名称
    total_list=excelread(wb,sheetname)
    #利用字典存数据
    infos={}
    for term in total_list:
        if isinstance(term[0],int): #判断标号是否是数值
            # 使用sheet中列的标号 判断元组中每个单元格的索引
            # term[2]对应姓名
            #term[4] 养老，term[8] 失业，term[12] 医疗，term[16] 工伤，term[17] #生育
            infos.setdefault(term[2], []).extend([valuechange(term[4]),valuechange(term[8]),\
                                                  valuechange(term[12]),valuechange(term[16]),valuechange(term[17])])
            #以上为 一键 对列表的字典，如果键term[2]不存在，添加键值对，只默认为空列表[] 随后使用extend()方法扩展列表
    return infos

#----------------数据提取操作-------------
wb=openpyxl.load_workbook("XXX公司应缴社会保险明细表（2019.1-12）.xlsx")
data=procSheet(wb,"2019.1") #初始值
print(data)
sheetnames=["2019.{}".format(str(i)) for i in range(2,13)]
for sheetname in sheetnames:
    infos=procSheet(wb,sheetname)
    for name in infos:
        #列表对应项累加
        if name in data:
            for i in range(len(data[name])):
                data[name][i]+=infos[name][i]
        else:
            data[name]=infos[name]
print(data) #最终的统计数据
print("张一:",data["张一"])
worker=set(data)
print(worker) #统计XXX公司的所有员工

#------------Excel写操作--------------
#打开要写数据的工作表
workbook=openpyxl.load_workbook("XXX区域2019年度年薪人员人工成本统计表.xlsx")
worksheet=workbook["附表二、2019年度人工成本汇总"]

#对每个单元格进行格式设置 总共385行
for eachCommonRow in worksheet.iter_rows(min_row=1,min_col=1,max_row=400,max_col=47):# 每一行为元组类型
    #使用sheet中列的标号 判断元组中每个单元格的索引
    if eachCommonRow[8].value in data:#姓名是否在字典中 第I列对应索引9-1=8
        eachCommonRow[37].value = data[eachCommonRow[8].value][0] #养老保险
        eachCommonRow[38].value = data[eachCommonRow[8].value][2] #医疗保险
        eachCommonRow[39].value = data[eachCommonRow[8].value][1]  # 失业保险
        eachCommonRow[40].value = data[eachCommonRow[8].value][3]  # 工伤保险
        eachCommonRow[41].value = data[eachCommonRow[8].value][4]  # 生育保险
#保存
workbook.save("XXX区域2019年度年薪人员人工成本统计表.xlsx")

总结

在实践中不断的修改完善，善用搜索能力，不断总结提高，减小工作量。最后希望对大家有帮助。

参考文献
[1]两种excel 去掉公式保留数值的方法. http://www.ittribalwo.com/article/1369.html
[2]excel表格问题 怎么清除公式保留数据？https://www.zhihu.com/question/21638771