TestLink学习七：TestLink测试用例Excel转换XML工具

http://www.cnblogs.com/yangxia-test/p/4460551.html

TestLink对于测试用例的管理来说，是蛮强大的，但是在导入导出这块，功能有点弱，本文针对测试用例的导入，转载了一个Excel转换成xml工具。

1、根据到处的测试用例xml，定义一下我的Excel的格式如下：

编号	用例名称	摘要	重要性	测试方式	前提	步骤	期望结果	实际结果
1	2	3	4	5	6	7	8	9

2、如果还没有开始写用例的，可以一依据这个模板，在excel中编写用例，以下脚本是按照单元格的顺序读取的，所以这里的格式要调整，脚本中也要做相应的调整。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162

Dim objexcel,objworkbook,objsheet,objxml_inter,objxml,totalrow,row,id,excelStr '======================================== '创建Excel对象,关闭Excel对象--函数 '========================================= Function getExcel(excelname, excelpath) Set objexcel = CreateObject("excel.application") Set objworkbook = objexcel.Workbooks.Open(excelpath) Set objsheet = objworkbook.Sheets(excelname) End Function Function clsExcel() objworkbook.Close End Function '============================================ Function dealStr(excelStr) For id=2 To 8 excelStr = Replace(excelStr,id&"、","<br/>"&id&"、") excelStr = Replace(excelStr,id&".","<br/>"&id&".") Next dealStr=excelStr End Function '======================== '获取Excel单元格数据,并将XML的格式写入到对应的单元格，之后通过创建XML对象生成XML文件 '======================== Function getExcelData() row=2 objxml_inter="" Do While Not (objsheet.cells(row,2).value="") 'internalid objxml_inter=objxml_inter&CStr("<testcase internalid=""") ' objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,1))) '内部用例编号 objxml_inter=objxml_inter&CStr(""" name=""") 'caseName objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,2))) '用例名称 objxml_inter=objxml_inter&CStr(""">") objxml_inter=objxml_inter&CStr("<node_order><![CDATA[0]]></node_order>") 'externalid objxml_inter=objxml_inter&CStr("<externalid><![CDATA[") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,1))) '外部用例编号 objxml_inter=objxml_inter&CStr("]]></externalid>") 'summary objxml_inter=objxml_inter&CStr("<summary><![CDATA[<p>") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,3))) '摘要 objxml_inter=objxml_inter&CStr("</p>]]></summary>") 'preconditions objxml_inter=objxml_inter&CStr("<preconditions><![CDATA[<p>") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,6))) '前置条件 objxml_inter=objxml_inter&CStr("</p>]]></preconditions>") 'execution_type objxml_inter=objxml_inter&CStr("<execution_type><![CDATA[") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,5))) '执行方式:手工1，自动的2 objxml_inter=objxml_inter&CStr("]]></execution_type>") 'importance 'objxml_inter=objxml_inter&CStr("<importance><![CDATA[1]]></importance>") objxml_inter=objxml_inter&CStr("<importance><![CDATA[") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,4))) '重要性:高3中2低1 objxml_inter=objxml_inter&CStr("]]></importance>") 'steps objxml_inter=objxml_inter&CStr("<steps>") 'step objxml_inter=objxml_inter&CStr("<step>") 'step_number objxml_inter=objxml_inter&CStr("<step_number><![CDATA[1]]></step_number>") 'action objxml_inter=objxml_inter&CStr("<actions><![CDATA[<p>") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,7))) '测试步骤 objxml_inter=objxml_inter&CStr("</p>]]></actions>") 'expectedresults objxml_inter=objxml_inter&CStr("<expectedresults><![CDATA[<p>") objxml_inter=objxml_inter&CStr(dealStr(objsheet.cells(row,8))) '预期结果 objxml_inter=objxml_inter&CStr("</p>]]></expectedresults>") 'execution_type objxml_inter=objxml_inter&CStr("<execution_type><![CDATA[1]]></execution_type>") objxml_inter=objxml_inter&CStr("</step>") objxml_inter=objxml_inter&CStr("</steps>") objxml_inter=objxml_inter&CStr("</testcase>") 'CStr(objsheet.cells(x,1)),CStr(objsheet.cells(x,2)) 'MyFile.Write(objxml_inter) 'WScript.Echo x-1 row=row+1 Loop totalrow = row-2 End Function '======================== '创建XML文件 '======================== Sub CreateXML Dim fileObj, XmlFile Set fileObj = CreateObject("Scripting.FileSystemObject") Set XmlFile = fileObj.CreateTextFile(XMLname, True) 'xml title objxml=CStr("<?xml version=""1.0"" encoding=""GBK""?>") objxml=objxml&CStr("<testcases>") objxml=objxml&objxml_inter objxml=objxml&CStr("</testcases>") XmlFile.Write(objxml) XmlFile.Close End Sub excelpath = InputBox("请输入Excel文件正确的路径名和文件名：","TestLink 1.9.13小助手: Excel转换XML工具") If excelpath = "" Then MsgBox "文件名不能为空！" WScript.Quit ElseIf InStr(excelpath,".xls") < 1 Then MsgBox "文件名格式不对！" WScript.Quit End If excelname = InputBox("请输入Excel中所要操作的表格名称：","TestLink 1.9.13小助手: Excel转换XML工具") If excelname = "" Then MsgBox "文件名不能为空！" WScript.Quit End If XMLname = InputBox("请输入转换之后的XML文件保存路径和名称：","TestLink 1.9.13小助手: Excel转换XML工具") If XMLname = "" Then MsgBox "文件名不能为空！" WScript.Quit ElseIf InStr(XMLname,".xml") < 1 Then MsgBox "文件名格式不对！" WScript.Quit End If '初始化excel对象 Call getExcel(excelname, excelpath) '读入Excel数据 Call getExcelData() '写入数据， XML CreateXML '关闭Excel对象 Call clsExcel() '提示信息 MsgBox "完成从Excel到XML的数据转换，总共"+CStr(totalrow)+"条！"

脚本保存本地，选择编码方式为unicode，后缀名为.vbs

注意：excel表中的用例编号是不能相同的，应该是唯一的

遗留问题 ：有多行步骤的时候，只能生成一行，即三个actions和expectresult,这个只能生成一个