Alex Lonyay

Alex Lonyay

  • 2k
  • 104
  • 3.9k

Need help when dealing with Excel Complex Data in MSTest

May 10 2020 8:52 AM
Currently, I am fetching data from particular sheet where it matched a "Key" value - But now I want to access data from all sheets where it matches "Key" value
 
In my excel - I have two sheets: LogInUserData and Sheet2

Now in LogInUserData
Key Email Password RememberMe
LogInSuccessfully a@a.a a 0
LogInWithWrongEmail a.a a 0
LogInWithWrongPass a@a.a b 0
 
And in Sheet2 
Key Klarna
LogInSuccessfully Demo
 
And in Sheet2
KeyKlarna
LogInSuccessfullyDemo
 
Now, I want to access values of Key "Loginsuccessfully" from both the sheets - I have code but that fetches it from only first sheet and not from other - so how do i access complex data sheet from excel
 
I'm using ODBC Connection
  1. public static string TestDataFileConnection(string fileName)  
  2. {  
  3. string Filename = "C:\\Users\\PalakS\\source\\repos\\Daily update\\UnitTestProject2 - 10-04\\UnitTestProject2\\ExcelData\\LogInPageData.xlsx";  
  4. string connectionString = string.Format("Dsn=Excel Files;READONLY=false;DBQ={0};", Filename);  
  5. System.Data.Odbc.OdbcCommand odbcCmd = new System.Data.Odbc.OdbcCommand(""new System.Data.Odbc.OdbcConnection(connectionString));  
  6. return connectionString;  
  7. }  
  8. public static T GetTestData<T>(string fileName, string sheet, string keyName)  
  9. {  
  10. using (var connection = new OdbcConnection(TestDataFileConnection(fileName)))  
  11. {  
  12. connection.Open();  
  13. var query = string.Format("select * from [{0}$]where key = '{1}'", sheet, keyName);  
  14. var value = connection.Query<T>(query).FirstOrDefault();  
  15. connection.Close();  
  16. return value;  
  17. }  
  18.  
Function call:
  1. LogInUserModel user = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx""LogInUserData""LogInSuccessfully");  
  2. string email = user.Email.ToString();   

Answers (3)