openpyxl合并单元格详解 - merge_cells用法与复杂表头制作

# 合并单元格
ws.merge_cells('A1:D1')
ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=4)

# 取消合并
ws.unmerge_cells('A1:D1')

# 读取合并区域
ws.merged_cells.ranges

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

wb = Workbook()
ws = wb.active

# 合并A1:D1作为标题
ws.merge_cells('A1:D1')
ws['A1'] = '员工信息表'
ws['A1'].font = Font(size=16, bold=True, color='2196F3')
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws['A1'].fill = PatternFill(start_color='E3F2FD', end_color='E3F2FD', fill_type='solid')

# 表头
headers = ['编号', '姓名', '部门', '工资']
for col, h in enumerate(headers, 1):
    ws.cell(row=2, column=col, value=h)

# 数据
data = [[1, '张三', '技术部', 15000],
        [2, '李四', '市场部', 12000]]
for r, row in enumerate(data, 3):
    for c, val in enumerate(row, 1):
        ws.cell(row=r, column=c, value=val)

# 查看合并区域
print(f"合并区域: {list(ws.merged_cells.ranges)}")

wb.save('merge_basic.xlsx')
print("基本合并文件已保存")

合并区域: [A1:D1]
基本合并文件已保存

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill

wb = Workbook()
ws = wb.active

thin_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)
center_align = Alignment(horizontal='center', vertical='center')
header_fill = PatternFill(start_color='2196F3', end_color='2196F3', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

# 第一行：大标题
ws.merge_cells('A1:E1')
ws['A1'] = '2024年度销售报表'
ws['A1'].font = Font(size=14, bold=True)
ws['A1'].alignment = center_align

# 第二行：合并表头
ws.merge_cells('A2:A3')
ws['A2'] = '区域'
ws.merge_cells('B2:C2')
ws['B2'] = '上半年'
ws.merge_cells('D2:E2')
ws['D2'] = '下半年'

# 第三行：子表头
ws['B3'] = 'Q1'
ws['C3'] = 'Q2'
ws['D3'] = 'Q3'
ws['E3'] = 'Q4'

# 应用样式
for row in ws.iter_rows(min_row=2, max_row=3, min_col=1, max_col=5):
    for cell in row:
        cell.border = thin_border
        cell.alignment = center_align
        cell.fill = header_fill
        cell.font = header_font

# 数据
regions = ['华东', '华南', '华北']
for r, region in enumerate(regions, 4):
    ws.cell(row=r, column=1, value=region)
    for c in range(2, 6):
        ws.cell(row=r, column=c, value=(r-3)*100 + c*10)
        ws.cell(row=r, column=c).border = thin_border
        ws.cell(row=r, column=c).alignment = center_align
    ws.cell(row=r, column=1).border = thin_border

print(f"合并区域: {list(ws.merged_cells.ranges)}")
wb.save('merge_complex_header.xlsx')
print("复杂表头文件已保存")

合并区域: [A1:E1, A2:A3, B2:C2, D2:E2]
复杂表头文件已保存

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 先合并
ws.merge_cells('A1:C1')
ws['A1'] = '合并标题'
print(f"合并后: {list(ws.merged_cells.ranges)}")

# 取消合并
ws.unmerge_cells('A1:C1')
print(f"取消后: {list(ws.merged_cells.ranges)}")

# 动态合并：根据数据量合并
data = [
    ['技术部', '张三', 15000],
    ['技术部', '王五', 18000],
    ['技术部', '钱七', 16000],
    ['市场部', '李四', 12000],
    ['市场部', '赵六', 13000],
]

# 按部门合并第一列
current_dept = None
merge_start = 2
for i, row in enumerate(data):
    ws.cell(row=i+2, column=1, value=row[0])
    ws.cell(row=i+2, column=2, value=row[1])
    ws.cell(row=i+2, column=3, value=row[2])
    
    if row[0] != current_dept:
        if current_dept is not None and i - merge_start + 1 > 0:
            if i - merge_start > 0:
                ws.merge_cells(start_row=merge_start, start_column=1,
                             end_row=i+1, end_column=1)
        current_dept = row[0]
        merge_start = i + 2

# 处理最后一组
if len(data) - merge_start + 2 > 1:
    ws.merge_cells(start_row=merge_start, start_column=1,
                 end_row=len(data)+1, end_column=1)

print(f"动态合并: {list(ws.merged_cells.ranges)}")
wb.save('merge_dynamic.xlsx')
print("动态合并文件已保存")

合并后: [A1:C1]
取消后: []
动态合并: [A2:A4, A5:A6]
动态合并文件已保存