'''
Author: seven 865762826@qq.com
Date: 2023-09-30 13:21:25
LastEditors: seven 865762826@qq.com
LastEditTime: 2023-11-22 09:51:02
FilePath: \python_excelTest\TSExcelConver.py
Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
'''
import json
import time
import openpyxl
from openpyxl import load_workbook
from .ConstValuesMod1 import *
import os

_curr_path = os.path.dirname(__file__)

ConstMain = 1
ConstAction = 2


class ExcelConverMod1:
    def __init__(self, inputFileName,outputFileName):
        self.TestCaseinfo = {}
        self.inputFileName = inputFileName
        self.outputFileName = outputFileName
        self.SetTable = {}
        self.GetTable = {}
        self.APITable = {}
        self.EXPTable = {}
        self.NOPTable = {}
        self.JMPTable = {}
        self.FROMTable = {}
        self.LocalVarTable = {}
        self.GroupTable = {}
        self.ProgramTable = {}
        self.EPMaxCol = 0
        self.GroupMaxCol = 0
        self.EPTABLE = {
        }
        self.GroupTestList = {}
        self.Ret = True
        if os.path.exists(os.path.join(_curr_path, SaveJsonName)):
            self.read_testcase_info()
        parseret = self.parse_excel()
        if IS_Save_TestCaseInfo:
            self.save_testcase_info()
        if parseret & ConstAction == ConstAction:
            self.Ret = self.creat_excel()

    def parse_excel(self):
        wb = load_workbook(self.inputFileName)
        sheets = wb.worksheets
        ret = 0
        for sheet in sheets:
            if sheet.title.lower()== 'action':
                for idx in range(STEPNUMBER,sheet.max_row+1):
                    actionName = sheet.cell(idx,STEPNUMBER).value
                    condtion = sheet.cell(idx,STEPNAME).value
                    if actionName!= None:
                        if actionName[CHECKIDX].upper() == SET and condtion != None:
                            self.SetTable[actionName] = {}
                            self.SetTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.SetTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            SiganlCount = int(sheet.cell(idx,SIGNALCOUNT).value)
                            self.SetTable[actionName][TSConfiguration] = ''
                            for i in range(SiganlCount):
                                self.SetTable[actionName][TSConfiguration] += f'[{VarTable[sheet.cell(idx + i,SIGNALTYPE).value.upper()]}]' + sheet.cell(idx + i,SIGNALNAME).value +','+ str(sheet.cell(idx + i,SIGNALMINVALUE).value)
                                if i<SiganlCount - 1:
                                    self.SetTable[actionName][TSConfiguration] += ';'
                        elif actionName[CHECKIDX].upper() == GET and condtion != None:
                            self.GetTable[actionName] = {}
                            self.GetTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.GetTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            SiganlCount = int(sheet.cell(idx,SIGNALCOUNT).value)
                            self.GetTable[actionName][TSConfiguration] = ''
                            GetTimeOut = sheet.cell(idx,TIMEOUT).value
                            if GetTimeOut != None:
                                self.GetTable[actionName][TSConfiguration] += str(GetTimeOut) + ";"
                            else:
                                self.GetTable[actionName][TSConfiguration] += ";"
                            for i in range(SiganlCount):
                                self.GetTable[actionName][TSConfiguration] += f'[{VarTable[sheet.cell(idx + i,SIGNALTYPE).value.upper()]}]' + sheet.cell(idx + i,SIGNALNAME).value +','+ str(sheet.cell(idx + i,SIGNALMINVALUE).value)+','+ str(sheet.cell(idx + i,SIGNALMAXVALUE).value)+','+ sheet.cell(idx + i,CONDITION).value 
                                if i < SiganlCount - 1:
                                    self.GetTable[actionName][TSConfiguration] += ";"
                        elif actionName[CHECKIDX].upper() == API and condtion != None:
                            self.APITable[actionName] = {}
                            self.APITable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.APITable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            funcType = sheet.cell(idx,FUNCTIONTYPE).value
                            if funcType not in FunctionType:
                                del self.APITable[actionName]
                                continue
                            self.APITable[actionName][TSConfiguration] = sheet.cell(idx,FUNCTIONNAME).value + ";" + FunctionType[funcType] + ";0;"
                            returnvarType = sheet.cell(idx,RETURNTYPE).value.upper()
                            if returnvarType not in VarTable:
                                self.APITable[actionName][TSConfiguration] += ";"
                            else:
                                returnType = VarTable[returnvarType]
                                if returnType == '':
                                    self.APITable[actionName][TSConfiguration] += ";"
                                else:
                                    returnName = sheet.cell(idx,RETURNNAME).value
                                    self.APITable[actionName][TSConfiguration] += f"[{returnType}]{returnName};"
                            ArgCount = int(sheet.cell(idx,ARGUMENTCOUNT).value)
                            Argcow = ARGUMENT1VALUE
                            for i in range(ArgCount):    
                                if i < ArgCount-1:
                                    self.APITable[actionName][TSConfiguration] += f"Arg{i+1}," +str(sheet.cell(idx,Argcow).value) + ';'
                                else:
                                    self.APITable[actionName][TSConfiguration] += f"Arg{i+1}," +str(sheet.cell(idx,Argcow).value)
                                Argcow += ADDNUMBER
                        elif actionName[CHECKIDX].upper() == EXP and condtion != None:
                            self.EXPTable[actionName] = {}
                            self.EXPTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.EXPTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            ExpVarCount = int(sheet.cell(idx,EXPRESSIONVARCOUNT).value)
                            self.EXPTable[actionName][TSConfiguration] = f'{ExpVarCount};' + sheet.cell(idx,EXPRESSIONFUNC).value +";"
                            varType = EXPRESSIONVARTYPE
                            for i in range(ExpVarCount + 1):
                                yType = sheet.cell(idx,varType).value.upper()
                                if (yType in VarTable) and VarTable[yType]!='' :
                                    self.EXPTable[actionName][TSConfiguration]+= f'[{VarTable[yType]}]'+ str(sheet.cell(idx,varType+TYPEADDVAR).value) 
                                else:
                                    self.EXPTable[actionName][TSConfiguration]+= str(sheet.cell(idx,varType+TYPEADDVAR).value) 
                                if i < ExpVarCount:
                                    self.EXPTable[actionName][TSConfiguration]+=";"
                                varType += ADDNUMBER
                        elif actionName[CHECKIDX].upper() == NOP and condtion != None:
                            self.NOPTable[actionName] = {}
                            self.NOPTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.NOPTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            self.NOPTable[actionName][TSConfiguration] = ''
                            NextAction = sheet.cell(idx,NEXTACTION).value.upper()
                            if NextAction in NOPAction:
                                self.NOPTable[actionName][TSConfiguration] += NOPAction[NextAction]+";"
                            else:
                                self.NOPTable[actionName][TSConfiguration] += "0;"
                            results = sheet.cell(idx,NOWRESULT).value.upper()
                            if results in NOPRESULT:
                                self.NOPTable[actionName][TSConfiguration] += NOPRESULT[results]+";0"
                            else:
                                self.NOPTable[actionName][TSConfiguration] += "1;0"
                        elif actionName[CHECKIDX].upper() == JMP and condtion != None:
                            self.JMPTable[actionName] = {}
                            self.JMPTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.JMPTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            self.JMPTable[actionName][TSConfiguration] = sheet.cell(idx,JMPLBL).value
                        elif actionName[CHECKIDX].upper() == FROM and condtion != None:
                            self.FROMTable[actionName] = {}
                            self.FROMTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.FROMTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            self.FROMTable[actionName][TSConfiguration] = sheet.cell(idx,FROMLBL).value
                        elif actionName[CHECKIDX].upper() == VAR and condtion != None:
                            LVarName = sheet.cell(idx,LOCALVARNAME).value
                            if LVarName == None:
                                continue
                            self.LocalVarTable[actionName] = {}
                            self.LocalVarTable[actionName][TSstepname] = LVarName
                            self.LocalVarTable[actionName][TScomment] = sheet.cell(idx,LOCALVARCOMMENT).value
                            self.LocalVarTable[actionName][TSConfiguration] = ''
                            CreateVarType = sheet.cell(idx,LOCALVARTYPE).value.upper()
                            if CreateVarType in VarTypeDict:
                                self.LocalVarTable[actionName][TSConfiguration] += VarTypeDict[CreateVarType]+';'
                            else:
                                self.LocalVarTable[actionName][TSConfiguration] +='0;'
                            CreateVarValue = str(sheet.cell(idx,LOCALVARVALUE).value)
                            if CreateVarValue != None:
                                self.LocalVarTable[actionName][TSConfiguration] += CreateVarValue+';'
                            else:
                                self.LocalVarTable[actionName][TSConfiguration] += '0;'
                        elif actionName[CHECKIDX].upper() == GROUP and condtion != None:
                            self.GroupTable[actionName] = {}
                            self.GroupTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.GroupTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            self.GroupTable[actionName][TSConfiguration] = ''
                            GType = sheet.cell(idx,GRREPEATTYPE).value.upper()
                            if GType in VarTable and VarTable[GType]!='':
                                self.GroupTable[actionName][TSConfiguration] += F"[{VarTable[GType]}]"+str(sheet.cell(idx,GRREPEATNAME).value)
                            else:
                                self.GroupTable[actionName][TSConfiguration] += sheet.cell(idx,GRREPEATNAME).value.upper()
                        elif actionName.upper() == PROGRAM and condtion != None:
                            self.ProgramTable[actionName] = {}
                            self.ProgramTable[actionName][TSstepname] = sheet.cell(idx,STEPNAME).value
                            self.ProgramTable[actionName][TScomment] = sheet.cell(idx,COMMENT).value
                            self.ProgramTable[actionName][TSConfiguration] = ''
                            ProCount = sheet.cell(idx,PROREPEATCOUNT).value
                            if ProCount != None:
                                self.ProgramTable[actionName][TSConfiguration] += str(ProCount)+';'
                            else:
                                self.ProgramTable[actionName][TSConfiguration] += '1;'
                            ProSelect = sheet.cell(idx,PROSELECTED).value
                            if ProSelect in SelectList:
                                self.ProgramTable[actionName][TSConfiguration] += SelectList[ProSelect]+';'
                            else:
                                self.ProgramTable[actionName][TSConfiguration] += '1;'
                            self.ProgramTable[actionName][TSConfiguration] += self.ProgramTable[actionName][TSstepname] + ";"+self.ProgramTable[actionName][TScomment]
                ret |= ConstMain
            elif sheet.title.lower()== 'main1':
                self.EPMaxCol = 0
                self.GroupMaxCol = 0
                for col in range(1, sheet.max_column+1):
                    EP_Value = sheet.cell(ENTRYROW,col).value
                    if EP_Value != None:
                        if EP_Value.upper() == EP:
                            EPRow = ENTRYROW + EPADD
                            EPcol = col
                            EPStep = 0
                            for now_row in range(ENTRYROW + EPADD,sheet.max_row+1):
                                TableValue = sheet.cell(now_row,col).value
                                if TableValue == None:
                                    continue
                                EPStep += 1
                                if (EPStep-1) in self.EPTABLE and len(self.EPTABLE[EPStep-1])>self.EPMaxCol:
                                    self.EPMaxCol = len(self.EPTABLE[EPStep-1])
                                self.EPTABLE[EPStep] = []
                                self.EPTABLE[EPStep].append(TableValue)
                                for CheckCol in range(col + EPADD,sheet.max_column+1):
                                    RTableValue = sheet.cell(now_row,CheckCol).value
                                    if RTableValue == '-':
                                        continue
                                    elif RTableValue != None:
                                        self.EPTABLE[EPStep].append(RTableValue)
                                    else:
                                        break
                        elif EP_Value[CHECKIDX].upper() == GROUP:
                            GroupTest = {}
                            EPRow = ENTRYROW + EPADD
                            EPcol = col
                            GROUPStep = 0
                            for now_row in range(ENTRYROW + EPADD,sheet.max_row+1):
                                TableValue = sheet.cell(now_row,col).value
                                if TableValue == None:
                                    continue
                                GROUPStep += 1
                                if (GROUPStep-1) in GroupTest and len(GroupTest[GROUPStep-1])>self.GroupMaxCol:
                                    self.GroupMaxCol = len(GroupTest[GROUPStep-1])
                                GroupTest[GROUPStep] = []
                                GroupTest[GROUPStep].append(TableValue)
                                #防止界限 无法使得 GROUPStep自增
                                for CheckCol in range(col + EPADD,sheet.max_column + 1): 
                                    RTableValue = sheet.cell(now_row,CheckCol).value
                                    if RTableValue == '-':
                                        continue
                                    elif RTableValue != None:
                                        GroupTest[GROUPStep].append(RTableValue)
                                    else:
                                        break
                            self.GroupTestList[EP_Value] = GroupTest
                ret |= ConstAction
        self.EPMaxCol = TestFactor * self.EPMaxCol + TestOffset
        self.GroupMaxCol = TestFactor * self.GroupMaxCol + TestOffset
        return ret

    def creat_excel(self):
        ret = False
        workbook = openpyxl.Workbook()
        if len(self.GroupTestList)!=0:
            for GKey in self.GroupTestList:
                startCol = 1
                startRow = 2
                main_sheet = workbook.create_sheet(GKey+GROUP_Sheet)
                main_sheet["A1"].value = "EP"
                main_sheet.cell(startCol,self.GroupMaxCol).value = "Def"
                main_sheet.cell(startCol,self.GroupMaxCol+EP2Name).value = "Name"
                main_sheet.cell(startCol,self.GroupMaxCol+EP2Comment).value = "Comment"
                main_sheet.cell(startCol,self.GroupMaxCol+EP2Conf).value = "Configuration"
                DefStartRow = 2
                DefStartCol = self.GroupMaxCol
                GroupTestMap = {}
                for key in self.GroupTestList[GKey]:
                    keyRowValue =  self.GroupTestList[GKey][key]
                    for RowValueidx in range(len(keyRowValue)):
                        main_sheet.cell(startRow,startCol + TestFactor * RowValueidx).value = ACTIONLIST[keyRowValue[RowValueidx][0].upper()]
                        GroupTestMap[main_sheet.cell(startRow,startCol + TestFactor * RowValueidx).coordinate] = keyRowValue[RowValueidx]
                        if RowValueidx < len(keyRowValue) - 1:
                            main_sheet.cell(startRow,startCol + TestFactor * RowValueidx + TestOffset).value = NextConnect
                    startRow += EPADD
                for key in GroupTestMap:
                    if GroupTestMap[key][CHECKIDX].upper() == SET:
                        if GroupTestMap[key] in self.SetTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.SetTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.SetTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.SetTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == GET:
                        if GroupTestMap[key] in self.GetTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.GetTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.GetTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.GetTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == API:
                        if GroupTestMap[key] in self.APITable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.APITable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.APITable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.APITable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == EXP:
                        if GroupTestMap[key] in self.EXPTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.EXPTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.EXPTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.EXPTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == NOP:
                        if GroupTestMap[key] in self.NOPTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.NOPTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.NOPTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.NOPTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == JMP:
                        if GroupTestMap[key] in self.JMPTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.JMPTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.JMPTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.JMPTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == FROM:
                        if GroupTestMap[key] in self.FROMTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.FROMTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.FROMTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.FROMTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
                    elif GroupTestMap[key][CHECKIDX].upper() == GROUP:
                        if GroupTestMap[key] in self.GroupTable:
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.GroupTable[GroupTestMap[key]][TSstepname]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.GroupTable[GroupTestMap[key]][TScomment]
                            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.GroupTable[GroupTestMap[key]][TSConfiguration]
                            DefStartRow += 1
        if len(self.EPTABLE) != 0:
            startCol = 1
            startRow = 2
            main_sheet = workbook.active
            main_sheet.title = "Main"
            main_sheet["A1"].value = "EP"
            main_sheet.cell(startCol,self.EPMaxCol).value = "Def"
            main_sheet.cell(startCol,self.EPMaxCol+EP2Name).value = "Name"
            main_sheet.cell(startCol,self.EPMaxCol+EP2Comment).value = "Comment"
            main_sheet.cell(startCol,self.EPMaxCol+EP2Conf).value = "Configuration"
            DefStartRow = 2
            DefStartCol = self.EPMaxCol
            EPTestMap = {}
            for key in self.EPTABLE:
                keyRowValue =  self.EPTABLE[key]
                for RowValueidx in range(len(keyRowValue)):
                    main_sheet.cell(startRow,startCol + TestFactor * RowValueidx).value = ACTIONLIST[keyRowValue[RowValueidx][0].upper()]
                    EPTestMap[main_sheet.cell(startRow,startCol + TestFactor * RowValueidx).coordinate] = keyRowValue[RowValueidx]
                    if RowValueidx < len(keyRowValue) - 1:
                        main_sheet.cell(startRow,startCol + TestFactor * RowValueidx + TestOffset).value = NextConnect
                startRow += EPADD
            for key in EPTestMap:
                if EPTestMap[key][CHECKIDX].upper() == SET:
                    if EPTestMap[key] in self.SetTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.SetTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.SetTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.SetTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == GET:
                    if EPTestMap[key] in self.GetTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.GetTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.GetTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.GetTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == API:
                    if EPTestMap[key] in self.APITable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.APITable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.APITable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.APITable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == EXP:
                    if EPTestMap[key] in self.EXPTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.EXPTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.EXPTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.EXPTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == NOP:
                    if EPTestMap[key] in self.NOPTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.NOPTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.NOPTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.NOPTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == JMP:
                    if EPTestMap[key] in self.JMPTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value =self.JMPTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.JMPTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.JMPTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == FROM:
                    if EPTestMap[key] in self.FROMTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.FROMTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.FROMTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.FROMTable[EPTestMap[key]][TSConfiguration]
                        DefStartRow += 1
                elif EPTestMap[key][CHECKIDX].upper() == GROUP:
                    if EPTestMap[key] in self.GroupTable:
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = key
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.GroupTable[EPTestMap[key]][TSstepname]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.GroupTable[EPTestMap[key]][TScomment]
                        main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.GroupTable[EPTestMap[key]][TSConfiguration]
                        # Gsheet = workbook[EPTestMap[key]]
                        # Gsheet.title = key
                        # DefStartRow += 1
                        Asheet = workbook[EPTestMap[key]+GROUP_Sheet]
                        Gsheet = workbook.create_sheet(key)  
                        for row in Asheet.iter_rows():
                            row_list=[]
                            for cell in row:
                                row_list.append(cell.value)
                            # print(row_list)
                            Gsheet.append(row_list)
                        DefStartRow += 1
            main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = PROGRAM
            main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.ProgramTable[PROGRAM][TSstepname]
            main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.ProgramTable[PROGRAM][TScomment]
            main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.ProgramTable[PROGRAM][TSConfiguration]
            DefStartRow += 1
            for varkey in self.LocalVarTable:
                main_sheet.cell(DefStartRow,DefStartCol + EP2Def).value = LOCALVAR
                main_sheet.cell(DefStartRow,DefStartCol + EP2Name).value = self.LocalVarTable[varkey][TSstepname]
                main_sheet.cell(DefStartRow,DefStartCol + EP2Comment).value = self.LocalVarTable[varkey][TScomment]
                main_sheet.cell(DefStartRow,DefStartCol + EP2Conf).value = self.LocalVarTable[varkey][TSConfiguration]
                DefStartRow += 1
            workbook.save(self.outputFileName)
            ret = True
        else:
            print("intput file format error")
        return ret


    def save_testcase_info(self):
        self.TestCaseinfo['SetTable'] = self.SetTable
        self.TestCaseinfo['GetTable'] = self.GetTable
        self.TestCaseinfo['APITable'] = self.APITable
        self.TestCaseinfo['EXPTable'] = self.EXPTable
        self.TestCaseinfo['NOPTable'] = self.NOPTable
        self.TestCaseinfo['JMPTable'] = self.JMPTable
        self.TestCaseinfo['FROMTable'] = self.FROMTable
        self.TestCaseinfo['LocalVarTable'] = self.LocalVarTable
        self.TestCaseinfo['GroupTable'] = self.GroupTable
        self.TestCaseinfo['ProgramTable'] = self.ProgramTable
        self.TestCaseinfo['EPMaxCol'] = self.EPMaxCol
        self.TestCaseinfo['GroupMaxCol'] = self.GroupMaxCol
        # self.TestCaseinfo['EPTABLE'] = self.EPTABLE
        # self.TestCaseinfo['GroupTestList'] = self.GroupTestList
        with open(os.path.join(_curr_path, SaveJsonName), "w", encoding="utf-8") as f1:
            json.dump(self.TestCaseinfo, f1, indent=4, ensure_ascii=False)
            f1.close()
    def read_testcase_info(self):
        with open(os.path.join(_curr_path, SaveJsonName), "r", encoding="utf-8") as f1:
            self.TestCaseinfo = json.load(f1)
            f1.close()
        self.SetTable = self.TestCaseinfo.get('SetTable',{})
        self.GetTable = self.TestCaseinfo.get('GetTable',{})
        self.APITable = self.TestCaseinfo.get('APITable',{})
        self.EXPTable = self.TestCaseinfo.get('EXPTable',{})
        self.NOPTable = self.TestCaseinfo.get('NOPTable',{})
        self.JMPTable = self.TestCaseinfo.get('JMPTable',{})
        self.FROMTable = self.TestCaseinfo.get('FROMTable',{})
        self.LocalVarTable = self.TestCaseinfo.get('LocalVarTable',{})
        self.GroupTable = self.TestCaseinfo.get('GroupTable',{})
        self.ProgramTable = self.TestCaseinfo.get('ProgramTable',{})
        self.EPMaxCol = self.TestCaseinfo.get('EPMaxCol',0)
        self.GroupMaxCol = self.TestCaseinfo.get('GroupMaxCol',0)
        # self.EPTABLE = self.TestCaseinfo.get('EPTABLE',{})
        # self.GroupTestList = self.TestCaseinfo.get('GroupTestList',{})
        
if __name__ == "__main__":
    import sys
    if len(sys.argv)<3:
        print("input arg count error")
        
    start_time = time.perf_counter()
    a = ExcelConverMod1(sys.argv[1],sys.argv[2])
    print("用时：",time.perf_counter() - start_time)
    # a = ExcelConverMod1("1.xlsx","2.xlsx")