Export excel test data to executable jar file(bundle excel within jar) and read it from the jar for java-selenium-cucumber projects (OR) Read excel test data from the eclipse resources (OR) Read excel test data from the location where jar is generated

In this post, I'd like to share some fundamental coding techniques for the following questions. These techniques are useful when working on selenium based QA projects or generally in java based projects. It is pretty handy to play with excel read/write while working in tools, for instance Eclipse but what if the project needs a distribution to lab environments.

Problem statement : 
1) How to read excel test file/content data from eclipse project folder structure ?
2) How to bundle and read the excel test data file/content within the executable jar file ?
3) How to read the excel test data file from the location where jar is generated ?

Google search helped me to achieve the question-1, the latter ones are achieved through a java expert folk(thank you for the inputs and explanation). I'd like to keep it open for the community folks who are beginners with selenium frameworks.

Here we go.!
Maven based eclipse project structure:

Common code in all the 3 scenarios above: 

Read the excel data using getResourceAsSteam 

public static InputStream getResource(String filename) {
        InputStream inputStream = ExcelUtils.class.getResourceAsStream(filename);
        if (null == inputStream) {
            //fail("Resource not found: `" + filename + "`");
            Log.error("Resource not found: `" + filename + "`");
        }
        return inputStream;
    }

1) How to read excel test file/content data from eclipse project folder structure ? 

This is useful to feed the forms with excel test data from the eclipse project.

a) Create a "data" folder in src/main/resources and
             add "Accounts_Test_Data_Automation.xlsx"  file.

b) Below piece of code does the trick.

fileName = "/data/" + fileName;
ExcelFile = getResource(fileName);

2) How to bundle and read the excel test data file/content within the executable jar file ?

This piece of code does the trick of  bundling excel file into jar. Same as in question-1, look at the setExcelfile method.

//This code ships the excel file into the jar
ExcelFile = getResource("/data/"+fileName);

//Instead of above one case use blow direct project path for the excel file to be bundled

//ExelFile = getResource("/data/Accounts_Test_Data_Automation.xlsx");

pom.xml should be included with the maven-assembly-plugin

<plugin>
 <!-- NOTE: We don't need a groupId specification because the group is
   org.apache.maven.plugins ...which is assumed by default.
  -->
 <artifactId>maven-assembly-plugin</artifactId>
 <version>3.1.1</version>
 <configuration>
 <archive>
   <manifest>
    <addClasspath>true</addClasspath>
    <mainClass>com.sadakar.selenium.common.BasePage</mainClass>
   </manifest>
  </archive>
   <descriptorRefs>
  <descriptorRef>jar-with-dependencies</descriptorRef>
   </descriptorRefs>
 </configuration>
 <executions>
  <execution>
  <id>make-assembly</id>
  <phase>package</phase>
  <goals>
   <goal>single</goal>
  </goals>
  </execution>
 </executions>
</plugin>

Use below command while building the Maven project
clean compile assembly:single install

It will generated the jar file in .m2 repository. Extract of jar should contain the excel file within the "data" folder. While running the command use DtestEnv environment variable. By passing this variable from the command prompt, the code checks for the logic(above jar code) written to read the excel path.

String testEnv = System.getProperty("testEnv");

C:\Users\sadakar\.m2\repository\SADAKAR_POC\SADAKAR_POC\0.0.1-SNAPSHOT>java -DtestEnv=local -jar SADAKAR_POC-0.0.1-SNAPSHOT-jar-with-dependencies.jar

3) How to read the excel test data file from the location where jar is generated ?
Every time when you run scenario(s) or run the jar you CAN NOT edit the excel data file that is bundled inside the jar and it become cumbersome to manually update the excel then bundle it.
This may be tedious job if the test data is huge. To overcome this, it is always a good practice to keep the excel file externally where the jar is going to be generated or where the jar is going be deployed, so I file kept in such location can be accessible with the following snippet in the method.

//This piece of code search for the excel file where the jar is generated or deployed
  fileName = "./" + fileName;
  ExcelFile = new FileInputStream(fileName);

Complete picture of these 3 logic(s) are implemented in ExcelUtils.java where the file name is being sent from AddAccount.java with the Sheet name.
To get the Cell data look at readAccountsData method.

ExcelUtils.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

package com.sadakar.excel.utilities; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.InputStream; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.sadakar.resource.utilities.Log; public class ExcelUtils { public static XSSFSheet ExcelWSheet; public static XSSFWorkbook ExcelWBook; public static XSSFCell Cell; public static String currentDir = System.getProperty("user.dir"); // public static final String currentDir = System.getProperty("user.dir"); // This method is to set the File path and to open the Excel file, Pass // Excel Sheet name as Arguments to this method public static InputStream getResource(String filename) { InputStream inputStream = ExcelUtils.class.getResourceAsStream(filename); if (null == inputStream) { //fail("Resource not found: `" + filename + "`"); Log.error("Resource not found: `" + filename + "`"); } return inputStream; } public static void setExcelFile(String fileName, String SheetName) throws Exception { InputStream ExcelFile = null; try { String testEnv = System.getProperty("testEnv"); if (testEnv != null && testEnv.equalsIgnoreCase("local")) { //This piece of code search for the excel file where the jar is generated or deployed fileName = "./" + fileName; ExcelFile = new FileInputStream(fileName); //This code ships the excel file into the jar //ExcelFile = getResource("/data/SMAPI_UI_Automation.xlsx"); } else { fileName = "/data/" + fileName; ExcelFile = getResource(fileName); } ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (FileNotFoundException e) { System.out.println("Excel Reading have problems : " + e); Log.error("Excel Reading have problems : " + e); } } // This method is to read the test data from the Excel cell, in this we are // passing parameters as Row num and Col num public static String getCellData(int RowNum, int ColNum) throws Exception { try { Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); DataFormatter formatter = new DataFormatter(); String CellData = formatter.formatCellValue(Cell); return CellData; } catch (Exception e) { return ""; } } }

AddAccount.java 

(This class extends BasePage class that can be removed if you want to use the same code)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

package com.sadakar.cucumber.stepdefinitions; import org.openqa.selenium.By; import org.openqa.selenium.WebElement; import com.sadakar.excel.utilities.ExcelUtils; import com.sadakar.resource.utilities.Log; import com.sadakar.selenium.common.BasePage; import cucumber.api.java.en.Then; import org.junit.Assert; import java.util.concurrent.TimeUnit; public class AddAccount extends BasePage{ ExcelUtils eu = new ExcelUtils(); public void readAccountsData() throws Exception{ try { Log.debug("Reading Accounts Excel data file"); ExcelUtils.setExcelFile("Accounts_Test_Data_Automation.xlsx", "Accounts"); } catch(Exception e) { System.out.println("\n Unable to read excel file or Sheet:\n"+e); Log.error("\n Unable to read excel file or Sheet:\n"+e); throw e; } try { Log.debug("---------Accounts Test Data------------"); System.out.println("---------Accounts Test Data-------"); Log.debug("AccountID* ="+ExcelUtils.getCellData(1,1)); Log.debug("AccountName* ="+ExcelUtils.getCellData(2,1)); System.out.println(ExcelUtils.getCellData(1,1)+"\n"); System.out.println(ExcelUtils.getCellData(2,1)+"\n"); Log.debug("-----------------------------------------"); System.out.println("---------------------------------"); }catch(Exception e) { Log.error("Unable to display Accounts test data"+e); System.out.println("Unable to display Accounts test data"+e); } } }

I hope this helps, someone in community.!

Cheers.!

- Sadakar Pochampalli