openpyxl 取消合并单元格且填充

 

上图初始数据，

这些数据我需要使用，openpyxl 读取时 A（2，3，4） 是 None，但是我需要的是 A （2，3，4）也是A1的value，又不想改脚本，那我就在写一个脚本改格式吧

想法：先取消单元格，然后把None 补全成 合并单元格的第一个值，代码如下

# -*- coding: utf-8 -*-
# create time    : 2021-06-08 17:21
# author  : CY
# file    : excel_tools.py
# modify time:
import openpyxl


def cancel_merge_first_value(file_path):
    """取消合并单元格 并且 把 None 的值 补全 """
    wb = openpyxl.load_workbook(filename=file_path)
    sheets = wb.sheetnames  # 获取所有的表格
    start_index_list = []
    sheet_name_dict = {
        '工人考勤（接口）': start_index_list
    }

    for sheet in sheets:
        if sheet not in sheet_name_dict.keys():
            continue
        ws = wb[sheet]
        merge_list = ws.merged_cells
        all_merge_index = []
        for merge_area in merge_list:
            index_dict = {'all_key': merge_area.coord, 'max_row': merge_area.max_row, 'min_row': merge_area.min_row}
            start_index_list.append(index_dict)
            min_row, max_row, min_col, max_col = merge_area.min_row, merge_area.max_row, merge_area.min_col, merge_area.max_col
            all_merge_index.append((min_row, max_row, min_col, max_col))
        for merge_index in all_merge_index:
            ws.unmerge_cells(start_row=merge_index[0], end_row=merge_index[1], start_column=merge_index[2], end_column=merge_index[3])
        for use_index in start_index_list:
            all_index = use_index['all_key'].split(':')
            min_index = use_index['min_row']
            max_index = use_index['max_row']
            col_name = all_index[0].split(str(min_index))[0]
            start_value = ws[f'{col_name}{min_index}'].value
            for num_index in range(min_index, max_index+1):
                ws[f'{col_name}{num_index}'] = start_value
    wb.save(file_path)


if __name__ == '__main__':
    use_file_path = r""
    cancel_merge_first_value(use_file_path)

run 之后 就成了