Add .xls File Reading Utility in Framework

In the previous post, we have added the required excel data sheet. Here, we will discuss how can we read data from excel sheet using apache POI.

For reading the excel data we should require the apache POI dependencies in our pom.xml file. Please check the below dependencies in pom.xml file:

Follow the below steps to read the data:

• Create a package “com.demo.pom.util” inside src/main/java.
• Download TestCaseMetaData.java & DataUtil.java files from here & copy those two files into com.demo.pom.util package.

After adding the files the project should look like below:

• Now run the DataUtil.java file. To run right click on the file > Run As > Java Application.
• After successful run, you should check whether it’s displaying the test case names & test data for each test case as shown below:

If you are seeing the above message in console, means the utility is working properly & you can able to use the test data using @DataProvider. Will discuss about @DataProvider in later posts.

By using the above java files, we are ready to get data from the excel sheet. Let’s go through the files.  


The logic behind reading data from excel sheet:

TestCaseMetaData.java

TestCaseMetaData class file is mainly used to get the Test Case Name & Runmode from the TestCases sheet & also to set the test case data. Here, we are using getter setter methods to get the data.

DataUtil.java

DataUtil class file is the main class to read the data. Please check the below methods used in this class & their description:

·         setupTestCaseSuite:In This method we will pass the excel data sheet location & from the data sheet it will read all the test case names from the sheet1(TestCases) & will put in a map known as testCaseMetaDataMap.

·         getCellDataAsString: This method will take a parameter know as Cell & reads the cell data as string.

·         setupTestCaseData: This method will read data from the sheet2(Data). This method will read the 1^st cell value & compare with the test case name. If the test case name matches, then it will add the header values in key & all the cell values (till it gets an blank cell) in value of a hashmap.

·         getTestData: This method takes a parameter known as test case name (which is same as in TestCases sheet) & returns the test case data in a two-dimensional object array.

·         isRunnable: This method takes the parameter known as test case name (which is same as in TestCases sheet) & checks it’s Runmode. If the Runmode is set to Y then it returns true else it returns false.

<<<Prev       Next>>>