openpyxl

openpyxl 是一个用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。

安装

pip install openpyxl

创建 Excel 文件

from openpyxl import Workbook

# 创建工作簿
wb = Workbook()

# 获取活动工作表
ws = wb.active
ws.title = "Sheet1"

# 写入数据
ws['A1'] = '姓名'
ws['B1'] = '年龄'
ws['C1'] = '城市'

# 写入多行数据
data = [
    ['张三', 25, '北京'],
    ['李四', 30, '上海'],
    ['王五', 28, '广州'],
    ['赵六', 32, '深圳']
]

for row in data:
    ws.append(row)

# 保存文件
wb.save('example.xlsx')
print("Excel 文件已保存")

读取 Excel 文件

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('example.xlsx')

# 获取工作表
ws = wb.active

# 读取所有数据
for row in ws.iter_rows(values_only=True):
    print(row)

# 读取特定范围
for row in ws['A1:C4']:
    for cell in row:
        print(cell.value, end=' ')
    print()

# 获取工作表信息
print(f"工作表名: {ws.title}")
print(f"最大行数: {ws.max_row}")
print(f"最大列数: {ws.max_column}")

设置单元格样式

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

wb = Workbook()
ws = wb.active

# 设置标题样式
header_font = Font(name='Arial', size=12, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center')

# 设置边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# 写入标题
headers = ['姓名', '年龄', '城市']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col)
    cell.value = header
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment
    cell.border = thin_border

# 写入数据并设置样式
data = [['张三', 25, '北京'], ['李四', 30, '上海']]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx)
        cell.value = value
        cell.alignment = Alignment(horizontal='center')
        cell.border = thin_border

wb.save('styled_example.xlsx')

使用公式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 写入数据
ws['A1'] = '商品'
ws['B1'] = '单价'
ws['C1'] = '数量'
ws['D1'] = '总价'

data = [
    ['商品A', 10, 5],
    ['商品B', 20, 3],
    ['商品C', 15, 4]
]

for row in data:
    ws.append(row)

# 添加公式
for row in range(2, 5):
    ws[f'D{row}'] = f'=B{row}*C{row}'

# 添加总计
ws['A5'] = '总计'
ws['D5'] = '=SUM(D2:D4)'

wb.save('formula_example.xlsx')

操作多个工作表

from openpyxl import Workbook

wb = Workbook()

# 删除默认工作表
if 'Sheet' in wb.sheetnames:
    del wb['Sheet']

# 创建多个工作表
sheets = ['一月', '二月', '三月', '四月']
for sheet_name in sheets:
    wb.create_sheet(title=sheet_name)

# 在不同工作表中写入数据
for sheet in wb.worksheets:
    sheet['A1'] = '日期'
    sheet['B1'] = '销售额'

wb.save('multiple_sheets.xlsx')

图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# 写入数据
data = [
    ['产品', '销售额'],
    ['A', 100],
    ['B', 150],
    ['C', 120],
    ['D', 180]
]

for row in data:
    ws.append(row)

# 创建图表
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "产品销售统计"
chart.y_axis.title = '销售额'
chart.x_axis.title = '产品'

# 设置数据范围
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)

# 添加图表到工作表
ws.add_chart(chart, "E2")

wb.save('chart_example.xlsx')

合并单元格

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

# 合并单元格
ws.merge_cells('A1:D1')
ws['A1'] = '合并单元格示例'
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

# 合并多行
ws.merge_cells('A3:B5')
ws['A3'] = '多行合并'

wb.save('merged_cells.xlsx')