第3.1课、read_yaml、read_excel文件模板

一、read_excel.py

import xlrd
import xlwt

class ExcelRead():

    def __init__(self,excelPath,sheetName="Sheet1"):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_name(sheetName)
        #获取第一行为key值
        self.keys = self.table.row_values(0)
        #获取总行数
        self.rowNum = self.table.nrows
        #获取总列数
        self.colNum = self.table.ncols

    def dict_date(self):
        if self.rowNum <=1:
            print("总行数小于1")
        else:
            r = []
            j = 1
            for i in range(self.rowNum-1):
                s = {}
                #从第二行取对应的values值
                values = self.table.row_values(j)
                for x in range(self.colNum):
                    s[self.keys[x]] = values[x]
                r.append(s)
                j+=1
            return r

    def get_rowinfo(self,row):
        '''获取exl中行信息,row--行数（int）'''
        if row <= 1:
            rowdata = None
        else:
            testdates = self.dict_date()
            rowdata = testdates[row-2]
        return rowdata

    def get_colinfo(self,name):
        '''获取exl中列信息'''
        name_data = []
        testdates = self.dict_date()
        for data in testdates:
            name_data.append(data[name])
        return name_data

    def get_cellinfo(self,row,name):
        '''获取exl中某一单元格信息，row--行数（int）；name--列名(char)'''
        if row <= 1:
            rowdata = None
        else:
            testdates = self.dict_date()
            rowdata = testdates[row-2][name]
        return rowdata


class ExcelWrite():

    def __init__(self,sheetName="Sheet1"):
        self.workbook = xlwt.Workbook(encoding='utf-8')
        # 获取工作表对象Worksheet
        self.worksheet = self.workbook.add_sheet(sheetName)

    def set_header(self,list_data):
        if not isinstance(list_data, list):
            raise TypeError("数据必须是列表")
        keys_data = list(list_data[0].keys())
        num = len(keys_data)
        for c in range(num):
            self.worksheet.write(0, c, label=keys_data[c])

    def excl_write(self,list_data, excel_path):
        '''表格写入全部数据'''
        if not isinstance(list_data, list):
            raise TypeError("数据必须是列表")
        # 获取每行列表
        self.set_header(list_data)
        rows_num = len(list_data)
        for r in range(rows_num):
            values_data = list(list_data[r].values())
            cows_num = len(values_data)
            for c in range(cows_num):
               self.worksheet.write(r + 1, c, values_data[c])
        # 保存数据到硬盘
        self.workbook.save(excel_path)

if __name__ == "__main__":
    # Data = ExcelWrite()
    # filepath = r"E:/TestDemo/excelFile.xls"
    # l = [{'姓名': '张三', '年龄': 18, '职业': '学生'},
    #      {'姓名': '李四', '年龄': 19, '职业': '学生'},
    #      {'姓名': '王五', '年龄': 20, '职业': '学生'}]
    # Data.excl_write(l, filepath)
    path = r'E:/TestDemo/excelFile.xls'
    data = ExcelRead(path,'Sheet1')
    col = data.get_colinfo('姓名')
    print(col)

 

二、read_yaml.py

#!/usr/bin/python
# -*- coding:utf-8 -*-

import yaml
import os


def read_yaml(yamlPath):
    '''读取yaml文件内容
    realPath: 文件的真实绝对路径 '''
    if not os.path.isfile(yamlPath):
        raise FileNotFoundError("文件路径不存在，请检查路径是否正确：%s" % yamlPath)
    # open方法打开直接读出来
    with open(yamlPath, 'r', encoding='utf-8') as f:
        cfg = f.read()
    content = yaml.load(cfg,Loader=yaml.FullLoader)
    # 用load方法转字典
    return content


def write_yaml(yamlPath, data):
    '''写入yaml文件单组数据'''
    with open(yamlPath, 'w', encoding='utf-8') as f:
        yaml.dump(data=data, stream=f, allow_unicode=True)


def read_yaml_group(yamlPath):
    '''读取yaml文件中的多组数据'''
    if not os.path.isfile(yamlPath):
        raise FileNotFoundError("文件路径不存在，请检查路径是否正确：%s" % yamlPath)
    # open方法打开直接读出来
    with open(yamlPath, 'r', encoding='utf-8') as f:
        cfg = f.read()
    content = yaml.load_all(cfg,Loader=yaml.FullLoader)
    # 用load方法转字典
    return content


def write_yaml_group(yamlPath, documents):
    '''写入yaml文件多组数据'''
    if not isinstance(documents,list):
        raise TypeError("数据类型错误，请输入列表数据：%s" % yamlPath)
    with open(yamlPath, 'w', encoding='utf-8') as f:
        yaml.dump(documents=documents, stream=f, allow_unicode=True)


if __name__ == '__main__':
    yamlPath = r'E:\TestDemo\TestFramework_po\Data\Temp\testcases.yaml'
    data = read_yaml(yamlPath)
    print(data)

 

三、以上两个py文件可以放在common中，os文件路径

if __name__ == '__main__':
    # 获取文件的绝对路径
    cur_path = os.path.dirname(os.path.realpath(__file__))
    yml_path = os.path.join(os.path.dirname(cur_path),"当前文件夹", "要读的yaml文件名.yml")
    print(yml_path)  # D:\kecheng202107\userinfo.yml

    r = read_yml_data(yml_path)
    print(r)