接下来都是完整项目源码实现
本篇主要是com部分excel操作和安全解锁的方法
execute_excel.py
from openpyxl import load_workbook
def get_data_from_excel(file, sheet_name=None):
"""
获取excel文件中指定表里的数据
:param file:
:param sheet_name:
:return:
"""
#读取excel文件
wb = load_workbook(file)
#读取对应表
if sheet_name is None:
ws = wb.active
else:
ws = wb[sheet_name]
#创建一个列表容器存放数据
data = []
#获取表头,作为字典的key
row_list = list(ws.rows)
title = [item.value for item in row_list[0]]
#获取其他数据
for row in row_list[1:]:
#获取每一行的数据
temp = [i.value for i in row]
#将表头与这一行数据打包成字典
data.append(dict(zip(title, temp)))
return data
def write_excel(file, sheet_name=None, actual_result=None, status=None, time_str=None, row=None, actual_name=None, status_name=None, date_name=None):
"""
向excel中写入内容
:param file: excel文件名
:param sheet_name: sheet页名称
:param actual_result: 实际结果
:param status: 测试结果
:param row: 行
:param actual_name: 实际结果列名
:param status_name: 判断结果列名
:return:
"""
# 打开excel文件
wb = load_workbook(file)
# 读取对应表
if sheet_name is None:
sheet = wb.active
else:
sheet = wb[sheet_name]
# 查找第一行,如果第一行名称等于传入的实际结果名称记录列号
for cell in sheet[1]:
if cell.value == actual_name:
actual_column = cell.column
break
for cell in sheet[1]:
if cell.value == status_name:
status_column = cell.column
break
for cell in sheet[1]:
if cell.value == date_name:
date_column = cell.column
break
actual_string = '\n'.join(actual_result)
sheet.cell(row=row, column=actual_column).value = actual_string # 对应行列写入值
sheet.cell(row=row, column=status_column).value = status
sheet.cell(row=row, column=date_column).value = time_str
wb.save(file)
if __name__ == '__main__':
res = get_data_from_excel(r"D:\svn\NAVECO\A7_SoftwareTest\B1_B样测试\动态测试\软件合格性测试\Bootloader刷写\BootLoader_TestCase1.xlsx","诊断服务测试")
print(res)
security_key.py
import ctypes
import configparser
from ctypes import *
from comm.canoe_application import *
config = configparser.ConfigParser()
# 读取 properties 文件,并指定编码格式为 UTF-8
config.read("./config.ini", encoding="utf-8")
# 获取安全算法dll路径
security_file = config.get("Security", "security_path")
def security_key(seed_list):
"""
计算出安全密钥
:param seed_list: 种子
:return: 密钥
"""
security_path = security_file
mylib = ctypes.WinDLL(security_path)
# print("key_list========",key_list)
# print(type(key_list))
res = seed_list.rstrip()
ressplit = res.split(" ")
try:
hex_list = [int(i, 16) for i in ressplit]
seed = (ctypes.c_byte * 4)(hex_list[2], hex_list[3], hex_list[4], hex_list[5])
key = (ctypes.c_byte * 4)()
keylength = ctypes.c_int(4)
mylib.GenerateKeyExOpt(
ctypes.pointer(seed),
ctypes.c_int(4),
ctypes.c_int(1),
POINTER(c_char)(),
POINTER(c_char)(),
ctypes.pointer(key),
ctypes.c_int(4),
ctypes.pointer(keylength),
)
reqlist = []
for i in range(len(key)):
reqlist.append((hex(key[i] & 0xFF)).strip("0x"))
for m in range(len(reqlist)):
if len(reqlist[m]) == 1:
reqlist[m] = "0" + reqlist[m]
# print("reqlist:", reqlist)
req = ''.join(list(map(str, reqlist)))
except Exception as e:
e = "Request Error"
req = e
return req
可能会遇到的报错及解决方案
报错:OSError: exception: access violation writing 0x00000007解决方法
GenerateKeyExOpt的参数需要注意,确认好dll中该函数的参数内容是多少,如果确认不了一般可以去掉第5个参数POINTER(c_char)()尝试一下,我写的时候在网上找的方法,不知道GenerateKeyExOpt具体的参数内容就碰到过该错误,排查了好久,最后加了第5个参数POINTER(c_char)()就好了
如果还是不行,可能是方法的名称不对,换一个尝试一下
mylib.ExGenerateKeyEx (
ctypes.pointer(seed),
ctypes.c_int(4),
ctypes.c_int(1),
POINTER(c_char)(),
ctypes.pointer(key),
ctypes.c_int(4),
ctypes.pointer(keylength),
报错:OSError: [WinError 193] %1 不是有效的 Win32 应用程序
就要检查python和dll是不是都是32位或者都是64位的了,一般情况下dll是32位的,把python改成32位即可