Python创建Excel表格并合并行列

1. 使用 openpyxl（推荐）

基本用法

from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter

# 创建新的工作簿
wb = Workbook()
ws = wb.active
ws.title = "示例表格"

# 写入数据
data = [
    ["部门", "姓名", "职位", "工资"],
    ["技术部", "张三", "工程师", 8000],
    ["技术部", "李四", "高级工程师", 12000],
    ["市场部", "王五", "经理", 15000],
    ["市场部", "赵六", "主管", 10000]
]

for row_idx, row in enumerate(data, start=1):
    for col_idx, value in enumerate(row, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 合并单元格 - 合并行
ws.merge_cells('A2:A3')  # 合并A2和A3（技术部）
ws.merge_cells('A4:A5')  # 合并A4和A5（市场部）

# 合并单元格 - 合并列
ws.merge_cells('A1:D1')  # 合并A1到D1创建标题
ws['A1'].value = "员工信息表"

# 设置居中对齐
for merged_cell in ws.merged_cells.ranges:
    for cell in ws[merged_cell.coord]:
        cell.alignment = Alignment(horizontal='center', vertical='center')

# 保存文件
wb.save("示例表格.xlsx")
print("Excel文件已创建: 示例表格.xlsx")

高级示例 - 创建复杂表格

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

def create_excel_with_merged_cells():
    wb = Workbook()
    ws = wb.active
    ws.title = "销售报表"

    # 定义样式
    title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
    header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
    center_alignment = Alignment(horizontal='center', vertical='center')

    # 创建标题 - 合并多列
    ws.merge_cells('A1:F1')
    title_cell = ws['A1']
    title_cell.value = "2024年第一季度销售报表"
    title_cell.font = title_font
    title_cell.alignment = center_alignment
    title_cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

    # 创建二级标题 - 合并行
    ws.merge_cells('A2:A4')
    ws['A2'].value = "产品分类"
    ws['A2'].alignment = Alignment(horizontal='center', vertical='center', text_rotation=90)

    ws.merge_cells('B2:E2')
    ws['B2'].value = "销售月份"
    ws['B2'].alignment = center_alignment

    # 月份子标题
    months = ["一月", "二月", "三月", "季度总计"]
    for col, month in enumerate(months, start=2):
        ws.cell(row=3, column=col, value=month)
        ws.cell(row=4, column=col, value="(万元)")

    # 产品数据
    products = [
        ["电子产品", 120, 150, 180, ""],
        ["服装", 80, 95, 110, ""],
        ["食品", 65, 70, 85, ""],
        ["总计", "", "", "", ""]
    ]

    for row_idx, product in enumerate(products, start=5):
        ws.cell(row=row_idx, column=1, value=product[0])
        for col_idx in range(2, 6):
            ws.cell(row=row_idx, column=col_idx, value=product[col_idx-1])

    # 计算总计
    for col in range(2, 5):  # 一月到三月
        total = sum(ws.cell(row=row, column=col).value for row in range(5, 8))
        ws.cell(row=8, column=col, value=total)

    # 计算季度总计
    for row in range(5, 9):
        quarter_total = sum(ws.cell(row=row, column=col).value or 0 for col in range(2, 5))
        ws.cell(row=row, column=5, value=quarter_total)

    # 合并总计行
    ws.merge_cells('F5:F8')
    ws['F5'].value = "数据来源：\n财务部\n2024年4月"
    ws['F5'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    # 应用样式
    for row in ws.iter_rows(min_row=2, max_row=4, max_col=5):
        for cell in row:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = center_alignment

    # 设置列宽
    for col in range(1, 7):
        ws.column_dimensions[get_column_letter(col)].width = 15

    # 设置行高
    for row in range(1, 9):
        ws.row_dimensions[row].height = 25

    # 保存文件
    wb.save("销售报表.xlsx")
    print("复杂Excel表格已创建: 销售报表.xlsx")

create_excel_with_merged_cells()

2. 使用 pandas（结合openpyxl）

import pandas as pd
from openpyxl import load_workbook

# 创建DataFrame
data = {
    '产品': ['手机', '手机', '笔记本', '笔记本'],
    '型号': ['A1', 'A2', 'B1', 'B2'],
    '季度': ['Q1', 'Q2', 'Q1', 'Q2'],
    '销量': [1000, 1200, 800, 950]
}
df = pd.DataFrame(data)

# 使用pandas创建Excel文件
with pd.ExcelWriter('pandas_excel.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='销售数据', index=False)

    # 获取工作表
    workbook = writer.book
    worksheet = writer.sheets['销售数据']

    # 合并单元格
    worksheet.merge_cells('A2:A3')  # 合并手机行
    worksheet.merge_cells('A4:A5')  # 合并笔记本行

    # 设置样式
    for merged_range in ['A2:A3', 'A4:A5']:
        cell = worksheet[merged_range.split(':')[0]]
        cell.alignment = Alignment(horizontal='center', vertical='center')

print("使用pandas创建的Excel文件已保存")

3. 使用 xlwt 和 xlrd（适合.xls格式）

import xlwt

# 创建新的工作簿
wb = xlwt.Workbook()
ws = wb.add_sheet('员工表')

# 写入标题
ws.write_merge(0, 0, 0, 3, '员工信息表', 
               xlwt.easyxf('font: bold on; align: horiz center'))

# 写入表头
headers = ['部门', '姓名', '职位', '工资']
for col, header in enumerate(headers):
    ws.write(1, col, header)

# 写入数据
data = [
    ['技术部', '张三', '工程师', 8000],
    ['', '李四', '高级工程师', 12000],
    ['市场部', '王五', '经理', 15000],
    ['', '赵六', '主管', 10000]
]

# 合并部门列
ws.write_merge(2, 3, 0, 0, '技术部', 
               xlwt.easyxf('align: horiz center'))
ws.write_merge(4, 5, 0, 0, '市场部', 
               xlwt.easyxf('align: horiz center'))

# 写入其他数据
for row in range(2, 6):
    for col in range(1, 4):
        ws.write(row, col, data[row-2][col])

# 保存文件
wb.save('员工表.xls')
print("xls格式Excel文件已创建: 员工表.xls")

4. 合并单元格的实用函数

from openpyxl import Workbook
from openpyxl.styles import Alignment

def create_merged_table():
    wb = Workbook()
    ws = wb.active

    # 定义合并区域
    merge_areas = [
        ('A1:D1', '公司年度报告'),  # 合并标题
        ('A2:A5', '技术部'),        # 合并部门
        ('A6:A9', '市场部'),        # 合并部门
        ('B3:C3', '第一季度'),      # 合并季度
        ('B6:C6', '第一季度')       # 合并季度
    ]

    # 应用合并
    for merge_range, value in merge_areas:
        ws.merge_cells(merge_range)
        start_cell = ws[merge_range.split(':')[0]]
        start_cell.value = value
        start_cell.alignment = Alignment(horizontal='center', vertical='center')

    # 填充其他数据
    other_data = [
        (2, 'B', '张三'), (2, 'D', 8000),
        (3, 'B', '李四'), (3, 'D', 12000),
        (4, 'B', '王五'), (4, 'D', 15000),
        (5, 'B', '赵六'), (5, 'D', 10000),
        (6, 'B', '钱七'), (6, 'D', 9000),
        (7, 'B', '孙八'), (7, 'D', 11000),
        (8, 'B', '周九'), (8, 'D', 13000),
        (9, 'B', '吴十'), (9, 'D', 9500)
    ]

    for row, col, value in other_data:
        ws[f"{col}{row}"].value = value

    # 保存文件
    wb.save("合并表格示例.xlsx")
    print("实用函数创建的Excel文件已保存")

create_merged_table()

主要库对比

建议：

• 对于复杂格式和合并单元格操作，推荐使用 openpyxl
• 对于数据处理后再导出，使用 pandas + openpyxl
• 如果需要兼容旧版.xls格式，使用 xlwt