python excel转xml 用例_Testlink用例转换小工具（excel转为xml，python版）

1#coding:utf-8

2importos

3importsys

4reload(sys)

5sys.setdefaultencoding("utf-8")

6

7fromeasy_excelimporteasy_excel

8classoperate():

9def__init__(self,ExcelFileName,SheetName):

10self.excelFile=ExcelFileName+'.xls'

11self.excelSheet=SheetName

12self.temp=easy_excel(self.excelFile)

13self.dic_testlink={}

14self.row_flag=3

15self.testsuite=self.temp.getCell(self.excelSheet,2,1)

16self.dic_testlink[self.testsuite]={"node_order":"13","details":"","testcase":[]}

17self.content=""

18self.content_list=[]

19

20defxlsx_to_dic(self,SheetName):

21whileTrue:

22#print'loop1'

23#list_testcase=dic_testlink[testsuite].["testcase"]

24

25testcase={"name":"","node_order":"100","externalid":"","version":"1","summary":"",

26"preconditions":"","execution_type":"1","importance":"3","steps":[],"keywords":"P1"}

27testcase["name"]=self.temp.getCell(self.excelSheet,self.row_flag,1)

28testcase["summary"]=self.temp.getCell(self.excelSheet,self.row_flag,3)

29testcase["preconditions"]=self.temp.getCell(self.excelSheet,self.row_flag,4)

30execution_type=self.temp.getCell(self.excelSheet,self.row_flag,7)

31ifexecution_type=="自动":

32testcase["execution_type"]=2

33#printself.temp.getCell('Sheet1',self.row_flag,3)

34step_number=1

35testcase["keywords"]=self.temp.getCell(self.excelSheet,self.row_flag,2)

36#printtestcase["keywords"]

37whileTrue:

38#print'loop2'

39step={"step_number":"","actions":"","expectedresults":"","execution_type":""}

40step["step_number"]=step_number

41step["actions"]=self.temp.getCell(self.excelSheet,self.row_flag,5)

42step["expectedresults"]=self.temp.getCell(self.excelSheet,self.row_flag,6)

43testcase["steps"].append(step)

44step_number+=1

45self.row_flag+=1

46ifself.temp.getCell(self.excelSheet,self.row_flag,1)isnotNoneorself.temp.getCell(self.excelSheet,self.row_flag,5)isNone:

47break

48#printtestcase

49

50self.dic_testlink[self.testsuite]["testcase"].append(testcase)

51#printself.row_flag

52ifself.temp.getCell(self.excelSheet,self.row_flag,5)isNoneandself.temp.getCell(self.excelSheet,self.row_flag+1,5)isNone:

53break

54self.temp.close()

55#printself.dic_testlink

56

57defcontent_to_xml(self,key,value=None):

58ifkey=='step_number'orkey=='execution_type'orkey=='node_order'orkey=='externalid'orkey=='version'orkey=='importance':

59return""+str(key)+">"

60elifkey=='actions'orkey=='expectedresults'orkey=='summary'orkey=='preconditions':

61return""+str(value)+"

]]>"+str(key)+">"

62elifkey=='keywords':

63return''

64elifkey=='name':

65return''

66else:

67return'##########'

68

69defdic_to_xml(self,ExcelFileName,SheetName):

70testcase_list=self.dic_testlink[self.testsuite]["testcase"]

71fortestcaseintestcase_list:

72forstepintestcase["steps"]:

73self.content+=""

74self.content+=self.content_to_xml("step_number",step["step_number"])

75self.content+=self.content_to_xml("actions",step["actions"])

76self.content+=self.content_to_xml("expectedresults",step["expectedresults"])

77self.content+=self.content_to_xml("execution_type",step["execution_type"])

78self.content+=""

79self.content=""+self.content+""

80self.content=self.content_to_xml("importance",testcase["importance"])+self.content

81self.content=self.content_to_xml("execution_type",testcase["execution_type"])+self.content

82self.content=self.content_to_xml("preconditions",testcase["preconditions"])+self.content

83self.content=self.content_to_xml("summary",testcase["summary"])+self.content

84self.content=self.content_to_xml("version",testcase["version"])+self.content

85self.content=self.content_to_xml("externalid",testcase["externalid"])+self.content

86self.content=self.content_to_xml("node_order",testcase["node_order"])+self.content

87self.content=self.content+self.content_to_xml("keywords",testcase["keywords"])

88self.content=self.content_to_xml("name",testcase["name"])+self.content

89self.content=self.content+""

90self.content_list.append(self.content)

91self.content=""

92self.content="".join(self.content_list)

93self.content=''+self.content+""

94self.content='<?xmlversion ="1.0"encoding="UTF-8"?>'+self.content

95self.write_to_file(ExcelFileName,SheetName)

96

97defwrite_to_file(self,ExcelFileName,SheetName):

98xmlFileName=ExcelFileName+'_'+SheetName+'.xml'

99cp=open(xmlFileName,"w")

100cp.write(self.content)

101cp.close()

102

103if__name__=="__main__":

104

105fileName=raw_input('enterexcelname:')

106sheetName=raw_input('entersheetname:')

107sheetList=sheetName.split("")

108forsheetNameinsheetList:

109test=operate(fileName,sheetName)

110test.xlsx_to_dic(sheetName)

111test.dic_to_xml(fileName,sheetName)

112print"Convertsuccess!"

113os.system('pause')