Excel 文件读写（二）

openpyxl 读写新版 Excel

openpyxl 支持读写 .xlsx 格式，功能丰富，支持样式、公式、图表。

pip install openpyxl

读取 Excel

import openpyxl
import datetime

wb = openpyxl.load_workbook('data.xlsx')
print(wb.sheetnames)  # ['Sheet1', ...]

sheet = wb.worksheets[0]
print(sheet.dimensions)  # A1:G255
print(sheet.max_row, sheet.max_column)

# 读取单元格
print(sheet.cell(3, 3).value)  # 按行列（从1开始）
print(sheet['C3'].value)        # 按坐标

# 遍历所有数据
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

写入 Excel

import openpyxl
import random

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '成绩单'

headers = ['姓名', '语文', '数学', '英语']
for col, h in enumerate(headers, 1):
    sheet.cell(1, col, h)

names = ['Alice', 'Bob', 'Charlie']
for row_idx, name in enumerate(names, 2):
    sheet.cell(row_idx, 1, name)
    for col in range(2, 5):
        sheet.cell(row_idx, col, random.randint(50, 100))

wb.save('scores.xlsx')

设置样式

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

# 字体
sheet['A1'].font = Font(size=14, bold=True, color='FF1493')

# 对齐
sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')

# 边框
side = Side(color='FF7F50', style='mediumDashed')
sheet['A1'].border = Border(left=side, top=side, right=side, bottom=side)

# 行高列宽
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['A'].width = 15

公式计算

# 写入公式（与 Excel 中写法一致）
for i in range(2, 7):
    sheet[f'E{i}'] = f'=AVERAGE(B{i}:D{i})'
    sheet[f'F{i}'] = f'=MAX(B{i}:D{i})'

wb.save('scores.xlsx')

生成图表

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

wb = Workbook()
sheet = wb.active

rows = [
    ('类别', 'A组', 'B组'),
    ('手机', 40, 30),
    ('平板', 50, 60),
    ('笔记本', 80, 70),
]
for row in rows:
    sheet.append(row)

chart = BarChart()
chart.type = 'col'
chart.title = '销售统计'
chart.y_axis.title = '销量'
chart.x_axis.title = '商品'

data = Reference(sheet, min_col=2, min_row=1, max_row=4, max_col=3)
cats = Reference(sheet, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, 'A6')

wb.save('chart.xlsx')

总结

• openpyxl：读写 .xlsx 文件，支持样式、公式、图表
• load_workbook：既可读也可写
• cell(row, col) 或 sheet['A1']：两种访问单元格方式
• 数据分析场景推荐 pandas，但简单的读写用 openpyxl 更直接