How to Read Data from Excel in Selenium C#

Read test data from any external source in selenium is very important when we are working with framework. Test data can be store in any format like excel, csv, text etc.

We have to use approach as per which language we are using in selenium. If you are using C# then you have to use below reference in your code.

using Excel = Microsoft.Office.Interop.Excel;

I have created below function for read data from Excel file. You have to provide Excel file path and sheet number of sheet from which you want to read data.

public String[,] GetDataFromExcelFile(String vXlpath, int vSheetNum)
        {

            Excel.Application vXlApp;
            Excel.Workbook vXlWorkBook;
            Excel.Worksheet vXlWorkSheet;
            Excel.Range vXlrange;

            vXlApp = new Excel.Application();            
            vXlWorkBook = vXlApp.Workbooks.Open(@vXlpath);                       
            vXlWorkSheet = vXlWorkBook.Sheets[vSheetNum];

            vXlrange = vXlWorkSheet.UsedRange;

            int vRowCnt = vXlrange.Rows.Count;
            int vColCnt = vXlrange.Columns.Count;

            String[,] vXlData = new String[vRowCnt, vColCnt];

            for (int i = 1; i <= vRowCnt; i++)
            {
                for (int j = 1; j <= vColCnt; j++)
                {
                    vXlData[i - 1, j - 1] = Convert.ToString(vXlrange.Cells[i, j].Value2);
                }
            }

            vXlWorkBook.Close(true, null, null);
            vXlApp.Quit();

            Marshal.ReleaseComObject(vXlWorkSheet);
            Marshal.ReleaseComObject(vXlWorkBook);
            Marshal.ReleaseComObject(vXlApp);

            return vXlData;
        }

Above function will give you excel sheet data in form of two dimensional string array.

Please add comment if you have any question.