python">import json
import re
import xlrd as xr
import yaml  # pip install ruamel.yaml
import os
from ruamel import yaml
from collections import OrderedDict

from ruamel.yaml.compat import ordereddict
import ruamel.yaml.comments


class Excel2Yapi:
    # 获取excel内容并转为json，再换成yaml
    def excel2yapi(self, file):
        wb = xr.open_workbook(file)
        sheetList = wb.sheet_names()
        sh = wb.sheet_by_name(sheetList[0])
        for sheet_name in sheetList:
            # 初始化一个yaml
            file = sheet_name+'.yaml'
            self.initYaml(file, title=sheet_name)
            sh = wb.sheet_by_name(sheet_name)
            # 遍历excel，让每一行数据写入到yaml的test_step
            if sh.nrows < 2:
                continue
            else:
                for row in range(1, sh.nrows):
                    # 首行列名与某一行的值映射成字典
                    row_value = dict(zip(sh.row_values(0), sh.row_values(row)))
                    js = {}
                    js['api_service'] = row_value['application']
                    rootdir = 'D:\\02-软件\\pythonProject\\w_Test\\Yaml'
                    self._mapApiName(rootdir, row_value, js)
                    # 需要加个匹配api_name的方法，通过url去匹配
                    for key in row_value.keys():
                        self._mapping(key, row_value, js)
                    self.addServiceStep(file, js)
                    print(row_value)

    # 通过url匹配接口模板
    def _mapApiName(self,rootdir, row_values, js):
        file_list = self.get_allFile(rootdir)
        for file in file_list:
            with open(file, 'r', encoding='utf-8') as f:
                doc = yaml.round_trip_load(f)
                if ('api_info' in doc.keys()) and ('address' in doc['api_info'].keys()):
                    if self.replaceVar(doc['api_info']['address']) == self.replaceVar(row_values['url']):
                        names = file.split('\\')
                        js['api_name'] = names[-1].split('.')[0]
                        break

    def replaceVar(self, s):
        rule = "\$\{.*?\}"  # 变量匹配规则，目前为${xxxxx}
        rule2 = "\{.*?\}"   # 变量匹配规则，目前为{xxxxx}
        rule3 = '/'         # 变量匹配规则，目前为/
        s = re.sub(rule, '', s)
        s = re.sub(rule2, '', s)
        s = re.sub(rule3, '', s)
        return s

    # 映射excel的每个单元格到yaml的字段
    def _mapping(self, key, row_value, js):
        if row_value[key] == '':
            return
        elif key =='application' or key == 'url' or key == 'method' or key == 'priority' or key == 'scenario' or key == 'auth':
            return
        elif key == 'set_up':
            self._toTestCase(row_value, js, key, key)
            self._toTestCase(row_value, js, key, 'tear_down')
        elif key == 'filename':
            self._toTestCase(row_value, js, key, 'file')
        elif key == 'expectedRes':
            #s = json.loads(row_value['expectedRes'].replace('\n', ''))
            self._toCheck(row_value, js)
        elif key == 'var_from_body':
            self._toRelevance(row_value, js, key, 'body.')
        elif key == 'var_from_db':
            self._toRelevance(row_value, js, key, 'db.')
        elif key == 'var_from_res':
            self._toRelevance(row_value, js, key, 'res.')
        else:
            self._toTestCase(row_value, js, key, key)

    # excel用例各参数写入yaml的check
    def _toCheck(self, row_value, js):
        s = json.loads(row_value['expectedRes'])
        for s_key in s.keys():
            if 'check' in js['test_case']:
                if s_key == "code" or s_key == "status":
                    js['test_case']['check']['status'] = s[s_key]
                if s_key == "sql":
                    js['test_case']['check']['db'] = s[s_key]
                else:
                    js['test_case']['check'][s_key] = str(s[s_key])
            else:
                js['test_case']['check'] = {}
                if s_key == "code" or s_key == "status":
                    js['test_case']['check']['status'] = s[s_key]
                if s_key == "sql":
                    js['test_case']['check']['db'] = s[s_key]
                else:
                    js['test_case']['check'][s_key] = str(s[s_key])

    # excel用例各参数写入yaml的Relevance
    def _toRelevance(self, row_value, js, old_key, prefix):
        s = json.loads(row_value[old_key])
        if 'relevance' in js['test_case']:
            for s_key in s.keys():
                js['test_case']['relevance'][s_key] = prefix + row_value[old_key]
        else:
            js['test_case']['relevance'] = {}
            for s_key in s.keys():
                js['test_case']['relevance'][s_key] = prefix + row_value[old_key]

    # excel用例各参数写入yaml的test_case
    def _toTestCase(self, row_value, js, old_key, new_key):
        if 'test_case' in js:
            #row_value[old_key] = json.loads(row_value[old_key].replace('\n', ''))
            if old_key == 'header' or old_key == 'body':
                row_value[old_key] = re.sub(r'\[', '\"[', row_value[old_key])
                row_value[old_key] = re.sub(r'\]', ']\"', row_value[old_key])
                row_value[old_key] = json.loads(row_value[old_key])
                row_value[old_key] = str(row_value[old_key])
                row_value[old_key] = re.sub(r'\'\[', '[', row_value[old_key])
                row_value[old_key] = re.sub(r']\'', ']', row_value[old_key])
            js['test_case'][new_key] = row_value[old_key]
        else:
            js['test_case'] = {}
            js['test_case'][new_key] = row_value[old_key]

    # 生成yaml文件，暂无引用
    @staticmethod
    def writeYaml(file, data):
        # 数据写入到yaml文件中，使用dump函数，将python数据转换为yaml数据，并保存在文件中
        with open(file, 'w', encoding='utf-8') as f:
            yaml.round_trip_dump(data, f, default_flow_style=False, allow_unicode=True)

    # 获取excel表格对应列,暂无引用
    @staticmethod
    def getColumn():
        column = {"yes or no": 0, "ID": 1, "name": 2, "priority": 3, "application": 4, "url": 5, "method": 6,
                  "set_up": 7, "header": 8, "params": 9, "body": 10, "filename": 11, "expectedRes": 12,
                  "var_from_body": 13, "var_from_db": 14, "var_from_res": 15, "wait_untile": 16, "scenario": 17,
                  "auth": 18}
        return column

    def getDictValue(self, dic, c):
        pass

    # 更新yaml字段内容,file文件，keyPath文件中需要修改的参数（用.隔开），value新的值，可能不适用于列表，暂无引用
    def updateYaml(self, file, keyPath, value):
        with open(file, 'r', encoding='utf-8') as f:
            current_yaml = yaml.round_trip_load(f)
            print(current_yaml)
            keys = keyPath.split('.')
            keys_num = len(keys)
            if keys_num == 1:
                current_yaml[keys[0]] = value
            elif keys_num == 2:
                current_yaml[keys[0]][keys[1]] = value
            elif keys_num == 3:
                current_yaml[keys[0]][keys[1]][keys[2]] = value
            elif keys_num == 4:
                current_yaml[keys[0]][keys[1]][keys[2]][keys[3]] = value
            else:
                print('字典路径超出范围，不与修改')
        if current_yaml:
            with open(file, 'w', encoding='utf-8') as f_new:
                yaml.round_trip_dump(current_yaml, f_new, default_flow_style=False)

    # file需要使用固定的模板，
    def addServiceStep(self, file, data):
        with open(file, 'r', encoding='utf-8') as f:
            doc = yaml.round_trip_load(f)
            # 传入的data如下，是一个step
            # data = {'api_service': 'dome3', 'api_name': 'demo_api10(download)','test_case': {'check': {}, 'relevance': {}}}
            doc['test_step'].append(data)
            if doc['test_step'][0] is None:
                del doc['test_step'][0]
        with open(file, 'w', encoding='utf-8') as f:
            yaml.round_trip_dump(doc, f, default_flow_style=False)

    # 初始化一个yaml模板，用于后续写入用例
    def initYaml(self, file, testcase_id=None, title=None, priority=1, version=None):
        data = {'testcase_id': testcase_id, 'title': title, 'priority': priority, 'version': version, 'test_step': [None]}
        with open(file, 'w', encoding='utf-8') as f:
            yaml.round_trip_dump(data, f, default_flow_style=False)

    def get_allFile(self, rootdir):
        file_list = []
        for root, dirs, files in os.walk(rootdir):
            for file in files:  # 遍历目录里的所有文件
                print(os.path.join(root, file), " --- file")
                file_list.append(os.path.join(root, file))
        return file_list



if __name__ == "__main__":
    file = 'E:\\API_test\\openAPITest\\QA\\QA\\szapitest\\Graphx\\testdata\\release-test\\TC000001-后台中心-租户用户角色操作校验.xlsx'
    ey = Excel2Yapi()
    ey.excel2yapi(file)
    path = 'E:\\API_test\\openAPITest\\QA\\QA\\szapitest\\Graphx\\testdata'
    print(ey.get_allFile(path))

# ey.updateYaml('demo_api1.yaml', 'testcase.test.abc', 'yes')
# ey.addYaml('demo_test.yaml')


# doc = {'testcase_id': None, 'title': None, 'priority': None, 'version': None, 'test_step': [None]}
# with open('demo_test.yaml', 'w', encoding='utf-8') as f:
#     yaml.round_trip_dump(doc, f, default_flow_style=False)
# with open('demo_test.yaml', 'r', encoding='utf-8') as f:
#     doc = yaml.round_trip_load(f)
#     print(doc)
#     dic = {'api_service': 'dome3', 'api_name': 'demo_api10(download)', 'test_case': {'check': {}, 'relevance': {}}}
#     doc['test_step'].append(dic)
#     if doc['test_step'][0] == None:
#         del doc['test_step'][0]
# with open('demo_test.yaml', 'w', encoding='utf-8') as f:
#     yaml.round_trip_dump(doc, f, default_flow_style=False)