Write Into the Data Source

PROBLEM

I have a data driven test that checks whether a specific condition is met. I'd like to write a message into each corresponding row based on a condition.

SOLUTION

This is possible with a coded solution. Here's an example:

 

Let's automate www.checkdomain.com to check whether the domains listed in an Excel file are still available. We'll write Registered or Available into the first column of the Excel file for each domain name.

 


Before Test Run
After Test Run

 

Here's a sample test that automates this case:

 

 

Note: Ensure you Add an Assembly Reference to Microsoft.Office.Interop.Excel. You can download a version of that file on Microsoft's website that matches your version of MS Office.

 

Here's the code from that sample:

 

C#
string dataSourcePath = this.ExecutionContext.DeploymentDirectory + @"\Data\domainResults.xlsx";
string myPath = "C:\\domainResults.xlsx";
 
if (!System.IO.File.Exists(myPath))
{
    System.IO.File.Copy(dataSourcePath, myPath);
}
 
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(myPath);
 
System.Threading.Thread.Sleep(1000);
ActiveBrowser.RefreshDomTree();
 
if (ActiveBrowser.ContainsText("already been registered"))
{
    excelApp.Cells[Data.IterationIndex + 2 , 1] = "Registered";
}
if (ActiveBrowser.ContainsText("is still available"))
{
    excelApp.Cells[Data.IterationIndex + 2 , 1] = "Available";
}
  
excelApp.Visible = true;
excelApp.ActiveWorkbook.Save();
  
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
           
excelApp.Quit();
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

 

Visual Basic
Dim dataSourcePath As String = Me.ExecutionContext.DeploymentDirectory + "\Data\domainResults.xlsx"
Dim myPath As String = "C:\domainResults.xlsx"
 
If Not System.IO.File.Exists(myPath) Then
    System.IO.File.Copy(dataSourcePath, myPath)
End If
 
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open(myPath)
 
System.Threading.Thread.Sleep(1000)
ActiveBrowser.RefreshDomTree()
 
If ActiveBrowser.ContainsText("already been registered") Then
    excelApp.Cells(Data.IterationIndex + 2, 1) = "Registered"
End If
If ActiveBrowser.ContainsText("is still available") Then
    excelApp.Cells(Data.IterationIndex + 2, 1) = "Available"
End If
 
excelApp.Visible = True
excelApp.ActiveWorkbook.Save()
 
workbook.Close(False, Type.Missing, Type.Missing)
excelApp.Workbooks.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
 
excelApp.Quit()
GC.Collect()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)