很多时候,存在着一个excel有很多个sheet需要汇总,然后汇总完之后又需要分类求和。当然,我们第一反应是excel的数据透视表,当然,数据透视表也是ok的,但是单条件的数据透视还好,如果是多条件,比如说同一个名称有多个不同的单位,需要对其不同的名称,单位进行汇总,这样用数据透视表就不怎么方便了。那么用Python试试,会不会更简单呢?
![d448b9b04f3bdf2b1fc05ec9afd85221.png](https://img-blog.csdnimg.cn/img_convert/d448b9b04f3bdf2b1fc05ec9afd85221.png)
一.样本分析
假设我要处理的文件如下,一个工作簿中有多个工作表,有的是3列,有的是4列,而且表头顺序也不一致,同一名称可能存在不同单位。
sheet1:
![e443eef4cac90ada8ecd3ce837ea3a91.png](https://img-blog.csdnimg.cn/img_convert/e443eef4cac90ada8ecd3ce837ea3a91.png)
sheet2:
![467cdfeb293d0bab457f23d63cd8171e.png](https://img-blog.csdnimg.cn/img_convert/467cdfeb293d0bab457f23d63cd8171e.png)
sheet3
![417ba3036b65c671a612d008bbad808b.png](https://img-blog.csdnimg.cn/img_convert/417ba3036b65c671a612d008bbad808b.png)
假设数据量很大,而且sheet的数量很多。我们需要遍历所有sheet,然后将表头满足我们需要的“代码”,“单位”,“数量”的三列弄出来。
二.代码实现过程
1.导入必要的模块
import pandas as pdfrom openpyxl import load_workbookimport numpy as npimport osimport PySimpleGUI as sg
2.写一个函数,用于获取满足条件的工作表,导出结果为List.并且截取工作簿的文件名
def func1(file_path): """ 作用:获取满足条件的工作表 :param file_path:路径,建议用绝对路径 :return:返回满足条件的工作表,以及当前工作簿的名称 """ file_name1 = os.path.split(file_path)[1] # 取后面的文件名+后缀 file_name2 = os.path.splitext(file_name1)[0] # 取文件名 print(file_name2) wb = load_workbook(file_path) # 打开工作簿 ws_list1 = wb.sheetnames # 获取工作簿的工作表名称 ws_list2 = [] # 收集可以汇总的sheet for ws in ws_list1: sheet = wb[ws] max_column = sheet.max_column # 计算最大列 head_list1 = [] # 用来收集表头,也就是收集第一行 for i in range(1, max_column + 1): head_list1.append(sheet.cell(1, i).value) # print(head_list1) # 打印表头 if '代码' in head_list1 and '数量' in head_list1 and '单位' in head_list1: ws_list2.append(ws) print(ws_list2) return ws_list2, file_name2
3.再写一个函数,用于判断导出路径data文件夹是否存在,如果不存在,则创建该文件夹
def func2(listdir): """ 用于验证当前目录的子文件夹是否存在 如果不存在,那就创建它 :param listdir: 文件夹名称 :return: 不用返回 """ a = os.getcwd() # 获取当前绝对路径 print(a) b = a + '' + listdir print(b) if not os.path.exists(b): # 如果路径不存在,那就创建它 os.mkdir(b)
4.最后一个函数,利用pandas实现多个sheet汇总,然后利用groupby实现分类汇总。
def func3(list2, file_name4): """ 将满足条件的工作表合并汇总 :param list2: 列表类型,代表满足条件的工作表 :param file_name4:文件名,代表原始文件的文件名 :return: """ data = [['', '', '']] df0 = pd.DataFrame(data=data, columns=['代码', '单位', '数量']) # 创建一个空白的DataFrame for ws in list2: df = pd.read_excel(file_name, sheet_name=ws) df2 = df[['代码', '单位', '数量']] # 抽取这三列 print(df2.shape) df0 = df0.append(df2) # 合并满足条件的工作表 df3 = df0.groupby(['代码', '单位']).sum() # 第一次分类汇总 file_name5 = './data/' + file_name4 + '(汇总合并版)' + '.xlsx' df3.to_excel(file_name5) # 保存到excel,但是有合并单元格 df4 = pd.read_excel(file_name5) # 第二次读取 file_name6 = './data/' + file_name4 + '(最终版)' + '.xlsx' df4.to_excel(file_name6, index=None) # 保存到excel,此时没有合并单元格 df5 = pd.read_excel(file_name6) # 第三次读取 column_list1 = df5['代码'].values.tolist() # 提取第一列的值,并且转换成列表 for i in range(1, len(column_list1)): # 从第二个数开始循环 if column_list1[i] is np.nan: # 如果为空 column_list1[i] = column_list1[i - 1] # 则填充上一个单元格的值 df5['代码'] = np.array(column_list1) # 给第一列整体赋值 df5 = df5.dropna() # 删除空单元格,此时第一列会被删除 df5.to_excel(file_name6, index=None) # 导出到excel,此时为最终版
5.依次调用各个函数即可。大概用时,一秒钟。
粗略验证一下A01,满足条件的应该只有sheet1和sheet2,算一下,sheet2均为“A01,单位为m”,所以“A01,m”共有8个,sheet1中的“A01,个”刚好是1+5=6,也没有问题。
![8916d39393d32b93e4dd8b45fd051d9d.png](https://img-blog.csdnimg.cn/img_convert/8916d39393d32b93e4dd8b45fd051d9d.png)
三、封装函数到exe
利用pysimplegui可以实现py文件运行可视化窗口。
利用pyinstaller可以将py文件打包成exe文件。
1.pyinstaller可视化界面
if __name__ == '__main__': menu_def = [['&菜单', ['&退出']], ['&帮助', ['使用说明', '关于']]] layout = [ [sg.MenuBar(menu_def, tearoff=True)], [sg.Text('选择文件'), sg.InputText(key='file_name'), sg.FileBrowse(button_text='选择文件')], [sg.Button('开始汇总'), sg.Button('退出')] ] window = sg.Window('自动汇总小助手', layout) # 利用布局创建窗口
2.添加事件
while True: event, values = window.read() # 读取窗口 if event in ('退出', None): # 第一步,加一个退出功能 break if event == '开始汇总': # 第二步,让选择文件按钮生效 file_name = window['file_name'].get() # 获取文件路径 ws_list, file_name3 = func1(file_name) # 挑选满足条件的工作表,返回满足条件的列表 func2('data') # 验证是否需要创建data文件夹 func3(ws_list, file_name3) # 开始执行自动汇总 sg.Popup('汇总已完成, 请到data文件夹下查看', ) # 增加成功提示 if event == '关于': sg.Popup('自动汇总小助手', '用于excel文件自动汇总', '表头需要包括代码,单位,数量三列', '当前版本:1.0Beta版', )window.close()
添加完成后,运行main.py文件,查看效果
![e71578dc3179ae33cfe59781e5dd0922.png](https://img-blog.csdnimg.cn/img_convert/e71578dc3179ae33cfe59781e5dd0922.png)
3.py文件转exe。
conda install pyinstaller # 终端命令行安装模块pyinstaller -F -w main.py # 打包py文件为exe文件,并且以无cmd窗口运行。
之后会在dist文件夹下生成一个main.exe文件。直接运行即可。
因为Py依赖的包比较大,所以最后的exe也有点大,200多M。
试了一下,汇总速度很快,1秒搞定。
需要Python源码或者成品客户端的,关注后,私信回复“excel快速汇总”即可获取相关文件。