import openpyxl
from openpyxl.styles import PatternFill
workbook = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\user_info_format1.xlsx') # 加载已经存在的excel
name_list = workbook.sheetnames #获取所有的sheet表名
worksheet = workbook[name_list[0]]
fill = PatternFill("solid", fgColor="FF0000") #设置填充样式
m_list = worksheet.merged_cells # 合并单元格的位置信息，可迭代对象（单个是一个'openpyxl.worksheet.cell_range.CellRange'对象），print后就是excel坐标信息
cr = []
for m_area in m_list:
# 合并单元格的起始行坐标、终止行坐标。。。。，
r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
# 纵向合并单元格的位置信息提取出
if r2 - r1 > 0 or c2 - c1 > 0:
cr.append((r1, r2, c1, c2))
print('符合条件%s' % str(m_area))
#print(cr)
# 这里注意需要把合并单元格的信息提取出再拆分
for r in cr:
worksheet.unmerge_cells(start_row=r[0], end_row=r[1],
start_column=r[2], end_column=r[3])
if r[3]-r[2]==0: #单独列合并
for row in (range(r[0], r[1]+1)):
worksheet.cell(row=row, column=r[3], value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row, column=r[3]).fill = fill #将对应拆分单元格填充颜色
elif r[1]-r[0]==0 : #单独行合并
for column in (range(r[2], r[3]+1)):
worksheet.cell(row=r[1], column=column, value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row=r[0], column=column).fill = fill #将对应拆分单元格填充颜色
else : #多行、列合并
for row in (range(r[0], r[1]+1)):
for column in (range(r[2], r[3]+1)):
worksheet.cell(row, column, value=worksheet.cell(r[0], r[2]).value)
worksheet.cell(row, column).fill=fill #将对应拆分单元格填充颜色
workbook.save(r'C:\Users\Administrator\Desktop\data25.xlsx') #另存为新表
