TestLink测试用例Excel转换XML工具

发表于:2015-6-02 13:43  作者:一米阳光做测试   来源:51Testing软件测试网采编

字体: | 上一篇 | 下一篇 |我要投稿 | 推荐标签: 软件测试工具 TestLink 测试管理工具

  TestLink对于测试用例的管理来说,是蛮强大的,但是在导入导出这块,功能有点弱,本文针对测试用例的导入,转载了一个Excel转换成xml工具。
  1、根据到处的测试用例xml,定义一下我的Excel的格式如下:
  2、如果还没有开始写用例的,可以一依据这个模板,在excel中编写用例,以下脚本是按照单元格的顺序读取的,所以这里的格式要调整,脚本中也要做相应的调整。
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,这个只能生成一个

评 论

  • shenlei001001 (2017-4-11 15:44:46)

    这段代码复制下来后,& 要换成+。还有跑不通,提示在end sub 编译错误

论坛新帖



建议使用IE 6.0以上浏览器,800×600以上分辨率,法律顾问:上海信义律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2022, 沪ICP备05003035号
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪公网安备 31010102002173号

51Testing官方微信

51Testing官方微博

扫一扫 测试知识全知道