Wednesday, April 18, 2012

How to import test cases to TestLink


Hi,

I think those who are using TestLink to write test cases, it is a pain that it has lot of mouse clicks & navigations involved when writing test cases.

To resolve that I used to follow a simple technique of writing all my test cases in excel and then import them to TestLink at once.

TestLink allows you to import test cases as XML, hence we need to convert our test cases written in excel to XML, using a simple macro.




Then simply copy paste the following macro in to your excel file.





 Sub genReqnTcArr()  
 Dim lRow, lCol As Long  
 Dim duplicate As Boolean  
 Dim oneCell, onesMerged, tsRange, tcRange, reqRange As Range  
 Dim tsColCount, tcColCount, reqColCount, tcCFCount, arrRow, temp As Integer  
 Dim arrReq(), arrTS(), arrTc(), arrReqTc(), arrTcCF() As Variant  
 Dim strTemp, xmlStr, xlsFileName, xmlTcFileName, xmlReqFileName As String  
 'Initializing Counts  
 tsColCount = 0  
 tcColCount = 0  
 reqColCount = 0  
 tcCFCount = 0  
 'Obtaining File Names  
 'Get the name of the excel file in which the requirements and testcases have been stored  
 'and store it as the XML Requirments File name and XML Test Case File Name  
 xlsFileName = ActiveWorkbook.FullName  
 xmlStr = Left(xlsFileName, InStr(xlsFileName, "."))  
 xmlReqFileName = Left(xlsFileName, Len(xlsFileName) - 4) & "_Req.xml"  
 xmlTcFileName = Left(xlsFileName, Len(xlsFileName) - 4) & "_Tc.xml"  
 'Get the last row and last column in the list  
 lRow = lastRow()  
 arrRow = lRow - 2  
 lCol = lastColumn()  
 'Count the columns for test suite, test cases and requirements and identify if any custom fields are present  
 'Custom fields import for other than test cases is NOT supported in TestLink  
 With Sheets(1)  
   For Each oneCell In Range(Cells(1, 1), Cells(1, lCol))  
     If oneCell.MergeCells Then  
       If oneCell.Text = "Test Suite" Then  
        Set tsRange = oneCell.MergeArea  
        tsColCount = oneCell.MergeArea.Count  
       ElseIf oneCell.Text = "Test Case" Then  
        Set tcRange = oneCell.MergeArea  
        tcColCount = oneCell.MergeArea.Count  
       ElseIf oneCell.Text = "Requirements" Then  
        Set reqRange = oneCell.MergeArea  
        reqColCount = oneCell.MergeArea.Count  
       End If  
     Else  
      If oneCell.Text = "Test Suite" Then  
        tsColCount = 1  
       ElseIf oneCell.Text = "Test Case" Then  
        tcColCount = 1  
       ElseIf oneCell.Text = "Requirements" Then  
        reqColCount = 1  
       End If  
     End If  
   Next oneCell  
 End With  
 'Define array sizes to store the contents  
 If reqColCount <> 0 Then  
  ReDim Preserve arrReq(0 To arrRow, 1 To reqColCount) As Variant  
 End If  
 If tsColCount <> 0 And tcColCount <> 0 Then  
  ReDim Preserve arrTS(0 To arrRow, 1 To tsColCount) As Variant  
 End If  
 If tcColCount <> 0 Then  
  ReDim Preserve arrTc(0 To arrRow, 1 To tcColCount) As Variant  
 End If  
 If tcColCount <> 0 And reqColCount <> 0 Then  
  ReDim Preserve arrReqTc(0 To arrRow, 1 To reqColCount) As Variant  
 End If  
 'Collect the Requirements associated with Test Cases in an Array  
 If reqColCount <> 0 And tcColCount <> 0 Then  
  With Sheets(1)  
   For Each oneCell In reqRange  
    Select Case oneCell.Offset(1, 0).Text  
    Case "Spec Title"  
     arrReqTc(0, 1) = "Spec Title"  
     For iRow = 2 To lRow - 1  
      arrReqTc(iRow - 1, 1) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Document ID"  
     arrReqTc(0, 2) = "Document ID"  
     For iRow = 2 To lRow - 1  
      arrReqTc(iRow - 1, 2) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Req Title"  
     arrReqTc(0, 3) = "Req Title"  
     For iRow = 2 To lRow - 1  
      arrReqTc(iRow - 1, 3) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Description"  
     arrReqTc(0, 4) = "Description"  
     For iRow = 2 To lRow - 1  
      arrReqTc(iRow - 1, 4) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    End Select  
   Next oneCell  
  End With  
 End If  
 'Collect the Test Suite in an Array  
 If tsColCount <> 0 Then  
  With Sheets(1)  
   For Each oneCell In tsRange  
    Select Case oneCell.Offset(1, 0).Text  
    Case "Name"  
     arrTS(0, 1) = "Name"  
     For iRow = 2 To lRow - 1  
      arrTS(iRow - 1, 1) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Details"  
     arrTS(0, 2) = "Details"  
     For iRow = 2 To lRow - 1  
      arrTS(iRow - 1, 2) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    End Select  
   Next oneCell  
  End With  
 End If  
 'Collect the Test Cases in an Array  
 If tcColCount <> 0 Then  
  With Sheets(1)  
   For Each oneCell In tcRange  
    Select Case oneCell.Offset(1, 0).Text  
    Case "TC#"  
     arrTc(0, 2) = "TC#"  
     For iRow = 2 To lRow - 1  
      arrTc(iRow - 1, 2) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Name"  
     arrTc(0, 1) = "Name"  
     For iRow = 2 To lRow - 1  
      arrTc(iRow - 1, 1) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Summary"  
     arrTc(0, 3) = "Summary"  
     For iRow = 2 To lRow - 1  
      arrTc(iRow - 1, 3) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Steps"  
     arrTc(0, 4) = "Steps"  
     For iRow = 2 To lRow - 1  
      arrTc(iRow - 1, 4) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case "Expected Results"  
     arrTc(0, 5) = "Expected Results"  
     For iRow = 2 To lRow - 1  
      arrTc(iRow - 1, 5) = oneCell.Offset(iRow, 0).Text  
     Next iRow  
    Case Else  
     'Count the number of custom fields to import  
     tcCFCount = tcCFCount + 1  
    End Select  
   Next oneCell  
  End With  
  ReDim Preserve arrTc(0 To arrRow, 1 To tcColCount - tcCFCount) As Variant  
  'Collect the Custom fields for Test Cases in an Array  
  If tcCFCount <> 0 Then  
   temp = arrRow * tcCFCount  
   ReDim Preserve arrTcCF(0 To temp, 1 To 3) As Variant  
   arrTcCF(0, 1) = "TC#"  
   arrTcCF(0, 2) = "Name"  
   arrTcCF(0, 3) = "Value"  
   temp = 0  
   With Sheets(1)  
    For Each oneCell In tcRange  
     Select Case oneCell.Offset(1, 0).Text  
      Case "TC#"  
       For i = 1 To tcCFCount  
        For iRow = 2 To lRow - 1  
         arrTcCF(iRow - 1 + temp, 1) = oneCell.Offset(iRow, 0).Text  
        Next iRow  
        temp = temp + lRow - 2  
       Next i  
       temp = 0  
      Case "Name"  
       'Do Nothing  
      Case "Summary"  
       'Do Nothing  
      Case "Steps"  
       'Do Nothing  
      Case "Expected Results"  
       'Do Nothing  
      Case Else  
       For iRow = 2 To lRow - 1  
        arrTcCF(iRow - 1 + temp, 2) = oneCell.Offset(1, 0).Text  
        arrTcCF(iRow - 1 + temp, 3) = oneCell.Offset(iRow, 0).Text  
       Next iRow  
       temp = temp + lRow - 2  
      End Select  
    Next oneCell  
   End With  
  End If  
 End If  
 'Remove the duplicate listings of requirements and store the unique listings in an array  
 If reqColCount <> 0 Then  
  If tcColCount <> 0 Then  
   temp = 0  
   For iRow = 0 To arrRow  
    duplicate = False  
    For jRow = 0 To iRow  
     If arrReqTc(iRow, 2) = arrReq(jRow, 2) Then  
      duplicate = True  
      Exit For  
     End If  
    Next jRow  
    If duplicate = False Then  
     arrReq(temp, 1) = arrReqTc(iRow, 1)  
     arrReq(temp, 2) = arrReqTc(iRow, 2)  
     If reqColCount > 2 Then  
      arrReq(temp, 3) = arrReqTc(iRow, 3)  
     End If  
     If reqColCount > 3 Then  
      arrReq(temp, 4) = arrReqTc(iRow, 4)  
     End If  
     temp = temp + 1  
    End If  
   Next iRow  
  Else  
   With Sheets(1)  
    For Each oneCell In reqRange  
     Select Case oneCell.Offset(1, 0).Text  
     Case "Spec Title"  
      arrReq(0, 1) = "Spec Title"  
      For iRow = 2 To lRow - 1  
       arrReq(iRow - 1, 1) = oneCell.Offset(iRow, 0).Text  
      Next iRow  
     Case "Document ID"  
      arrReq(0, 2) = "Document ID"  
      For iRow = 2 To lRow - 1  
       arrReq(iRow - 1, 2) = oneCell.Offset(iRow, 0).Text  
      Next iRow  
     Case "Req Title"  
      arrReq(0, 3) = "Req Title"  
      For iRow = 2 To lRow - 1  
       arrReq(iRow - 1, 3) = oneCell.Offset(iRow, 0).Text  
      Next iRow  
     Case "Description"  
      arrReq(0, 4) = "Description"  
      For iRow = 2 To lRow - 1  
       arrReq(iRow - 1, 4) = oneCell.Offset(iRow, 0).Text  
      Next iRow  
     End Select  
    Next oneCell  
  End With  
  End If  
 End If  
 'Create the Requirements XML file  
 If reqColCount <> 0 Then  
  Open xmlReqFileName For Output As #1  
  Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"  
  Print #1, "<requirements>"  
  For iRow = 1 To arrRow  
   temp = 0  
   For iCol = 1 To reqColCount  
    If arrReq(iRow, iCol) <> "" Then  
     Select Case arrReq(0, iCol)  
      Case "Spec Title"  
       'Do Nothing  
      Case "Document ID"  
       temp = 1  
       Print #1, "  <requirement>"  
       xmlStr = "    <docid><![CDATA[" & arrReq(iRow, iCol) & "]]></docid>"  
       Print #1, xmlStr  
      Case "Req Title"  
       xmlStr = "    <title><![CDATA[" & arrReq(iRow, iCol) & "]]></title>"  
       Print #1, xmlStr  
      Case "Description"  
       xmlStr = "    <description><![CDATA[" & arrReq(iRow, iCol) & "]]></description>"  
       Print #1, xmlStr  
      Case Else  
       'Do Nothing  
      End Select  
     End If  
   Next iCol  
   If temp <> 0 Then  
    Print #1, "  </requirement>"  
   End If  
  Next iRow  
  Print #1, "</requirements>"  
  Close #1  
 End If  
 'Create the Test Cases XML file  
 If tcColCount <> 0 Then  
  Open xmlTcFileName For Output As #2  
  Print #2, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"  
  If tsColCount <> 0 Then  
   Print #2, "<testsuite name=" & Chr(34) & Chr(34) & ">"  
   Print #2, "  <details><![CDATA[]]></details>"  
  Else  
   Print #2, "<testcases>"  
  End If  
  strTemp = ""  
  For iRow = 1 To arrRow  
   temp = 0  
   If tsColCount <> 0 Then  
    If arrTS(iRow, 1) <> "" Then  
     xmlStr = "  <testsuite name=" & Chr(34) & arrTS(iRow, 1) & Chr(34) & ">"  
     Print #2, xmlStr  
     xmlStr = "    <details><![CDATA[" & arrTS(iRow, 2) & "]]></details>"  
     Print #2, xmlStr  
     strTemp = "  "  
    End If  
   End If  
   For iCol = 1 To tcColCount - tcCFCount  
    If arrTc(iRow, iCol) <> "" Then  
     Select Case arrTc(0, iCol)  
      Case "Name"  
       xmlStr = strTemp & "  <testcase name=" & Chr(34) & arrTc(iRow, iCol) & Chr(34) & ">"  
       Print #2, xmlStr  
      Case "TC#"  
       xmlStr = strTemp & "    <node_order><![CDATA[" & arrTc(iRow, iCol) & "]]></node_order>"  
       Print #2, xmlStr  
      Case "Summary"  
       xmlStr = strTemp & "    <summary><![CDATA[" & arrTc(iRow, iCol) & "]]></summary>"  
       Print #2, xmlStr  
      Case "Steps"  
       xmlStr = strTemp & "    <steps><step><step_number><![CDATA[1]]></step_number><actions><![CDATA[" & arrTc(iRow, iCol) & "]]></actions>"  
       Print #2, xmlStr  
      Case "Expected Results"  
       xmlStr = strTemp & "    <expectedresults><![CDATA[" & arrTc(iRow, iCol) & "]]></expectedresults></step></steps>"  
       Print #2, xmlStr  
      End Select  
     End If  
   Next iCol  
   'Get the custom fields if any  
   temp = 0  
   If tcCFCount <> 0 Then  
    For i = 1 To arrRow * tcCFCount  
     If arrTc(iRow, 2) = arrTcCF(i, 1) Then  
      If arrTcCF(i, 2) <> "" And arrTcCF(i, 3) <> "" Then  
       If temp = 0 Then  
        xmlStr = strTemp & "    <custom_fields>"  
        Print #2, xmlStr  
        temp = 1  
       End If  
       xmlStr = strTemp & "      <custom_field>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "        <name>" & arrTcCF(i, 2) & "</name>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "        <value>" & arrTcCF(i, 3) & "</value>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "      </custom_field>"  
       Print #2, xmlStr  
      End If  
     End If  
    Next i  
    If temp = 1 Then  
     xmlStr = strTemp & "    </custom_fields>"  
     Print #2, xmlStr  
     temp = 0  
    End If  
   End If  
   'Get the requirements associated with test cases  
   If reqColCount <> 0 Then  
    For i = 1 To arrRow  
     If arrTc(iRow, 2) = Trim(Str(i)) Then  
      If arrReqTc(i, 1) <> "" And arrReqTc(i, 2) <> "" Then  
       xmlStr = strTemp & "    <requirements>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "      <requirement>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "        <req_spec_title>" & arrReqTc(i, 1) & "</req_spec_title>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "        <doc_id>" & arrReqTc(i, 2) & "</doc_id>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "      </requirement>"  
       Print #2, xmlStr  
       xmlStr = strTemp & "    </requirements>"  
       Print #2, xmlStr  
      End If  
     End If  
    Next i  
   End If  
   xmlStr = strTemp & "  </testcase>"  
   Print #2, xmlStr  
   If tsColCount <> 0 Then  
    If iRow < arrRow Then  
     If arrTS(iRow + 1, 1) <> "" Then  
       Print #2, "  </testsuite>"  
     End If  
    Else  
     Print #2, "  </testsuite>"  
    End If  
   End If  
  Next iRow  
  If tsColCount <> 0 Then  
   Print #2, "</testsuite>"  
  Else  
   Print #2, "</testcases>"  
  End If  
  Close #2  
 End If  
 End Sub  
 Function lastRow() As Long  
  If WorksheetFunction.CountA(Cells) > 0 Then  
   'Search for any entry, by searching backwards by Rows.  
   lastRow = Cells.Find(What:="*", After:=[A1], _  
              SearchOrder:=xlByRows, _  
              SearchDirection:=xlPrevious).Row  
  End If  
 End Function  
 Function lastColumn() As Long  
  If WorksheetFunction.CountA(Cells) > 0 Then  
   'Search for any entry, by searching backwards by Columns.  
   lastColumn = Cells.Find(What:="*", After:=[A1], _  
               SearchOrder:=xlByColumns, _  
               SearchDirection:=xlPrevious).Column  
  End If  
 End Function  




Then run the macro to Generate the XML


Now you can go to TestLink --> Test Specification , select the root, and then click on Import , select the xml file and then upload.




Then check whether all your test cases imported successfully.


References:
http://testlink-import.blogspot.com/2009/11/test-link-import.html
http://testlink.sourceforge.net/docs/docs/print.php?page=all

2 comments:

  1. for testlink 1.9.x: https://sourceforge.net/projects/xlstoxml/

    ReplyDelete
  2. When i try to import using this tool, i get error in Testlink. When i tried to validate the XML, it said that there is an error in line of XML

    ReplyDelete