?

TestLink1.9.3測試用例：Excel轉換XML工具<二>實現代碼

?

http://blog.csdn.net/candle806/article/details/7490599

?

以下是通過VBScript實現的Excel數據轉換成XML格式，主要用于實現Testlink1.9.3的測試用例導入。代碼實現如下：

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?="")

'testcase
objxml_inter=objxml_inter&CStr("???<testcase?internalid=""02""?name=""")
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>")

objxml_inter=objxml_inter&CStr("<externalid><![CDATA[7]]></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[1]]></execution_type>")

'importance
objxml_inter=objxml_inter&CStr("<importance><![CDATA[2]]></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.3小助手: 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.3小助手: Excel轉換XML工具")
If?excelname?=?""?Then
????MsgBox?"文件名不能為空！"
????WScript.Quit
End?If

XMLname?=?InputBox("請輸入轉換之后的XML文件保存路徑和名稱：","TestLink 1.9.3小助手: 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)+"條！"

?

轉載于:https://www.cnblogs.com/testway/p/5993942.html

總結

以上是生活随笔為你收集整理的转：TestLink1.9.3测试用例：Excel转换XML工具二实现代码的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。