excel2feature/bin/e2f.py

# -*- coding:utf-8 -*-
from __future__ import unicode_literals
import os
import io
import _load
import shutil
import time
import xlrd

from fnmatch import fnmatch
from jinja2 import Environment, PackageLoader
#处理py2的编码和字典顺序的问题
#from __future__ import unicode_literals --必须放在第一个
from collections import OrderedDict


class xl2fe:

    def __init__(self):
        HERE = os.path.dirname(__file__)
        TOP = os.path.join(HERE, "..")
        self.templates_dir = os.path.join(TOP, "templates")
        self.output_dir = os.path.join(TOP, "output")
        if os.path.exists(self.output_dir):
            shutil.rmtree(self.output_dir)
        time.sleep(2)
        os.mkdir(self.output_dir)
        self.excel_files_dir = os.path.join(TOP, "excel_files")
        self.xlsx_sheets = {"封面": "00_home_index.feature",
                            "统计表": "01_statistics.feature",
                            "基础功能测试用例": "02_function.feature",
                            "异常测试": "03_abnormality_test.feature",
                            "性能测试": "04_performance.feature"
                            }

    def read_xlsx(self):
        """
        指定的sheet页，对内容进行处理
        """
        #获取所有的数据
        work_book = xlrd.open_workbook(self.xlsx_file_path)
        #为了让py2字典的顺序与py3一致
        all_data = OrderedDict()
        for i, sheet_obj in enumerate(work_book.sheets()):
            all_data[sheet_obj.name] = [sheet_obj.row_values(row)
                                        for row in range(sheet_obj.nrows)]
        #按sheet页处理
        for sheet_name, self.feature_name in self.xlsx_sheets.items():
            datas = all_data[sheet_name]
            # 处理数据
            self.context = OrderedDict()
            self.context = xl2fe.get_feature_data(self, datas, sheet_name)
            # 渲染模板
            xl2fe.feature_tpl(self)

    def get_feature_data(self, datas, sheet_name):
        """
        根据sheet_name 处理数据
        """
        context_temp = OrderedDict()

        if sheet_name == "封面":
            context_temp['project'] = self.project
            context_temp['sheet_name'] = sheet_name
            # 处理更新记录
            Scenario_table = []
            lines = datas[4:]
            for line in lines:
                cells = line
                # 处理换行
                for index, cell in enumerate(cells):
                    #因为py2的编码问题，不能判断str
                    #isinstance(cell,str)
                    if not isinstance(cell,(int,float)):
                        cells[index] = cell.replace('\n', '\\n')
                Scenario_table.append(cells)
            context_temp['Scenario_table'] = Scenario_table

        elif sheet_name == "统计表":
            context_temp['project'] = self.project
            context_temp['sheet_name'] = sheet_name

        elif sheet_name == "基础功能测试用例":
            context_temp['project'] = self.project
            context_temp['sheet_name'] = sheet_name
            # 处理基础测试用例中的数据
            Scenario_table = OrderedDict()
            lines = datas[2:]
            for line in lines:
                cells = line[0:9]
                #补全合并单元格的信息
                #模块
                if cells[0]:
                    model = cells[0]
                else:
                    cells[0] = model
                #子模块
                if cells[1]:
                    sub_model = cells[1]
                else:
                    cells[1] = sub_model
                #处理编号
                if '-ST-' in cells[2]:
                    cells[2] = 'NUM'
                # 处理换行
                for index, cell in enumerate(cells):
                    if not isinstance(cell,(int,float)):
                        cells[index] = cell.replace('\n', '\\n')
                #以模块为单位存储
                if model not in list(Scenario_table.keys()):
                    Scenario_table[model] = []
                Scenario_table[model].append(cells)
            context_temp['Scenario_table'] = Scenario_table

        elif sheet_name == "异常测试":
            context_temp['project'] = self.project
            context_temp['sheet_name'] = sheet_name
            # 处理更新记录
            Scenario_table = []
            lines = datas[4:]
            for line in lines:
                cells = line[0:8]
                cells[0] = 'NUM'
                # 处理换行
                for index, cell in enumerate(cells):
                    if not isinstance(cell,(int,float)):
                        cells[index] = cell.replace('\n', '\\n')
                Scenario_table.append(cells)
            context_temp['Scenario_table'] = Scenario_table

        elif sheet_name == "性能测试":
            context_temp['project'] = self.project
            context_temp['sheet_name'] = sheet_name

        return context_temp

    def feature_tpl(self):
        """
        拿处理后的数据来渲染指定的模板
        """
        # 读取模板
        tpl = os.path.join(self.templates_dir, self.feature_name + ".j2")
        tpl_data = io.open(tpl, encoding="utf-8").read()
        # 渲染模板
        env = Environment()
        text = env.from_string(tpl_data).render(self.context)
        # 保存文件
        xl2fe.save_feature(self, text)

    def save_feature(self, text):
        """
        保存渲染好的模板为feature文件
        """
        #为了解决windows换行符的问题转为二进制，主要是由于py2中open不支持newline参数
        #py2没有bytes()函数
        #text_bytes = bytes(text,'utf-8')
        text_bytes = text.encode('utf-8')
        feature_path = os.path.join(self.project_dir, self.feature_name)
        # 写入文件
        with open(feature_path, 'wb+') as fp:
            fp.write(text_bytes)

    def main(self):
        xlsx_files = os.listdir(self.excel_files_dir)
        for xlsx_file in xlsx_files:
            # 排除掉非xlsx结尾的文件
            if not fnmatch(xlsx_file, "*.xlsx"):
                continue
            self.project = xlsx_file.split('_')[0]
            self.xlsx_file_path = os.path.join(self.excel_files_dir, xlsx_file)
            # 按项目存放
            self.project_dir = os.path.join(self.output_dir, self.project)
            os.mkdir(self.project_dir)

            xl2fe.read_xlsx(self)


if __name__ == '__main__':
    test_env = xl2fe()
    test_env.main()