1、一个简单的excel

2、简单的一些操作

# openpyxl
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

# 加载excel
wb_obj = load_workbook(filename="big.xlsx")
# 获取表单名称
names = wb_obj.sheetnames
print(names)
# 获取表单对象 sheet_obj
sheet_obj: Worksheet = wb_obj["Sheet1"]
print(type(sheet_obj))  # openpyxl.worksheet.worksheet.Worksheet

# 通过索引取值，索引从0开始
# sheet_obj = wb_obj.worksheets[0]
# print(sheet_obj) # <Worksheet "Sheet2">

# 行操作
max_row = sheet_obj.max_row
max_column = sheet_obj.max_column
print(max_row, max_column)

# rows = sheet_obj.rows
# for obj in sheet_obj.rows:
#     print(obj)  # 每一行作为一个对象
#     for i in obj:
#         print(i)
#         print(i.value)

# 行切片
# min_row=None, 起始行索引值 默认从1开始
# max_row=None, 结束行索引值 默认从1开始
# min_col=None,  起始列索引值 默认从1开始
# max_col=None,  结束列索引值 默认从1开始

# values_only=False 返回对象   如果是True： 返回单元格对应的数据
# result1 = sheet_obj.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=True)
result1 = sheet_obj.iter_rows(values_only=True)
print(result1)  # 生成器
print(list(result1))

# 列操作
result2 = sheet_obj.iter_cols(values_only=True)
print(result2)  # 生成器
print(list(result2))

# 获取单元格数据 通过行和列来获取
res1 = sheet_obj.cell(2, 3)
print(res1, res1.value)
# 获取单元格数据 B列的第几行
res2 = sheet_obj["B3"]
print(res2.value)

wb_obj.close()

 打印结果：

 3、数据的修改

# openpyxl
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

# 加载excel
wb_obj = load_workbook(filename="big.xlsx")
# 获取表单名称
names = wb_obj.sheetnames
print(names)
# 获取表单对象 sheet_obj
sheet_obj: Worksheet = wb_obj["Sheet2"]
print(type(sheet_obj))  # openpyxl.worksheet.worksheet.Worksheet

res1 = sheet_obj.cell(3, 3)
print(res1, res1.value)
res1 = sheet_obj.cell(3, 3, "3,3你被我改了")
print(res1, res1.value)

res2 = sheet_obj["B3"]
print(res2.value)
sheet_obj["B3"] = "B3我把你改了哈"
print(res2.value)

wb_obj.save("big.xlsx")
wb_obj.close()

打印结果： 

 修改后的excel：

 4、excel封装

# 数据封装

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet


class HandleExcel:
    def __init__(self, file_name, sheet_name):
        self.wb_obj = load_workbook(filename=file_name)
        self.sheet_obj: Worksheet = self.wb_obj[sheet_name]
        # sheet_obj: Worksheet = wb_obj["Sheet1"]

    def get_excel_case(self):
        case_list = []
        # wb_obj = load_workbook(filename="big.xlsx")
        # sheet_obj: Worksheet = wb_obj["Sheet1"]
        datas = list(self.sheet_obj.iter_rows(values_only=True))
        # print(datas)
        case_title = datas[0]
        case_datas = datas[1:]
        # print(case_title, case_datas)
        for case in case_datas:
            # print(case)  # 每一行即是一个元组
            res = dict(zip(case_title, case))
            # print(res)
            case_list.append(res)
        self.close_excel()
        print(case_list)
        # return case_list

    def close_excel(self):
        self.wb_obj.close()


if __name__ == '__main__':
    cl = HandleExcel("big.xlsx", "Sheet2")
    cl.get_excel_case()

打印结果：

这样的封装用于ddt类似场景

 [{"key1": "val1", "key2": "val2"},{"key11": "val11", "key22": "val22"}]