selenium web driver c # and I try to access the file excel is not working.get error > System.IndexOutOfRange Exception. cannot find column 5

Question

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"));

Answer 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);
 }