How to Read data from excel in selenium C#

During the automation some time we need to read data from excel using selenium c#. There are multiple way to read data from excel in selenium c#. Let’s see one by one. In the Example 1 will see how to read data from excel by using key. In example 2 will see how to read data from a cell by using the column and row matrix.

Read data from excel in selenium using c# by key.

Example 1: Suppose we have a excel file with name “TestData.xlxs”.

Create a connection with excel file in selenium C#.

publicstaticstring TestDataFileConnection()
var path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
path = path.Substring(6) + @"\Data\TestData.xlsx";
var con = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {0}; Extended Properties=Excel 12.0;", path);
return con;


Read data from excel in selenium C#

Read data from excel in selenium C#

publicstatic UserData GetTestData(string keyName)
using (var connection = new OleDbConnection(TestDataFileConnection()))
var query = string.Format("select * from [DataSet$] where key='{0}'", keyName);
var value = connection.Query<UserData>(query).FirstOrDefault();
return value;

Here the “DataSet” is a sheetname of your excel.

Method calling and output

var userData = ExcelDataAccess.GetTestData("Admin");
String s1 = userData.Username;

Output: user1


Example 2

Read Data from excel in selenium c# using from a cell

public static String getData(int row, int col)
    String value;

    excel.Application x1app = new excel.Application();

    excel.Workbook x1Wb = x1app.Workbooks.Open(@ConfigurationManager.AppSettings["TestDataPath"]);
    //excel.Workbook x1Wb = x1app.Workbooks.Open(@"D:\TestDataFile.xlsx");

    excel.Worksheet x1Ws = x1Wb.Sheets[1];

    excel.Range x1range = x1Ws.UsedRange;

    // value = x1range.Cells[row][col].value2;

    value = Convert.ToString(x1range.Cells[row][col].value2);
    return value;

Method calling and output

String s1 = getData(1,1);

Output: User1