Python 读写 Excel 文件

1. Excel 与办公自动化

Excel 是常见的电子表格工具，适合存放表格数据、做简单统计和图表展示。在业务系统里，导入导出表格、批量合并多份报表、按条件抽取字段，都是高频需求。用 Python 处理这些任务，可以减少重复手工操作，也便于把结果接到后续数据处理流程中。

同类产品（如在线表格、开源电子表格）通常能兼容较新的 Excel 格式。对开发者而言，关键是选对库、弄清文件格式差异：

文件格式	典型场景	常用库
.xls（较旧）	历史系统导出、旧模板	读：xlrd；写：xlwt；读写衔接：xlutils
.xlsx（较新）	日常办公、新项目	openpyxl（同一工作簿可读写）

安装示例：

pip install xlrd xlwt xlutils
pip install openpyxl

选型建议：只接触旧版 .xls 时用 xlrd / xlwt；新建项目或处理 .xlsx 时优先 openpyxl（样式、公式、图表更顺手，但不支持 Office 2007 以前的 .xls）。

---

2. 读取表格

2.1 使用 xlrd 读取 .xls

核心对象链路：工作簿（Book）→ 工作表（Sheet）→ 单元格（Cell）。

import xlrd

wb = xlrd.open_workbook('inventory_2024.xls')
print(wb.sheet_names())

sheet = wb.sheet_by_name(wb.sheet_names()[0])
print(sheet.nrows, sheet.ncols)

for r in range(sheet.nrows):
    for c in range(sheet.ncols):
        cell = sheet.cell(r, c)
        value = cell.value
        if r > 0 and c == 0 and cell.ctype == xlrd.XL_CELL_DATE:
            # 0：以 1900-01-01 为基准；1：以 1904-01-01 为基准
            y, m, d, *_ = xlrd.xldate_as_tuple(value, 0)
            value = f'{y:04d}-{m:02d}-{d:02d}'
        elif r > 0 and isinstance(value, float):
            value = f'{value:.2f}'
        print(value, end='\t')
    print()

# 类型码：0 空 / 1 字符串 / 2 数字 / 3 日期 / 4 布尔 / 5 错误
print(sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1))
print(sheet.row_values(0))
print(sheet.row_slice(2, 0, 4))  # 第 3 行、前 4 列

要点：

• 行、列索引从 0 开始。
• 日期在文件中常以数字存储，需用 xldate_as_tuple 再格式化。
• row_values 取整行；row_slice 可限定列区间。

2.2 使用 openpyxl 读取 .xlsx

from datetime import datetime

import openpyxl

wb = openpyxl.load_workbook('inventory_2024.xlsx')
print(wb.sheetnames)

sheet = wb.worksheets[0]
print(sheet.dimensions)
print(sheet.max_row, sheet.max_column)

# 两种定位方式：行列号（从 1 起）或 Excel 坐标
print(sheet.cell(2, 3).value)
print(sheet['C2'].value)

# 区域切片，结果为嵌套元组
print(sheet['A2:C4'])

for row in range(2, sheet.max_row + 1):
    for col in 'ABCDE':
        value = sheet[f'{col}{row}'].value
        if isinstance(value, datetime):
            print(value.strftime('%Y-%m-%d'), end='\t')
        elif isinstance(value, float):
            print(f'{value:.4f}', end='\t')
        else:
            print(value, end='\t')
    print()

要点：

• cell(row, col) 的行列均从 1 开始，贴近 Excel 习惯。
• 支持 A1 风格坐标与区域切片，读写接口统一，后续改样式或写回更省事。

---

3. 写入表格

3.1 使用 xlwt 生成 .xls

import random

import xlwt

names = ['陈默', '林晓', '周凯', '沈悦', '韩磊']
subjects = ('姓名', '产品设计', '数据分析', '沟通协作')

wb = xlwt.Workbook()
sheet = wb.add_sheet('培训测评')

for col, title in enumerate(subjects):
    sheet.write(0, col, title)

for row, name in enumerate(names):
    sheet.write(row + 1, 0, name)
    for col in range(1, 4):
        sheet.write(row + 1, col, random.randint(60, 100))

wb.save('training_scores.xls')

流程：Workbook → add_sheet → write(行, 列, 值) → save。

3.2 使用 openpyxl 生成 .xlsx

import random

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '培训测评'

headers = ('姓名', '产品设计', '数据分析', '沟通协作')
for col, title in enumerate(headers, start=1):
    sheet.cell(1, col, title)

names = ['陈默', '林晓', '周凯', '沈悦', '韩磊']
for row, name in enumerate(names, start=2):
    sheet.cell(row, 1, name)
    for col in range(2, 5):
        sheet.cell(row, col, random.randint(60, 100))

wb.save('training_scores.xlsx')

默认会有一张活动工作表，可用 title 改名，用 cell 按 1 基索引写入。

---

4. 单元格样式

4.1 xlwt：通过 XFStyle 组合样式

import xlwt

wb = xlwt.Workbook()
sheet = wb.add_sheet('样式示例')

style = xlwt.XFStyle()

# 背景
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5  # 常见色号：2 红 3 绿 4 蓝 5 黄 …
style.pattern = pattern

# 字体
font = xlwt.Font()
font.name = 'Microsoft YaHei'
font.height = 20 * 14  # 基准 20，约等于字号相关高度
font.bold = True
font.colour_index = 1
style.font = font

# 对齐
align = xlwt.Alignment()
align.horz = xlwt.Alignment.HORZ_CENTER
align.vert = xlwt.Alignment.VERT_CENTER
style.alignment = align

# 边框
borders = xlwt.Borders()
for edge in ('top', 'bottom', 'left', 'right'):
    setattr(borders, edge, xlwt.Borders.THIN)
    setattr(borders, f'{edge}_colour', 4)
style.borders = borders

sheet.write(0, 0, '指标名称', style)
sheet.row(0).set_style(xlwt.easyxf('font:height 800'))
sheet.col(0).width = 20 * 256

wb.save('style_demo.xls')

说明：字体名称需本机已安装；颜色多为调色板索引，实际显示依赖 Excel 版本与主题。

4.2 openpyxl：直接设置 Cell 属性

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

wb = load_workbook('training_scores.xlsx')
sheet = wb.worksheets[0]

sheet.row_dimensions[1].height = 28
sheet.column_dimensions['E'].width = 14

side = Side(style='medium', color='FF7F50')
align = Alignment(horizontal='center', vertical='center')

cell = sheet['E1']
cell.value = '综合均分'
cell.font = Font(name='Microsoft YaHei', size=14, bold=True, color='C71585')
cell.alignment = align
cell.border = Border(left=side, right=side, top=side, bottom=side)

wb.save('training_scores.xlsx')

相对 xlwt，openpyxl 用属性赋值即可，不必先拼一整套 XFStyle。

---

5. 公式计算

5.1 xlrd 读取后经 xlutils 再写回

xlrd 侧重读；要在已有 .xls 上追加公式，常见做法是 copy 成可写工作簿，再写入 Formula。

import xlrd
import xlwt
from xlutils.copy import copy

src = xlrd.open_workbook('inventory_2024.xls')
sheet_r = src.sheet_by_index(0)
nrows = sheet_r.nrows

dst = copy(src)
sheet_w = dst.get_sheet(0)
# 假设 E 列为单价、G 列为数量
sheet_w.write(nrows, 4, xlwt.Formula(f'AVERAGE(E2:E{nrows})'))
sheet_w.write(nrows, 6, xlwt.Formula(f'SUM(G2:G{nrows})'))
dst.save('inventory_2024_summary.xls')

注意：公式是否立刻显示结果，取决于打开文件时 Excel 是否重算；边界行号、空行也会影响范围是否正确。

5.2 openpyxl 直接写入公式字符串

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment

wb = load_workbook('training_scores.xlsx')
sheet = wb.active
align = Alignment(horizontal='center', vertical='center')

sheet['E1'] = '综合均分'
for row in range(2, 7):
    sheet[f'E{row}'] = f'=AVERAGE(B{row}:D{row})'
    sheet[f'E{row}'].font = Font(size=11, italic=True, color='4169E1')
    sheet[f'E{row}'].alignment = align

wb.save('training_scores.xlsx')

写法与在 Excel 中编辑公式一致，维护成本通常低于「读库 + 拷贝 + 写库」三段式。

---

6. 插入统计图表（openpyxl）

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

wb = Workbook()
sheet = wb.active
sheet.title = '渠道销量'

rows = [
    ('品类', '华东', '华北'),
    ('耳机', 42, 35),
    ('音箱', 28, 31),
    ('键盘', 55, 48),
    ('鼠标', 33, 29),
]
for row in rows:
    sheet.append(row)

chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.title = '区域品类销量'
chart.y_axis.title = '销量'
chart.x_axis.title = '品类'

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

wb.save('channel_sales_chart.xlsx')

步骤概要：准备数据区 → 创建图表对象 → 用 Reference 指定数值与分类 → add_chart 锚到单元格。

---

总结

能力	.xls 路径	.xlsx 路径
读取	xlrd	openpyxl.load_workbook
写入	xlwt	openpyxl.Workbook
改已有文件	常需 xlutils.copy	同一工作簿读写即可
样式	XFStyle 组合	Cell 属性直接设
公式	xlwt.Formula	单元格写入公式字符串
图表	基本不依赖此路线	openpyxl.chart

日常办公中，多表合并、字段抽取、套模板导出，用上述库即可覆盖。若数据体量大、清洗与聚合步骤多，可把表格读入 pandas 再分析或回写。格式上：兼容旧 .xls 时用 xlrd/xlwt；新项目与进阶能力（样式、公式、图表）优先 openpyxl。
`)