Python中openpyxl模块如何玩转Excel

发布时间：2021-12-07 11:34:10 作者：小新
来源：亿速云阅读：162

# Python中openpyxl模块如何玩转Excel

## 一、前言：为什么选择openpyxl？

在数据分析和办公自动化领域，Excel文件处理是常见需求。Python作为最受欢迎的编程语言之一，提供了多个操作Excel的模块（如xlrd/xlwt、pandas等），而**openpyxl**因其独特优势脱颖而出：

- **全面支持.xlsx格式**：完美兼容Excel 2010+文件格式
- **读写兼备**：既能读取现有文件，也能创建新工作簿
- **丰富的API**：支持单元格格式、公式、图表等高级操作
- **纯Python实现**：无需安装Excel软件
- **活跃社区**：持续维护更新（最新版本3.1.2）

本文将带你从安装配置到实战应用，全面掌握这个强大的工具。

---

## 二、环境配置与基础操作

### 1. 安装openpyxl
```bash
pip install openpyxl
# 如需处理图片安装额外依赖
pip install pillow

2. 创建第一个工作簿

from openpyxl import Workbook

# 创建空白工作簿
wb = Workbook()
# 获取活动工作表
ws = wb.active
# 写入数据
ws['A1'] = "Hello"
ws['B1'] = "World"
# 保存文件
wb.save("first_excel.xlsx")

3. 核心对象模型

Workbook：整个Excel文件对象
Worksheet：单个工作表
Cell：单元格对象
Column/Row：列和行对象

三、数据读写进阶技巧

1. 多种数据写入方式

# 单单元格写入
ws['A2'] = 42
ws.cell(row=3, column=1, value=3.14)

# 批量写入（效率更高）
data = [
    ["ID", "Name", "Score"],
    [1, "Alice", 95],
    [2, "Bob", 88]
]
for row in data:
    ws.append(row)

# 使用公式
ws['D2'] = "=SUM(B2:C2)"

2. 数据读取全攻略

# 按坐标读取
print(ws['A1'].value)

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

# 按范围读取
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

# 获取最大行列数
print(f"最大行：{ws.max_row}, 最大列：{ws.max_column}")

3. 特殊数据类型处理

# 日期处理
from datetime import datetime
ws['A4'] = datetime.now()

# 布尔值
ws['B4'] = True

# 保存后读取时会自动转换类型

四、样式定制与格式化

1. 字体与颜色设置

from openpyxl.styles import Font, Color

bold_font = Font(name='微软雅黑', size=14, bold=True, color='FF0000')
ws['A1'].font = bold_font

2. 单元格边框与填充

from openpyxl.styles import Border, Side, PatternFill

thin_border = Border(left=Side(style='thin'), 
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin'))

yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

ws['B2'].border = thin_border
ws['B2'].fill = yellow_fill

3. 对齐与数字格式

from openpyxl.styles import Alignment, numbers

# 居中对齐
center_alignment = Alignment(horizontal='center', vertical='center')
ws['C3'].alignment = center_alignment

# 货币格式
ws['D2'].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE

五、高级功能探索

1. 工作表操作

# 创建/删除工作表
wb.create_sheet("月度报表", 0)  # 插入到首位
del wb["Sheet"]  # 删除默认表

# 工作表复制
source = wb["月度报表"]
target = wb.copy_worksheet(source)
target.title = "年度汇总"

2. 公式与数据验证

# 添加数据验证
from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"男,女"')
dv.add('B1:B10')
ws.add_data_validation(dv)

# 使用数组公式
ws['E1'] = '=SUM(IF(A1:A10>5,1,0))'

3. 图表制作

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "E5")

六、性能优化技巧

1. 只读模式（处理大文件）

from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
# 处理完成后需手动关闭
wb.close()

2. 只写模式

wb = Workbook(write_only=True)
ws = wb.create_sheet()
# 只能使用append()添加整行数据
for row in range(10000):
    ws.append([row, row*2])

3. 内存优化建议

避免频繁保存操作
批量处理数据而非单个单元格
及时关闭不再使用的工作簿

七、实战案例：销售报表自动化

1. 场景需求

从数据库导出销售数据
生成带格式的月报表
自动添加图表和汇总公式

2. 完整实现代码

import sqlite3
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
from openpyxl.styles import Font, Alignment

def generate_sales_report():
    # 连接数据库
    conn = sqlite3.connect('sales.db')
    cursor = conn.cursor()
    
    # 获取数据
    cursor.execute("SELECT product, amount FROM sales WHERE strftime('%Y-%m', date) = '2023-05'")
    data = cursor.fetchall()
    
    # 创建工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "五月销售"
    
    # 写入标题
    ws['A1'] = "五月产品销售报表"
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:B1')
    
    # 写入数据
    ws.append(["产品名称", "销售额(元)"])
    for row in data:
        ws.append(row)
    
    # 添加汇总公式
    last_row = len(data) + 2
    ws[f'A{last_row}'] = "总计"
    ws[f'B{last_row}'] = f"=SUM(B2:B{last_row-1})"
    
    # 创建饼图
    chart = PieChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=last_row-1)
    values = Reference(ws, min_col=2, min_row=2, max_row=last_row-1)
    chart.add_data(values, titles_from_data=False)
    chart.set_categories(labels)
    chart.title = "五月销售占比"
    ws.add_chart(chart, "D2")
    
    # 保存文件
    wb.save("五月销售报表.xlsx")
    conn.close()

generate_sales_report()

八、常见问题解答

Q1: 如何处理加密的Excel文件？

A: openpyxl不支持直接读取加密文件，建议先用其他工具解密或使用msoffcrypto-tool库解密后再处理。

Q2: 为什么修改后的文件变大了？

A: openpyxl会保留修改历史，可通过optimized_write=True或手动清理冗余数据减少体积。

Q3: 如何设置自动列宽？

from openpyxl.utils import get_column_letter

for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[column].width = adjusted_width

九、总结与扩展学习

通过本文，你已经掌握了： - openpyxl的核心API使用方法 - Excel样式定制技巧 - 大数据量处理的优化方案 - 完整的自动化报表生成流程

进一步学习建议： 1. 官方文档：https://openpyxl.readthedocs.io 2. 结合Pandas进行复杂数据分析 3. 使用win32com实现openpyxl不支持的Excel功能 4. 探索自动化报告邮件发送（搭配smtplib）

“自动化不是替代人类，而是让我们有更多时间解决更有价值的问题。” —— 佚名

现在就开始用openpyxl解放你的双手，让重复的Excel操作成为历史吧！ “`

注：本文实际约3800字（含代码），可根据需要调整代码示例的复杂度或增加更多实战案例来精确控制字数。