I am trying to perform Data driven testing by loading client codes from Excel file to login and perform other operation.I am trying to iterate my test for all clients in the client_code column. I only need to read client_code column and get data to repeat same test with those clients. I am getting Null value when I run below code
Any help with this would be much appreciated thank you.
I am using ExcelDataReader v3.4.0, ExcelDataReader.DataSet v3.4.0, selenium Webdriver v3.11.0
My Excel table looks like below.
client_code client_name
11111 Client1
22222 Client2
33333 Client3
44444 Client4
55555 Client5 This is my code:
ExcelUtil.cs
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Threading.Tasks;
namespace MyDemoAutomation
{
public class ExcelUtil
{
public DataTable ExcelToDatable(string fileName)
{
// open file and returns as stream
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
// create openXmlReader via ExcelReaderFactory
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//Set the first row as column name
var result1 = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
// Return as dataset
DataSet result = excelReader.AsDataSet();
// Get all tables
DataTableCollection table = result.Tables;
// Store in Database
DataTable resultTable = table["Sheet1"];
// return
return resultTable;
}
List<DataCollection> dataCol = new List<DataCollection>();
public void PopulateInCollection(string fileName)
{
DataTable table = ExcelToDatable(fileName);
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
DataCollection dtTable = new DataCollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
dataCol.Add(dtTable);
}
}
}
public string ReadData(int rowNumber, string columnName)
{
try
{
// Retriving data using LINQ to reduce much of iterations
string data = (from colData in dataCol
where colData.colName == columnName && colData.rowNumber == rowNumber
select colData.colValue).SingleOrDefault();
return data.ToString();
}
catch (Exception e)
{
return null;
}
}
internal class DataCollection
{
public int rowNumber { get; internal set; }
public string colName { get; internal set; }
public string colValue { get; internal set; }
}
}
}Test Class :
[Test]
public void DataDrivenTest_FromExcel()
{
Driver = new ChromeDriver();
ExcelUtil util = new ExcelUtil();
util.PopulateInCollection(@"C:\dan\AutomationTest\TestData\test.xlsx");
Driver.FindElement(By.Id("contentPlaceholder_txtClientCode")).SendKeys(util.ReadData(i));
Driver.FindElement(By.XPath("//*[@id='btnLogin']")).Click();
Driver.FindElement(By.XPath("//*[@id='tabContent0']/table/tbody/tr[2]/td[1]")).Click();
Driver.FindElement(By.Id("contentPlaceholder_txtcloseButton")).Click();
Driver.Quit
}Hi @Miguel D'Alessio I tried running the code you provided. Below is the exact copy paste code which I am running with error. Thank you.
ExcelReader Class:
namespace Automation
{
class ExcelReader
{
public List<TestCaseData> ReadExcelData(string excelFile, string sheetname)
{
string cmdText = "SELECT * FROM [" + sheetname + "$]";
if (!File.Exists(excelFile))
throw new Exception(string.Format("File name: {0}", excelFile), new
FileNotFoundException());
string connectionStr =
string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", excelFile);
var ret = new List<TestCaseData>();
using (var connection = new OleDbConnection(connectionStr))
{
connection.Open();
var command = new OleDbCommand(cmdText, connection);
var reader = command.ExecuteReader();
if (reader == null)
throw new Exception(string.Format("No data return from file, file name:{ 0 }", excelFile));
while (reader.Read())
{
var row = new List<string>();
var feildCnt = reader.FieldCount;
for (var i = 0; i < feildCnt; i++)
row.Add(reader.GetValue(i).ToString());
ret.Add(new TestCaseData(row.ToArray()));
}
}
return ret;
}
}
}Test Class :
namespace Automation
{
[TestFixture]
class Excel_TC_1
{
private readonly string testCaseData;
public static IEnumerable<TestCaseData> BudgetData
{
get
{
List<TestCaseData> testCaseDataList = new ExcelReader().ReadExcelData(@"C:\Safety_dan\AutomationTest\TestData\test.xlsx",
"Sheet1");
if (testCaseDataList != null)
foreach (TestCaseData testCaseData in testCaseDataList)
yield return testCaseData;
}
}
[Test]
[TestCaseSource(typeof(Excel_TC_1), "BudgetData")]
public void TestCase1(string attribbutte1)
{
// Login
CcLoginPageObject ccPageLogin = new CcLoginPageObject();
ClientLoginPageObject clientLoginPage = new ClientLoginPageObject();
ccPageLogin.Login("username", "password");
// Loading client code:
Driver.FindElement(By.Id("ClientsSearch")).SendKeys(testCaseData);
}
}
}Error:
Test Name: TestCase1
Test FullName: Automation.Excel_TC_1.TestCase1
Test Source: C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs : line 32
Test Outcome: Failed
Test Duration: 0:00:00.005
Result StackTrace:
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Automation.ExcelReader.ReadExcelData(String excelFile, String sheetname) in C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs:line 27
at Automation.Excel_TC_1.<get_BudgetData>d__2.MoveNext() in C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs:line 20
at NUnit.Framework.TestCaseSourceAttribute.GetTestCasesFor(IMethodInfo method) in C:\src\nunit\nunit\src\NUnitFramework\framework\Attributes\TestCaseSourceAttribute.cs:line 177
Result Message: System.InvalidOperationException : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.