My Excel Utils Code public class ExcelUtils { xl.Application xlApp = null; xl.Workbooks workbooks = null; xl.Workbook workbook = null; Hashtable sheets; public XSSFSheet sheet = null; public string xlFilePath; public ExcelUtils(string xlFilePath) { this.xlFilePath = xlFilePath; } public class Datacollection { public int rowNumber { get; set; } public String colName { get; set; } public string colValue { get; set; } } private static DataTable ExcelToDataTable(String fileName) { //open file and returns as Stream FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read); //Createopenxmlreader via ExcelReaderFactory IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx excelReader.IsFirstRowAsColumnNames = true; //Return as DataSet DataSet result = excelReader.AsDataSet(); //Get all the Tables DataTableCollection table = result.Tables; //Store it in DataTable DataTable resultTable = table["Sheet1"]; //return return resultTable; } //static List<Datacollection> dataCol = new List<Datacollection>(); static List<Datacollection> dataCol; public static void PopulateInCollection(string fileName) { dataCol = new List<Datacollection>(); DataTable table = ExcelToDataTable(fileName); //Iterate through the rows and columns of the Table 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() }; //Add all the details for each row dataCol.Add(dtTable); } } fileName = ""; } public static String ReadData(int rowNumber, string columnName) { try { // DataFormatter formatter = new DataFormatter(); //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(); //var datas = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue; return data.ToString(); } catch (Exception e) { throw e; } { return null; } } /////////and calling this Method in Test Case public class Login { [Test] //[TestCaseSource(typeof(BaseTest), "BrowserRunWith")] public void ValidCredntialTest_01() { var login = new PageObject.Login(driver); login.ClickLogin(); TestContext.Out.WriteLine("+++++Clarity click has happened++++++++"); var LoginPagecredential = login.ClickLogin(); //Here Url Stored in App.config and Calling ExcelUtils.PopulateInCollection(@ConfigurationManager.AppSettings["ExcelDocPath"].ToString() + @ConfigurationManager.AppSettings["LoginFile"].ToString()); // here im Caliing Username and Pass word from Excel LoginPagecredential.LoginToApplication(ExcelUtils.ReadData(1, "UserName"), ExcelUtils.ReadData(1, "Password")); test.Log(Status.Info, "Login With valid Credentials"); TestContext.Out.WriteLine("*****Fetching UserName and Password from Excel and clicked to Login*******"); //Caliing Inventory Page For Testing driver.Close(); test.Log(Status.Info, "ValidCredntialTest_01 TestCase Passed"); } catch(Exception ex) { throw ex; } } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)