Need help when dealing with Excel Complex Data in MSTest

Alex Lonyay

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	[email protected]	a	0
LogInWithWrongEmail	a.a	a	0
LogInWithWrongPass	[email protected]	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

public static string TestDataFileConnection(string fileName)
{
string Filename = "C:\\Users\\PalakS\\source\\repos\\Daily update\\UnitTestProject2 - 10-04\\UnitTestProject2\\ExcelData\\LogInPageData.xlsx";
string connectionString = string.Format("Dsn=Excel Files;READONLY=false;DBQ={0};", Filename);
System.Data.Odbc.OdbcCommand odbcCmd = new System.Data.Odbc.OdbcCommand("", new System.Data.Odbc.OdbcConnection(connectionString));
return connectionString;
}
public static T GetTestData(string fileName, string sheet, string keyName)
{
using (var connection = new OdbcConnection(TestDataFileConnection(fileName)))
{
connection.Open();
var query = string.Format("select * from [{0}$]where key = '{1}'", sheet, keyName);
var value = connection.Query(query).FirstOrDefault();
connection.Close();
return value;
}
}

Function call:

LogInUserModel user = AccessExcelData.GetTestData("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");
string email = user.Email.ToString();

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sai Kumar Koona6y ago
Accepted answer
Hello Alex,

This is as simple as querying data from multiple database tables. Please follow the below link to fetch data using the ODBC connector and replace the query in oComm.CommandText as follows

oComm.CommandText = "Select * From [LogInUserData] L left join [Sheet2] S on L.Key = S.Key";

https://www.c-sharpcorner.com/uploadfile/yougerthen/connect-to-an-excel-file-as-a-data-source-via-obdc/
Alex Lonyay6y ago
@sai-kumar51 - If you can help
Alex Lonyay6y ago
Hey there,

Below is my Excel sheet, which has 4 tabs having unique TestName

Now my function is this to fetch data:
public static T GetAllTestData(string keyName)

{
using (var connection = new
OdbcConnection(TestDataFileConnection()))
{
connection.Open();
System.Data.DataTable dt = null;
dt = connection.GetSchema(OdbcMetaDataCollectionNames.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
var i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
T value = default(T);
// Loop through all of the sheets if you want to...
for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
var query = string.Format("select * from [{0}] where TestName = '{1}'", excelSheets[j], keyName);
value = connection.Query(query).FirstOrDefault();
}
//Console.Write(list1);
connection.Close();
return value;
}

This function seems to be having an issue - I want to fetch all the data from all the tabs having unique key value, currently my value variable keeps getting override with latest value so at the end I'm not geting all the values from all the tabs

Is there a way to handle such case or some other approach I should try on handling data driven with multiple tabs in c#