Read & Update Excel file in Selenium

To load and use test data in automation script is a key element in  Automation script execution.  Today we are going to discuss about loading excel files in selenium. There are number of API  are there to support Excel like jExcelAPi, Apache POI.   I found using JDBC connection is the most flexible and method rich option.

How to load excel file in memory:

by giving path of excel file we can load n number of diff excel files to memory for further use:

public void loadExcel(String path) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
this.con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=" + path + ";DriverID=790;READONLY=false;");
} catch (Exception e) {
e.printStackTrace();

}
}


How to read full Row in Excel:

You need to give two parameters sheet name and row id. So you can retrieve any row id from given sheet.

public Map<String, String> getRowbyID(String sheet, String id) throws SQLException {
Map<String, String> map = new HashMap<>();
String strQuery = "SELECT * FROM [" + sheet + "$] WHERE [ID]= '" + id + "'";
try {
st = con.createStatement();
resultSet = st.executeQuery(strQuery);
ResultSetMetaData rmd = resultSet.getMetaData();
while (resultSet.next()) {
int columntCount = rmd.getColumnCount();
for (int i = 1; i <= columntCount; i++) {
map.put(rmd.getColumnName(i), resultSet.getString(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return map;
}

This will return Map having two values one for column and second for value. You can retrieve row value by giving column name.

How to update Excel sheet:

You can write any costume query in the following method and update the appropriate value. Like the following example update the status to yes after given module execution.


public void updateExecutionConfig(String strModuleName, String strTestCategory) throws SQLException {
String strQuery = "update [Sheetname$] set Executed = 'Yes' where ModuleName = '" + strModuleName + "' and TestCategory = '" + strTestCategory + "'";
st = con.createStatement();
int updateRow = st.executeUpdate(strQuery);
System.out.println("updateRow = " + updateRow);
}

These are some examples you can write any costume method appropriate to your selenium framework need.