openpyxl 是一个用于读写 Excel 2010 xlsx/xlsm 文件的 Python 库。它提供了强大的接口，让开发者可以轻松地操作 Excel 文档，进行数据读写和格式化处理。
特性
- 易用性：简单直观的 API，易于学习和使用。
- 功能丰富：支持读写操作，以及多种格式化选项。
- 扩展性：允许自定义扩展，满足不同需求。
- 社区支持：拥有活跃的社区，提供丰富的资源和问题解答。
- 性能：优化性能，处理大型文件时更加高效。
如何安装或者引入 openpyxl
使用 pip 命令安装 openpyxl 库：
pip install openpyxl
在 Python 代码中引入 openpyxl：
import openpyxl
基本功能openpyxl
读取 Excel 文件
# 加载已存在的 Excel 文件
wb = openpyxl.load_workbook('example.xlsx')
# 获取活动工作表
sheet = wb.active
# 读取单元格值
value = sheet['A1'].value
创建 Excel 文件
# 创建一个工作簿
wb = openpyxl.Workbook()
# 创建一个新的工作表
sheet = wb.create_sheet(title='My Sheet')
# 向单元格写入数据
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
# 保存工作簿
wb.save('new_document.xlsx')
修改单元格数据
# 修改活动工作表中的单元格数据
sheet['A1'] = 'Updated Value'
# 添加新的单元格数据
sheet['C1'] = 'New Data'
删除工作表
# 删除工作表
del wb['Sheet']
# 保存修改后的工作簿
wb.save('modified_document.xlsx')
高级功能openpyxl
使用样式
from openpyxl.styles import Font, Border, Side, PatternFill
# 设置字体
cell = sheet['A1']
cell.font = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None, underline='none', strike=False)
# 设置边框
cell.border = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
# 设置填充色
cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
处理公式
# 设置单元格公式
sheet['A2'] = '=SUM(A1:B1)'
# 计算公式结果
value = sheet['A2'].value
数据验证
from openpyxl.worksheet.datavalidation import DataValidation
# 创建数据验证对象
dv = DataValidation(type="whole", operator="between", formula1=1, formula2=5)
sheet.add_data_validation(dv)
# 添加数据验证到单元格
dv.add(sheet['A1'])
openpyxl的实际应用场景
数据分析
# 读取数据并进行分析
data = []
for row in sheet.iter_rows(min_row=2, max_col=sheet.max_column, max_row=sheet.max_row):
row_data = [cell.value for cell in row]
data.append(row_data)
# 对数据进行处理
# ...（数据处理逻辑）
自动化报告生成
# 生成报告
for index, row_data in enumerate(data):
for col_index, value in enumerate(row_data):
sheet.cell(row=index+2, column=col_index+1, value=value)
# 保存报告
wb.save('report.xlsx')
总结
openpyxl 是处理 Excel 文件的强大工具，无论是基本的数据操作还是复杂的数据分析，都能轻松应对。通过本文的介绍，相信你已经对 openpyxl 有了初步的认识，开始探索它的无限可能吧！
如何安装openpyxl
首先，要使用openpyxl这个强大的库来操作Excel文件，你需要先安装它。你可以使用以下命令通过pip来安装：
pip install openpyxl
安装完成后，你可以在Python代码中通过以下方式引入openpyxl库：
import openpyxl
openpyxl的功能特性
易用性
openpyxl 提供了简单直观的 API，使得操作 Excel 文件变得轻而易举。
功能强大
支持读写 Excel 文件，包括但不限于单元格操作、样式应用、公式处理等。
兼容性
完全兼容 Excel 的 .xlsx 文件格式，确保文件的正确读写。
扩展性
允许自定义数据验证规则，满足复杂的数据处理需求。
性能
优化了内存使用和执行速度，适用于处理大型 Excel 文件。
openpyxl的基本功能
读取Excel文件
使用openpyxl，可以轻松读取Excel文件中的数据。以下是一个简单的示例：
from openpyxl import load_workbook
# 加载现有工作簿
workbook = load_workbook(filename='example.xlsx')
# 获取活动工作表
sheet = workbook.active
# 读取A1单元格的值
value = sheet['A1'].value
print(value) # 输出单元格内容
写入Excel文件
除了读取，还可以向Excel文件中写入数据：
from openpyxl import Workbook
# 创建一个新的工作簿
workbook = Workbook()
# 获取当前活动工作表
sheet = workbook.active
# 写入数据到A1单元格
sheet['A1'] = 'Hello World!'
# 保存工作簿
workbook.save(filename='hello_world.xlsx')
修改单元格内容
修改已存在的单元格内容同样简单：
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook(filename='example.xlsx')
# 获取工作表
sheet = workbook.active
# 修改A1单元格内容
sheet['A1'] = 'New Value'
# 保存工作簿
workbook.save(filename='example_modified.xlsx')
添加行和列
在Excel中添加行和列是常见操作，以下是如何实现的示例：
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook(filename='example.xlsx')
# 获取工作表
sheet = workbook.active
# 在第一行前插入一行
sheet.insert_rows(1)
# 在第一列前插入一列
sheet.insert_cols(1)
# 写入新行和新列的数据
sheet['A1'] = 'New Column Data'
sheet['B1'] = 'New Row Data'
# 保存工作簿
workbook.save(filename='example_with_new_rows_cols.xlsx')
删除行和列
删除行和列也是openpyxl的一个基本功能：
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook(filename='example.xlsx')
# 获取工作表
sheet = workbook.active
# 删除第一行
sheet.delete_rows(1)
# 删除第一列
sheet.delete_cols(1)
# 保存工作簿
workbook.save(filename='example_with_deleted_rows_cols.xlsx')
复制工作表
有时候需要复制整个工作表，可以使用以下方法：
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook(filename='example.xlsx')
# 获取原始工作表
original_sheet = workbook.active
# 复制工作表
new_sheet = workbook.copy_worksheet(original_sheet)
new_sheet.title = 'Copied Sheet'
# 保存工作簿
workbook.save(filename='example_with_copied_sheet.xlsx')
使用样式
openpyxl允许你对单元格应用样式，比如字体、颜色等：
from openpyxl import Workbook
from openpyxl.styles import Font, Color
# 创建工作簿
workbook = Workbook()
sheet = workbook.active
# 设置单元格字体和颜色
cell = sheet['A1']
cell.font = Font(name='Calibri', size=11, bold=True, color='00FF0000')
cell.value = 'Styled Cell'
# 保存工作簿
workbook.save(filename='styled_example.xlsx')
openpyxl的高级功能
数据验证
在openpyxl中，你可以对单元格设置数据验证规则，以确保用户输入的数据符合预期格式。
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.active
# 创建一个数据验证对象，只允许输入大于0的整数
dv = DataValidation(type="whole", operator="greater than", formula1=0, showDropDown=True)
ws.add_data_validation(dv)
# 将数据验证添加到第一列的前十行
for row in range(1, 11):
cell = f'A{row}'
dv.add(ws[cell])
wb.save("data_validation.xlsx")
条件格式
openpyxl支持条件格式，可以根据单元格的值自动更改样式。
from openpyxl import Workbook
from openpyxl.styles import Font, Color
wb = Workbook()
ws = wb.active
# 添加条件格式
ws.conditional_formatting.add(
ColorScale(
min_color=Color("FF0000"),
max_color=Color("00FF00"),
min_font=Font(color="FF0000"),
max_font=Font(color="00FF00"),
formula=['$A$1:$A$10']
)
)
# 填充数据
for i in range(1, 11):
ws[f'A{i}'] = i
wb.save("conditional_formatting.xlsx")
图表
创建图表可以帮助直观展示数据。
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
# 添加数据
for i in range(1, 11):
ws[f'A{i}'] = i
ws[f'B{i}'] = i**2
# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=2)
categories = Reference(ws, min_col=1, min_row=1, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# 将图表添加到工作表中
ws.add_chart(chart, "C2")
wb.save("chart.xlsx")
数据透视表
数据透视表是分析大量数据的有力工具。
from openpyxl import Workbook
from openpyxl.pivot import PivotTable
wb = Workbook()
ws = wb.active
# 添加数据
data = [
["Name", "Sales", "Date"],
["Alice", 1000, "2023-01-01"],
["Bob", 1500, "2023-01-02"],
["Charlie", 2000, "2023-01-01"],
["David", 2500, "2023-01-03"]
]
for row in data:
ws.append(row)
# 创建数据透视表
pt = PivotTable(
fields=["Date", "Sales"],
rows=["Date"],
cols=["Name"],
values=["Sales"],
data_range=ws["A1:C5"],
table_range=ws["G1"]
)
# 将数据透视表添加到工作表
ws.add_pivot_table(pt)
wb.save("pivot_table.xlsx")
自定义函数
openpyxl允许你定义自定义函数，以便在Excel中使用。
from openpyxl import Workbook
from openpyxl.utils import register_function
def custom_function(x):
return x * 10
# 注册自定义函数
register_function(custom_function, name="CUSTOMFN")
wb = Workbook()
ws = wb.active
# 使用自定义函数
ws["A1"] = "Custom Function Result"
ws["B1"] = "=CUSTOMFN(10)"
wb.save("custom_function.xlsx")
openpyxl的实际应用场景
数据分析处理
在数据分析领域，openpyxl 允许程序员轻松读取和写入 Excel 文件，以下是一个简单的数据读取示例：
from openpyxl import load_workbook
# 加载现有的Excel文件
wb = load_workbook('example.xlsx')
# 选择工作表
sheet = wb.active
# 读取数据
data = []
for row in sheet.iter_rows(values_only=True):
data.append(row)
# 处理数据
# ...
# 保存文件
wb.save('example_processed.xlsx')
自动化报告生成
使用 openpyxl 可以自动化生成包含图表和统计数据的报告：
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.chart import BarChart, Reference
# 创建新的Excel文件
wb = Workbook()
sheet = wb.active
# 写入数据
sheet.append([1, 2, 3])
sheet.append([4, 5, 6])
sheet.append([7, 8, 9])
# 添加图表
chart = BarChart()
data = Reference(sheet, min_col=1, min_row=1, max_row=3, max_col=3)
chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, "E2")
# 添加样式
fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
sheet['A1'].fill = fill
# 保存文件
wb.save('report.xlsx')
数据库数据导入导出
openpyxl 可以用于将数据库数据导入到 Excel 文件中，或者将 Excel 数据导出到数据库：
import sqlite3
from openpyxl import Workbook
# 连接到SQLite数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 创建新的Excel文件
wb = Workbook()
sheet = wb.active
# 从数据库读取数据并写入Excel
cursor.execute("SELECT * FROM table_name")
rows = cursor.fetchall()
for row in rows:
sheet.append(row)
# 保存文件
wb.save('exported_data.xlsx')
# 关闭数据库连接
conn.close()
Excel模板应用
openpyxl 支持使用现有的 Excel 模板，使得重复的任务更加高效：
from openpyxl import load_workbook
# 加载模板文件
template_wb = load_workbook('template.xlsx')
template_sheet = template_wb.active
# 填充模板
template_sheet['A1'] = "New Data"
# 保存文件
template_wb.save('filled_template.xlsx')
API自动化测试
在自动化测试中，openpyxl 可以用来验证 API 返回的 Excel 文件内容：
from openpyxl import load_workbook
import requests
# 请求API获取Excel文件
response = requests.get('http://example.com/data.xlsx')
with open('downloaded_data.xlsx', 'wb') as f:
f.write(response.content)
# 加载Excel文件
wb = load_workbook('downloaded_data.xlsx')
sheet = wb.active
# 验证数据
assert sheet['A1'].value == "Expected Value"
教育和培训
在教育和培训中，openpyxl 可以用来创建交互式的 Excel 练习，帮助学生更好地理解数据处理的原理：
from openpyxl import Workbook
# 创建新的Excel文件
wb = Workbook()
sheet = wb.active
# 添加示例数据
sheet.append(["Name", "Age", "Score"])
sheet.append(["Alice", 22, 90])
sheet.append(["Bob", 24, 85])
sheet.append(["Charlie", 23, 95])
# 添加公式
sheet['D2'] = "=SUM(B2:C2)"
sheet['D3'] = "=SUM(B3:C3)"
sheet['D4'] = "=SUM(B4:C4)"
# 保存文件供学生练习
wb.save('exercise.xlsx')
总结
openpyxl 为我们操作 Excel 文件提供了强大的功能，让我们能够轻松实现数据的读写、处理和分析。通过本文的介绍，相信你已经对 openpyxl 有了更深入的了解，可以将其应用到实际工作中，提高工作效率。不断学习和实践，让我们一起成为更好的程序员！
编程、AI、副业交流：https://t.zsxq.com/19zcqaJ2b
领【150 道精选 Java 高频面试题】请 go 公众号：码路向前 。
扫一扫
6000
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
