【python小記】使用openpyxl庫在同一個工作表下複製單元格（包括它們的值、樣式和合並屬性）

鲍鱼不是鱼發表於2024-07-03
Python
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')

相關文章