1

I wrote code selenium web driver c # and I try to access the file excel is not working. It is throwing System.IndexOutOfRange Exception. cannot find column 5.

   public class ExcelLib

    {

  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
           //Set the First Row as Column Name
           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>();

       public static void PopulateInCollection(string fileName)
       {
           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);
               }
           }
       }

       public static 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();

               //var datas = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
               return data.ToString();
           }
           catch (Exception e)
           {
               return null;
           }
       }

    }


public class Datacollection

{

    public int rowNumber { get; set; }
    public string colName { get; set; }
    public string colValue { get; set; }
}

}


_______________________________________________________________________________

        [Test]
        public void ExecuteTest()
        {

            ExcelLib.PopulateInCollection(@"C:\Users\Numg\Desktop\da\data.xlsx");

            //Login to Application
            LoginPageObject pageLogin = new LoginPageObject();
            EAPageObject pageEA = pageLogin.Login(ExcelLib.ReadData(1, "UserName"), ExcelLib.ReadData(1, "Password"));
            //Fill User Details
            pageEA.FillUserForm(ExcelLib.ReadData(1, "Initial"), ExcelLib.ReadData(1, "MiddleName"), ExcelLib.ReadData(1, "FirstName"));
1
1

Can you pls try modifying the following section of the PopulateInCollection code to something like below. The issue is due to the for iterator initialized at 0 but trying to loop until column count. For example, if your Columns.Count is 4, you would want it to run until 4 times, col = 0,1,2,3 hence the following code.

Main Change: Instead of col<=table.Columns.Count in the loop condition, Change it to col< table.Columns.Count.

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);
 }
1
  • when i change to what u wrote it's fill the page 1 but it's not move to page 2 get error message > Test Name: ExecuteTest Test Outcome: Failed Result Message: System.Reflection.TargetInvocationException : Exception has been thrown by the target of an invocation. ----> System.ArgumentNullException : text cannot be null Parameter name: text Result StandardOutput: opened url close the broswer – Daniel Shmayovich Mar 27 '17 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.