xlsx文件内容合并

xlsx文件内容合并

import os
from openpyxl import (
  load_workbook,
  Workbook
)

def merge_xlsx_files(
    xlsx_dir_path, 
    xlsx_merge_path,
    merge_sheet_name="Sheet1"
):
    """
    合并相同xlsx标题的文件
    """
    # 加载工作簿
    wb_write = Workbook()
    ws = wb_write.create_sheet(merge_sheet_name)
    sheet_first_row_content = [
        "标题1",
        "标题2",
    ]
    #删除默认的Sheet名
    del wb_write['Sheet']
    for i, c in enumerate(sheet_first_row_content):
        ws.cell(row=1, column=i+1, value=c)

    merge_row_num = 2
    
    # 遍历目录下的所有文件
    xlsx_name_list = sorted(os.listdir(xlsx_dir_path))
    for xlsx_name in xlsx_name_list:
        xlsx_path = os.path.join(xlsx_dir_path, xlsx_name)
        # 加载工作簿
        workbook = load_workbook(xlsx_path)
        # 获取工作表
        sheet = workbook.active
        max_row_num = sheet.max_row
        # 读取内容行
        for i, row in enumerate(
            sheet.iter_rows(
                min_row=2, max_row=max_row_num, 
                min_col=1, max_col=sheet.max_column
            )
        ):
            for j, cell in enumerate(row):
                ws.cell(
                    row=merge_row_num+i, 
                    column=j+1,
                    value=cell.value
                )
        merge_row_num += max_row_num - 1



    wb_write.save(xlsx_merge_path)
    wb_write.close()

if __name__ == '__main__':
    xlsx_dir_path = "./test_1"
    xlsx_merge_path = "./test_1_merged.xlsx"
    merge_xlsx_files(
        xlsx_dir_path, 
        xlsx_merge_path,
        merge_sheet_name="Sheet1"
    )