第四十章：openpyxl模块
1.安装
pip install openpyxl
2.快速使用
1.新建文件
from openpyxl import Workbook
# 创建一个新的工作簿
wb = Workbook()
# 激活默认的工作表，如果没有指定，默认就是第一个工作表
ws = wb.active
# 给工作表命名
ws.title = "Sheet1"
# 改变 sheet 标签按钮颜色（很少用）
# ws.sheet_properties.tabColor = "1072BA"
# 添加数据
ws["A1"] = "Hello"
ws["B1"] = "World"
ws["A2"] = 42
ws["B2"] = 3.14
# 保存工作簿到文件
wb.save("first_workbook.xlsx")
2.打开文件
from openpyxl import load_workbook
wb = load_workbook('文件名称.xlsx')
ws = wb.active # 获取当前活动的工作表
3.pandas 方式
import pandas as pd
# 读取 Excel 文件
ra_path = "文件路径/文件名.xlsx"
df = pd.read_excel(ra_path)
# 获取指定列的数据
column_name = "列名称"
column_data = df[column_name]
# 获取多个列的数据
columns = ["列名1", "列名2", "列名3"]
selected_data = df[columns]
# 输出列数据
print(column_data)
print(selected_data)
3.性能介绍
如果文件较大，最好参考下优化模式中的只读模式、只写模式
来源：https://openpyxl-chinese-docs.readthedocs.io/zh-cn/latest/performance.html
1.只读模式
from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']
for row in ws.rows:
for cell in row:
print(cell.value)
2.只写模式
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# now we'll fill it with 100 rows x 200 columns
for irow in range(100):
...ws.append(['%d' % i for i in range(200)])
# save the file
wb.save('new_big_file.xlsx') # doctest: +SKIP
4.详细介绍
1.sheet 增删改查
from openpyxl import load_workbook
wb = load_workbook('first_workbook.xlsx')
# 创建
## 方式一：插入到最后(default)
ws = wb.create_sheet("Sheet2")
## 方式二：插入到最开始的位置
ws = wb.create_sheet("Sheet3", 0)
## 方式三：复制工作簿中的第一个工作表（放在了最后）
new_ws = wb.copy_worksheet(wb.worksheets[1])
# 删除
## 删除工作表 Sheet2
del wb['Sheet2']
# 查询
## 查询所有工作表,输出列表: [<Worksheet "Sheet3">, <Worksheet "Sheet1">, <Worksheet "Sheet1 Copy">]
all_work = wb.worksheets
print(all_work)
## 获取当前活动的工作表: <Worksheet "Sheet1 Copy">
ws = wb.active
print(ws)
## 访问工作簿中名为"Sheet1"的工作表: <Worksheet "Sheet1">
ws = wb["Sheet1"]
print(ws)
# 修改
## 修改工作表名称: 将名为 Sheet1 修改为 Sheet11
ws = wb["Sheet1"]
ws.title = "Sheet11"
# 注意：
1.在创建、访问或操作工作表时,都需要先确保工作簿已经被加载
2.在对工作表进行任何修改后,都需要调用 wb.save() 方法来保存工作簿
wb.save("first_workbook.xlsx")
2.call 单元格操作
from openpyxl import load_workbook
wb = load_workbook('first_workbook.xlsx')
ws = wb.active
1.获取最大行、列
# 返回值类型：int
ws.max_row: 获取最大行
ws.max_column：获取最大列
2.单元格
# 方法一：坐标
a = ws['A4']
print(type(a)) # <class 'openpyxl.cell.cell.Cell'>
# 方法二：row 行；column 列
b = ws.cell(row=4, column=2)
print(type(b)) # <class 'openpyxl.cell.cell.Cell'>
# 方法三：只要访问就创建
for row in range(1, ws.max_row + 1):
for col in range(1, ws.max_column + 1):
val = ws.cell(row=row, column=col)
print(type)
# 注意：
## 常用
# 基础单元格对象： <class 'openpyxl.cell.cell.Cell'>
# 合并单元格对象： <class 'openpyxl.cell.cell.MergedCell'>
## 不常用
# 只读单元格： <class 'openpyxl.cell.cell.ReadOnlyCell'>
# 只写单元格： <class 'openpyxl.cell.cell.WriteOnlyCell'>
2.多单元格
from openpyxl import load_workbook
wb = load_workbook('first_workbook.xlsx')
1.切片、行、列
返回类型：元祖，<class 'tuple'>
元祖内的值：单元格对象
((<Cell 'Sheet1'.A1>, <MergedCell 'Sheet1'.B1>, <MergedCell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <MergedCell 'Sheet1'.B2>, <MergedCell 'Sheet1'.C2>))
# 方法一：
cell_range = ws['A1':'C2']
print(type(cell_range), cell_range)
# 方法二：
col_c = ws['C'] # 列
row_5 = ws[5] # 行
2.行、列索引
使用行和列的索引，按行从工作表中生成单元格，指定迭代范围。
如果没有指定索引，则范围从A1开始。
1.如果指定 min_row=3, min_col=4，表示行从第 3 行起，末行止；列从第 4 列起，末列止
2.如果指定 max_row=7, max_col=4，表示行从第 1 行起，第 7 行止；列从第 1 列起，第 4 列止
如果工作表中没有单元格，则将返回一个空元组。
# min_row: 开始行数
# max_row: 结束行数
# min_col: 开始列数
# max_col: 结束列数
# values_only：是否返回单元格值
## 是：返回值，为空时 None
## 否：返回单元格对象
# 方法一：通过指定范围(行 → 行)
for row in ws.iter_rows(min_row=3, max_row=7, min_col=3, max_col=4, values_only=True):
for cell in row:
print(cell)
# 方法二：通过指定范围(列 → 列)
for row in ws.iter_cols(min_col=3, max_col=5, min_row=2, max_row=3, values_only=False):
for cell in row:
print(cell)
3.遍历 Sheet
# 方法一：生成器
# 包含每一行的数据，每一行类型为 tuple
rows = ws.rows
cols = ws.columns
print(type(rows), rows)
print(type(cols), cols)
# <class 'generator'> <generator object Worksheet._cells_by_row at 0x7fb8f2281e50>
# <class 'generator'> <generator object Worksheet._cells_by_col at 0x7fb8f2281f50>
row_list = [row for row in rows]
col_list = [col for col in cols]
# 方法二：序列取值
for row_index in range(1, ws.max_row + 1):
for col_index in range(1, ws.max_column + 1):
cell = ws.cell(row=row_index, column=col_index)
print(cell)
3.矩阵置换 行 → 列
1.zip 方法
rows = [
['num', 'filed_1', 'filed_1'],
[1, 5, 34],
[2, 2, 56],
[3, 0, 56],
[4, 20, 90],
[5, 7, 14],
]
print(list(zip(*rows)))
# [('num', 1, 2, 3, 4, 5), ('filed_1', 5, 2, 0, 20, 7), ('filed_1', 34, 56, 56, 90, 14)]
# 注意
# zip() 方法会舍弃缺少数据的列，可以先使用 pandas 进行补全
# 操作如下
df = pd.DataFrame(rows[1:], columns=rows[0])
print(df)
2.pandas 方法
import pandas as pd
# 读取 Excel 文件
df = pd.read_excel('file.xlsx')
# 对矩阵进行置换
t_df = df.T
# 将置换后的矩阵保存到新的 Excel 文件
with pd.ExcelWriter('new_file.xlsx') as fw:
t_df.to_excel(fw, index=False, sheet_name='Sheet')
4.赋值
# 方法一：设置单元格的值
ws['A1'] = 'Hello, Excel!'
# 方法二：使用行列坐标设置单元格的值
ws.cell(row=2, column=2, value='Hello, Excel!')
3.设置单元格风格
wb = load_workbook('first_workbook.xlsx')
ws = wb.active # 获取当前活动的工作表
1.字体
# 导入库
from openpyxl.styles import Font, colors
# 设置字体:
## name：字体样式、seze：字体大小、bold：加粗、italic：斜体、color：字体颜色
font = Font(name='Arial', size=16, bold=True, italic=True, color='FF0000')
ws['A1'].font = font
# 保存到 xlsx
wb.save('first_workbook.xlsx')
2.单元格底色
from openpyxl.styles import PatternFill
# 设置单元格底色
## start_color：起始颜色（十六进制）、end_color：结束颜色（十六进制）、fill_type: 填充类型（如 'solid'、'none' 等）
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A2'].fill = fill
# 保存工作簿
wb.save('first_workbook.xlsx')
3.对齐方式
from openpyxl.styles import Alignment
# 设置对齐方式
## horizontal：水平、vertical：垂直
## horizontal："general", "left", "center", "right", "fill", "justify", "centerContinuous","distributed"
## vertical："top", "center", "bottom", "justify", "distributed"
alignment = Alignment(horizontal='center', vertical='center')
ws['B1'].alignment = alignment
# 保存工作簿
wb.save('first_workbook.xlsx')
4.行高、列宽
# 设置列宽：width
ws.column_dimensions['A'].width = 20 # 设置 A 列宽为 20
ws.column_dimensions['B'].width = 30 # 设置 B 列宽为 30
# 设置行高：height
ws.row_dimensions[1].height = 40 # 设置第 1 行高为 40
ws.row_dimensions[2].height = 25 # 设置第 2 行高为 25
# 保存工作簿
wb.save('first_workbook.xlsx')
5.合并单元格
# 在合并的单元格中写入文本
# 合并 A1 到 D1 的单元格
ws.merge_cells('A1:D1')
ws['A1'] = "This is a merged cell across four columns."
# 合并第二行的单元格
# 合并 A2 到 C2
ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=3)
ws['A2'] = "Merged cell in row 2"
wb = load_workbook('first_workbook.xlsx')
ws = wb.active
1.常用方法
# 1.获取所有合并单元格的列表
merged_ranges = ws.merged_cells.ranges
# 2.遍历单元格列表，判断单元格是否为合并单元格
cell = ws.cell(row=2, column=3) # row: 第 2 行 col: 第 3 列
for merged_range in merged_ranges:
# cell.coordinate: 表示单元格的坐标,输出 C2
if cell.coordinate in merged_range: # 判断该单元格所属的合并区域
return True
return False
# 3.判断该单元格是否为合并单元格
## 方法一
from openpyxl.cell import MergedCell
isinstance(cell, MergedCell) # 用于检查 cell 对象是否是一个 MergedCell 的实例
## 方法二
if ws['A1'] in ws.merged_cells:
print('A1是合并单元格')
# 4.获取合并区域左上角的单元格作为该单元格的值返回
# 即循环中的 merged_range
merged_range.min_row # 合并单元格的最小行
merged_range.min_col # 合并单元格的最小列
merged_range.max_row # 合并单元格的最大行
merged_range.max_col # 合并单元格的最大列
# 5.返回合并单元格的左上角至右下角单元格的行列号
from openpyxl.utils import range_boundaries
min_row, min_col, max_row, max_col = range_boundaries(merged_range)
2.实例
遍历整个 excel 文件，输出是否为单元格，如果是，则输出该单元格的合并范围
def parser_merged_cell(self, ws: Worksheet, row, col, merged_ranges):
"""
判断单元格是否为合并单于格
是：获取合并的偏移量，否：返回 None
:param ws: 当前工作表对象
:param row: 需要获取的单元格所在行
:param col: 需要获取的单元格所在列
:param merged_ranges: 所有合并单元格的范围
:return: 单元格对象、合并范围
"""
cell = ws.cell(row=row, column=col)
if not isinstance(cell, MergedCell): # 判断该单元格是否为合并单元格
for merged_range in merged_ranges: # 循环查找该单元格所属的合并区域
if cell.coordinate in merged_range:
# 获取合并区域左上角的单元格作为该单元格的值返回
# +1 是为了横向或纵向没有合并时，表示存在一个占位
coord = {
'x_use': merged_range.max_col - merged_range.min_col + 1,
'y_use': merged_range.max_row - merged_range.min_row + 1,
}
return cell, coord
return cell, True
return None, None
excel_data = []
for row_index in range(1, ws.max_row + 1):
for col_index in range(1, ws.max_column + 1):
pt = {
'id': None, # 单元格坐标ID
'x': None, # X轴位置
'y': None, # Y轴位置
'value': '', # 单元格的值
'bg_color': '', # 单元格背景颜色
'is_edit': 1, # 是否可以编辑
'x_use': 1, # X轴所占格数
'y_use': 1, # Y轴所占格数
}
cell_, coord = parser_merged_cell(ws, row_index, col_index, merged_ranges)
# 过滤合并单元格造成的空单元格
if cell_ and coord:
pt['id'] = f'{col_index}_{row_index}'
pt['x'] = col_index
pt['y'] = row_index
cell_v = cell_.value
bg_color = cell_.fill.bgColor.value # 获取背景色
# 单元格的值不为空且无背景色：
# 存在一个 key 列表，单元格值存在与该列表中，则不可编辑
if cell_v:
pt['value'] = cell_v
if cell_v in detection_keys:
pt['is_edit'] = 0
if is_template and cell_v and isinstance(bg_color, int):
pt['value'] = ''
# coord：合并单元格所占的 X、Y 轴的长度
if isinstance(coord, dict):
pt.update(coord)
# 用于判断单元格为空：且不可编辑
# 单元格是否存在背景颜色（该位置颜色代码：64 灰色，占不判断）且值为空
if isinstance(bg_color, int) and not cell_v:
pt['is_edit'] = 0
pt['bg_color'] = bg_color
excel_data.append(pt)
6.图表
# 注意事项
图表的位置是通过指定单元格（如 "E5"、"E20"）来确定的。
可以根据需要调整数据和图表的样式
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, PieChart
def create_icon():
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 写入数据
data = [
['品类', '销售额'],
['电子产品', 300],
['服装', 150],
['家居', 200],
['玩具', 100]
]
for row in data:
ws.append(row)
# 创建柱状图
bar_chart = BarChart()
bar_chart.title = "销售额柱状图"
bar_chart.x_axis.title = "品类"
bar_chart.y_axis.title = "销售额"
# 设置数据范围，即最小行至最大行、最小列至最大列
data_ref = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=5)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
# 添加数据和类别
bar_chart.add_data(data_ref, titles_from_data=True)
bar_chart.set_categories(categories_ref)
# 将柱状图插入工作表
ws.add_chart(bar_chart, "E5")
# 创建饼图
pie_chart = PieChart()
pie_chart.title = "销售额饼图"
# 设置数据范围
pie_chart.add_data(data_ref, titles_from_data=True)
pie_chart.set_categories(categories_ref)
# 将饼图插入工作表
ws.add_chart(pie_chart, "E20")
# 保存工作簿
wb.save('second_workbook.xlsx')
if __name__ == '__main__':
create_icon()
浙公网安备 33010602011771号