文章目录
openpyxl 简介
openpyxl是一个卓越的第三方库，专门用于处理Excel文件（.xlsx）；
兼容于Excel 2010及后续版本的文件格式，提供高效且直观的方式来操控Excel文件；
无论是读取和写入单元格数据，还是创建和修改工作表，甚至Excel的各种特性openpyxl都能轻松应对；
openpyxl 核心优势
- **先进的xlsx支持：**openpyxl专注于处理最新的.xlsx文件格式；
- **强大的读写功能：**对现有的Excel文件，轻松提取数据，进行修改，并保存为新的文件；
- **灵活的单元格操作：**支持按行、列或单元格进行数据的读取和写入；
- **工作表的全面控制：**创建新的工作表、复制或删除现有工作表、设置工作表属性等；
- **丰富的样式设置：**支持设置单元格的字体、颜色、边框、对齐等样式；
- **图表与公式支持：**支持创建图表和添加复杂的公式；
- **数字和日期格式的精确处理：**支持处理数字和日期等特殊格式，确保在Excel中呈现的数据格式准确无误；
openpyxl 核心功能
数据操作：
- 读取与写入：轻松从Excel文件中读取数据并将其写入Python变量，或反之。
- 创建与修改：允许创建全新的Excel文件，添加、修改或删除工作表，以及合并或拆分单元格。
样式与格式化：
- 单元格样式：可以设置单元格的字体、颜色、对齐方式、边框等。
- 条件格式化：支持基于特定条件（如值范围、文本内容等）自动应用样式。
公式与函数：
- 内置公式支持：可以直接在单元格中使用Excel的内置公式和函数，如SUM、AVERAGE等。
可视化与多媒体：
- 图表插入：允许在工作表中插入各种图表，如柱状图、折线图等，以增强数据展示效果。
- 图像插入：可以将图片插入到Excel工作表中，为数据报告提供直观的视觉内容。
数据验证与安全性：
- 数据验证：可以设置单元格的数据验证规则，确保输入数据的准确性和一致性。
- 工作簿保护：可以对整个工作簿或特定工作表设置保护，防止未经授权的修改。
打印与导出：
- 打印设置：提供打印参数设置，如页边距、页眉页脚、打印区域等。
- 数据导出：除了Excel格式，还支持将数据导出为CSV、JSON等其他常见格式。
性能与兼容性：
- 性能优化：针对大型数据集，openpyxl提供了优化的读写性能，确保处理大量数据时的高效性。
- 与其他库兼容：与Python中的其他数据处理库（如Pandas、NumPy等）具有良好的兼容性，可以方便地与其他库结合使用，进行更复杂的数据分析和处理任务。
综上所述，openpyxl库为Python用户提供了一个强大的工具，使他们能够轻松地与Excel文件进行交互，无论是读取、写入、修改数据，还是创建复杂的报告和可视化内容。
openpyxl 核心的类
Workbook
- 对应 Excel 工作簿，是 openpyxl 中最核心的类。
- 用于创建、打开、保存和关闭 Excel 文件。
- 可以通过 Workbook 类来添加、删除或修改工作表（Worksheet）。
Worksheet
- 对应 Excel 工作表。
- 提供了一系列方法来操作单元格数据，如读取、写入、修改等。
- 可以通过 Worksheet 类来操作单元格的样式、公式、图表等。
Cell
- 对应 Excel 单元格。
- 可以通过 Cell 类来访问、修改单元格的值、样式等。
- Cell 类还提供了方法来获取单元格的坐标、数据类型等信息。
Font
- 用于修改单元格字体。
- 通过 Font 类可以设置字体名称、大小、颜色、加粗、斜体等样式。
- 这些设置可以应用于单个单元格或多个单元格。
Alignment
- 用于设置单元格文本的对齐方式。
- 可以设置水平对齐（如居中、靠左、靠右）和垂直对齐（如靠上、居中、靠下）。
Border
- 用于设置单元格边框的样式。
- 可以设置边框的粗细、颜色、线条样式等。
Fill
- 用于设置单元格的填充样式。
- 可以设置填充的背景色、前景色、填充模式等。
NamedStyle
- 用于创建和管理自定义样式。
- 可以将一组样式设置保存为一个命名样式，并在多个单元格或工作表中应用。
openpyxl 应用场景
openpyxl库广泛应用于Excel文件的自动化处理和分析，包括数据清洗、报告生成、自动化办公、数据分析和可视化，以及与其他系统集成等场景，可帮助提高工作效率和数据处理能力。
openpyxl 安装使用
openpyxl属于第三方库，需要额外下载安装，命令如下：
pip install openpyxl
创建新工作薄
创建Excel文件时，必须包含至少一个Sheet页（工作表）；
openpyxl默认会自动创建一个Sheet页，名称叫Sheet；
from openpyxl import workbook
# 实例化Workbook类时，会创建一个新的Excel文件，并生成一个默认工作表，叫“Sheet”
excel_object = workbook.Workbook()
# 保存文件到指定路径
excel_object.save("./example.xlsx")
创建新工作表
from openpyxl import workbook
# 实例化Workbook类时，会创建一个新的Excel文件，并生成一个默认工作表，叫“Sheet”
excel_object = workbook.Workbook()
# 创建新sheet页，默认在最后追加创建，可通过index参数，在指定索引处创建
excel_object.create_sheet("Sheet1")
excel_object.create_sheet("Sheet2")
excel_object.create_sheet("Sheet3", index=0)
# 保存文件到指定路径
# 但凡对工作薄产生影响的操作，都需要调用save函数进行保存
excel_object.save("./example.xlsx")
获取工作表对象
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 无论是数据的增删改查还是样式的新增修改都是基于工作表进行的
# 工作流程：获取工作薄-通过工作薄获取工作表-通过工作表操作数据-保存工作薄
# 通过sheet页名称获取工作表对象，此时获取前面创建文件时生成的默认工作表
# 方式一：通过 excel_object.active 获取默认的（第一个）工作表
# sheet_object = excel_object.active
# 方式二：通过工作表的名称
sheet_object = excel_object["Sheet1"]
# 方式三：通过工作表的名称
# sheet_object = excel_object.get_sheet_by_name("Sheet1")
print(type(sheet_object), sheet_object) # <class 'openpyxl.worksheet.worksheet.Worksheet'> <Worksheet "Sheet1">
# 修改默认工作表的名称
sheet_object.title = "Sheet4"
# 保存文件到指定路径
excel_object.save("./example.xlsx")
print(sheet_object.title) # 工作表的名称
print(sheet_object.max_row) # 工作表中已使用的最大行
print(sheet_object.min_row) # 工作表中已使用的最小行
print(sheet_object.max_column) # 工作表中已使用的最大列
print(sheet_object.min_column) # 工作表中已使用的最小列
print(sheet_object.dimensions) # 工作表的大小，即含有数据的表格的左上角坐标和右下角坐标
print(sheet_object.rows) # 按行获取单元格（Cell对象）生成器
print(sheet_object.columns) # 按列获取单元格（Cell对象）生成器
print(sheet_object.freeze_panes)# 冻结窗格
print(sheet_object.values) # 按行获取表格的内容（数据）生成器
获取所有工作表名称
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 使用sheetnames属性获取工作薄中的所有工作表名称
sheet_names = excel_object.sheetnames
print(sheet_names) # ['Sheet3', 'Sheet4', 'Sheet1', 'Sheet2']
获取所有工作表对象
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 使用worksheets属性获取工作薄中所有工作表对象
print(excel_object.worksheets) # [<Worksheet "Sheet3">, <Worksheet "Sheet4">, <Worksheet "Sheet1">, <Worksheet "Sheet2">]
###删除指定工作表
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
print(excel_object.sheetnames) # ['Sheet3', 'Sheet4', 'Sheet1', 'Sheet2']
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet4"]
# 使用remove函数删除指定的工作表对象
excel_object.remove(sheet_object)
print(excel_object.sheetnames) # ['Sheet3', 'Sheet1', 'Sheet2']
# 保存文件到指定路径
excel_object.save("./example.xlsx")
拷贝指定工作表
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet3"]
# 使用copy_worksheet方法拷贝指定工作表对象
copy_sheet = excel_object.copy_worksheet(sheet_object)
# 默认拷贝后的sheet名称是原sheet页名称+ Copy
print(copy_sheet.title) # Sheet3 Copy
# 保存文件到指定路径
excel_object.save("./example.xlsx")
写入单元格内容
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 准备写入工作薄的数据
sheet_data1 = ["学号", "姓名", "性别", "生日", "班级"]
sheet_data2 = ["S01", "王铁锤", "女", "2000-11-11", "S9501"]
sheet_data3 = [
["S02", "赵德柱", "男", "1999-08-25", "S9502"],
["S03", "洪绿灯", "女", "1998-09-19", "S9502"],
["S04", "吴鱼子", "女", "1999-06-26", "S9501"],
["S05", "林蛋大", "男", "2000-03-03", "S9502"],
["S06", "刘鼻涕", "男", "1997-09-09", "S9502"],
["S07", "杜紫藤", "女", "1998-12-29", "S9501"],
["S08", "陈大海", "男", "2000-02-22", "S9502"],
["S09", "叶夏阳", "女", "1999-05-15", "S9501"],
["S10", "夏投南", "男", "1997-11-10", "S9501"]
]
# 方式一：指定坐标写入
sheet_object['A1'].value = sheet_data1[0]
sheet_object['B1'].value = sheet_data1[1]
sheet_object['C1'].value = sheet_data1[2]
# 方式二：通过行和列索引写入
sheet_object.cell(row=1, column=4).value = sheet_data1[3]
sheet_object.cell(row=1, column=5).value = sheet_data1[4]
# 方式三：通过append方法将列表或元祖的数据追加到工作表的末尾行
sheet_object.append(sheet_data2)
# 将所有的数据写入工作表
for item in sheet_data3:
sheet_object.append(item)
# 保存文件到指定路径
excel_object.save("./example.xlsx")
读取单元格内容
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 读取单元格数据
cell_value = sheet_object['A2'].value
print("单元格A2的值：", cell_value) # 单元格A2的值： S01
# 通过行和列索引读取数据
cell_value = sheet_object.cell(row=2, column=2).value
print("单元格B2的值：", cell_value) # 单元格B2的值： 王铁锤
修改单元格内容
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 修改单元格数据
print(sheet_object['B11'].value) # 夏投南
sheet_object['B11'].value = "笑哈哈"
print(sheet_object['B11'].value) # 笑哈哈
# 通过行和列索引修改数据
print(sheet_object.cell(row=11, column=2).value) # 笑哈哈
sheet_object.cell(row=11, column=2).value = "笑嘻嘻"
print(sheet_object.cell(row=11, column=2).value) # 笑嘻嘻
# 保存文件到指定路径
excel_object.save("./example.xlsx")
读取一行内容
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过行号直接获取
# 获取工作表中指定行号的数据对象（返回一行中所有的单元格对象）
row_object = sheet_object[1] # (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
# 通过列表推导式完成获取每个单元格对象的值（value）
row_values = [i.value for i in row_object]
print("第一行单元格内容：", row_values) # 第一行单元格内容： ['学号', '姓名', '性别', '生日', '班级']
# 方式二：通过iter_rows方法获取
# 使用iter_rows方法获取指定最小行号和最大行号之间的所有行数据（返回的是一个生成器对象）
row_object = sheet_object.iter_rows(min_row=2, max_row=2, values_only=True) # <generator object Worksheet._cells_by_row at 0x7ff73854cf20>
for row_values in row_object:
print("第二行单元格内容：", row_values) # 第二行单元格内容： ('S01', '王铁锤', '女', '2000-11-11', 'S9501')
# 方式三：通过rows属性获取
# sheet_object.rows 是一个生成器对象，其中包含了整个工作表中的数据对象
for row_num, row in enumerate(sheet_object.rows, start=1):
if row_num == 3: # 假设要获取第2行（表头不算）
row_values = [cell.value for cell in row]
print("第三行单元格内容是：", row_values) # 第三行单元格内容： ['S02', '赵德柱', '男', '1999-08-25', 'S9502']
break # 因为我们只想获取指定行的数据，当满足条件时，跳出循环
# 方式四：通过单元格坐标获取
# sheet_object.max_column可以获取工作表已使用的最大列数
# 通过遍历第4行的每一列，获取一行数据
row_values = []
for col in range(1, sheet_object.max_column + 1):
cell_value = sheet_object.cell(row=4, column=col).value
row_values.append(cell_value)
print("第四行单元格内容：", row_values) # 第四行单元格内容： ['S03', '洪绿灯', '女', '1998-09-19', 'S9502']
读取一列数据
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过列号直接获取
# 指定要遍历列的字母索引（例如，列 A）
# 获取工作表中指定列号的数据对象（返回一列中所有的单元格对象）
column_object = sheet_object["A"] # (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>)
column_values = [i.value for i in column_object]
print("第一列单元格内容：", column_values) # 第一列单元格内容： ['学号', 'S01', 'S02', 'S03', 'S04', 'S05', 'S06', 'S07', 'S08', 'S09', 'S10']
# 方式二：通过iter_rows方法获取
# 使用iter_cols方法获取指定最小列号和最大列号之间的所有行数据（返回的是一个生成器对象）
column_object = sheet_object.iter_cols(min_col=2, max_col=2, values_only=True) # <generator object Worksheet._cells_by_col at 0x7ff3b8365900>
for row_values in column_object:
print("第二列单元格内容：", row_values) # 第二列单元格内容： ('姓名', '王铁锤', '赵德柱', '洪绿灯', '吴鱼子', '林蛋大', '刘鼻涕', '杜紫藤', '陈大海', '叶夏阳', '笑嘻嘻')
# 方式三：通过列范围获取
# 通过列范围读取一列数据，例如读取从 C1 到 C11 的数据
column_object = sheet_object['C1:C11']
column_values = []
for cell in column_object:
column_values.append(cell[0].value)
print("第三列单元格内容：", column_values) # 第三列单元格内容： ['性别', '女', '男', '女', '女', '男', '男', '女', '男', '女', '男']
# 方式四：通过单元格坐标获取
# sheet_object.max_row可以获取工作表的最大行数
# 通过遍历第4列的每一行，获取一列数据
column_values = []
for row in range(1, sheet_object.max_row + 1):
cell_value = sheet_object.cell(row=row, column=4).value
column_values.append(cell_value)
print("第四列单元格内容：", column_values) # 第四列单元格内容： ['生日', '2000-11-11', '1999-08-25', '1998-09-19', '1999-06-26', '2000-03-03', '1997-09-09', '1998-12-29', '2000-02-22', '1999-05-15', '1997-11-10']
读取所有行所有列
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过for循环和max_row、max_column属性获取
for row in range(1, sheet_object.max_row + 1):
for col in range(1, sheet_object.max_column + 1):
cell = sheet_object.cell(row=row, column=col).value
print(cell, end=' ')
# 输出内容如下：
# 学号 姓名 性别 生日 班级
# S01 王铁锤 女 2000-11-11 S9501
# S02 赵德柱 男 1999-08-25 S9502
# S03 洪绿灯 女 1998-09-19 S9502
# S04 吴鱼子 女 1999-06-26 S9501
# S05 林蛋大 男 2000-03-03 S9502
# S06 刘鼻涕 男 1997-09-09 S9502
# S07 杜紫藤 女 1998-12-29 S9501
# S08 陈大海 男 2000-02-22 S9502
# S09 叶夏阳 女 1999-05-15 S9501
# S10 笑嘻嘻 男 1997-11-10 S9501
print()
# 方式二：通过iter_rows()遍历获取
# iter_rows()方法返回一个生成器，按行产生单元格的值。
# values_only=True参数意味着仅返回单元格的值，而不是单元格对象。
for row in sheet_object.iter_rows(values_only=True):
print(row)
# 输出内容如下：
# ('学号', '姓名', '性别', '生日', '班级')
# ('S01', '王铁锤', '女', '2000-11-11', 'S9501')
# ('S02', '赵德柱', '男', '1999-08-25', 'S9502')
# ('S03', '洪绿灯', '女', '1998-09-19', 'S9502')
# ('S04', '吴鱼子', '女', '1999-06-26', 'S9501')
# ('S05', '林蛋大', '男', '2000-03-03', 'S9502')
# ('S06', '刘鼻涕', '男', '1997-09-09', 'S9502')
# ('S07', '杜紫藤', '女', '1998-12-29', 'S9501')
# ('S08', '陈大海', '男', '2000-02-22', 'S9502')
# ('S09', '叶夏阳', '女', '1999-05-15', 'S9501')
# ('S10', '笑嘻嘻', '男', '1997-11-10', 'S9501')
# 方式三：通过iter_cols()遍历所有列并通过zip方法进行行列转换
# iter_cols()方法类似于iter_rows()，但是它是按列产生单元格的值。
values = []
for col in sheet_object.iter_cols(values_only=True):
values.append(col)
values = list(zip(*values))
for value in values:
print(value)
# 输出内容如下：
# ('学号', '姓名', '性别', '生日', '班级')
# ('S01', '王铁锤', '女', '2000-11-11', 'S9501')
# ('S02', '赵德柱', '男', '1999-08-25', 'S9502')
# ('S03', '洪绿灯', '女', '1998-09-19', 'S9502')
# ('S04', '吴鱼子', '女', '1999-06-26', 'S9501')
# ('S05', '林蛋大', '男', '2000-03-03', 'S9502')
# ('S06', '刘鼻涕', '男', '1997-09-09', 'S9502')
# ('S07', '杜紫藤', '女', '1998-12-29', 'S9501')
# ('S08', '陈大海', '男', '2000-02-22', 'S9502')
# ('S09', '叶夏阳', '女', '1999-05-15', 'S9501')
# ('S10', '笑嘻嘻', '男', '1997-11-10', 'S9501')
读取指定行列范围的单元格
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过切片的方式获取
# 获取叫'A2'到'E4'范围的所有单元格
rows = sheet_object['A2':'E4']
for cells in rows:
for cell in cells:
print(cell.value, end=" ")
# 输出内容如下：
# S01 王铁锤 女 2000-11-11 S9501
# S02 赵德柱 男 1999-08-25 S9502
# S03 洪绿灯 女 1998-09-19 S9502
print()
# 方式二：通过iter_rows()方法获取
# 获取 2到4行，1到3列 范围的所有单元格
for row in sheet_object.iter_rows(min_row=2, min_col=1, max_col=5, max_row=4):
for cell in row:
print(cell.value, end=" ")
# 输出内容如下：
# S01 王铁锤 女 2000-11-11 S9501
# S02 赵德柱 男 1999-08-25 S9502
# S03 洪绿灯 女 1998-09-19 S9502
print()
新增行
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过append方法在数据末尾追加一行数据
data = ["S11", "白莲花", "女", "1999-10-10", "S9501"]
sheet_object.append(data)
# 方式二：通过insert_rows方法在指定行插入一行数据
sheet_object.insert_rows(12)
data = ["S12", "王芭丹", "男", "1999-08-20", "S9502"]
for idx, item in enumerate(data, start=1):
sheet_object.cell(row=12, column=idx).value = item
# 方法三：通过insert_rows方法在指定行插入多行数据
sheet_object.insert_rows(12, 2)
data = [
["S13", "史珍香", "女", "1999-06-21", "S9501"],
["S14", "朱八拐", "男", "1998-05-20", "S9502"]
]
for row, items in enumerate(data, start=12):
for col, item in enumerate(items, start=1):
sheet_object.cell(row=row, column=col).value = item
# 保存文件到指定路径
excel_object.save("./example.xlsx")
复制行
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 复制第12行数据（获取第12行数据，插入到第12行前）
row_values = [i.value for i in sheet_object[12]]
row_values_len = len(row_values)
sheet_object.insert_rows(12)
for col in range(1, row_values_len + 1):
sheet_object.cell(row=12, column=col).value = row_values[col-1]
# 保存文件到指定路径
excel_object.save("./example.xlsx")
删除行
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 删除行号为12的数据
sheet_object.delete_rows(12)
# 删除行号为12及后面的5行数据
sheet_object.delete_rows(12, 5)
# 保存文件到指定路径
excel_object.save("./example.xlsx")
新增列
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 在指定列前插入一列数据
sheet_object.insert_cols(6)
data = [
"联系电话",
"10086001", "10086002", "10086003", "10086004", "10086005",
"10086006", "10086007", "10086008", "10086009", "10086010"
]
for row, item in enumerate(data, start=1):
sheet_object.cell(row=row, column=6).value = item
# 在指定行插入多行数据
sheet_object.insert_cols(7, 2)
data = [
["编程语言",
"Python", "Java", "Go", "Python", "C++",
"Python", "Java", "C", "Go", "Python"
],
["编程等级",
"初级", "中级", "中级", "高级", "中级",
"高级", "资深", "大神", "初级", "中级"
]
]
for col, items in enumerate(data, start=7):
for row, item in enumerate(items, start=1):
sheet_object.cell(row=row, column=col).value = item
# 保存文件到指定路径
excel_object.save("./example.xlsx")
复制列
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 复制第七列数据
column_object = sheet_object.iter_cols(min_col=7, max_col=7, values_only=True)
column_values = [item for item in column_object][0]
sheet_object.insert_cols(7)
for row in range(1, len(column_values) + 1):
sheet_object.cell(row=row, column=7).value = column_values[row - 1]
# 保存文件到指定路径
excel_object.save("./example.xlsx")
删除列
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 删除列号为7的数据
sheet_object.delete_cols(7)
# 删除列号为7及后面5列数据
sheet_object.delete_cols(7, 5)
# 保存文件到指定路径
excel_object.save("./example.xlsx")
合并单元格
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过切片的方式
# 合并A2到C4范围的单元格
# sheet_object.merge_cells("A2:C4")
# 方式二：通过指定起始行和列的方式
sheet_object.merge_cells(start_row=2, start_column=1, end_row=4, end_column=3)
拆分单元格
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 方式一：通过切片的方式
# 合并A2到C4范围的单元格
# sheet_object.unmerge_cells('A2:C4')
# 方式二：通过指定起始行和列的方式
sheet_object.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=3)
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置字体样式
Font类用于定义单元格中文本的字体样式
常用参数及含义如下所示：
- **name：**字体名称，如 Calibri、Arial、微软雅黑等等
- **size：**字体大小
- **bold：**是否加粗，True为加粗，False为不加粗
- **italic：**是否斜体，True为斜体，False为不斜体
- **underline：**下划线类型，如none、single、double、singleAccounting、doubleAccounting等
- **color：**字体颜色，可以是RGB颜色值或预定义颜色名称
- **strike：**字体是否带有删除线True 或 False
- **vertAlign：**字体垂直对齐方式，如baseline、superscript、subscript等
from openpyxl import load_workbook
from openpyxl.styles import Font
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 创建一个加粗、红色字体的样式对象
font = Font(name='微软雅黑', size=20, bold=True, color='00FF0000', italic=True)
# 将字体样式应用到第一行的第一列的数据值上
sheet_object.cell(row=1, column=1).font = font
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置背景颜色
用于设置单元格的填充样式；
支持为单元格的背景设置颜色、填充模式（如实心、渐变等）以及其他相关属性；
常用参数及含义如下所示：
- **start_color：**填充的起始颜色（通常为字符串表示的十六进制颜色代码）
- **end_color：**填充的结束颜色（用于渐变填充）
- **rotation：**填充旋转角度（0-180度）
- **fill_type：**填充类型，例如：
- solid - 实心填充
- darkDown - 深色向下渐变
- darkUp - 深色向上渐变
- darkGrid - 深色网格
- darkHorizontal - 深色水平渐变
- darkVertical - 深色垂直渐变
- lightDown - 浅色向下渐变
- lightUp - 浅色向上渐变
- lightGrid - 浅色网格
- lightHorizontal - 浅色水平渐变
- lightVertical - 浅色垂直渐变
- none - 无填充
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 创建一个实心填充样式，颜色为黄色
fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
# 将字体样式应用到第一行的第一列的数据值上
sheet_object.cell(row=1, column=2).fill = fill
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置边框样式
用于设置单元格边框的样式，包括边框线条的样式、颜色、粗细等；
Border类通常作为Side对象的集合；
Side对象表示单元格的一个特定边（上、下、左、右）；
常用参数及含义如下所示：
- **top：**上边框的样式。Side对象，Border(top=Side(style=‘thin’, color=‘000000’))将上边框设置为细线且颜色为黑色。
- **bottom：**下边框的样式。Side对象，Border(bottom=Side(style=‘medium’, color=‘FF0000’))将下边框设置为中等粗细且颜色为红色。
- **left：**下边框的样式。Side对象，Border(left=Side(style=‘dashed’, color=‘00FF00’))将左边框设置为虚线且颜色为绿色。
- **right：**下边框的样式。Side对象，Border(right=Side(style=‘double’, color=‘0000FF’))将右边框设置为双线且颜色为蓝色。
- **diagonalUp：**设置单元格对角线向上（从左到右）的样式。Side对象，Border(diagonalUp=Side(style=‘thin’))设置对角线向上为细线。
- **diagonalDown：**设置单元格对角线向下（从右到左）的样式。Side对象，Border(diagonalDown=Side(style=‘dotted’))设置对角线向下为点线。
- **diagonal：**同时设置对角线向上和向下的样式。Side对象，Border(diagonal=Side(style=‘thick’))同时设置对角线向上和向下为粗线。
Side对象用于定义边框线条的样式，它包含以下参数：
- **style：**边框线条的样式，如thin、medium、dashed、dotted、thick、double、hair等；
- **color：**边框线条的颜色，可以是RGB颜色代码或预定义的颜色名称；
from openpyxl import load_workbook
from openpyxl.styles import Border, Side
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 创建一个边框对象，上下左右边框都是thin细线样式
border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# 将字体样式应用到第一行的第一列的数据值上
sheet_object.cell(row=1, column=3).border = border
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置对齐方式
用于设置单元格中文本的对齐方式
常用参数及含义如下所示：
- **horizontal：**控制文本在单元格内的水平对齐方式，如general、left
center、right、fill、justify、centerContinuous、distributed等- **vertical：**设置文本在单元格内的垂直对齐方式，如bottom、center、top、justify、distributed等；
- **text_rotation：**设置文本旋转的角度，一个介于 -90 到 90 之间的整数。
- **wrap_text：**控制文本是否自动换行，如果设置为 True，则文本会根据单元格宽度自动换行；
- **shrink_to_fit：**控制文本是否缩小以适应单元格宽度，如果设置为 True，则文本字体大小会自动缩小以适应单元格宽度。
- **indent：**设置文本的首行缩进，一个介于 0 到 5 的整数，表示首行缩进的字符数
from openpyxl import load_workbook
from openpyxl.styles import Alignment
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 创建一个对齐方式对象
alignment = Alignment(horizontal='center', vertical='center', wrap_text=True, text_rotation=80)
# 将字体样式应用到第一行的第一列的数据值上
sheet_object.cell(row=2, column=1).alignment = alignment
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置超链接
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# 外部超链接（指向 URL）
sheet_object.cell(row=5,column=5).value = "访问百度"
sheet_object.cell(row=5,column=5).hyperlink = "https://www.baidu.com"
sheet_object.cell(row=5,column=5).style = 'Hyperlink'
# 内部超链接
# 在Sheet1中写入超链接，链接到Sheet2
link = "#Sheet2!A1"
# 注意点：如果sheet名称中包含特殊的字符，如：-(中划线)，需要用单引号将sheet名称引起来
# link = "#'Sheet-1'!A1"
sheet_object.cell(row=5,column=6).hyperlink = link
sheet_object.cell(row=5,column=6).style = 'Hyperlink'
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置行高
from openpyxl import load_workbook
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
sheet_object.row_dimensions[2].height = 50
# 保存文件到指定路径
excel_object.save("./example.xlsx")
设置列宽
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
# 打开现有的Excel文件（工作薄）
excel_object = load_workbook('./example.xlsx')
# 创建需要读取的工作表对象
sheet_object = excel_object["Sheet1"]
# get_column_letter 是 openpyxl 模块中的一个实用方法，它用于将列的数字索引转换为 Excel 中的列字母标识。
# 在 Excel 中，列通常使用字母来标识，例如 A、B、C 等，对于超过 26 列的情况，则使用 AA、AB、AC 等双字母标识，依此类推。
column_name = get_column_letter(2)
sheet_object.column_dimensions[column_name].width = 50
# 保存文件到指定路径
excel_object.save("./example.xlsx")
真实封装
代码
import os
import re
from typing import Optional, List, Dict, Union, Any, Generator, Literal, Tuple, Pattern
import openpyxl
from openpyxl.cell import Cell
from openpyxl.drawing.image import Image
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
class OpenpyxlExcelTool:
"""
利用openpyxl库封装的Excel文件操作工具类
:func create_excel: 如果目标文件路径上的excel文件不存在，则在该路径上创建excel实体文件
:func create_sheet: 如果指定的sheet页名称不存在，则在该excel文件操作对象中新建sheet页
:func acquire_excel_object: 如果目标文件路径上的excel文件存在，则获取该文件的操作对象
:func acquire_sheet_object: 获取excel文件中指定的sheet页操作对象
:func acquire_sheet_names: 获取excel文件中所有的sheet页名称
:func acquire_sheet_cols: 获取sheet页中第一行所有的数据列内容（表头）和列数
:func acquire_sheet_rows: 获取sheet页中所有的数据行、单元格坐标和行数
:func acquire_sheet_data: 获取sheet页中所有的数据行和数据列中的内容，并根据表头内容压缩成列表嵌套字典对象，每个子元素代表一行数据
:func acquire_cell_value: 获取sheet页中指定单元格的值
:func acquire_sheet_series: 获取sheet页中指定行或列的所有单元格内容
:func acquire_header_letter: 获取sheet页中指定的表头行对应的字母索引
:func append_data_to_sheet: 在sheet页的尾部追加数据行或数据列，又或是，在指定行号或列号前插入数据行或数据列
:func modify_cell_value: 修改sheet页中指定单元格的内容
:func modify_series: 修改sheet页中指定数据行或数据列的所有单元格的内容
:func delete_series: 删除sheet页中指定数据行或数据列的所有单元格的内容
:func set_rows_height: 设置sheet页中所有数据行的高度，或者，设置指定行的高度
:func set_cols_width: 设置sheet页中所有数据列的宽度（自适应单元格文本长度），或者，设置指定列的宽度
:func set_cell_color: 设置指定单元格的背景颜色
:func set_rows_color: 设置隔行变色（默认）将sheet页中偶数行设置背景颜色，或者，为指定行设置背景色
:func set_cols_color: 设置隔列变色（默认）将sheet页中偶数列设置背景颜色，或者，为指定列设置背景色
:func set_rows_style: 为指定的行，设置单元格样式
:func set_cols_style: 为指定的列，设置单元格样式
:func set_cell_hyperlink: 为指定的单元格，设置超链接
:func set_cell_image: 为指定的单元格，插入图片
:func set_range_cell_merged: 为指定的单元格区域设置单元格合并
:func by_cell_value_set_color: 根据单元格的内容设置单元格背景色
:func by_series_value_set_color:根据数据行或数据列的内容进行背景色填充
:func by_header_acquire_letter: 根据表头名称获取字母索引、数字索引
:func check_cell_is_merged: 检查指定坐标的单元格是否为合并单元格
:func check_sheet_exists: 检查指定的sheet页名称是否存在
:func save_excel: 保存文件
"""
def __init__(self, file_path, read_only: bool = False):
self.file_path = file_path
self.read_only = read_only
self.cell_style = {
"实心填充": "solid",
"居中对齐": "center", "左对齐": "left", "右对齐": "right",
"红色": "DE5D83", "灰色": "E8E8E8",
"黄色": "FFFF00", "白色": "FFFFFF",
"蓝色": "3399FF", "粉色": "FFC0CB",
"绿色": "90EE90", "紫色": "CC99FF",
"细线": "thin", "双线": "double",
"粗线": "thick", "无线": "none",
}
def create_excel(self, sheet_name: str = "Sheet1", sheet_data: Optional[List[list]] = None) -> bool:
"""
如果目标文件路径上的excel文件不存在，则在该路径上创建excel实体文件
:param sheet_name: 非必填项；字符类型；默认值：Sheet1； 创建excel文件时必须创建一个sheet页
:param sheet_data: 非必填项；列表类型；默认值：None； 需要写入sheet页的数据，每个子列表代表一行数据
:return: 新建成功返回True，新建失败返回False
"""
# 目标文件路径已存在，跳过新建，返回False
if os.path.exists(path=self.file_path):
return False
# 目标文件路径不存在，执行新建并修改sheet页名称
excel_object: Workbook = Workbook()
sheet_object: Worksheet = excel_object.active
sheet_object.title = sheet_name
# 检查是否需要写入数据，处理完成后保存文件，并返回True
if sheet_data is not None:
for row in sheet_data:
sheet_object.append(row)
excel_object.save(filename=self.file_path)
return True
def create_sheet(self, excel_object: Workbook, sheet_name: str, sheet_data: Optional[List[list]] = None,
header_name: Optional[list] = None) -> None:
"""
如果指定的sheet页名称不存在，则在该excel文件操作对象中新建sheet页
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_name: 必填参数；字符类型；目标新建sheet页名称
:param sheet_data: 非必填项；列表类型；目标写入sheet页的数据内容
:param header_name: 非必填项；列表类型；目标写入sheet页的表头内容
:return:
"""
if self.check_sheet_exists(excel_object=excel_object, sheet_name=sheet_name):
raise ValueError(f"参数异常：无法新建同名sheet页")
# 创建新的sheet页并为其命名
excel_object.create_sheet(title=sheet_name)
sheet_object = excel_object[sheet_name]
# 检查是否需要写入数据
if sheet_data is not None:
for row in sheet_data:
sheet_object.append(row)
# 检查是否需要写入表头
elif header_name is not None:
for row in [header_name]:
sheet_object.append(row)
self.save_excel(excel_object=excel_object)
def acquire_excel_object(self) -> Workbook:
"""
如果目标文件路径上的Excel文件存在，则获取该文件的操作对象
:return: Workbook
"""
# 如果文件不存在，则抛出异常
if not os.path.exists(path=self.file_path):
raise FileNotFoundError(f"参数异常：文件路径不存在")
excel_object: Workbook = openpyxl.load_workbook(
filename=self.file_path,
data_only=True,
read_only=self.read_only
)
return excel_object
def acquire_sheet_object(self, excel_object: Workbook, sheet_name: str) -> Worksheet:
"""
获取excel文件中指定的sheet页操作对象
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_name: 必填参数；字符类型；目标新建sheet页名称
:return:
"""
if sheet_name not in self.acquire_sheet_names(excel_object):
raise KeyError(f"参数异常：上送sheet页名称不存在")
sheet_object: Worksheet = excel_object[sheet_name]
return sheet_object
@staticmethod
def acquire_sheet_names(excel_object: Workbook) -> List[str]:
"""
获取excel文件中所有的sheet页名称
:return:
"""
sheet_names: List[str] = excel_object.sheetnames
return sheet_names
@staticmethod
def acquire_sheet_cols(sheet_object: Worksheet) -> Dict[str, Union[list, int]]:
"""
获取sheet页中第一行所有的数据列内容（表头）和列数
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:return: 以字典形式返回；
{
'data': ['xxx', 'xxx', 'xxx', 'xxx'],
'count': x
}
"""
global index
# 记录标题字段
name = []
# 使用enumerate有序遍历第一行的每一个字段
for index, column in enumerate(list(sheet_object.rows)[0]):
# 将当前字段添加到列表
if column.value:
name.append(column.value)
else:
break
return {'data': name, 'count': index + 1}
@staticmethod
def acquire_sheet_rows(sheet_object: Worksheet, header_index: int = 0) -> Dict[str, Union[list, int]]:
"""
获取sheet页中所有的数据行、单元格坐标和行数
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param header_index: 非必填项；数值类型；表头所在行
:return: 以字典形式返回；
{
'data': [['xxx', 'xxx', 'xxx', 'xxx']],
'local': [(<Cell 'xx'.xx>, <Cell 'xx'.xx>, <Cell 'xx'.xx>, <Cell 'xx'.xx>)],
'count': x
}
"""
global index # 记录除表头外总行数
datagram = [] # 记录每行数据
located = [] # 记录数据坐标
# 使用enumerate有序遍历所有行
for index, row in enumerate(sheet_object.rows):
# 第一行数据为表头，跳过
if index == header_index:
continue
# 当前行的第一个字段有值，则将记录当前行所有列数据和坐标（当某行第一个单元格内容为False时则会终止记录）
# if row[0].value:
# datagram.append([cell.value for cell in row])
# located.append(row)
# else:
# break
datagram.append([cell.value for cell in row])
located.append(row)
return {'data': datagram, 'local': located, 'count': index}
def acquire_sheet_data(self, excel_object: Workbook, sheet_name: str) -> List[Dict[str, Any]]:
"""
获取sheet页中所有的数据行和数据列中的内容，并根据表头内容压缩成列表嵌套字典对象，每个子元素代表一行数据
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_name: 必填参数；字符类型；sheet页名称
:return: 以列表套字典形式返回，每一个字典都是以表头和行数据组合成的Key-Value
[{'XX': 'xx', 'XX': 'xx', 'XX': 'xx', 'XX': 'xx'}]
"""
# 获取sheet对象
sheet_object: Worksheet = self.acquire_sheet_object(
excel_object=excel_object,
sheet_name=sheet_name
)
# 获取sheet页中所有列的表头和列数
cols = self.acquire_sheet_cols(sheet_object)
cols_name, cols_num = cols.get("data"), cols.get("count")
if not cols_name or not cols_num:
raise KeyError(f"数据异常：获取表头或列数异常或为空")
# 获取sheet页中所有行的坐标和总行数
rows = self.acquire_sheet_rows(sheet_object)
rows_local, rows_num = rows.get("local"), rows.get("count")
if not rows_local or not rows_num:
raise KeyError(f"数据异常：获取数据或行数异常或为空")
# 压缩并转为字典：使用二维循环推导式（先看最右侧循环遍历每一行所使用的单元格，其次再在循环列的数量取出每一个单元格的值）
return [dict(zip(cols_name, [row[i].value for i in range(cols_num)])) for row in rows_local]
@staticmethod
def acquire_cell_value(sheet_object: Worksheet, row_index: int, col_index: int) -> str:
"""
获取sheet页中指定单元格的值
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param row_index: 必填参数；数值类型；行号
:param col_index: 必填参数；数值类型；列号
:return:
"""
cell_value = sheet_object.cell(row=row_index, column=col_index)
return cell_value
def acquire_sheet_series(self, excel_object: Workbook, sheet_name: str, row_index: int = None,
col_index: int = None) -> list:
"""
获取sheet页中指定行或列的所有单元格内容
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_name: 必填参数；字符类型；sheet页名称
:param row_index: 非必填项；数值类型；行号
:param col_index: 非必填项；数值类型；列号
:return:
"""
# 获取sheet页操作对象
sheet_object: Worksheet = self.acquire_sheet_object(
excel_object=excel_object,
sheet_name=sheet_name
)
# 如果同时上送了行和列，获取行内容优先
if row_index:
row_data: Generator = sheet_object.iter_rows(
min_row=row_index,
max_row=row_index,
values_only=True
)
row_data: tuple = row_data.__next__()
return list(row_data)
if col_index:
col_data: Generator = sheet_object.iter_cols(
min_col=col_index,
max_col=col_index,
values_only=True
)
col_data: tuple = col_data.__next__()
return list(col_data)
@staticmethod
def acquire_header_letter(sheet_object: Worksheet, header_index: int = 1) -> Tuple[list, list]:
"""
获取sheet页中指定的表头行对应的字母索引
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param header_index: 非必填项；数值类型；表头所在行
:return:
"""
header_names: list = []
header_letters: list = []
# 获取sheet页的表头对象
sheet_header_name: list = sheet_object[header_index]
for name in sheet_header_name:
header_names.append(name.value)
header_letters.append(name.column_letter)
return header_names, header_letters
def append_data_to_sheet(self, excel_object: Workbook, sheet_object: Worksheet,
append_data: List[list], append_name: Optional[List] = None,
append_index: Optional[int] = None, axis: Literal[0, 1] = 0) -> None:
"""
在sheet页的尾部追加数据行或数据列，又或是，在指定行号或列号前插入数据行或数据列
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param append_data: 必填参数；列表类型；需要写入sheet页的数据，每一个子列表代表一行或一列
:param append_name: 非必填项；列表类型；作用于列时使用，代表新建列的列名
:param append_index: 非必填项；列表类型；插入时使用，默认是追加，代表在某行或某列前进行插入
:param axis: 必填参数；数值类型；0表示垂直（添加行，默认），1表示水平（添加列）
:return:
"""
# 追加数据行（垂直）
if axis == 0 and append_index is None:
for row_data in append_data:
sheet_object.append(row_data)
# 插入数据行（垂直）
elif axis == 0 and append_index is not None and isinstance(append_index, int):
sheet_object.insert_rows(idx=append_index, amount=len(append_data))
for row_index, row_data in enumerate(append_data, start=append_index):
for col_index, col_data in enumerate(row_data, start=1):
sheet_object.cell(row=row_index, column=col_index, value=col_data)
# 作用于数据列
elif axis == 1:
if not append_name or not append_data:
raise ValueError("参数异常：axis为1时表示作用于数据列，append_name和append_data不可为None值")
append_name_len = len(append_name)
append_data_len = len(append_data)
if append_name_len != append_data_len:
raise ValueError("参数异常：axis为1时表示作用于数据列，append_name和append_data长度需要一致")
# 插入数据列（水平）
if append_index is not None and isinstance(append_index, int):
append_start_column = append_index # 从指定列数开始追加新的列
append_ended_column = append_start_column + append_name_len
sheet_object.insert_cols(idx=append_index, amount=append_name_len)
# 追加数据行（水平）
else:
# 根据最大列数确定追加的位置
append_start_column = sheet_object.max_column + 1 # 从最大列数之后开始追加新的列
append_ended_column = append_start_column + append_data_len
# 追加新的列名到工作表的标题行
for col_num in range(append_start_column, append_ended_column):
sheet_object.cell(row=1, column=col_num, value=append_name[col_num - append_start_column])
# 追加新的数据到工作表的数据区域
for col_num in range(append_start_column, append_ended_column):
for row_num, row_data in enumerate(append_data[col_num - append_start_column]):
sheet_object.cell(
row=row_num + 2,
column=col_num,
value=row_data
)
else:
axis_msg: str = "axis为0时表示作用于数据行，axis为1时表示作用于数据列；为1时append_name不可为None值且需与append_data长度一致；"
append_index_msg: str = "append_index不为None时表示在指定行索引或列索引处插入；"
raise ValueError(f"参数异常：{axis_msg}{append_index_msg}")
self.save_excel(excel_object=excel_object)
def modify_cell_value(self, excel_object: Workbook, sheet_object: Worksheet, row_index: int, col_index: int,
value: str) -> None:
"""
修改sheet页中指定单元格的内容
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param row_index: 必填参数；数值类型；行号
:param col_index: 必填参数；数值类型；列号
:param value: 必填参数；字符类型；写入单元格的内容
:return:
"""
sheet_object.cell(row=row_index, column=col_index, value=value)
self.save_excel(excel_object=excel_object)
def modify_series(self, excel_object: Workbook, sheet_object: Worksheet, index: int, new_values: list,
axis: Literal[0, 1]) -> None:
"""
修改sheet页中指定数据行或数据列的所有单元格的内容
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param index: 必填参数；数值类型；行号或列号
:param new_values: 必填参数；列表类型；写入某数据行或数据列的数据内容
:param axis: 必填参数；数值类型；0表示垂直（行），1表示水平（列）
:return:
"""
new_value_len = len(new_values)
if axis == 0:
for col in range(1, new_value_len + 1):
sheet_object.cell(row=index, column=col, value=new_values[col - 1])
elif axis == 1:
for row in range(1, new_value_len + 1):
sheet_object.cell(row=row + 1, column=index, value=new_values[row - 1])
self.save_excel(excel_object=excel_object)
def delete_series(self, excel_object: Workbook, sheet_object: Worksheet, axis: Literal[0, 1],
index: int, count: int = 1) -> None:
"""
删除sheet页中指定数据行或数据列的所有单元格的内容
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param axis: 必填参数；数值类型；0表示垂直（行），1表示水平（列）
:param index: 必填参数；数值类型；行号或列号
:param count: 非必填项，数值类型；删除数量，默认删除1行或1列
:return:
"""
if axis == 0:
sheet_object.delete_rows(idx=index, amount=count)
elif axis == 1:
sheet_object.delete_cols(idx=index, amount=count)
else:
raise ValueError("参数异常：axis参数只接受0或1")
self.save_excel(excel_object=excel_object)
def set_rows_height(self, excel_object: Workbook, sheet_object: Worksheet,
rows: Optional[list] = None, row_height: int = 30):
"""
设置sheet页中所有数据行的高度，或者，设置指定行的高度
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param rows: 非必填项；列表类型；需要指定设置行高的行号
:param row_height: 非必填项；数值类型；设置数据行的高度（默认30）
:return:
"""
if rows is None:
# 循环设置每一行的高度
max_row = sheet_object.max_row
for row in range(1, max_row + 1):
sheet_object.row_dimensions[row].height = row_height
else:
for row in rows:
sheet_object.row_dimensions[row].height = row_height
self.save_excel(excel_object=excel_object)
def set_cols_width(self, excel_object: Workbook, sheet_object: Worksheet,
auto_width: bool = True, cols: Optional[list] = None,
col_width: int = 30):
"""
设置sheet页中所有数据列的宽度（自适应单元格文本长度），或者，设置指定列的宽度
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param auto_width: 非必填项；布尔类型；默认为True；为所有列设置自适应宽度
:param cols: 非必填项；列表类型；指定需要设置列宽的列号
:param col_width: 非必填项；数值类型；设置指定列号宽度（默认30）
:return:
"""
def count_chinese_char(string: str) -> int:
# 预编译一个正则表达式，匹配所有中文
_pattern: str = r"[\u4e00-\u9fa5]"
_compile: Pattern = re.compile(_pattern)
# 使用findall找出所有匹配正则表达式的结果
_matches: List[str] = _compile.findall(string)
return len(_matches)
if auto_width:
# 获取当前sheet页的数据shape
max_row, max_column = sheet_object.max_row, sheet_object.max_column
for col in range(max_column):
# 根据列的数字返回对应的字母，暂定每列宽度为1
max_widths, col_name = 1, get_column_letter(col + 1)
# 获取当前列下的所有数据，取单元格值内数据最大长度
for row in range(max_row):
cell_value = str(sheet_object[f'{col_name}{row + 1}'].value)
chinese_count = count_chinese_char(cell_value)
content_length = len(str(cell_value or '' or None)) + chinese_count + 2
# 如果单元格内最大长度比假设大则修改
max_widths = content_length if content_length > max_widths else max_widths
# 把当前列所有单元格的长度设为最大长度
max_widths = 100 if max_widths > 100 else max_widths
sheet_object.column_dimensions[col_name].width = max_widths
else:
for col in cols:
column_letter = get_column_letter(col)
if column_letter not in sheet_object.column_dimensions:
sheet_object.column_dimensions[column_letter] = sheet_object.column_dimensions[column_letter]
else:
del sheet_object.column_dimensions[column_letter]
sheet_object.column_dimensions[column_letter] = sheet_object.column_dimensions[column_letter]
sheet_object.column_dimensions[column_letter].width = col_width
self.save_excel(excel_object=excel_object)
def set_cell_color(self, excel_object: Workbook, sheet_object: Worksheet, row: int, col: int,
color_name: Literal["红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"]) -> None:
"""
设置指定单元格的背景颜色
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param row: 必填参数；数值类型；行号
:param col: 必填参数；数值类型；列号
:param color_name: 必填参数；字符类型；预设的颜色名称
:return:
"""
if not row or not col:
raise ValueError("参数异常：row和col必须大于等于1")
color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(color_name)
)
sheet_object.cell(row=row, column=col).fill = color
self.save_excel(excel_object=excel_object)
def set_rows_color(self, excel_object: Workbook, sheet_object: Worksheet, rows: Optional[list] = None,
color_name: Literal["红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"] = "灰色") -> None:
"""
设置隔行变色（默认）将sheet页中偶数行设置背景颜色，或者，为指定行设置背景色
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param rows: 非必填项；列表类型；需要指定设置背景色的行号
:param color_name: 非必填项；字符类型；预设的颜色名称
:return:
"""
max_row, max_col = sheet_object.max_row, sheet_object.max_column
color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(color_name)
)
if rows is None:
for row in range(1, max_row + 1):
# 如果i能被2整除就可以得到偶数行
if row % 2 == 0:
# 循环设置每一行的背景颜色
for col in range(1, max_col + 1):
sheet_object.cell(row=row, column=col).fill = color
else:
for row in rows:
for col in range(1, max_col + 1):
sheet_object.cell(row=row, column=col).fill = color
self.save_excel(excel_object=excel_object)
def set_cols_color(self, excel_object: Workbook, sheet_object: Worksheet, cols: Optional[list] = None,
color_name: Literal["红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"] = "灰色",
start_index: int = 2) -> None:
"""
设置隔列变色（默认）将sheet页中偶数列设置背景颜色，或者，为指定列设置背景色
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param cols: 非必填项；列表类型；需要指定设置背景色的行号
:param color_name: 非必填项；字符类型；预设的颜色名称
:param start_index: 非必填项；数值类型；表头所在行（是否作用到表头）
:return:
"""
max_row, max_col = sheet_object.max_row, sheet_object.max_column
color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(color_name)
)
if cols is None:
for col in range(1, max_col + 1):
# 如果i能被2整除就可以得到偶数列
if col % 2 == 0:
# 循环设置偶数列当前单元格的背景颜色
for row in range(start_index, max_row + 1):
sheet_object.cell(row=row, column=col).fill = color
else:
for col in cols:
# 循环设置偶数列当前单元格的背景颜色
for row in range(start_index, max_row + 1):
sheet_object.cell(row=row, column=col).fill = color
self.save_excel(excel_object=excel_object)
def set_rows_style(self, excel_object: Workbook, sheet_object: Worksheet,
start_index: int = 2, rows: Optional[list] = None,
is_bold: bool = True, is_border: bool = True,
alignment_type: Literal["居中对齐", "左对齐", "右对齐"] = "居中对齐") -> None:
"""
为指定的行，设置单元格样式
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param start_index: 非必填项；数值类型；表头所在行（是否作用到表头）
:param rows: 非必填项；列表类型；需要指定设置样式的行号
:param is_bold: 必填参数；布尔类型；是否加粗
:param is_border: 必填参数；布尔类型；是否加边框
:param alignment_type:必填参数；字符类型；预设的对其方式名称
:return:
"""
side = Side(
style=self.cell_style.get("细线"),
color=self.cell_style.get("黑色")
)
max_row, max_col = sheet_object.max_row, sheet_object.max_column
condition: dict = {
"font": Font(bold=True),
"border": Border(left=side, right=side, top=side, bottom=side),
"alignment": Alignment(
horizontal=self.cell_style.get(alignment_type),
vertical=self.cell_style.get(alignment_type)
)
}
if rows is None:
rows = range(start_index, max_row + 1)
else:
if not is_bold: condition.pop("font")
if not is_border: condition.pop("border")
for row in rows:
for col in range(1, max_col + 1):
sheet_object.cell(row=row, column=col).font = condition.get("font")
sheet_object.cell(row=row, column=col).border = condition.get("border")
sheet_object.cell(row=row, column=col).alignment = condition.get("alignment")
self.save_excel(excel_object=excel_object)
def set_cols_style(self, excel_object: Workbook, sheet_object: Worksheet,
start_index: int = 2,
cols: Optional[list] = None,
is_bold: bool = True,
is_border: bool = True,
alignment_type: Literal["居中对齐", "左对齐", "右对齐"] = "居中对齐") -> None:
"""
为指定的列，设置单元格样式
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param start_index: 非必填项；数值类型；表头所在行（是否作用到表头）
:param cols: 非必填项；列表类型；需要指定设置样式的列号
:param is_bold: 必填参数；布尔类型；是否加粗
:param is_border: 必填参数；布尔类型；是否加边框
:param alignment_type:必填参数；字符类型；预设的对其方式名称
:return:
"""
side = Side(
style=self.cell_style.get("细线"),
color=self.cell_style.get("黑色")
)
max_row, max_col = sheet_object.max_row, sheet_object.max_column
condition: dict = {
"font": Font(bold=True),
"border": Border(left=side, right=side, top=side, bottom=side),
"alignment": Alignment(
horizontal=self.cell_style.get(alignment_type),
vertical=self.cell_style.get(alignment_type)
)
}
if cols is None:
cols = range(start_index, max_row + 1)
else:
if not is_bold: condition.pop("font")
if not is_border: condition.pop("border")
for col in cols:
for row in range(start_index, max_row + 1):
sheet_object.cell(row=row, column=col).font = condition.get("font")
sheet_object.cell(row=row, column=col).border = condition.get("border")
sheet_object.cell(row=row, column=col).alignment = condition.get("alignment")
self.save_excel(excel_object=excel_object)
def set_cell_hyperlink(self, excel_object: Workbook, sheet_object: Worksheet,
rows: List[int], cols: List[int], link_paths: List[str],
link_names: Optional[List[str]] = None,
color_name: Literal[
"红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"] = "蓝色") -> None:
"""
为指定的单元格，设置超链接
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param rows: 必填参数；列表类型；需要指定设置超链接的行号
:param cols: 必填参数；列表类型；需要指定设置超链接的列号
:param link_paths: 必填参数；列表类型；需要指定设置超链接的路径
:param link_names: 非必填项；列表类型；需要指定设置超链接的显示文本
:param color_name: 非必填项；字符类型；预设的颜色名称
:return:
"""
rows_len = len(rows)
cols_len = len(cols)
link_paths_len = len(link_paths)
if not (rows_len == cols_len == link_paths_len):
raise ValueError("参数异常：rows、cols、link_paths的长度必须一致")
if link_names is not None and len(link_names) != link_paths_len:
raise ValueError("参数异常：当link_names不为None时，必须和link_paths参数长度一致")
if link_names is None:
link_names = ["超链接" + str(num) for num in range(1, link_paths_len + 1)]
color = Font(bold=True, color=self.cell_style.get(color_name))
for row, col, path, name in zip(rows, cols, link_paths, link_names):
sheet_object.cell(row=row, column=col).value = f'=HYPERLINK("{path}","{name}")'
sheet_object.cell(row=row, column=col).font = color
self.save_excel(excel_object=excel_object)
def set_cell_image(self, excel_object: Workbook, sheet_object: Worksheet,
rows: List[int], cols: List[int], image_paths: List[str],
row_height: int = 50, col_width: int = 35) -> None:
"""
为指定的单元格，插入图片
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param rows: 必填参数；列表类型；需要插入图片的行号
:param cols: 必填参数；列表类型；需要插入图片的列号
:param image_paths: 必填参数；列表类型；需要插入图片的路径
:param row_height: 非必填项；数值类型；设置单元格的高度（作用到图片）
:param col_width: 非必填项；数值类型；设置单元格的宽度（作用到图片）
:return:
"""
rows_len = len(rows)
cols_len = len(cols)
image_paths_len = len(image_paths)
if not (rows_len == cols_len == image_paths_len):
raise ValueError("参数异常：rows、cols、image_paths的长度必须一致")
for row, col, path in zip(rows, cols, image_paths):
image = Image(path)
column_letter = get_column_letter(col)
image.width, image.height = col_width * 8, row_height * 1.36
sheet_object.add_image(image, f"{column_letter}{row}")
sheet_object.row_dimensions[row].height = row_height
if column_letter not in sheet_object.column_dimensions:
sheet_object.column_dimensions[column_letter] = sheet_object.column_dimensions[column_letter]
else:
del sheet_object.column_dimensions[column_letter]
sheet_object.column_dimensions[column_letter] = sheet_object.column_dimensions[column_letter]
sheet_object.column_dimensions[column_letter].width = col_width
self.save_excel(excel_object=excel_object)
def set_range_cell_merged(self, excel_object: Workbook, sheet_object: Worksheet,
shape: List[List[int]]) -> None:
"""
为指定的单元格区域设置单元格合并
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param shape: 二维列表，[开始行、开始列、结束行、结束列]
:return:
"""
for _range in shape:
left_up_anchor = _range[0]
left_down_anchor = _range[1]
right_up_anchor = _range[2]
right_down_anchor = _range[3]
sheet_object.merge_cells(
start_row=left_up_anchor,
start_column=left_down_anchor,
end_row=right_up_anchor,
end_column=right_down_anchor
)
self.save_excel(excel_object=excel_object)
def by_cell_value_set_color(self, excel_object: Workbook, sheet_object: Worksheet, row: int, col: int,
color_name: Literal["红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"],
expected_value: Union[int, str, None] = None) -> None:
"""
根据单元格的内容设置单元格背景色
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param row: 必填参数；数值类型；行号
:param col: 必填参数；数值类型；列号
:param color_name: 必填参数；字符类型；预设的颜色名称
:param expected_value:非必填项；字符类型；预期值，如果上送该参数，则会判断是否与单元格上的内容一致
:return:
"""
if not row or not col:
raise ValueError("参数异常：row和col必须大于等于1")
color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(color_name)
)
if expected_value is None or sheet_object.cell(row=row, column=col).value == expected_value:
sheet_object.cell(row=row, column=col).fill = color
self.save_excel(excel_object=excel_object)
def by_series_value_set_color(self, excel_object: Workbook, sheet_object: Worksheet, expected_values: list,
axis: Literal[0, 1] = 0, rows: Optional[list] = None, cols: Optional[list] = None,
start_index: int = 2,
passed_color_name: Literal[
"红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"] = "绿色",
failed_color_name: Literal[
"红色", "黄色", "蓝色", "绿色", "灰色", "白色", "粉色", "紫色"] = "红色"
):
"""
根据数据行或数据列的内容进行背景色填充
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param expected_values: 必填参数；字符类型；预期值，如果上送该参数，则会判断是否与单元格上的内容一致
:param axis: 非必填项；数值类型；0表示垂直（行）（默认），1表示水平（列）
:param rows: 非必填项；列表类型；需要根据单元格的内容进行填充背景色的行号
:param cols: 非必填项；列表类型；需要根据单元格的内容进行填充背景色的列号
:param start_index: 非必填项；数值类型；表头所在行（是否作用到表头）
:param passed_color_name:非必填项；字符类型；预设的颜色名称作用于匹配预期值
:param failed_color_name:非必填项；字符类型；预设的颜色名称作用于不匹配预期值
:return:
"""
if axis == 0 and not rows:
raise ValueError("参数异常：axis为0时表示作用于数据行，rows不可为None值")
if axis == 1 and not cols:
raise ValueError("参数异常：axis为1时表示作用于数据列，cols不可为None值")
max_row, max_col = sheet_object.max_row, sheet_object.max_column
passed_color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(passed_color_name)
)
failed_color = PatternFill(
patternType=self.cell_style.get("实心填充"),
fgColor=self.cell_style.get(failed_color_name)
)
if axis == 0:
for row in rows:
for col in range(1, max_col + 1):
if sheet_object.cell(row=row, column=col).value in expected_values:
sheet_object.cell(row=row, column=col).fill = passed_color
else:
sheet_object.cell(row=row, column=col).fill = failed_color
else:
for col in cols:
for row in range(start_index, max_row + 1):
if sheet_object.cell(row=row, column=col).value in expected_values:
sheet_object.cell(row=row, column=col).fill = passed_color
else:
sheet_object.cell(row=row, column=col).fill = failed_color
self.save_excel(excel_object=excel_object)
@staticmethod
def by_header_acquire_letter(sheet_object, header_names: list, header_index: int = 1) -> Tuple[list, list, list]:
"""
根据表头名称获取字母索引、数字索引
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param header_names: 必填参数；列表类型；表头内容
:param header_index: 必填参数；列表类型；表头内容所在行
:return:
"""
column_letters = []
column_indexes = []
sheet_header_cells: tuple = sheet_object[header_index]
for header_cell in sheet_header_cells:
for header_name in header_names:
if header_cell.value == header_name:
column_letter = header_cell.column_letter
column_letters.append(column_letter)
column_indexes.append(column_index_from_string(column_letter))
return header_names, column_letters, column_indexes
@staticmethod
def check_cell_is_merged(sheet_object: Worksheet,
row_index: int, col_index: int) -> Tuple[bool, any]:
"""
检查指定坐标的单元格是否为合并单元格
:param sheet_object: 必填参数；对象类型；excel文件中的sheet页操作对象
:param row_index: 必填参数；数值类型；需要检查是否为合并单元格的行号
:param col_index: 必填参数；数值类型；需要检查是否为合并单元格的列号
:return:
"""
all_merged_cells = sheet_object.merged_cell_ranges
for merged_cell in all_merged_cells:
min_row = merged_cell.min_row # 合并单元格最小行
max_row = merged_cell.max_row # 合并单元格最大行
min_col = merged_cell.min_col # 合并单元格最小列
max_col = merged_cell.max_col # 合并单元格最大列
if (min_row <= row_index <= max_row) and (min_col <= col_index <= max_col):
merged_cell_value = sheet_object.cell(min_row, min_col).value
return True, merged_cell_value
return False, None
def check_sheet_exists(self, excel_object: Workbook, sheet_name: str) -> bool:
"""
检查指定的sheet页名称是否存在
:param excel_object: 必填参数；对象类型；excel文件操作对象
:param sheet_name: 必填参数；字符类型；sheet页名称
:return:
"""
return sheet_name in self.acquire_sheet_names(excel_object=excel_object)
def save_excel(self, excel_object: Workbook):
"""
检查指定的sheet页名称是否存在
:param excel_object: 必填参数；对象类型；excel文件操作对象
:return:
"""
excel_object.save(self.file_path)
测试
import os.path
import time
import unittest
import openpyxl
from openpyxl.cell import Cell
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from utils.excel.OpenpyxlExcelTool import OpenpyxlExcelTool
class OpenpyxlExcelToolTest(unittest.TestCase):
@classmethod
def setUpClass(cls) -> None:
file_path = "OpenpyxlExcelToolTest.xlsx"
cls.openpyxl_excel_tool = OpenpyxlExcelTool(file_path=file_path)
print("开始：测试使用openpyxl封装的excel文件操作工具类")
print()
@classmethod
def tearDownClass(cls) -> None:
print()
print("结束：测试使用openpyxl封装的excel文件操作工具类")
@unittest.skip
def test_create_excel(self):
sheet_data = [
["编号", "姓名", "性别", "城市"],
["1", "张三", "男", "上海"],
["2", "李四", "女", "北京"],
["3", "王五", "男", "广州"],
["4", "赵六", "女", "深圳"]
]
create_result = self.openpyxl_excel_tool.create_excel(
sheet_data=sheet_data
)
print("Excel文件创建函数执行成功，结果：", create_result)
@unittest.skip
def test_create_sheet_set_header(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
old_sheet_names = self.openpyxl_excel_tool.acquire_sheet_names(excel_object)
old_sheet_names_len = len(old_sheet_names)
self.openpyxl_excel_tool.create_sheet(
excel_object=excel_object,
sheet_name='test_1',
header_name=["表头名称_" + str(i) for i in range(10)]
)
new_sheet_names = self.openpyxl_excel_tool.acquire_sheet_names(excel_object)
new_sheet_names_len = len(new_sheet_names)
assert old_sheet_names_len + 1 == new_sheet_names_len
print("创建sheet页成功")
@unittest.skip
def test_create_sheet_and_write_data(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
old_sheet_names = self.openpyxl_excel_tool.acquire_sheet_names(excel_object)
old_sheet_names_len = len(old_sheet_names)
self.openpyxl_excel_tool.create_sheet(
excel_object=excel_object,
sheet_name='test_2',
sheet_data=[
["ROW1_" + str(i) for i in range(10)],
["ROW2_" + str(i) for i in range(10)],
["ROW3_" + str(i) for i in range(10)],
]
)
new_sheet_names = self.openpyxl_excel_tool.acquire_sheet_names(excel_object)
new_sheet_names_len = len(new_sheet_names)
assert old_sheet_names_len + 1 == new_sheet_names_len
print("创建sheet页成功")
@unittest.skip
def test_acquire_excel_object(self):
excel_object = self.openpyxl_excel_tool.acquire_excel_object()
assert isinstance(excel_object, Workbook)
print("获取excel对象成功，结果：", excel_object)
@unittest.skip
def test_acquire_sheet_object(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
assert isinstance(sheet_object, Worksheet)
print("获取sheet对象成功，结果：", sheet_object)
@unittest.skip
def test_acquire_sheet_names(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_names = self.openpyxl_excel_tool.acquire_sheet_names(
excel_object=excel_object
)
assert sheet_names
assert isinstance(sheet_names, list)
print("获取excel文件中所有的sheet页名称函数执行成功，结果：", sheet_names)
@unittest.skip
def test_acquire_sheet_cols(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
sheet_cols: dict = self.openpyxl_excel_tool.acquire_sheet_cols(sheet_object)
assert sheet_cols
assert isinstance(sheet_cols, dict)
print("获取sheet页中的表头成功，结果：", sheet_cols.get("data"))
print("获取sheet页中的列数成功，结果：", sheet_cols.get("count"))
@unittest.skip
def test_acquire_sheet_rows(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
sheet_rows: dict = self.openpyxl_excel_tool.acquire_sheet_rows(sheet_object)
assert sheet_rows
assert isinstance(sheet_rows, dict)
print("获取sheet页中的数据成功，结果：", sheet_rows.get("data"))
print("获取sheet页中的坐标成功，结果：", sheet_rows.get("local"))
print("获取sheet页中的行数成功，结果：", sheet_rows.get("count"))
@unittest.skip
def test_acquire_sheet_data(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_data = self.openpyxl_excel_tool.acquire_sheet_data(
excel_object=excel_object,
sheet_name="Sheet1"
)
assert isinstance(sheet_data, list)
print("获取sheet页中的数据成功，结果：", sheet_data)
@unittest.skip
def test_acquire_cell_value(self):
excel_object = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
cell: Cell = self.openpyxl_excel_tool.acquire_cell_value(
sheet_object=sheet_object,
row_index=1,
col_index=1
)
assert cell
print("获取sheet页中指定单元格的数据成功，结果：", cell)
@unittest.skip
def test_acquire_sheet_series_row(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
row_data = self.openpyxl_excel_tool.acquire_sheet_series(
excel_object=excel_object,
sheet_name="Sheet1",
row_index=1
)
assert row_data
assert isinstance(row_data, list)
print("获取sheet页中指定行的数据成功，结果：", row_data)
@unittest.skip
def test_acquire_sheet_series_col(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
col_data = self.openpyxl_excel_tool.acquire_sheet_series(
excel_object=excel_object,
sheet_name="Sheet1",
col_index=2
)
assert col_data
assert isinstance(col_data, list)
print("获取sheet页中指定列的数据成功，结果：", col_data)
@unittest.skip
def test_acquire_header_letter(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1")
title_char = self.openpyxl_excel_tool.acquire_header_letter(sheet_object=sheet_object)
assert title_char
assert isinstance(title_char, tuple)
print("获取sheet页中表头内容对应的字母索引成功，结果：", title_char)
@unittest.skip
def test_append_data_to_sheet_col(self):
col_name = ["追加列1", "追加列2", "追加列3"]
col_data = [["追加列1_" + str(i) for i in range(1, 5)],
["追加列2_" + str(i) for i in range(1, 5)],
["追加列3_" + str(i) for i in range(1, 5)]]
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
old_cols_data = self.openpyxl_excel_tool.acquire_sheet_cols(
sheet_object=sheet_object
)
old_col_count = old_cols_data.get("count")
self.openpyxl_excel_tool.append_data_to_sheet(
excel_object=excel_object,
sheet_object=sheet_object,
append_data=col_data,
append_name=col_name,
axis=1)
new_cols_data = self.openpyxl_excel_tool.acquire_sheet_cols(
sheet_object=sheet_object
)
new_col_count = new_cols_data.get("count")
assert old_col_count < new_col_count
print(f"在sheet页中追加数据列成功，原始数据列：{old_col_count}，追加后数据列：{new_col_count}")
@unittest.skip
def test_append_data_to_sheet_col_assign(self):
col_name = ["插入列1", "插入列2", "插入列3"]
col_data = [["插入列1_" + str(i) for i in range(1, 5)],
["插入列2_" + str(i) for i in range(1, 5)],
["插入列3_" + str(i) for i in range(1, 5)]]
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
old_cols_data = self.openpyxl_excel_tool.acquire_sheet_cols(sheet_object=sheet_object)
old_col_count = old_cols_data.get("count")
self.openpyxl_excel_tool.append_data_to_sheet(
excel_object=excel_object,
sheet_object=sheet_object,
append_data=col_data,
append_name=col_name,
append_index=2,
axis=1
)
new_cols_data = self.openpyxl_excel_tool.acquire_sheet_cols(sheet_object=sheet_object)
new_col_count = new_cols_data.get("count")
assert old_col_count < new_col_count
print(f"在sheet页中追加数据列成功，原始数据列：{old_col_count}，追加后数据列：{new_col_count}")
@unittest.skip
def test_append_data_to_sheet_row(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
old_rows_data = self.openpyxl_excel_tool.acquire_sheet_rows(sheet_object)
old_row_count = old_rows_data.get("count")
new_row_data = [[i + 100 for i in range(4)], [i + 200 for i in range(4)]]
self.openpyxl_excel_tool.append_data_to_sheet(
excel_object=excel_object,
sheet_object=sheet_object,
append_data=new_row_data,
axis=0)
new_rows_data = self.openpyxl_excel_tool.acquire_sheet_rows(sheet_object)
new_row_count = new_rows_data.get("count")
assert new_row_count > old_row_count
print(f"在sheet页中追加数据行成功，原始数据行：{old_row_count}，追加后数据行：{new_row_count}")
@unittest.skip
def test_append_data_to_sheet_row_assign(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
old_rows_data = self.openpyxl_excel_tool.acquire_sheet_rows(sheet_object)
old_row_count = old_rows_data.get("count")
new_row_data = [["insert_" + str(i) for i in range(5)],
["insert_" + str(i) for i in range(5)],
["insert_" + str(i) for i in range(5)]]
self.openpyxl_excel_tool.append_data_to_sheet(
excel_object=excel_object,
sheet_object=sheet_object,
append_data=new_row_data,
append_index=3,
axis=0
)
new_rows_data = self.openpyxl_excel_tool.acquire_sheet_rows(sheet_object)
new_row_count = new_rows_data.get("count")
assert new_row_count > old_row_count
print(f"在sheet页中追加数据行成功，原始数据行：{old_row_count}，追加后数据行：{new_row_count}")
@unittest.skip
def test_modify_cell_value(self):
excel_object = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.modify_cell_value(
excel_object=excel_object,
sheet_object=sheet_object,
row_index=1,
col_index=1,
value="测试修改"
)
cell_value: str = self.openpyxl_excel_tool.acquire_cell_value(
sheet_object=sheet_object,
row_index=1,
col_index=1
)
assert cell_value == "测试修改"
print("修改sheet页中指定单元格数据成功，结果：", cell_value)
@unittest.skip
def test_modify_series_row(self):
values = ["ROW_" + str(i) for i in range(101, 108)]
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.modify_series(
excel_object=excel_object,
sheet_object=sheet_object,
index=2,
new_values=values,
axis=0
)
print("修改sheet页中指定行成功")
@unittest.skip
def test_modify_series_col(self):
values = ["COL_" + str(i) for i in range(101, 105)]
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.modify_series(
excel_object=excel_object,
sheet_object=sheet_object,
index=2,
new_values=values,
axis=1
)
print("在sheet页中修改指定列成功")
@unittest.skip
def test_delete_series_row(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.delete_series(
excel_object=excel_object,
sheet_object=sheet_object,
axis=0,
index=4,
count=1,
)
print("在sheet页中删除指定行成功")
@unittest.skip
def test_delete_series_col(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.delete_series(
excel_object=excel_object,
sheet_object=sheet_object,
axis=1,
index=1,
count=1,
)
print("在sheet页中删除指定列成功")
@unittest.skip
def test_set_rows_height(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_rows_height(
excel_object=excel_object,
sheet_object=sheet_object,
# rows=[3],
# row_height=66
)
print("将sheet页的行高修改成功")
@unittest.skip
def test_set_cols_width(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cols_width(
excel_object=excel_object,
sheet_object=sheet_object,
# auto_width=False,
# cols=[3,5],
# col_width=35
)
print("将指定sheet页的列宽修改成功")
@unittest.skip
def test_set_cell_color(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cell_color(
excel_object=excel_object,
sheet_object=sheet_object,
row=1,
col=1,
color_name="粉色"
)
print("为指定sheet页中指定单元格设置背景色成功")
@unittest.skip
def test_set_rows_color(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1")
self.openpyxl_excel_tool.set_rows_color(
excel_object=excel_object,
sheet_object=sheet_object,
# rows=[2, 3],
color_name="粉色"
)
print("将指定sheet页的行样式修改成功")
@unittest.skip
def test_set_cols_color(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cols_color(
excel_object=excel_object,
sheet_object=sheet_object,
# cols=[2, 3],
# color_name="粉色"
)
print("将指定sheet页的列样式修改成功")
@unittest.skip
def test_set_rows_style(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_rows_style(
excel_object=excel_object,
sheet_object=sheet_object,
rows=[5],
)
print("修改指定sheet页的指定行样式成功")
@unittest.skip
def test_set_cols_style(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cols_style(
excel_object=excel_object,
sheet_object=sheet_object,
cols=[5],
)
print("修改指定sheet页的指定列样式成功")
@unittest.skip
def test_set_cell_hyperlink(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cell_hyperlink(
excel_object=excel_object,
sheet_object=sheet_object,
rows=[6, 6, 6],
cols=[6, 7, 8],
link_names=["图1", "图2", "图3"],
link_paths=[r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg"],
)
print("在指定sheet页中按照指定单元格设置超链接成功")
@unittest.skip
def test_set_cell_image(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_cell_image(
excel_object=excel_object,
sheet_object=sheet_object,
rows=[1, 2, 3],
cols=[1, 1, 1],
image_paths=[
r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg"
]
)
sheet_object2: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="test_1"
)
self.openpyxl_excel_tool.set_cell_image(
excel_object=excel_object,
sheet_object=sheet_object2,
rows=[1, 2, 3],
cols=[1, 1, 1],
image_paths=[
r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg",
r"C:\Users\kf0266\Desktop\工作文档\0.jpg"
]
)
print("在指定sheet页中按照指定单元格的插入图片成功")
@unittest.skip
def test_set_range_cell_merged(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.set_range_cell_merged(
excel_object=excel_object,
sheet_object=sheet_object,
shape=[[3, 3, 5, 5]]
)
print("在指定sheet页中按照指定单元格区域设置单元格合并成功")
@unittest.skip
def test_by_cell_value_set_color(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.by_cell_value_set_color(
excel_object=excel_object,
sheet_object=sheet_object,
expected_value="COL_104",
row=5,
col=5,
color_name="紫色"
)
print("在指定sheet页中按照指定单元格的内容设置背景色成功")
@unittest.skip
def test_by_series_value_set_color(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
self.openpyxl_excel_tool.by_series_value_set_color(
excel_object=excel_object,
sheet_object=sheet_object,
expected_values=["COL_101"],
axis=1,
cols=[5],
)
print("在指定sheet页中按照单元格内容修改指定行的背景色成功")
@unittest.skip
def test_check_cell_is_merged(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
is_merged = self.openpyxl_excel_tool.check_cell_is_merged(
sheet_object=sheet_object,
row_index=3,
col_index=6,
)
print("在指定sheet页中检查指定的行和列是否为合并单元格成功，结果：", is_merged)
@unittest.skip
def test_by_header_acquire_letter(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
sheet_object: Worksheet = self.openpyxl_excel_tool.acquire_sheet_object(
excel_object=excel_object,
sheet_name="Sheet1"
)
header_names_letter = self.openpyxl_excel_tool.by_header_acquire_letter(
sheet_object=sheet_object,
header_names=["编号", "城市"],
)
print("在指定sheet页中获取指定标题的字母索引成功，结果：", header_names_letter)
@unittest.skip
def test_check_sheet_exists(self):
excel_object: Workbook = self.openpyxl_excel_tool.acquire_excel_object()
check_status: bool = self.openpyxl_excel_tool.check_sheet_exists(
excel_object=excel_object,
sheet_name="Sheet1"
)
assert isinstance(check_status, bool)
assert check_status
print("检查sheet页名称是否存在成功，结果：", check_status)
if __name__ == '__main__':
# 实例化测试套件
test_suite = unittest.TestSuite()
# 实例化测试加载器
test_loader = unittest.TestLoader()
# 根据指定的目录和匹配规则，递归所有子目录（模糊查询）加载测试用例
tests = test_loader.discover(start_dir=r"./", pattern="O*Test.py")
test_suite.addTest(tests)
# TextTestRunner
# 实例化测试运行器，存储测试结果，以文本形式输出到控制台
# 静默模式：verbosity=0
# 默认模式：verbosity=1
# 详细模式：verbosity=2
runner = unittest.TextTestRunner(verbosity=2)
runner.run(test_suite)
# HTMLTestRunner # 生成简易HTML报告（第三方）
# BeautifulReport # 生成详细HTML报告（第三方）
4230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
