Read data from excel in soapUI like a database

Treating Excel as a database is one of the most popular method used in automation testing. Web services testing is also not much different than that. There might be instances that we might need to treat an excel sheet as a database and read data from this excel sheet in soapUI for data validation or input data or for configuration purposes. In this example, instead of relying on groovy, we will be using a bit of java code to connect to an excel sheet and read data from this excel sheet. In order to connect to an excel as a database, we need a driver. We will be using "sun.jdbc.odbc.JdbcOdbcDriver" in our example to connect to an excel fom soapUI. Lets say we have a file named "soapUI-Excel-Example .xls" and it has a work sheet named "ExcelSheetName". Now, create a coulmn named "Name" in the sheet and enter some data in the column. Its time for us to look into the code now. Lets see the below example on how to connect to excel from soapUI.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


def soapUI_Excel = "C:\\soapUI-Excel\\soapUI-Excel-Example.xls"
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
soapUIDataConnection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + soapUI_Excel + ";ReadOnly=0", "", "");
Statement stmt = ((Connection) soapUIDataConnection).createStatement();
recordSet = stmt.executeQuery("Select * from [ExcelSheetName\$] where Name like 'soapUI%'"); 


while (recordSet.next())
{


log.info(recordSet.getString("Name"))

}


The above example will select all the Names like "soapUI" and print it to soapUI log.