Import test cases with Powershell
#Script imports Test Cases with steps and expected results from Excel file to Test Manager (and TFS). Filename, sheet name and column names are input parameters. AreaPath, TestPlan and TestSuite are common for all imported Test Cases.
Use e. g. following structure for Excel file
function Import-TestCases {
param (
[Parameter(Mandatory=$true, HelpMessage="Path to Excel file with Test Cases.")]
[string] $TestCaseExcelFilepath,
[Parameter(Mandatory=$false, HelpMessage="Sheet name in Excel file with Test Cases")]
[string] $SheetName = 'Test Script',
[Parameter(HelpMessage='Excel column names array for columns in following order: Test Case Title, Test Step Action, Test Step Expected Result. E. g. @("Test Condition" , "Action", "Expected Result").')]
[array] $ColumnMapping = @("Test Condition" , "Action", "Expected Result"),
[Parameter(Mandatory=$false, HelpMessage="Area Path for new Test Cases")]
[string] $AreaPath,
[Parameter(Mandatory=$true, HelpMessage="Test suite name in Test Manager.")]
[string] $TestSuiteName,
[Parameter(Mandatory=$true, HelpMessage="Name of the Test Plan in Test Manager.")]
[string] $TestPlanName,
[Parameter(Mandatory=$false, HelpMessage="TFS Server Url. 'https://tfsServer/tfs/yourProject' is default.")]
[string] $TfsServerUrl = "https://tfsServer/tfs/yourProject",
[Parameter(Mandatory=$false, HelpMessage="Project Name in TFS. 'yourProject' is default.")]
[string] $ProjectName = "yourProject",
[Parameter(Mandatory=$false, HelpMessage="Destination for log file. 'c:\temp' is default.")]
[string] $LogFileLocation = "c:\temp"
)
Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue
# Load assemblies
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.TeamFoundation.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.TeamFoundation.WorkItemTracking.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.TeamFoundation.TestManagement.Common")
[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.TeamFoundation.TestManagement.Client")
# Get TFS structure, project
[psobject] $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($TfsServerUrl)
$workItemStore = $tfs.TfsTeamProjectCollection.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])
# Get Test Manager structure, project
$tfs = new-object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection([Microsoft.TeamFoundation.Client.TfsTeamProjectCollection]::GetFullyQualifiedUriForName($TfsServerUrl));
$tms = $tfs.GetService([Microsoft.TeamFoundation.TestManagement.Client.ITestManagementService]);
$project = [Microsoft.TeamFoundation.TestManagement.Client.ITestManagementTeamProject]$tms.GetTeamProject($ProjectName);
#powershell bug workaround, $project.TestPlans property is not available
$testPlansProperty = [Microsoft.TeamFoundation.TestManagement.Client.ITestManagementTeamProject].GetProperty("TestPlans").GetGetMethod();
$testPlans = $testPlansProperty.Invoke($project, "instance,public", $null, $null, $null);
#access project.TestSuites property
$testSuitesProperty = [Microsoft.TeamFoundation.TestManagement.Client.ITestManagementTeamProject].GetProperty("TestSuites").GetGetMethod();
$testSuites = $testSuitesProperty.Invoke($project, "instance,public", $null, $null, $null);
$currentPlan = $testPlans.Query("Select * From TestPlan Where planName = '{0}'" -f $TestPlanName)
$plan = $currentPlan | where {$_.Name -eq $TestPlanName}
#Create log file
$logFileName = "TestCaseImport_{0}_{1}.txt" -f $testPlanName, (Get-Date -Format "yyyy-MM-dd_HH-mm")
$logFilePath = Join-Path -Path $LogFileLocation -ChildPath $logFileName
if(-not (Test-Path $LogFileLocation))
{
$void = New-Item -ItemType "Directory" -Path $LogFileLocation
}
if($plan)
{
#access plan.RootSuite.SubSuites property
$subSuitesProperty = [Microsoft.TeamFoundation.TestManagement.Client.IStaticTestSuite].GetProperty("SubSuites").GetGetMethod();
$subSuites = $subSuitesProperty.Invoke($plan.RootSuite, "instance,public", $null, $null, $null);
}
if (-not $plan)
{
#Plan doesn't exist, create new
$plan = $testPlans.Create();
$plan.Name = $TestPlanName;
$plan.Save();
Write-OutAndLog ("New Test Plan ""{0}"" with Id: {1} created." -f $plan.Name, $plan.Id) $logFilePath
#access plan.RootSuite.SubSuites property
$subSuitesProperty = [Microsoft.TeamFoundation.TestManagement.Client.IStaticTestSuite].GetProperty("SubSuites").GetGetMethod();
$subSuites = $subSuitesProperty.Invoke($plan.RootSuite, "instance,public", $null, $null, $null);
#Create new suite
$newSuite = Create-TestSuite -TestSuites $testSuites -Plan $plan -TestSuiteName $TestSuiteName -LogFilePath $logFilePath;
}
else
{
#Plan already exist, check if suite exists
$suiteFound = $false;
foreach ($suite in $subSuites)
{
if ($suite.Title -eq $TestSuiteName)
{
$suiteFound = $true;
$newSuite = $suite
break;
}
foreach ($subSubSuite in $suite.Entries)
{
if ($subSubSuite.Title -eq $TestSuiteName)
{
$suiteFound = $true;
$newSuite = $subSubSuite.TestSuite
break;
}
}
}
if (-not $suiteFound)
{
$newSuite = Create-TestSuite -TestSuites $testSuites -Plan $plan -TestSuiteName $TestSuiteName -LogFilePath $logFilePath;
}
}
$testCases = Get-TestCaseObjectFromExcel $TestCaseExcelFilepath -ColumnMapping $ColumnMapping -SheetName $SheetName
foreach($testCase in $testCases)
{
Create-TestCaseInTestPlan -TestCaseTitle $testCase.Title -Steps $testCase.Steps -Area $AreaPath -TestSuite $newSuite -Plan $plan -Project $project -LogFilePath $logFilePath;
}
}
function Create-TestSuite
{
param(
[Microsoft.TeamFoundation.TestManagement.Client.ITestSuiteHelper]$TestSuites,
[Microsoft.TeamFoundation.TestManagement.Client.ITestPlan]$Plan,
[string]$TestSuiteName,
[string]$LogFilePath = "c:\temp\TestCaseImport.txt"
)
#addding a new Test Suite to our plan
$newSuite = $TestSuites.CreateStatic();
$newSuite.Title = $TestSuiteName;
$entriesProperty = [Microsoft.TeamFoundation.TestManagement.Client.IStaticTestSuite].GetProperty("Entries").GetGetMethod();
$entries = $entriesProperty.Invoke($plan.RootSuite, "instance,public", $null, $null, $null);
$void = $entries.Add($newSuite);
$plan.Save();
Write-OutAndLog "Test suite '$TestSuiteName' was created." $LogFilePath
return $newSuite
}
function Create-TestCaseInTestPlan
{
param(
[string] $TestCaseTitle,
[array] $Steps,
[string] $Area,
[Microsoft.TeamFoundation.TestManagement.Client.IStaticTestSuite] $TestSuite,
[Microsoft.TeamFoundation.TestManagement.Client.ITestPlan] $Plan,
[Microsoft.TeamFoundation.TestManagement.Client.ITestManagementTeamProject] $Project,
[string]$LogFilePath = "c:\temp\TestCaseImport.txt"
)
#find if test case with Title is not already there
$testCaseFound = $false
foreach($tc in $TestSuite.AllTestCases)
{
if($tc.Title -eq $TestCaseTitle)
{
$testCaseFound = $true
break;
}
}
if (-not $testCaseFound)
{
#access $project.TestCases property is not available
$testCasesProperty = [Microsoft.TeamFoundation.TestManagement.Client.ITestManagementTeamProject].GetProperty("TestCases").GetGetMethod();
$testCases = $testCasesProperty.Invoke($project, "instance,public", $null, $null, $null);
#creating a Testcase to add to our Test Suite
$testCase = $testCases.Create();
$testCase.Title = $TestCaseTitle;
if($Area)
{
$testCase.Area = $Area
}
#Add Test Steps
foreach($stp in $Steps)
{
$step = $testCase.CreateTestStep();
if($stp.Action)
{
$step.Title = $stp.Action
}
if($stp.Result)
{
$step.ExpectedResult = $stp.Result
}
$testCase.Actions.Add($step);
}
$testCase.Save();
$entriesProperty = [Microsoft.TeamFoundation.TestManagement.Client.IStaticTestSuite].GetProperty("Entries").GetGetMethod();
$entries = $entriesProperty.Invoke($TestSuite, "instance,public", $null, $null, $null);
$void = $entries.Add($testCase);
$Plan.Save();
Write-OutAndLog ("Test case {0}: '{1}' created." -f $testCase.Id, $testCaseTitle) $LogFilePath
}
else
{
Write-OutAndLog ("Test case '{0}' already exists." -f $testCaseTitle) $LogFilePath;
}
}
function Get-ColumnNumberFromTitle($Worksheet, $RowNr, $Title)
{
$cells = $Worksheet.Cells
$found = $false
for($col = 1; $col -le 20; $col++)
{
if($cells.Item($RowNr, $col).Value() -eq $Title)
{
return $col
}
}
if(-not $found)
{
throw "'$Title' column not found in row $RowNr."
}
}
function Get-TestCaseObjectFromExcel {
param (
[Parameter(HelpMessage="Excel file path.")]
[string] $Path,
[string] $SheetName,
[Parameter(HelpMessage="Excel column names array for columns : @('Test Case Title', 'Test Step Action', 'Test Step Expected Result').")]
[array] $ColumnMapping
)
$cultureOld = [System.Threading.Thread]::CurrentThread.CurrentCulture
$cultureUS = [System.Globalization.CultureInfo]'en-US'
# Open excel file
[System.Threading.Thread]::CurrentThread.CurrentCulture = $CultureUS
$application = New-Object -comobject Excel.Application
$application.Visible = $false
$workbook = $application.Workbooks.Open($Path, 2, $true)
if ($SheetName)
{
$worksheet = $workbook.Worksheets.Item($SheetName)
}
else
{
$worksheet = $workbook.Worksheets.Item(1)
}
Write-Host "Reading columns...`r`n"
#Set header columns number
$row = 2
$titleCol = Get-ColumnNumberFromTitle $worksheet 1 $ColumnMapping[0]
$actionCol = Get-ColumnNumberFromTitle $worksheet 1 $ColumnMapping[1]
$resultCol = Get-ColumnNumberFromTitle $worksheet 1 $ColumnMapping[2]
$cells = $worksheet.Cells
$testCases = @();
#Iterate through excel file, until 'Action' column is empty and 'Result' column empty
while(($cells.Item($row, $actionCol).Value() -ne $null) -or ($cells.Item($row, $resultCol).Value() -ne $null))
{
#Check Test Case Title column if it's new Test Case
while($cells.Item($row, $titleCol).Value() -ne $null)
{
$steps = @()
$step = @{'Action' = $cells.Item($row, $actionCol).Value(); 'Result' = $cells.Item($row, $resultCol).Value();}
$testCase = @{'Title' = $cells.Item($row, $titleCol).Value(); 'Steps' = $steps}
$steps += $step
$row++
#Check if it's not test case in next row, if not, consider steps
while(($cells.Item($row, $titleCol).Value() -eq $null) -and ($cells.Item($row, $actionCol).Value() -ne $null))
{
#Read Test Steps
$step = @{'Action' = $cells.Item($row, $actionCol).Value(); 'Result' = $cells.Item($row, $resultCol).Value();}
$steps += $step
$row++
}
$testCase.Steps = $steps
$testCases += $testCase
}
$row++
}
#$testCases | foreach {write-host $_["Title"];$_["Steps"].Count}
# Close Workbook and Excel. Revert to original Regional settings
$workbook.Close()
$application.Quit()
[System.Threading.Thread]::CurrentThread.CurrentCulture = $cultureOld
return $testCases
}
function Write-OutAndLog([string]$Message, [string]$LogFilePath)
{
Write-Host $Message
$Message | Out-File -Append $LogFilePath
}