from openpyxl import load_workbook
from copy import copy
# 获取workbook
wb = load_workbook('数据源假设3个表.xlsx')
# 获取每一个worksheet
ws1 = wb['sheet1']
ws2 = wb['sheet2']
ws3 = wb['sheet3']
# 获取每一个worksheet数据
data = []
for row in ws1.iter_rows(min_row=2):
    # temp_row = []
    # for cell in row:
    #     temp_row.append(cell.value)
    # [cell.value for cell in row]
    data.append([cell.value for cell in row])

for row in ws2.iter_rows(min_row=2):
    data.append([cell.value for cell in row])

for row in ws3.iter_rows(min_row=2):
    data.append([cell.value for cell in row])
# 合并这些数据
#获取关键键
names = [col.value for col in ws1['A']]

# 汇总每个一个人销量 [[],[],]
total = []
# 添加表头
header = ['表头1','表头2','表头3']
total.append(header)
# 复制单元格的样式
font = copy(ws1['A1'].font)
border = copy(ws1['A1'].border)
fill = copy(ws1['A1'].fill)
alignment = copy(ws1['A1'].alignment)

for name in names[1:]:
    sum1 = 0
    sum2 = 0
    for row in data:
        if row[0] == name:
            # if row[1] == None:
            #     row[1] = 0
            row[1] = row[1] if row[1] else 0
            row[2] = row[2] if row[2] else 0
            sum1 += row[1]
            sum2 += row[2]
    total.append([name,sum1,sum2])

# 创建一个新的workseet,写入汇总后的数据
#在前面的3个sheet表的基础上创建1个汇总的表
ws = wb.create_sheet('前面3个sheet的汇总表')
for i,row in enumerate(total):
    # 第一行header单独处理
    if i == 0:
        for j in range(3):  # 循环复制A1,B1,C1
            ws.cell(row=1,column=j+1).value = row[j]
            ws.cell(row=1,column=j+1).font = font
            ws.cell(row=1,column=j+1).border = border
            ws.cell(row=1,column=j+1).fill = fill
            ws.cell(row=1,column=j+1).alignment = alignment
    else: # 其他行，直接赋值列表
        ws.append(row)

wb.save('新命名的工作簿.xlsx')

说明:本文章为大熊自动化办公课程的学习笔记