pip install openpyxl

from openpyxl import load_workbook
from copy import copy

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

    # 2. 提前讀取所有合并單元格區(qū)域（關(guān)鍵：拆分后無法獲取合并范圍，必須先緩存）
    merged_ranges = list(ws.merged_cells.ranges)
    if not merged_ranges:
        print("當(dāng)前工作表未檢測(cè)到任何合并單元格，無需執(zhí)行處理")
        return

    # 3. 循環(huán)處理每一塊合并區(qū)域
    for m_range in merged_ranges:
        # 獲取合并區(qū)域邊界：起始行、結(jié)束行、起始列、結(jié)束列
        min_row = m_range.min_row
        max_row = m_range.max_row
        min_col = m_range.min_col
        max_col = m_range.max_col
        # 獲取合并區(qū)域左上角單元格與原始數(shù)值
        top_left_cell = ws.cell(row=min_row, column=min_col)
        top_left_value = top_left_cell.value

        # 拆分當(dāng)前合并單元格
        ws.unmerge_cells(range_string=str(m_range))

        # 遍歷原合并區(qū)域內(nèi)所有單元格，統(tǒng)一填充左上角值并復(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)
                # 填充數(shù)值
                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

    # 4. 文件保存邏輯
    if output_path is None:
        output_path = file_path
    wb.save(output_path)
    print(f"處理完成！共拆分 {len(merged_ranges)} 個(gè)合并單元格區(qū)域，文件保存路徑：{output_path}")
    return output_path

# ===================== 調(diào)用示例 =====================
if __name__ == "__main__":
    # Windows本地文件路徑，r字符串避免轉(zhuǎn)義符報(bào)錯(cuò)
    input_file = r"C:\Users\Administrator\Desktop\cs11.xlsx"
    # 方式1：直接覆蓋原文件
    unmerge_merged_cells_fill_all(input_file)

    # 方式2：生成新文件，不修改原文件（取消注釋使用）
    # new_file = r"C:\Users\Administrator\Desktop\cs11_處理后.xlsx"
    # unmerge_merged_cells_fill_all(input_file, output_path=new_file)

merged_ranges = list(ws.merged_cells.ranges)

top_left_cell = ws.cell(row=min_row, column=min_col)
top_left_value = top_left_cell.value

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

input_file = r"C:\Users\Administrator\Desktop\cs11.xlsx"
unmerge_merged_cells_fill_all(input_file)

input_file = r"C:\Users\Administrator\Desktop\cs11.xlsx"
output_file = r"C:\Users\Administrator\Desktop\cs11_拆分填充后.xlsx"
unmerge_merged_cells_fill_all(input_file, output_path=output_file)

input_file = r"C:\Users\Administrator\Desktop\cs11.xlsx"