from openpyxl import load_workbook
from copy import copy
# 獲取workbook
wb = load_workbook('數(shù)據(jù)源假設(shè)3個表.xlsx')
# 獲取每一個worksheet
ws1 = wb['sheet1']
ws2 = wb['sheet2']
ws3 = wb['sheet3']
# 獲取每一個worksheet數(shù)據(jù)
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])
# 合并這些數(shù)據(jù)
#獲取關(guān)鍵鍵
names = [col.value for col in ws1['A']]

# 匯總每個一個人銷量 [[],[],]
total = []
# 添加表頭
header = ['表頭1','表頭2','表頭3']
total.append(header)
# 復(fù)制單元格的樣式
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])

# 創(chuàng)建一個新的workseet,寫入?yún)R總后的數(shù)據(jù)
#在前面的3個sheet表的基礎(chǔ)上創(chuàng)建1個匯總的表
ws = wb.create_sheet('前面3個sheet的匯總表')
for i,row in enumerate(total):
    # 第一行header單獨處理
    if i == 0:
        for j in range(3):  # 循環(huán)復(fù)制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')

說明:本文章為大熊自動化辦公課程的學習筆記