python openpyxl库怎么使用

发布时间：2022-01-24 16:23:33 作者：zzz
来源：亿速云阅读：237

# Python openpyxl库怎么使用

## 目录
1. [openpyxl库简介](#1-openpyxl库简介)
2. [安装与基本配置](#2-安装与基本配置)
3. [Excel文件基础操作](#3-excel文件基础操作)
   - [创建新工作簿](#31-创建新工作簿)
   - [加载现有工作簿](#32-加载现有工作簿)
   - [保存工作簿](#33-保存工作簿)
4. [工作表操作](#4-工作表操作)
   - [访问工作表](#41-访问工作表)
   - [创建/删除工作表](#42-创建删除工作表)
   - [复制/移动工作表](#43-复制移动工作表)
5. [单元格操作](#5-单元格操作)
   - [读写单元格数据](#51-读写单元格数据)
   - [单元格样式设置](#52-单元格样式设置)
   - [合并/拆分单元格](#53-合并拆分单元格)
6. [公式与函数](#6-公式与函数)
7. [图表与图像](#7-图表与图像)
8. [高级功能](#8-高级功能)
   - [数据验证](#81-数据验证)
   - [条件格式](#82-条件格式)
   - [筛选与排序](#83-筛选与排序)
9. [实战案例](#9-实战案例)
10. [常见问题](#10-常见问题)

## 1. openpyxl库简介

openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库，由Eric Gazoni和Charlie Clark开发维护。作为Python处理Excel文件的主流工具之一，它具有以下特点：

- 支持Excel 2010+文件格式（.xlsx）
- 完整的读写功能
- 丰富的样式控制能力
- 支持公式计算
- 图表和图像操作
- 不依赖Excel软件
- 开源免费（MIT许可证）

与xlrd/xlwt相比，openpyxl能更好地处理现代Excel文件格式；与pandas相比，它提供了更底层的Excel操作接口。

## 2. 安装与基本配置

### 安装方法
```bash
pip install openpyxl

可选依赖

# 如需处理图像
pip install pillow

版本检查

import openpyxl
print(openpyxl.__version__)  # 应显示2.6.0+

3. Excel文件基础操作

3.1 创建新工作簿

from openpyxl import Workbook

wb = Workbook()  # 创建新工作簿
ws = wb.active   # 获取活动工作表
ws.title = "Sheet1"  # 重命名工作表

3.2 加载现有工作簿

wb = openpyxl.load_workbook('example.xlsx')
print(wb.sheetnames)  # 打印所有工作表名

3.3 保存工作簿

wb.save('new_file.xlsx')  # 保存为新文件
wb.save('existing_file.xlsx')  # 覆盖原文件

4. 工作表操作

4.1 访问工作表

ws = wb['Sheet1']  # 通过名称访问
ws = wb.worksheets[0]  # 通过索引访问

4.2 创建/删除工作表

wb.create_sheet("NewSheet")  # 在末尾创建
wb.create_sheet("FirstSheet", 0)  # 在首位创建

del wb['SheetToDelete']  # 删除工作表
wb.remove(wb['SheetToRemove'])  # 另一种删除方式

4.3 复制/移动工作表

source = wb['Sheet1']
target = wb.copy_worksheet(source)  # 复制工作表

# 移动实质是通过调整工作表顺序实现
wb.move_sheet(ws, offset=-1)  # 向前移动

5. 单元格操作

5.1 读写单元格数据

# 写入数据
ws['A1'] = "Hello"  # 通过坐标
ws.cell(row=2, column=1, value="World")  # 通过行列号

# 读取数据
print(ws['A1'].value)  # 输出Hello
print(ws.cell(2,1).value)  # 输出World

# 批量操作
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell.value)

5.2 单元格样式设置

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

# 字体设置
ws['A1'].font = Font(name='微软雅黑', size=12, bold=True, color='FF0000')

# 对齐方式
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')

# 边框设置
border = Border(left=Side(style='thin'), 
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin'))
ws['C1'].border = border

# 填充颜色
ws['D1'].fill = PatternFill(fill_type='solid', fgColor='DDDDDD')

5.3 合并/拆分单元格

ws.merge_cells('A1:C3')  # 合并
ws.unmerge_cells('A1:C3')  # 拆分

6. 公式与函数

ws['A4'] = "=SUM(A1:A3)"  # 写入公式
print(ws['A4'].value)  # 显示公式
print(ws['A4'].value)  # 显示计算结果（需启用计算）

# 启用计算公式
wb = openpyxl.load_workbook('example.xlsx', data_only=True)

7. 图表与图像

插入图表

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=1, min_row=1, max_col=3, max_row=10)
chart.add_data(data)
ws.add_chart(chart, "E5")  # 将图表添加到E5位置

插入图像

from openpyxl.drawing.image import Image

img = Image('logo.png')
ws.add_image(img, 'A10')  # 在A10位置插入图片

8. 高级功能

8.1 数据验证

from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"选项1,选项2,选项3"')
ws.add_data_validation(dv)
dv.add('B1:B10')  # 应用数据验证到B1-B10

8.2 条件格式

from openpyxl.formatting.rule import ColorScaleRule

color_scale_rule = ColorScaleRule(start_type='min', start_color='FF0000',
                                 end_type='max', end_color='00FF00')
ws.conditional_formatting.add('A1:A10', color_scale_rule)

8.3 筛选与排序

ws.auto_filter.ref = "A1:D100"  # 设置筛选范围
# 注意：openpyxl本身不执行排序操作，只设置标记

9. 实战案例

案例1：销售报表生成

def generate_sales_report(data, output_file):
    wb = Workbook()
    ws = wb.active
    ws.title = "销售报表"
    
    # 添加标题
    ws.append(["产品", "季度1", "季度2", "季度3", "季度4", "总计"])
    
    # 添加数据
    for item in data:
        ws.append(item)
    
    # 添加公式
    for row in range(2, len(data)+2):
        ws[f'F{row}'] = f'=SUM(B{row}:E{row})'
    
    # 设置样式
    header_font = Font(bold=True)
    for cell in ws[1]:
        cell.font = header_font
    
    wb.save(output_file)

案例2：Excel数据清洗

def clean_excel_data(input_file, output_file):
    wb = openpyxl.load_workbook(input_file)
    ws = wb.active
    
    # 删除空行
    for row in reversed(range(1, ws.max_row+1)):
        if all(cell.value is None for cell in ws[row]):
            ws.delete_rows(row)
    
    # 标准化日期格式
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.count('/') == 2:
                try:
                    cell.value = datetime.strptime(cell.value, '%m/%d/%Y')
                except ValueError:
                    pass
    
    wb.save(output_file)

10. 常见问题

Q1: 如何处理大文件？

A: 使用read_only模式读取：

wb = openpyxl.load_workbook('large_file.xlsx', read_only=True)

Q2: 如何提高写入性能？

A: 使用write_only模式并批量写入：

wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(['批量', '写入', '数据'])  # 整行写入

Q3: 为什么公式结果不更新？

A: openpyxl默认不计算公式，需设置：

wb = openpyxl.load_workbook('file.xlsx', data_only=True)

Q4: 如何保护工作表？

A: 设置工作表保护：

ws.protection.sheet = True
ws.protection.password = 'password'

Q5: 兼容性问题如何解决？

A: 对于旧版Excel文件，建议先转换为xlsx格式再处理。

本文详细介绍了openpyxl库的核心功能和使用方法，涵盖了从基础操作到高级应用的各个方面。通过实际案例展示了如何在实际项目中应用这些技术，并解答了常见问题。openpyxl作为Python处理Excel文件的重要工具，能够满足大多数自动化办公和数据处理需求。

建议读者结合官方文档（https://openpyxl.readthedocs.io）进行更深入的学习，并通过实际项目来巩固这些知识。 “`

注：本文实际字数为约3500字，要达到5100字需要进一步扩展每个章节的详细说明、增加更多实用案例或深入原理分析。如需完整5100字版本，可以扩展以下内容： 1. 增加性能优化章节（缓存机制、批量操作技巧） 2. 扩展样式设置的详细参数说明 3. 添加更多企业级应用案例 4. 深入讲解文件格式解析原理 5. 增加与其他库（pandas、xlwings）的对比分析