Click here to Skip to main content
15,880,364 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 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
            fileName = "";
        public static String ReadData(int rowNumber, string columnName)
               // 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
        //[TestCaseSource(typeof(BaseTest), "BrowserRunWith")]
        public void ValidCredntialTest_01()
               var login = new PageObject.Login(driver);
                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
                test.Log(Status.Info, "ValidCredntialTest_01 TestCase Passed");
            catch(Exception ex)
                throw ex;

What I have tried:

I tried But Getting Error and i could not find Solution
im My Code Sheet1--- is Static so here i want ,, Pass Sheet name And Fetch vaues
Richard MacCutchan 10-Mar-21 8:04am    
Well since we cannot guess what error you are getting, it is difficult to make a suggestion.
Smita Ast 11-Mar-21 6:06am    
No Error , wants add SheetName parameter and based on SheetName need to fetch data

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900