Write data TO file in data driven tests

Best practices, code snippets for common functionality, examples, and guidelines.
zumar
Posts: 4
Joined: Wed Jun 21, 2017 6:30 pm

Write data TO file in data driven tests

Post by zumar » Wed Jun 21, 2017 6:33 pm

I have a file where new user accounts information is present. New users are created based on data from that excel file.

My scripts change the user data (e.g. password after first login etc.) so I want the most updated information in my excel file. How can I WRITE the data to the file?
Is it possible to update files from Ranorex connectors?

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 3921
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Write data TO file in data driven tests

Post by odklizec » Thu Jun 22, 2017 12:10 pm

Hi,

At the moment, it's only possible to write data to CSV-based data connectors (using Ranorex API). To write something to Excel, you will have to use a 3rd party methods (e.g. Microsoft.Office.Interop.Excel). For more ways to write something to Excel file, check this post:
https://www.ranorex.com/forum/writing-e ... t1605.html

And here is how you can write something to CSV (using CSVDataConnector):
https://www.ranorex.com/forum/the-excel ... tml#p31689
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

asdf
Posts: 174
Joined: Mon Mar 21, 2016 3:16 pm

Re: Write data TO file in data driven tests

Post by asdf » Thu Jun 22, 2017 12:29 pm

Hi Zumar,

In order to write information to an excel file during runtime, you will have to use a third party tool like microsoft interop. Here is a small example on how to use microsoft interop.
String xlPath = @"C:\Users\users\Desktop\Book14.xlsx";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Open(xlPath);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets["Sheet1"];
Excel.Range xlrange = (Excel.Range)xlSheet.Cells[2,2];
xlApp.DisplayAlerts = false;
int[] intArray = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
Range rng = xlApp.get_Range("A1", "J1");
rng.Value = intArray;
xlBook.Save();
xlBook.Close();
I hope this helps.

Regards,
asdf

Vaughan.Douglas
Posts: 250
Joined: Tue Mar 24, 2015 5:05 pm
Location: Des Moines, Iowa, USA

Re: Write data TO file in data driven tests

Post by Vaughan.Douglas » Thu Jun 22, 2017 4:53 pm

Connect to Excel via SQL
Dim myQuery = "UPDATE [" & excelWorksheetName & "$] Set" &
                        " [Update Field 1] = 'Updated Value'," &
                        " [Update Field 2] = 'Updated Value'," &
                        " [Updated Field 3] = 'Updated Value'" &
                             " WHERE [Field Name 1] = 'Selection Criteria'" &
                             " AND [Field Name 2]= 'Selection Criteria';"

Using myConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sExcelPath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
     OleDbConnection.ExecuteNonQuery()
End Using
Doug Vaughan