excel 合并

最近整理收钱吧的账单明细，因为收钱吧限制每次最多只能导出 1 个月的明细，所以我需要合并这些零零碎碎的表格，方便在 excel 中做统计筛选

https://openpyxl.readthedocs.io/en/stable/tutorial.html

#!/usr/bin/env python3
# coding: utf-8
# https://hangj.cnblogs.com

import openpyxl
import os


summaries = []
details = []

path = '~/Downloads/收钱吧账单'
for cur_dir, dirs, files in os.walk(path):
    for f in files:
        if '对账单汇总' in f:
            summaries.append(cur_dir + '/' + f)
        elif '账单明细' in f:
            details.append(cur_dir + '/' + f)
summaries.sort()
details.sort()


def src2tar(src_path, target_sheet):
    wrkbk = openpyxl.load_workbook(src_path)
    sheet = wrkbk.active

    for row in sheet.iter_rows(min_row=1, min_col=1):
        target_sheet.append([cell.value for cell in row])



target_wb = {
    '2021': openpyxl.Workbook(),
    '2022': openpyxl.Workbook(),
    '2023': openpyxl.Workbook(),
}

for f in details:
    print(f)
    if '_2021-' in f:
        year = '2021'
    elif '_2022-' in f:
        year = '2022'
    elif '_2023-' in f:
        year = '2023'
    else:
        raise Exception("wrong year")
    src2tar(f, target_wb[year].active)
    target_wb[year].active.append([])

for k in target_wb:
    target_wb[k].save(f'~/Downloads/收钱吧账单/对账单明细_{k}.xlsx')