【python小记】使用openpyxl库在同一个工作表下复制单元格（包括它们的值、样式和合并属性）

from openpyxl import load_workbook

# 加载工作簿和工作表
wb = load_workbook('test.xlsx')
sheet = wb['sheet1']


# 定义一个函数来复制样式
def copy_style(source_cell, target_cell):
    if source_cell.has_style:
        target_cell.font = source_cell.font.copy()
        target_cell.fill = source_cell.fill.copy()
        target_cell.border = source_cell.border.copy()
        target_cell.alignment = source_cell.alignment.copy()
        target_cell.number_format = source_cell.number_format
        target_cell.protection = source_cell.protection.copy()

    # 定义要复制的单元格范围和目标起始位置


source_start_row, source_start_col = 1, 1
source_end_row, source_end_col = 12, 11
target_start_row, target_start_col = 16, 1  # 假设从第14行开始复制

# 复制单元格值和样式
for row in range(source_start_row, source_end_row + 1):
    for col in range(source_start_col, source_end_col + 1):
        source_cell = sheet.cell(row=row, column=col)
        target_cell = sheet.cell(row=row + target_start_row - source_start_row,
                                 column=col + target_start_col - source_start_col)
        target_cell.value = source_cell.value
        copy_style(source_cell, target_cell)

    # 复制合并单元格属性
merged_ranges = []
for merge_range in sheet.merged_cells.ranges:
    min_col, min_row, max_col, max_row = merge_range.bounds
    if (min_row >= source_start_row and max_row <= source_end_row and
            min_col >= source_start_col and max_col <= source_end_col):
        # 这个合并区域与我们要复制的单元格范围有交集
        merged_ranges.append((min_row + target_start_row - source_start_row,
                              min_col + target_start_col - source_start_col,
                              max_row + target_start_row - source_start_row,
                              max_col + target_start_col - source_start_col))

    # 应用合并单元格
for min_row, min_col, max_row, max_col in merged_ranges:
    sheet.merge_cells(start_row=min_row, start_column=min_col,
                      end_row=max_row, end_column=max_col)

# 保存工作簿
wb.save('copied_within_same_sheet.xlsx')