写入：

 # -- coding: utf-8 --
# @Time : 2022/6/21 11:01
import openpyxl
class OperationExcel(object):
    """处理Excel数据,并且写⼊
    """
    def __init__(self, sheet):
        """
        根据嵌套字典获取Excel的表头
        :param sheet: 定义⼀个sheet的名称
        """
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.create_sheet(index=0,title=sheet)

    def get_title(self, data):
        """
                :param data: 传⼊json数据的嵌套字典
                :return: 返回所有字典的标题（keys)
                """
        title_list = []
        for dic in data:
            for key in dic:
                if key not in title_list:
                    title_list.append(key)
        return title_list

    def handle_data(self, title_list, data, filename):
        """
        :param title_list:
        :param data: 调⽤get_title返回的标题列表
        :param data: 传⼊json数据的嵌套字典
        :return: 返回所有字典的标题（keys)
        """
        # 1. 把列表的标题插⼊Excel的第⼀⾏
        first_row = 1
        for header in title_list:
            col = title_list.index(header)
            self.ws.cell(first_row, col + 1, header)
        # 2. 把所有的值,根据标题去筛选,插⼊excel
        row = 2
        for player in data:
            for _key, _value in player.items():
                col = title_list.index(_key)
                self.ws.cell(row, col + 1, _value)
            row += 1  # enter the next row
        self.wb.save(filename)
        self.wb.close()

if __name__ == '__main__':
    import os
    excel_path = os.path.join(os.path.abspath(__file__), "../data/zentao_login_cookies.xlsx")
    filename = "zentao_cookies.xlsx"
    sheet = "zentao_cookies"
    data = [{"name": "zentaosid", "value": "l97vmm6ovptiq8c47s6unb2e31", "Domain": "xx.107.178.45", "path": "/"},
                {"name": "theme", "value": "default", "Domain": "xx.107.178.45", "path": "/zentao/www/", },
                {"name": "device", "value": "desktop", "Domain": "xx.107.178.45", "path": "/zentao/www/", },
                {"name": "lang", "value": "zh-cn", "Domain": "xx.107.178.45", "path": "/zentao/www/"}]
    A = OperationExcel(sheet)
    title_list = A.get_title(data)
    # 清洗数据，写⼊Excel
    A.handle_data(title_list=title_list,
                  data=data,
                  filename=excel_path)
  

读取

 # -- coding: utf-8 --
# @Time : 2022/6/21 11:20
# @Author : siyu.yang
from openpyxl import load_workbook

class HandleExcel:
    def __init__(self,file_name,sheet_name):
        self.workbook_object = load_workbook(filename=file_name)
        self.sheet_object = self.workbook_object[sheet_name]

    def get_excel_test_case(self):
        cookie_list= []
        datas = list(self.sheet_object.iter_rows(values_only=True))
        case_title = datas[0]
        case_datas = datas[1:]
        for case in case_datas:
            result = dict(zip(case_title,case))
            cookie_list.append(result)
        self.close_file()
        return cookie_list

    def close_file(self):
        self.workbook_object.close()

if __name__ == '__main__':
    import os
    excel_path = os.path.join(os.path.abspath(__file__), "../data/zentao_login_cookies.xlsx")
    cl = HandleExcel(file_name="C:\\Users\\kcadmin\\Desktop\\ui_base_projec\\homework\\homework_20220619\\data\\zentao_login_cookies.xlsx",
                     sheet_name='zentao_cookies')
    print(cl.get_excel_test_case())  

获取cookie之后每次免登录，主要用于ui自动化中，登录存在验证码，使用cookie绕要验证码

 # -- coding: utf-8 --
# @Time : 2022/6/20 11:35
from selenium import webdriver
import time,os
from homework.handle_excel import HandleExcel

excel_path = os.path.join(os.path.abspath(__file__), "../data/zentao_login_cookies.xlsx")
cl = HandleExcel(file_name=excel_path,sheet_name='zentao_cookies')

driver_path = os.path.join(os.path.abspath(__file__),'../../../driver/chromedriver.exe')
print(driver_path)
driver = webdriver.Chrome(executable_path=driver_path)
driver.get(' ')
for i in cl.get_excel_test_case():
    driver.add_cookie(i)
time.sleep(3)
driver.refresh()