Python的OpenPyXL库

一、OpenPyXL库的安装

OpenPyXL是一个用于读取和写入Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它是由于缺乏从Python中读取/编写Office Open XML格式的现有库而诞生的，并且最初是基于PHPExcel的。以下是如何在Python环境中安装OpenPyXL库的详细步骤：

1. 使用pip安装

在大多数Python环境中，可以使用pip（Python的包管理工具）来安装OpenPyXL。打开你的命令行工具（在Windows中是CMD或PowerShell，在macOS或Linux中是Terminal），然后输入以下命令：

pip install openpyxl

如果你使用的是Python 3，并且你的系统中同时安装了Python 2和Python 3，你可能需要使用pip3来确保为Python 3安装OpenPyXL：

pip3 install openpyxl

2. 更新pip（可选）

在安装过程中，有时可能会遇到pip版本过旧导致的警告或安装问题。此时，提议先更新pip到最新版本，然后再安装OpenPyXL。更新pip的命令如下：

python -m pip install –upgrade pip

或者，如果你使用的是Python 3：

python3 -m pip install –upgrade pip

3. 使用国内镜像源（可选）

由于pip的默认源在国外，下载速度可能较慢。为了提高下载速度，可以使用国内的镜像源，如阿里云或中国科技大学的镜像。以下是一个使用阿里云镜像安装OpenPyXL的示例：

pip install -i https://mirrors.aliyun.com/pypi/simple/ openpyxl

二、OpenPyXL库的使用

OpenPyXL库提供了丰富的接口来读取、写入、修改Excel文件。以下是一些基础用法和具体例子。

1. 创建新的Excel工作簿

from openpyxl import Workbook

# 创建一个新的工作簿

wb = Workbook()

# 激活工作簿中的默认工作表

ws = wb.active

# 给单元格赋值

ws['A1'] = 'Hello'

ws['B1'] = 'World'

# 保存到文件

wb.save('new_workbook.xlsx')

2. 打开现有的Excel工作簿

from openpyxl import load_workbook

# 打开现有的工作簿

wb = load_workbook('existing_workbook.xlsx')

# 选择工作表

ws = wb['Sheet1']

# 读取单元格的值

value = ws['A1'].value

print(value)

# 修改单元格的值

ws['A1'] = 'Updated Value'

# 保存修改后的工作簿

wb.save('updated_workbook.xlsx')

3. 写入多行数据

from openpyxl import Workbook

# 创建一个新的工作簿

wb = Workbook()

# 激活工作簿中的默认工作表

ws = wb.active

# 写入多行数据

data = [

['Name', 'Age', 'City'],

['Alice', 24, 'New York'],

['Bob', 30, 'Los Angeles'],

['Charlie', 35, 'Chicago']

]

for row in data:

ws.append(row)

# 保存工作簿

wb.save('multi_row_data.xlsx')

4. 读取工作表中的数据

from openpyxl import load_workbook

# 打开现有的工作簿

wb = load_workbook('existing_workbook.xlsx')

# 选择工作表

ws = wb['Sheet1']

# 读取所有行数据

for row in ws.iter_rows(values_only=True):

print(row)

# 或者按行和列索引读取数据

cell_value = ws.cell(row=2, column=3).value

print(cell_value)

5. 样式设置

OpenPyXL还支持样式设置，如设置字体、背景颜色、边框等。

from openpyxl import Workbook

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

# 创建一个新的工作簿

wb = Workbook()

# 激活工作簿中的默认工作表

ws = wb.active

# 设置字体样式

font = Font(name='Arial', bold=True, size=14)

ws['A1'].font = font

# 设置背景颜色

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

ws['B1'].fill = fill

# 设置边框

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

ws['C1'].border = border

# 保存工作簿

wb.save('styled_workbook.xlsx')

6. 操作工作表

OpenPyXL还允许你创建、复制、删除工作表

from openpyxl import Workbook

# 创建一个新的工作簿

wb = Workbook()

# 创建一个新的工作表

new_sheet = wb.create_sheet(title='NewSheet')

# 复制工作表

copied_sheet = wb.copy_worksheet(wb.active)

copied_sheet.title = 'Copy of Sheet1'

# 删除工作表（注意：只能删除通过openpyxl创建的工作表）

如果要删除通过Excel创建的工作表，需要手动更改工作簿的sheetnames或使用其他方法

del wb['Sheet1'] # 这行代码在直接运行时可能会引发错误，由于Sheet1可能不存在或不允许删除

# 保存工作簿

wb.save('manipulated_workbook.xlsx')

三. python的openpyxl库的使用场景

openpyxl 是一个超级实用的 Python 库，它提供了对 Excel 2010 xlsx/xlsm/xltx/xltm 文件的读写支持。以下是一些常见的使用场景：

1. 数据报告生成：

从数据库或其他数据源提取数据，并将其整理成 Excel 报告。

2. 数据分析：

读取 Excel 文件中的数据进行进一步的数据处理和分析。

3. 自动化数据处理：

自动化 Excel 文件中的数据更新，例如，根据特定条件更新单元格值。

4. 模板生成：

使用 Excel 模板填充数据，生成定制化的文档。

5. 数据可视化：

利用 Excel 的图表功能，将数据以图形的方式展示。

6. 数据导入导出：

从应用程序导出数据到 Excel，或从 Excel 导入数据到应用程序。

7. 样式和格式应用：

为 Excel 文件中的单元格或范围应用特定的样式和格式。

8. 公式和函数：

在 Excel 文件中使用公式和函数进行复杂的数据计算。

9. 图表和数据透视表：

创建图表和数据透视表以分析和展示数据。

10. 工作表管理：

创建、删除、隐藏或复制工作表。

11. 数据验证：

对 Excel 文件中的数据进行验证，确保数据的准确性。

12. 条件格式化：

根据单元格中的数据应用不同的格式规则。

13. 宏和VBA脚本：

虽然 openpyxl 不直接支持宏，但可以保存包含宏的 Excel 文件。

14. 数据筛选和排序：

对 Excel 文件中的数据进行自动筛选和排序。

15. 多工作簿操作：

同时操作多个工作簿，合并或对比数据。

16. 自动化测试：

在自动化测试中生成测试数据或验证测试结果。

17. 教育和学术研究：

在教育和研究中生成数据集，进行统计分析。

18. 企业资源规划（ERP）：

与 ERP 系统集成，自动更新库存或财务数据。

19. 客户关系管理（CRM）：

导出 CRM 系统中的客户数据，进行进一步的分析和个性化营销。

20. 项目管理：

跟踪项目的进度和预算，生成项目状态报告。

openpyxl 的灵活性和功能丰富性使其成为处理 Excel 数据的理想选择，无论是在商业、教育、科研还是个人项目中。