openpyxl
是一个非常流行的 Python 库,用于读写 Excel 2007 及更新版本的 .xlsx 文件。它可以处理 Excel 文件的创建、修改、读取等操作,并且支持 Excel 的许多高级功能,如公式、图表、条件格式、筛选等
安装 openpyxl
要使用 openpyxl,首先需要安装它:
pip install openpyxl
基本概念
在使用 openpyxl 之前,了解一些基本的概念是很有帮助的:
- Workbook(工作簿):Excel 文件本身就是一个工作簿。
- Worksheet(工作表):工作簿中包含的单个表格。
- Cell(单元格):工作表中的最小数据单元。
- Row(行)和 Column(列):单元格是通过行和列的组合进行定位的。
操作 Excel 文件的基本步骤
1. 创建和保存 Excel 文件
可以使用 openpyxl
创建一个新的工作簿,并保存为 .xlsx
文件:
from openpyxl import Workbook
# 创建一个新的工作簿
wb = Workbook()
# 获取默认的工作表
ws = wb.active
# 给工作表命名
ws.title = "MySheet"
# 写入数据到单元格
ws['A1'] = "Hello"
ws['B1'] = "World"
# 保存工作簿
wb.save("example.xlsx")
2. 打开和读取 Excel 文件
可以使用 load_workbook
函数来加载现有的 Excel 文件,并读取其中的数据:
from openpyxl import load_workbook
# 加载已有的工作簿
wb = load_workbook("example.xlsx")
# 获取活动的工作表
ws = wb.active
# 读取单元格的数据
a1_value = ws['A1'].value
b1_value = ws['B1'].value
print(f"A1: {a1_value}, B1: {b1_value}")
3. 操作工作表
可以在工作簿中添加、删除、获取工作表:
# 创建一个新的工作表
new_sheet = wb.create_sheet(title="NewSheet")
# 删除一个工作表
wb.remove(new_sheet)
# 获取所有工作表的名称
sheetnames = wb.sheetnames
print(sheetnames)
4. 操作单元格
可以直接使用单元格的位置(如 A1)进行读取和写入。也可以使用行列的数字索引:
# 使用行列索引来获取单元格
cell = ws.cell(row=1, column=1)
print(cell.value)
# 写入数据到指定的单元格
ws.cell(row=2, column=2, value="New Value")
5. 操作行和列
可以对工作表的行和列进行批量操作:
# 访问一整行
row = ws[1] # 第一行
for cell in row:
print(cell.value)
# 访问一整列
col = ws['A'] # 第一列
for cell in col:
print(cell.value)
# 访问多个单元格
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell.value)
# 访问指定列和行
for col in ws.iter_cols(min_col=1, max_col=3, min_row=1, max_row=2):
for cell in col:
print(cell.value)
6. 公式与表达式
openpyxl
支持在单元格中使用公式:
# 写入公式
ws['C1'] = "=SUM(A1:B1)"
# 读取公式结果(需要重新计算后保存)
result = ws['C1'].value
print(result)
注意,openpyxl 不会自动计算公式的结果;它只会保存公式。要查看公式的计算结果,通常需要打开 Excel 或使用支持计算公式的库。
7. 样式和格式化
可以使用 openpyxl
对单元格进行样式设置,包括字体、颜色、边框、对齐等:
from openpyxl.styles import Font, Color, Alignment
# 设置列宽
ws.column_dimensions['A'].width = 20
# 设置行高
ws.row_dimensions[1].height = 30
# 设置字体样式
ws['A1'].font = Font(name='Arial', size=14, bold=True, color='FF0000')
# 设置单元格对齐,支持换行
ws['A1'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 设置单元格颜色
ws['A1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
# 设置渐变填充
gradient_fill = PatternFill(fill_type="linear", start_color="FF0000", end_color="FFFFFF")
ws['B2'].fill = gradient_fill
# 设置边框样式
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
ws['B2'].border = thin_border
# 隐藏列
ws.column_dimensions['A'].hidden = True
# 隐藏行
ws.row_dimensions[1].hidden = True
# 取消隐藏列
ws.column_dimensions['A'].hidden = False
# 取消隐藏行
ws.row_dimensions[1].hidden = False
8. 图表
openpyxl
支持创建和插入图表:
from openpyxl.chart import BarChart, Reference
# 创建一个柱状图
chart = BarChart()
data = Reference(ws, min_col=1, min_row=1, max_col=2, max_row=2)
chart.add_data(data, titles_from_data=True)
# 插入图表到工作表
ws.add_chart(chart, "E5")
在单元格内指定位置插入图片:
# 在B3B4C3C4区域插入图片
img = Image('example.png') # 替换为图片的路径
_from = AnchorMarker(1, 4 * 9525, 2, 10 * 9525) # 用于定义图片定位{行号(从0开始),行内偏移量(以EMU为单位),列号(从0开始),列内偏移量(以EMU为单位)}
to = AnchorMarker(3, -4 * 9525, 4, -8 * 9525)
img.anchor = TwoCellAnchor("twoCell", _from, to) # 设置图片的锚点位置。TwoCellAnchor 是一个锚点类型,表示图片将被放置在由 _from 和 to 定义的两个单元格之间。
ws.add_image(img) # 将图片添加到工作表 (worksheet) 中
9. 保护工作表
可以设置密码来保护工作表,防止未经授权的编辑:
ws.protection.password = 'mysecretpassword'
ws.protection.sheet = True
10. 处理合并单元格
可以合并和拆分单元格:
# 合并单元格
ws.merge_cells('A1:B1')
# 拆分合并的单元格
ws.unmerge_cells('A1:B1')
总结
openpyxl
是一个强大且灵活的工具,可以帮助你通过 Python 脚本处理 Excel 文件。无论是简单的读写操作,还是复杂的数据处理、格式化和图表创建,openpyxl 都可以胜任。以上示例涵盖了基本的功能,但 openpyxl 还支持更多高级操作,可以根据需要进一步探索和使用。