表格处理

import openpyxl
    import os
    from copy import copy
    
    def get_cell(sheet, cell):
        # 如果是合并单元格，找到合并区域的起始单元格
        for merged_cell_range in sheet.merged_cells.ranges:
            if cell.coordinate in merged_cell_range:
                return sheet[merged_cell_range.coord.split(':')[0]]
        return cell
    
    def copy_cell_format(src_cell, dest_cell):
        if src_cell.has_style:
            dest_cell.font = copy(src_cell.font)
            dest_cell.border = copy(src_cell.border)
            dest_cell.fill = copy(src_cell.fill)
            dest_cell.number_format = src_cell.number_format
            dest_cell.protection = copy(src_cell.protection)
            dest_cell.alignment = copy(src_cell.alignment)
    
    def copy_row(sheet_src, sheet_dest, src_row_num, dest_row_num):
        src_row = sheet_src[src_row_num]
        for cell in src_row:
            dest_cell = sheet_dest.cell(row=dest_row_num, column=cell.column, value=cell.value)
            copy_cell_format(cell, dest_cell)
        # 复制行高
        sheet_dest.row_dimensions[dest_row_num].height = sheet_src.row_dimensions[src_row_num].height
    
    def copy_sheet_properties(sheet_src, sheet_dest):
        # 复制列宽
        for col in sheet_src.column_dimensions:
            if col in sheet_src.column_dimensions:
                sheet_dest.column_dimensions[col].width = sheet_src.column_dimensions[col].width
        # 复制页边距和其他页面设置
        sheet_dest.page_margins = copy(sheet_src.page_margins)
        sheet_dest.page_setup = copy(sheet_src.page_setup)
        sheet_dest.print_options = copy(sheet_src.print_options)
    
    def copy_merged_cells(sheet_src, sheet_dest, start_row):
        for merged_cell_range in sheet_src.merged_cells.ranges:
            min_col, min_row, max_col, max_row = merged_cell_range.bounds
            dest_range = f"{openpyxl.utils.get_column_letter(min_col)}{min_row + start_row - 1}:{openpyxl.utils.get_column_letter(max_col)}{max_row + start_row - 1}"
            sheet_dest.merge_cells(dest_range)
    
    # 创建输出文件夹
    output_folder = 'out'
    if not os.path.exists(output_folder):
        os.makedirs(output_folder)
    
    # 打开表A.xlsx
    wbA = openpyxl.load_workbook('表A.xlsx')
    sheetA = wbA.active
    
    # 初始化行号，从第3行开始
    row_num = 3
    
    while True:
        A_value = sheetA[f'A{row_num}'].value
        if A_value is None:
            break
    
        # 获取当前行的其他单元格值
        D_value = sheetA[f'D{row_num}'].value
        C_value = sheetA[f'C{row_num}'].value
        B_value = sheetA[f'B{row_num}'].value
        E_value = sheetA[f'E{row_num}'].value
        F_value = sheetA[f'F{row_num}'].value
        G_value = sheetA[f'G{row_num}'].value
        H_value = sheetA[f'H{row_num}'].value
    
        # 打开原始的表B.xlsx，确保每次都是从未修改的表B开始
        wbB = openpyxl.load_workbook('表B.xlsx')
        sheetB = wbB.active
    
        # 获取目标单元格并处理合并单元格问题
        C3_B = get_cell(sheetB, sheetB['C3'])
        D4_B = get_cell(sheetB, sheetB['D4'])
        D6_B = get_cell(sheetB, sheetB['D6'])
        D7_B = get_cell(sheetB, sheetB['D7'])
        D8_B = get_cell(sheetB, sheetB['D8'])
        D9_B = get_cell(sheetB, sheetB['D9'])
    
        # 将数据写入表B中指定单元格
        C3_B.value = A_value  # 将表A的A列当前行写入表B的C3
        D4_B.value = f"{D_value} {B_value} {C_value}"  # 将表A的D B C列当前行写入表B的D4
        D6_B.value = E_value  # 将表A的E列当前行写入表B的D6
        D7_B.value = F_value  # 将表A的F列当前行写入表B的D7
        D8_B.value = G_value  # 将表A的G列当前行写入表B的D8
        D9_B.value = H_value  # 将表A的H列当前行写入表B的D9
    
        # 另存为新文件
        new_filename = os.path.join(output_folder, f"{A_value}.xlsx")
        wbB.save(new_filename)
    
        # 处理下一行
        row_num += 1
    
    # 合并out文件夹中的所有Excel文件
    combined_wb = openpyxl.Workbook()
    combined_ws = combined_wb.active
    combined_ws.title = 'All Data'
    
    current_row = 1  # 用于跟踪合并文件中的当前行
    
    # 遍历out文件夹中的所有文件并合并
    for filename in os.listdir(output_folder):
        if filename.endswith('.xlsx'):
            file_path = os.path.join(output_folder, filename)
            wb = openpyxl.load_workbook(file_path)
            ws = wb.active
    
            # 复制当前工作表的列宽和其他属性
            if current_row == 1:
                copy_sheet_properties(ws, combined_ws)
    
            # 复制合并单元格
            copy_merged_cells(ws, combined_ws, current_row)
    
            for row_num in range(1, ws.max_row + 1):
                copy_row(ws, combined_ws, row_num, current_row)
                current_row += 1
    
            # 每个文件内容之间空一行
            current_row += 1
    
    # 保存合并后的文件
    combined_wb.save('all.xlsx')
    
        代碼

LiuYuan's Blog

Navigation

Recent Posts