python操作excel之openpyxl的入门操作

一、安装openpyxl

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple openpyxl==3.0.7

二、excel构成

1、excel文件

2、表单对象（sheet）

3、单元格

三、读取excel信息

1、创建表格对象

1、导入模块：
    from openpyxl import load_workbook

2、语法
    wb_obj = load_workbook(filename="testCase.xlsx")

3、参数：
filename：文件名
read_only：默认 False，可以读写；True--只读
data_only： data_only=False，只会显示公式而已，默认 False
            data_only=True，有公式单元格显示公式最近计算结果
keep_vba：默认 KEEP_VBA 保留 vba 代码
keep_links：默认 True 保留外部链接

2、获取表单对象

sheet_obj = wb_obj["register"]    # 通过表单名称指定获取

sheet_obj = wb_obj.worksheets[2]  # 通过表单的索引获取，从0开始

sheet_names = wb_obj.sheetnames
print("返回的所有的sheet_name的list列表", sheet_names)
for sheet in sheet_names:
    print(sheet.title())

3、获取单元格对象

res = sheet_obj["A3"]
res = sheet_obj.cell(1,6)   # row--行, column--列

4、获取单元格内容

res = sheet_obj.cell(1,6)
print(res.value)
wb_obj.close()  # 关闭文件流

5、关于excel行操作

# 1、获取所有的行对象
rows = sheet_obj.rows
pprint.pprint(list(rows))   # [(每一行单元格对象),()]

# 2、获取所有的行的内容
for val in rows:
    for i in val:
        print(i.value)

# 3、获取表单的最大行
maxrows = sheet_obj.max_row
print("最大的行：",maxrows)

# 4、获取指定行,切片
"""
语法：sheet_obj.iter_rows()
参数：
self,
min_row=None, 最小的行索引值，从1开始的
max_row=None, 最大的行索引值，从1开始的
min_col=None, 最小的列索引值，从1开始
max_col=None, 最大的列索引值，从1开始的
values_only=False 默认False-表示获取到的是对象，
            True-获取到的是对应的值
行切片包含起始索引位置的值
默认获取的是最大行和最大列的数据（表单中所有的数据）
"""
# ----------行切片的demo实例---------

from openpyxl import load_workbook
wb_obj = load_workbook("testCase.xlsx")
sheet_obj = wb_obj["login"]
# 获取指定行和列的数据，空行就是None
data_list = sheet_obj.iter_rows(min_row=1, max_row=2, min_col=1, max_col=5, values_only=True)
data_list1 = sheet_obj.iter_rows(values_only=True)  # 全部数据
print(data_list)  # 生成器也是可迭代对象
# print(next(data_list1))  # next一下 获取一行数据
for val in data_list:
     print(val)

6、关于excel列操作

# 1、获取所有的列对象
res = sheet_obj.columns

# 2、获取最大的列
max_col = sheet_obj.max_column

# 3、获取所有列的内容
for val in res:
     for i in val:
        print(i.value)

# 4、获取指定列，切片操作
# values_only=True的场景
data_list = sheet_obj.iter_cols(values_only=True)  # 获取所有列的数据
# 获取指定的
data_list1 = sheet_obj.iter_cols(max_col=5, min_col=1,max_row=3,min_row=1,values_only=True)
# print(data_list)
for val in data_list1:
    print(val)

# values_only=False的场景
data_list1 = sheet_obj.iter_cols(max_col=5,min_col=1,max_row=3,min_row=1,values_only=False)
for val in data_list1:
    print(val.value)
wb_obj.close()

 四、写入表格信息

# 1、根据单元格名称来写
sheet_obj["A1"] = "test"

# 2、指定行和列来写
sheet_obj.cell(row=2, column=2).value = "py46"  
sheet_obj.cell(row=3, column=3, value="py46")

# 3、同时写入多个数据
data_list = ['test1', 'test2', 'test3','http://www.baidu.com']
sheet_obj.append(data_list)

data_list = [('test1', 'test2', 'test3'), ('test4', 'test5', 'test6')]
    for val in data_list:
        sheet_obj.append(val)
TODO:追加写入，不会覆盖现有数据
     接收一个可迭代对象（列表、元组、set类型不可以--无序的）
     支持嵌套数据写入，但是嵌套数据必须都是可迭代对象

# 4、----------关于写入表格信息的demo实例----------
from openpyxl import load_workbook
wb_obj = load_workbook("testCase.xlsx")
sheet_obj = wb_obj["login"]
# sheet_obj["A1"] = "test"  # 写单元格
# sheet_obj.cell(row=2, column=2).value = "py46"  # 指定行和列去写值
# sheet_obj.cell(row=3, column=3, value="py46")   # 指定行和列去写值

# data_list = ['test1', 'test2', 'test3','http://www.baidu.com']
data_list = [('test1', 'test2', 'test3'), ('test4', 'test5', 'test6')]
for val in data_list:
    sheet_obj.append(val)

wb_obj.save("testCase.xlsx") # 保存
wb_obj.close()   # 关闭
  

五、excel的数据封装

from openpyxl import load_workbook
from pprint import pprint
# wb_obj = load_workbook("testCase.xlsx")
# sheet_obj = wb_obj["register"]


class HandleExcel:
    def __init__(self, filename, sheet_name):
        self.wb_obj = load_workbook(filename=filename)
        self.sheet_obj = self.wb_obj[sheet_name]
        self.data_list = self.sheet_obj.iter_rows(values_only=True)
        # pprint(list(self.data_list))
        self.wb_obj.close()

    def get_case_data(self):
        test_case_list = []
        data_list = list(self.data_list)    # 有坑：如果不声明一个变量，否则导致读取不到数据，                                                                可能在别处打印的时候已经消费了这个数据
        excel_title = data_list[0]
        excel_data = data_list[1:]
        # print("表头", excel_title)
        # print("*" * 20)
        # print("表体", excel_data)
        for case_data in excel_data:
            case = dict(zip(excel_title, case_data))
            test_case_list.append(case)
        return test_case_list
if __name__ == '__main__':
    cl = HandleExcel(filename="testCase.xlsx", sheet_name="register")
    pprint(cl.get_case_data())

六、关于excel读取数据再写入读取的数据

"""
一、excel中有表头字段，id,method,url,data,result
其中id,method,url,data字段数据自己随便写，在result字段中写入对应行的data数据
"""

from openpyxl import load_workbook
from pprint import pprint


class HandleExcel:
    def __init__(self, filename, sheet_name):
        self.wb_obj = load_workbook(filename=filename)
        self.sheet_obj = self.wb_obj[sheet_name]
        self.data_list = self.sheet_obj.iter_rows(values_only=True)
        # pprint(list(self.data_list))
        self.wb_obj.close()

# 读取excel的数据
    def get_case_data(self):
        test_case_list = []
        data_list = list(self.data_list)
        excel_title = data_list[0]
        excel_data = data_list[1:]
        # print("表头", excel_title)
        # print("*" * 20)
        # print("表体", excel_data)
        for case_data in excel_data:
            case = dict(zip(excel_title, case_data))
            test_case_list.append(case)
        return test_case_list

# 拿到数据后，进行写数据，拿到数据的格式[{},{}]
    def write_case_data(self):
        data_list = self.get_case_data()
        # data_list2 = list(self.data_list)
        for index in range(len(data_list)):
            self.sheet_obj.cell(row=index+2, column=5).value=data_list[index]["data"]
        self.wb_obj.save("py15-homework.xlsx")
        self.wb_obj.close()


if __name__ == '__main__':
    cl = HandleExcel(filename="py15-homework.xlsx", sheet_name="register")
    # pprint(cl.get_case_data())
    cl.write_case_data()