利用python openpyxl库实现对多个excel工作簿的快速汇总

@[TOC]利用python openpyxl库实现对多个excel工作簿的快速汇总

---

前言

最近又参与了一个关于危险货物运输的项目，需要对调查表格进行汇总。收到了112个excel工作簿，分布于22个主文件夹中。本文基于python openpyxl库实现了上述112个excel工作簿的快速汇总，不到10秒钟快速结束战斗。

---

一、文件夹结构

一个主文件夹下包含着多个子文件夹，子文件夹里包含着exce工作簿，打开补充调研主文件夹，如下图所示。
打开金牛区文件夹，如下图所示。
打开某公司的excel工作簿，如下图所示。

二、思路

首先我们已经知道了文件夹的结构，同时通过观察每个工作簿，可以发现这些表格具有相同的表头、表尾，只是表身长度不同而已。那么我们就可以借助于Python的os库和openpyxl库来快速的解决这个问题！

首先借助于os库获取每一个Excel工作簿的地址。然后我们借助于openpyxl库来对任意一个表格进行读取，再新建工作部，写入读取的数据。最后再遍历读取的文件地址就实现了对所有表格的汇总。

对于表1，确定初始遍历行，向下进行遍历，同时对空行进行忽略，直到遍历到填表说明结束。

对于表2，由于表2是固定的高度和固定的宽度，所以对表2的处理相对比较方便，只需要通过对行和列进行遍历就可以对所有的单元格进行汇总。

对于表3，先判断应急预案和应急演练的所在单元格位置的行数，然后向下便利，同时，对于空行进行忽略，直到遍历到填表说明结束。

如此就实现了对所有工作簿中工作表的汇总。

三、代码

代码如下：

import os
from openpyxl import Workbook
from openpyxl import load_workbook
import time
start = time.time()

wb0 = Workbook()  # 新建工作簿
ws0 = wb0.active
ws0.append(
    ['管理单位', '填报人', '联系电话', '企业名称', '危货名称', '危货类别', '2018年运输量', '2019年运输量', '2020年运输量',
     '2021年1-8月运输量', '运输主要途经道路（成都市域内）', '出行类别', '流向情况', '出发地', '目的地'])  # 添加表头
ws0.title = '流量流向情况'
ws1 = wb0.create_sheet('安全培训情况')  # 新建工作表
ws1.append(
    ['管理单位', '填报人', '联系电话', '企业名称',
     '培训人员类别', '2018年开展培训次数', '2019年开展培训次数', '2020年开展培训次数', '2021年开展培训次数', '是否保留台账'])
ws2 = wb0.create_sheet('应急预案情况')
ws2.append(
    ['管理单位', '填报人', '联系电话', '企业名称', '应急预案名称', '最近修改日期'])
ws3 = wb0.create_sheet('应急演练情况')
ws3.append(
    ['管理单位', '填报人', '联系电话', '企业名称', '应急演练名称', '演练日期', '演练人数', '组织单位', '参与单位', '演练地点'])


def get_address(file_dir):
    i = 0
    for root, dirs, files in os.walk(file_dir):  # 利用os.walk()函数来遍历文件夹输出文件名
        for file in files:
            i += 1
            dir1 = os.path.join(root, file)  # 连接根目录和文件名
            # print(dir1)
            deal_excel(dir1)


def deal_excel(file_dir):
    print("当前打开文件地址为：" + file_dir)
    a0 = file_dir.split("\\", )[-2]  # 返回文件夹名
    print("文件所在的文件夹为：" + a0)
    wb = load_workbook(file_dir)  # 加载工作簿
    print(wb.sheetnames)
    ws = wb.active
    a = ws.cell(3, 2).value  # 填报人姓名
    b = ws.cell(3, 7).value  # 电话
    c = ws.cell(4, 2).value  # 企业名称
    list1 = [str(a0), str(a), str(b), str(c)]  # 存储表头
    max1 = ws.max_row  # 获取工作表最大行数

    # 以下处理表1
    for i in range(7, max1 - 34 + 1):  # 从第7行开始向下遍历
        list2 = []  # 存储行数据
        x = ws.cell(i, 1).value
        if x == '填写说明：':  # 判断是否到达“填写说明：”单元格所在行
            break
        else:
            if ws.cell(i, 1).value is None and ws.cell(i, 2).value is None and ws.cell(i, 3).value is None:
                continue  # 当遍历到空行时，利用continue啥也不做
            else:
                for j in range(1, 12):  # 遍历工作表行数据
                    d = ws.cell(i, j).value
                    list2.append(str(d))
            list3 = list1 + list2  # 与表头信息合并
            ws0.append(list3)  # 添加到新建工作簿活动工作表中

    # 以下处理表2
    for i in range(5, 9):
        list4 = []  # 存储行数据
        for j in range(13, 19):
            e = ws.cell(i, j).value
            list4.append(str(e))
        list5 = list1 + list4
        ws1.append(list5)

    # 以下处理表3第1部分
    for i in range(1, max1 + 1):
        x = ws.cell(i, 13).value
        if x != '应急预案名称':
            continue  # 从上下行进行遍历，判断第13列单元格的值是否为’应急预案名称‘，不是就继续向下。
        else:
            for j in range(i + 1, max1 + 1):
                x = ws.cell(j, 13).value
                if x == '填写说明：':  # 判断是否到达“填写说明：”单元格所在行
                    break
                else:
                    # 处理预案部分
                    list6 = []
                    f = ws.cell(j, 13).value
                    g = ws.cell(j, 14).value
                    if f is None and g is None:
                        continue  # 对空行进行排除
                    else:
                        list6.append(str(f))
                        list6.append(str(g))
                        list7 = list1 + list6
                        ws2.append(list7)

    # 以下处理表3第2部分
    for i in range(1, max1 + 1):
        x = ws.cell(i, 15).value
        if x != '应急演练名称':
            continue  # 从上下行进行遍历，判断第13列单元格的值是否为’应急演练名称‘，不是就继续向下。
        else:
            for j in range(i + 1, max1 + 1):
                x = ws.cell(j, 13).value
                if x == '填写说明：':  # 判断是否到达“填写说明：”单元格所在行
                    break
                else:
                    list8 = []
                    if ws.cell(j, 15).value is None and ws.cell(j, 16).value is None and ws.cell(j, 17).value is None:
                        continue  # 当遍历到空行时，利用continue啥也不做
                    else:
                        for k in range(15, 21):
                            h = ws.cell(j, k).value
                            list8.append(str(h))
                        list9 = list1 + list8
                        ws3.append(list9)


get_address(r"E:\补充调研")
wb0.save("汇总表.xlsx")
end = time.time()
print(end-start)

---

总结

运行代码后，得到如下所示汇总表。112个表格总共用时不到10秒，真来丝。