pip install openpyxl

from openpyxl import load_workbook
from copy import copy


# ===================== 拆分合并單元格（全區(qū)域填充值+保留樣式） =====================
def unmerge_merged_cells_fill_all(file_path, sheet_name="Sheet1", output_path=None):
    """
    拆分所有合并單元格，拆分后原合并區(qū)域全部填充原左上角值，完整保留樣式
    :param file_path: 輸入Excel路徑
    :param sheet_name: 工作表名稱
    :param output_path: 輸出路徑，默認(rèn)覆蓋原文件
    :return: 處理后文件路徑
    """
    # 加載工作簿，保留原格式
    wb = load_workbook(file_path)
    ws = wb[sheet_name]

    # 提前讀取所有合并區(qū)域（拆分后會丟失合并信息，必須前置讀取）
    merged_ranges = list(ws.merged_cells.ranges)
    if not merged_ranges:
        print("未檢測到合并單元格，無需處理")
        return file_path

    # 遍歷所有合并區(qū)域逐一處理
    for m_range in merged_ranges:
        min_row = m_range.min_row
        max_row = m_range.max_row
        min_col = m_range.min_col
        max_col = m_range.max_col

        # 獲取左上角值與樣式
        top_left_cell = ws.cell(row=min_row, column=min_col)
        top_left_value = top_left_cell.value

        # 拆分單元格
        ws.unmerge_cells(range_string=str(m_range))

        # 全區(qū)域填充值 + 復(fù)刻樣式
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                current_cell = ws.cell(row=row, column=col)
                current_cell.value = top_left_value
                # 完整復(fù)刻樣式
                current_cell.font = copy(top_left_cell.font)
                current_cell.fill = copy(top_left_cell.fill)
                current_cell.border = copy(top_left_cell.border)
                current_cell.alignment = copy(top_left_cell.alignment)
                current_cell.number_format = top_left_cell.number_format

    # 保存文件
    output_path = output_path if output_path else file_path
    wb.save(output_path)
    print(f"? 拆分完成！共處理 {len(merged_ranges)} 個(gè)合并區(qū)域，文件保存至: {output_path}")
    return output_path


# ===================== 按列縱向自動合并相同值 =====================
def merge_cells_by_column(file_path, sheet_name="Sheet1", start_row=1, end_row=None, col=None, output_path=None):
    """
    單列縱向合并：同一列連續(xù)相同值自動合并
    """
    wb = load_workbook(file_path)
    ws = wb[sheet_name]
    max_row = ws.max_row if end_row is None else end_row

    merge_blocks = []
    current_start = start_row
    prev_val = ws.cell(row=start_row, column=col).value

    # 掃描連續(xù)相同值區(qū)塊
    for r in range(start_row + 1, max_row + 1):
        curr_val = ws.cell(row=r, column=col).value
        if curr_val != prev_val:
            if current_start != r - 1:
                merge_blocks.append((current_start, col, r - 1, col))
            current_start = r
            prev_val = curr_val
    # 收尾最后一段
    if current_start != max_row:
        merge_blocks.append((current_start, col, max_row, col))

    # 執(zhí)行合并+樣式同步
    for min_r, min_c, max_r, max_c in merge_blocks:
        ws.merge_cells(min_row=min_r, min_col=min_c, max_row=max_r, max_col=max_c)
        top_cell = ws.cell(min_r, min_c)
        for r in range(min_r, max_r + 1):
            for c in range(min_c, max_c + 1):
                if not (r == min_r and c == min_c):
                    cell = ws.cell(r, c)
                    cell.value = None
                    cell.font = copy(top_cell.font)
                    cell.fill = copy(top_cell.fill)
                    cell.border = copy(top_cell.border)
                    cell.alignment = copy(top_cell.alignment)
                    cell.number_format = top_cell.number_format

    save_path = output_path if output_path else file_path
    wb.save(save_path)
    print(f"? 列合并完成，共 {len(merge_blocks)} 處合并，保存至：{save_path}")
    return save_path


# ===================== 按行橫向自動合并相同值 =====================
def merge_cells_by_row(file_path, sheet_name="Sheet1", start_col=1, end_col=None, row=None, output_path=None):
    """
    單行橫向合并：同一行連續(xù)相同值自動合并
    """
    wb = load_workbook(file_path)
    ws = wb[sheet_name]
    max_col = ws.max_column if end_col is None else end_col

    merge_blocks = []
    current_start = start_col
    prev_val = ws.cell(row=row, column=start_col).value

    for c in range(start_col + 1, max_col + 1):
        curr_val = ws.cell(row=row, column=c).value
        if curr_val != prev_val:
            if current_start != c - 1:
                merge_blocks.append((row, current_start, row, c - 1))
            current_start = c
            prev_val = curr_val
    if current_start != max_col:
        merge_blocks.append((row, current_start, row, max_col))

    for min_r, min_c, max_r, max_c in merge_blocks:
        ws.merge_cells(min_row=min_r, min_col=min_c, max_row=max_r, max_col=max_c)
        top_cell = ws.cell(min_r, min_c)
        for r in range(min_r, max_r + 1):
            for c in range(min_c, max_c + 1):
                if not (r == min_r and c == min_c):
                    cell = ws.cell(r, c)
                    cell.value = None
                    cell.font = copy(top_cell.font)
                    cell.fill = copy(top_cell.fill)
                    cell.border = copy(top_cell.border)
                    cell.alignment = copy(top_cell.alignment)
                    cell.number_format = top_cell.number_format

    save_path = output_path if output_path else file_path
    wb.save(save_path)
    print(f"? 行合并完成，共 {len(merge_blocks)} 處合并，保存至：{save_path}")
    return save_path


# ===================== 自定義區(qū)域合并 =====================
def manual_merge_range(file_path, sheet_name="Sheet1", min_r=1, min_c=1, max_r=1, max_c=1, output_path=None):
    """手動指定行列區(qū)域合并單元格"""
    wb = load_workbook(file_path)
    ws = wb[sheet_name]
    ws.merge_cells(min_row=min_r, min_col=min_c, max_row=max_r, max_col=max_c)
    top_cell = ws.cell(min_r, min_c)

    for r in range(min_r, max_r + 1):
        for c in range(min_c, max_c + 1):
            if not (r == min_r and c == min_c):
                cell = ws.cell(r, c)
                cell.value = None
                cell.font = copy(top_cell.font)
                cell.fill = copy(top_cell.fill)
                cell.border = copy(top_cell.border)
                cell.alignment = copy(top_cell.alignment)
                cell.number_format = top_cell.number_format

    save_path = output_path if output_path else file_path
    wb.save(save_path)
    print(f"? 區(qū)域合并完成！({min_r},{min_c})~({max_r},{max_c})")
    return save_path


# ===================== 一鍵聯(lián)動：拆分填充 + 重新合并 =====================
if __name__ == "__main__":
    # 配置文件路徑
    input_file = r"C:\Users\Administrator\Desktop\cs11.xlsx"
    temp_file = r"C:\Users\Administrator\Desktop\temp_拆分后.xlsx"
    final_file = r"C:\Users\Administrator\Desktop\最終規(guī)整表格.xlsx"

    # 1. 先拆分所有合并單元格，填充空白、保留樣式
    unmerge_merged_cells_fill_all(input_file, output_path=temp_file)

    # 2. 按需重新合并（示例：對第1列縱向合并相同內(nèi)容）
    merge_cells_by_column(temp_file, col=1, start_row=1, output_path=final_file)

    # 其他用法可單獨(dú)調(diào)用
    # merge_cells_by_row(input_file, row=2)
    # manual_merge_range(input_file, min_r=2, min_c=1, max_r=5, max_c=3)

unmerge_merged_cells_fill_all("test.xlsx", output_path="拆分后.xlsx")

# 第2列，從第2行開始縱向合并 merge_cells_by_column("拆分后.xlsx", col=2, start_row=2)

# 第3行橫向合并 merge_cells_by_row("test.xlsx", row=3)

# 2-5行，1-3列區(qū)域合并 manual_merge_range("test.xlsx", min_r=2, min_c=1, max_r=5, max_c=3)