python中將xmind轉成excel

jiyanjiao 發表於 2021-11-02
Python Excel

需求:最近公司專案使用tapd進行管理,現在遇到的一個難題就是,使用固定的模板編寫測試用例,使用excel匯入tapd進行測試用例管理,覺得太過麻煩,本人一直喜歡使用導圖來寫測試用例,故產生了這個工具,將匯入轉成固定格式的excel,然後匯入即可,附上了工具使用說明

xmind格式說明

image

xmind命名規則 xmind命名規則

image

轉成excel的結構

image

轉換工具使用
  • 壓縮包解壓到本地-點選Run_xmindToExcelTool.exe
  • 雙擊exe,彈出檔案選擇框-選擇需要轉換的xmind檔案
  • 轉換完成,給出統計資料,模組數以及匯入的用例數
  • 生成的excel檔案和所選擇的xmin的檔案在一個目錄下
工具如圖

image
image


附上原碼

from libs.openpyxl import Workbook
from libs.xmindparser import xmind_to_dict
from libs import re
from fileutil import FileUtil
import os
import frozen_dir

file = FileUtil()
# project_path = os.path.abspath(os.path.dirname(__file__))
# log_path = project_path + "\\Logs\\log"+'.log'
project_path = frozen_dir.app_path()
if not file.is_exists(project_path + "\Logs"):
    file.mik_dirs(project_path + "\Logs")
log_path = project_path + "\Logs\log"+'.log'


class Excel:
    # 操作excel類

    def __init__(self, excel_name):
        self.excel_name = excel_name
        self.wb = Workbook()
        self.ws = self.wb.active

    def write_excel(self, row, table_tile):
        # 寫入表頭
        for col in range(0, len(table_tile)):
            c = col + 1
            self.ws.cell(row=row, column=c, value=table_tile[col])
        self.wb.save(self.excel_name)

    def addExcel(self, sheet_name, num):
        self.wb.create_sheet(sheet_name, num)
        self.ws = self.wb[sheet_name]


# 寫入excel的行計數器
case_line = 1
mo_num = 0
tole_case_num = 0

# 單個主流程用例
# level_high_case_sign = []
# 所有主流程用例
level_high_case_total = []

# 寫入主流程的行計數器
level_line = 2


class Xmind:
    # 讀取xmind寫入excel

    def __init__(self, xmind_file):
        self.root = xmind_file.split('.xmind')[0]
        self.excel_name = xmind_file.split('.xmind')[0] + '.xlsx'
        self.out = xmind_to_dict(xmind_file)
        self.level_high_case_title = ["用例目錄", "用例名稱", "需求ID", "前置條件", "用例步驟", "預期結果", "用例型別", "用例狀態", "用例等級", "建立人"]

        # 一級xmind標籤名稱
        self.title = ''
        # 二級標籤列表
        self.story = ''
        # 二級標籤數量
        self.story_num = 0
        # 需求id
        self.story_id = ''
        # 前提條件
        self.premise_condition = ''
        # 用例等級
        self.level = ''

        self.excel = Excel(self.excel_name)

    def write_to_sheets(self):
        try:
            for i in range(len(self.out)):
                self.excel.addExcel(self.out[i]['title'], i)
                global case_line
                case_line = 1
                # 寫入excel表頭
                table_tile = ["用例目錄", "用例名稱", "需求ID", "前置條件", "用例步驟", "預期結果", "用例型別", "用例狀態", "用例等級", "建立人"]
                self.excel.write_excel(1, table_tile)
                # 一級xmind標籤名稱
                self.title = self.out[i]['topic']['title']
                # 二級標籤列表
                self.story = self.out[i]['topic']['topics']
                # 二級標籤數量
                self.story_num = len(self.story)
                self.find_xmind(self.title, self.story, self.story_num)
                global mo_num
                mo_num += 1
            # logger.info("轉換完成:總模數【{}】 總用用例數 【{}】".format(mo_num, tole_case_num))
            # logger.info("對應的Exce路徑為{}".format(self.excel_name))

            # 將主流程的測試用例單獨寫入一個sheet頁中
            # print(level_high_case_total)
            if len(level_high_case_total) != 0:
                self.excel.addExcel("主流程測試用例", mo_num)
                self.excel.write_excel(1, self.level_high_case_title)
                for i in range(len(level_high_case_total)):
                    global level_line
                    self.excel.write_excel(level_line, level_high_case_total[i])
                    level_line += 1
            else:
                pass

            file.write_file(log_path, "轉換完成:總模組數【{}】 總用用例數 【{}】\r\n".format(mo_num, tole_case_num))
            file.write_file(log_path, "對應的Exce路徑為{}\r\n".format(self.excel_name))

        except:
            file.write_file(log_path, "請關閉Excel後重新執行\r\n")

    def xmind_num(self, value):
        """獲取xmind標題個數"""
        try:
            return len(value['topics'])
        except KeyError:
            return 0

    def xmind_title(self, value) -> object:
        """獲取xmind標題內容"""
        return value['title']

    def find_xmind(self, title, story, story_num):
        # 將xmind資料寫入excel中
        if '【' in title:
            title = title.split('【')[0]
            regex_str = ".*[\[【](.+?)[\]】].*"
            story_id_reg = re.match(regex_str, self.title)
            if story_id_reg:
                self.story_id = story_id_reg.group(1)

        # 用例數
        for i in range(0, story_num):
            story_name = story[i]['title']
            if '【' in story_name:
                case_name = story_name.split('【')[0]
                regex_str = ".*[\[【](.+?)[\]】].*"
                story_id_reg = re.match(regex_str, story_name)
                if story_id_reg:
                    self.story_id = story_id_reg.group(1)
            else:
                case_name = story_name
            case_sub_list = story[i]['topics']
            sub_num = len(case_sub_list)

            # 子標籤
            for s in range(0, sub_num):
                level_high_case_sign = []
                case_list = []
                result_sum = []
                # 測試用例步驟
                step = case_sub_list[s]['topics'][0]['title']
                # 測試用例標題
                sum_title = case_sub_list[s]['title']

                # 判斷當前用例是否有前提條件
                if 'note' in case_sub_list[s]:
                    self.premise_condition = case_sub_list[s]['note']
                else:
                    self.premise_condition = ''

                result_num = len(case_sub_list[s]['topics'][0]['topics'])
                dirs = self.root.split('\\')
                l = len(dirs)

                # 這個區分總理目錄 拼接目錄 用例名稱以-相連線,拼接到excel的目錄結構
                if '-' in dirs[l-1]:
                    dir_str = []
                    for i in range(0, len(dirs[l-1].split('-'))):
                        if i == len(dirs[l - 1].split('-')) - 2:
                            dir_str.append(dirs[l - 1].split('-')[i])
                            break
                        dir_str.append(dirs[l-1].split('-')[i] + '-')

                    dir = ''.join(dir_str)
                else:
                    dir = dirs[l-1]

                # 新增用例目錄
                case_list.append(dir+'-'+title)
                # 新增用例名稱
                case_list.append(case_name + '-' + sum_title)
                # 新增需求ID
                case_list.append(self.story_id)
                # 新增前置條件
                case_list.append(self.premise_condition)
                # 新增用例步驟
                case_list.append(step)

                global case_line
                case_line += 1

                # 結果樹
                for r in range(0, result_num):
                    result = case_sub_list[s]['topics'][0]['topics'][r]['title']
                    result_sum.append(result+'\t\n')
                    r += 1

                # 新增預期結果
                case_list.append(''.join(result_sum))
                # 新增用例型別
                case_list.append("功能測試")
                # 用例狀態
                case_list.append("正常")

                # 讀取測試用例等級
                if 'makers' in case_sub_list[s] and 'flag-red' in case_sub_list[s]['makers'][0]:
                    self.level = "高"

                    # 測試用例等級高階別的,會重新寫入另一個sheet頁,為了方便excel匯入tapd主流程部分用例
                    level_high_case_sign.append(dir+'主流程'+'-'+title)
                    level_high_case_sign.append(case_name + '-' + sum_title)
                    level_high_case_sign.append(self.story_id)
                    level_high_case_sign.append(self.premise_condition)
                    level_high_case_sign.append(step)
                    level_high_case_sign.append(''.join(result_sum))
                    level_high_case_sign.append('功能測試')
                    level_high_case_sign.append('正常')
                    level_high_case_sign.append(self.level)
                    # 新增建立人
                    if '-' in self.root:
                        creater = self.root.split('-')[len(self.root.split('-'))-1]
                    else:
                        creater = "測試人員"
                    level_high_case_sign.append(creater)
                else:
                    self.level = '中'
                # print(level_high_case_sign)

                # 新增用例等級
                case_list.append(self.level)

                # 新增建立人
                if '-' in self.root:
                    creater = self.root.split('-')[len(self.root.split('-'))-1]
                else:
                    creater = "測試人員"
                case_list.append(creater)

                if len(level_high_case_sign) != 0:
                    global level_high_case_total
                    level_high_case_total.append(level_high_case_sign)
                else:
                    pass

                # logger.info("【{}】模組 第{}行 {} 寫入完成".format(title, case_line-1, case_name + '-' + sum_title))
                file.write_file(log_path, "【{}】模組 第{}行 {} 寫入完成\r\n".format(title, case_line-1, case_name + '-' + sum_title))
                self.excel.write_excel(case_line, case_list)

                s += 1
                global tole_case_num
                tole_case_num += 1
            i += 1


## 原創文章轉載請註明出處謝謝https://www.cnblogs.com/jiyanjiao-702521/p/15500044.html