openpyxl——多表合并以及数据汇总

BeApostle

已于 2023-02-23 16:29:52 修改

阅读量1.7k

点赞数 1

分类专栏： Python自动化办公案例文章标签： python 自动化

于 2023-02-23 15:58:08 首次发布

本文链接：https://blog.csdn.net/weixin_45998954/article/details/129182769

版权

Python自动化办公案例专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1.前言

日常工作中可能会遇到需要将多个表格的数据汇总到一张表格中，并对汇总表里面的数据进行汇总计算

2.需求

T31.xlsx文件中有两个子表，分别是“一月账单”和“二月账单”

先需要将这两个表格中的数据汇总，存成一个新的文件，并对数据进行汇总计算

预计的结果表如下图所示

3.处理思路

为了不做多余的表头写入，首先从第一个表格中提取出表头用列表存储好

# 导入模块
from openpyxl import load_workbook,Workbook

# 打开表格
wb=load_workbook('T31.xlsx')
# 根据表名取表,先打开一月账单
ws=wb['一月账单']

# 获取表头
list1=[]
for i in ws[1]:
    list1.append(i.value)

创建一个新表，先将表头写入，因为要循环遍历多个表格但是表头只需要写入一次

# 创建新表
wb2=Workbook()
ws2=wb2.active

# 先将表头写入新表
ws2.append(list1)

开始循环遍历“一月账单”表和“二月账单”表，并将数据添加进新表中

# 遍历表格获取数据
for row in ws.iter_rows(min_row=2,values_only=True):
    # 判断是否为空行，剔除掉空行
    if not all(i is None for i in row):
        ws2.append(row)

# 根据表名取表,再打开二月账单
ws_two=wb['二月账单']
for row2 in ws_two.iter_rows(min_row=2,values_only=True):
    # 判断是否为空行，剔除掉空行
    if not all(i is None for i in row2):
        ws2.append(row2)

注意：不排除表格中存在中间有空白行的情况，所以需要将空白行进行剔除

语法解读：if not all(i is None for i in row):

for i in row，对row中的数据进行遍历，依次存储进i中，判断i是否是None类型，前置all判断所有的i都是否是None，如果是空白行，all(i is None for i in row)会返回一个True

这时候if not all(i is None for i in row): 相当于是 if not True:，所以if判断不会成立，不会执行下面的写入操作，于是就达到了一个剔除空白行的作用。

当两个表格的数据都汇总之后，对汇总表的数据进行计算

sum=0
n=2 # 定义n变量记录行数
for row3 in ws2['B']:
    if row3.value=='消费金额':
        continue
    sum+=int(row3.value)
    n+=1

这里我定义一个变量sum用来存储最后的计算结果，变量n赋值初始值为2，因为是从第2行开始计算，for循环遍历新表中的B列，用if来跳过表头单元格，最后进行累加

因为最后写入总和的时候要在最大数据行的下一行进行写入，所以设置变量n来计算行数的变化

计算得到结果之后，需要将数据写入表格

# 循环结束后,由n和字母B拼接出要写入计算结果的单元格坐标
ws2['A'+str(n)].value='总和'
ws2['B'+str(n)].value=sum

直接利用单元格坐标来写入对应的数据即可

4.可优化模式

当一个xlsx文件中存在很多个子表时，很显然，利用表名来获取表格对象是行不通的，工作量会异常庞大，这时候我们可以利用工作簿对象的sheetnames属性来获取出文件中所有的表名列表，然后循环遍历这个列表即可得到单个的表名

sheet_list=wb.sheetnames
for name in sheet_list:
    ws=wb[name]

累加计算数据结果的时候也可以按行来获取数据

# 循环遍历新表,以行获取,对消费金额一列的数据进行求和
sum=0 # 定义sum变量存储结果
n=2 # 定义n变量记录行数
for row3 in ws2.iter_rows(min_row=2):
    sum+=int(row3[1].value)
    n+=1

5.源代码

# 导入模块
from openpyxl import load_workbook,Workbook

# 打开表格
wb=load_workbook('T31.xlsx')
# 根据表名取表,先打开一月账单
ws=wb['一月账单']
print(wb.sheetnames)

# 获取表头
list1=[]
for i in ws[1]:
    list1.append(i.value)

# 创建新表
wb2=Workbook()
ws2=wb2.active

# 先将表头写入新表
ws2.append(list1)

# 遍历表格获取数据
for row in ws.iter_rows(min_row=2,values_only=True):
    # 判断是否为空行，剔除掉空行
    if not all(i is None for i in row):
        ws2.append(row)

# 根据表名取表,再打开二月账单
ws_two=wb['二月账单']
for row2 in ws_two.iter_rows(min_row=2,values_only=True):
    # 判断是否为空行，剔除掉空行
    if not all(i is None for i in row2):
        ws2.append(row2)

# 循环遍历新表,以行获取,对消费金额一列的数据进行求和
# sum=0 # 定义sum变量存储结果
# n=2 # 定义n变量记录行数
# for row3 in ws2.iter_rows(min_row=2):
#     sum+=int(row3[1].value)
#     n+=1

sum=0
n=2 # 定义n变量记录行数
for row3 in ws2['B']:
    if row3.value=='消费金额':
        continue
    sum+=int(row3.value)
    n+=1

# 循环结束后,由n和字母B拼接出要写入计算结果的单元格坐标
ws2['A'+str(n)].value='总和'
ws2['B'+str(n)].value=sum


# 保存文件
wb2.save('T32.xlsx')