Microsoft Office Interop Excel Reader for Selenium Data Driven Testing C#

Excel Helper for Selenium Data Driven Testing using C#

Easily plug this helper class to your framework and start consuming it

Note: This will not support .Net Core Framework and its only for .Net Frameworks, so if in case you are having trouble in reading the values check your .Net Framework.

Please install Microsoft Office Interop Excel from Manage Nuget before starting

Use the below name space as part of class

using Excel = Microsoft.Office.Interop.Excel;

public void ExcelHelper(string fileName)

        {

            object misval = System.Reflection.Missing.Value;

 

            Excel.Application excelApp = new Excel.Application();

            if (excelApp != null)

            {

                Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileName, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval);

                Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];

 

                Excel.Range excelRange = excelWorksheet.UsedRange;

                int rowCount = excelRange.Rows.Count;

                int colCount = excelRange.Columns.Count;

 

                for (int i = 1; i <= rowCount; i++)

                {

                    for (int j = 1; j <= colCount; j++)

                    {

                        Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);

                        string cellValue = range.Value.ToString();

                        Console.WriteLine(cellValue);

 

                        //do anything

                    }

                }

 

                excelWorkbook.Close();

                excelApp.Quit();

            }

        }