0

I was faced with the below problem while trying to run a data driving testing and have tried to resolve it but nothing seem to be working for me. Please could you take a look at my code and tell me where i went wrong and what to do in order to correct it. thank you.

[Test]
    public void ExecuteTest()
    {
        //Import from excel
        ExcelLib.PopulateInCollection(@"C:\Users\CUBA\Desktop\Book1.xlsx");
        //Login to app
        EALoginPage loginPage = new EALoginPage();
        EAFormPage formPage = loginPage.Login(ExcelLib.ReadData(1, "UserName"), ExcelLib.ReadData(1, "Password"));
        PropertiesCollection.Driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(10);
        formPage.FillUserForm(ExcelLib.ReadData(1, "Initial"), ExcelLib.ReadData(1, "MiddleName"), ExcelLib.ReadData(1, "FirstName"));

    }

Below is my excelLib.cs class

    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;
        var result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = 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; }
    }

}

Any help will be appreciated. Thanks.

  • Which line throws an exception? – Alexey R. Aug 28 '18 at 17:10
  • when i debug my code it takes me to the "PopulateInCollection" method and then point at "DataCollection dtTable = new DataCollection(); – Ugochukwu Anajemba Aug 28 '18 at 17:28
  • It would help if you edited your post and added the full stack dump of the error. – Kate Paulk Aug 28 '18 at 19:06
0

Your issue seems to be here

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

You should carefully check the indices because for example this loop

for (int col = 0; col <= table.Columns.Count; col++)

will be executed one time more than your column count. Assume you have 2 columns in a table. Your loop start from 0, then 1 then finally 2 (because 2 <= 2) which will compose totally 3 loop cycles. I believe you will get out-of-range exception here.

Read more about fetching rows here. Below is the example from MSDN doc from the latter link:

private void PrintRows(DataTable table)
{
    // Print the CompanyName column for every row using the index.
    for(int i = 0; i < table.Rows.Count; i++)
    {
        Console.WriteLine(table.Rows[i]["CompanyName"]);
    }
}
  • Thanks Alex R. I seem to have found a solution to my problem. for(int col = 0; col<=table.columns.count -1; col++) solves the problem. Thanks for your contribution. – Ugochukwu Anajemba Aug 28 '18 at 19:05
  • 1
    @UgochukwuAnajemba You just can use for(int col = 0; col<table.columns.count; col++) . Avoid excessive calculation like table.columns.count-1. Your approach makes the code less readable. – Alexey R. Aug 28 '18 at 19:06
  • The right way would have been the posted code but I don’t know why it seem not to be responding without the complex approach. – Ugochukwu Anajemba Aug 28 '18 at 19:08
  • 1
    I will implement it and get back to you. Thanks Alex. – Ugochukwu Anajemba Aug 28 '18 at 19:11
  • 1
    Please mark this answer as accepted. – Bouke Sep 11 '18 at 15:05

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.