Hello Readers!! We are back with another exciting topic that is highly beneficial for projects involving data-driven testing, So we will begin our journey by providing a quick explanation of some of the phrases we will regularly use in our blog posts as well as an attempt to explain why we need this and how we’ll apply this approach to our existing Selenium projects.
What is Apache POI?
Apache POI, often known as POI (Poor Obfuscation Implementation), is a Java library API that enables us to read, write, and modify many Microsoft files, including Excel sheets, PowerPoint presentations, and Word documents.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
There are two main classes used for managing Excel Workbooks :
- HSSFWorkbook- These class methods are used to read/write data to Microsoft Excel files in .xls format.
- XSSFWorkbook- These class methods are used to read-write data to Microsoft Excel in .xls or .xlsx format.
You probably already know that we will use Apache POI in an Excel sheet to do two specific tasks/operations.
- Read Data
- Write Data
Read data from Excel file
So, in the above sheet, I’m utilising the provided data to login into the website with a fresh credential each time.
There are some common steps to follow to read the data:
Step 1: Declare the variable
As you can see we declared the XSSFSheet and XSSFWorkbook variables globally because we use .xlsv format for Excel files.
Step 2: Obtain the Excel workbook based on its location on the computer.
You may build an object in the workbook by referencing the FileInputStream object, which points to the Excel file.
public ReadExcelFile(String excelSheetPath){
try{
File src = new File(excelPath);
FileInputStream fs = new FileInputStream(src);
wb = new XSSFWorkbook(fs);
}
catch (Exception e){
System.out.println(e.getMessage());
}
}
Step 3: After we’ve created the workbook, the next step is to create a sheet in the workbook.
We need to access the sheet’s row and cell once it is created. To do this, we may use the getRow (int rowIndex) and getCell (int index) methods of the sheet object.
public String getData(int sheetNumber, int rows, int column) throws InterruptedException {
sheet = wb.getSheetAt(sheetNumber);
String data = sheet.getRow(rows).getCell(column).getStringCellValue();
return data;
}
After constructing an object in the main method, utilise this class function as follows:
ReadDataConfig excel = new ReadDataConfig("TestData/Untitled spreadsheet.xlsx");
You may use the getData() method after declaring the object.
excel.getData(0, 0, 0));
Write data from an Excel file
Writing data in Excel files may be equally as important as reading data since it enables you to save test results in Excel sheets. Along with putting the data into Excel, we also offer a solution to a challenge that testers face while conducting testing on this particular topic.
The primary issue is where we write and how the pointer selects the next empty cell to write the data, which is different from reading the data from the sheet, which we can control by giving some input.
we have similar steps here also like reading data, like
- Declare the variable
- Obtain the Excel workbook based on its location on the computer.
- After we’ve created the workbook, the next step is to create a sheet in the workbook.
public void writeExcelFile(String sheetName, String cellValue, int startRow, int col) throws IOException {
String excelPath = "src/main/resources/Book1.xlsx";
File file = new File(excelPath);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheet(sheetName);
Until then, the process remains the same. Now I’ll show you a short bit of code that handles the aforementioned problem and writes the data in an empty cell adjacent to the previous one.
int row = startRow;
if (sheet.getRow(row) == null) {
sheet.createRow(row);
}
while (sheet.getRow(row) != null && sheet.getRow(row).getCell(col) != null && !sheet.getRow(row).getCell(col).toString().isEmpty()) {
row++;
if (sheet.getRow(row) == null) {
sheet.createRow(row);
}
}
The preceding code just detects the previously full cell and changes the pointer to the corresponding one; now we must write things in the empty cell to which we now have a pointer by using createRow(), which creates new rows, and createCell(), which creates the cells in these rows and the other method setCellValue Moreover, you can create a new row in an Excel sheet, Basically, we will set the value with the help of this method
sheet.getRow(row).createCell(col).setCellValue(cellValue);
We still need FileOutputStream to write the stuff into the sheet now that we’ve constructed the row and column in the provided file only.
FileOutputStream fos = new FileOutputStream(new File(excelPath));
wb.write(fos);
In the end, we just need to close both of the objects we declared.
wb.close();
fos.close();
This class function will be used by declaring the object in the main call. as seen below
WriteExcelFile writeExcel = new WriteExcelFile();
writeExcel.writeExcelFile("Sheet1", randomEmailId, 0, 1);
Conclusion
In this brief blog post, we learned how we read and write data into an Excel sheet with the help of Apache POI. In my upcoming posts, I’ll go into more detail about it. I’ll also release a tech hub that contains all of my code along with the selenium test.
Till then, Happy learning, and happy sharing!
