写入:
# -- 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()