from openpyxl import load_workbook
from scripts.handle_config import conf
from scripts.constants import DATAS_FILE_PATH
actual_col = conf.get_int("message", "actual_col")
result_col = conf.get_int("message", "result_col")

class HandleExcel:
'''
定义一个文件处理类
'''
def __init__(self, filename, sheet_name=None):
self.filename = filename # 文件名
self.sheet_name = sheet_name # 表单名

def get\_cases(self):  # 获取所有的测试用例  
    wb = load\_workbook(self.filename)  
    if self.sheet\_name is None:  # 是否指定一个表单  
        ws = wb.active  
    else:  
        ws = wb\[self.sheet\_name\]  
    header\_info = tuple(ws.iter\_rows(max\_row=1, values\_only=True))\[0\]  
    cases\_dict = \[\]  
    for one\_case in tuple(ws.iter\_rows(min\_row=2, values\_only=True)):  
        cases\_dict.append(dict(zip(header\_info, one\_case)))  
    wb.close()  
    return cases\_dict

def get\_one\_case(self, row):  # 获取某一条测试用例  
    return self.get\_cases()\[row-1\]

def write\_case(self, row, write\_actual, write\_result):  # 写入数据到测试用例中  
    wb = load\_workbook(self.filename)  
    if self.sheet\_name is None:  
        ws = wb.active  
    else:  
        ws = wb\[self.sheet\_name\]  
    if row >= 2:  # 这里限制不能修改表头信息  
        ws.cell(row=row, column=actual\_col, value=write\_actual)  
        ws.cell(row=row, column=result\_col, value=write\_result)  
    else:  
        print("输入行号有误！")  
    wb.save(self.filename)  
    wb.close()

case_path = DATAS_FILE_PATH # 获取测试用例文件名

xxx_sheet_name = conf.get_value("file_path", "xxx_sheet_name") # 获取表单名
xxx_handle_case = HandleExcel(case_path, xxx_sheet_name)
xxx_cases = xxx_handle_case.get_cases() # 获取对应表单的所有测试用例