But, Apache POI is one of the most used libraries, which provides various classes and methods to read/write data from various formats of Excel files(xls, xlsx etc). Subsequently, in this article, we will understand the details of Apache POI and how we can use the same to read/write data from Excel files, by covering the details under the. We can read numeric data from Excel using apache poi for Selenium webdriver. Our test data contains numeric data, string data, and sometimes combination as well which is totally dynamic in nature. So we need to create such library which will take care of dynamic data.
We all know the importance of a file and how frequently we use it, for example, either we create a new file, update or delete something from it. Selenium helps to automate file manipulation.
So, In this blog, we will learn how to install the poi jar file, which jar file is required for that and how to perform the read operation on excel with the help of JAVA IO package and APACHE POI library.
POI library is enough to read write both XLS and XLSX file.
Note: For .XLS file read-write, we can use .JXL jar file but it won’t support to .xlsx file.
Let’s begin the journey: –
Step 1. If you haven’t used eclipse then Install the Eclipse latest version.
Step 2. If you haven’t added WebDriver then download the webdriver jar file and add to library.
Step 3. If you are using Maven then use the following dependencies.
<!– https://mvnrepository.com/artifact/org.apache.poi/poi –>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
Or else you can directly download the latest POI jar file from https://poi.apache.org/download.html
Step 4. Once the jar file is downloaded then unzip the file and add library file into your project.
Right click on your project -> Build Path -> Configure Build Path-> Library -> Add External Jar-> ok
Below are Java interfaces and classes that we will use for read/write xls and xlsx file in POI
XSSFWorkbook: – Is a class representation of XLSX file.
HSSFWrokbook: – Is a class representation of XLS file.
XSSFSheet:- XSSFSheet classes implement this interface represent XLSX file’s sheet.
HSSFSheet: – HSSFSheet classes implement this interface XLS file’s sheet.
XSSFRow:- XSSFSheet classes implement this interface represent a row of XLSX file.
HSSFRow: – HSSFSheet classes implement this interface represent a row of XLS file.
XSSFCell:- XSSFSheet classes implement this interface represent a cell in a row of XLSX file.
HSSFCell: – HSSFSheet classes implement this interface represent a cell in a row of XLS file.
Write Data in an Excel sheet
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
publicclass WriteInExcel {
publicstaticvoid main(String args[])throws IOException{
String ctVal= “Status”;
// File Location
File src = new File(“D://Dazeworks//Selenium Projects//Credential.xlsx”);
// Open File using FileInputStream class
FileInputStream fis = new FileInputStream(src);
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(fis);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Entering a value in Row[0] and Cell[4]. Here cell is nothing but Column number
sheet.getRow(0).createCell(4).setCellValue(ctVal);
// If user want to enter a value as per choice row then create row
XSSFRow r = sheet.createRow(14);
//Create a cell for the value where you want to enter.
XSSFCell c = r.createCell(10);
//Entering the Value for particular cell
c.setCellValue(“How’s Josh????”);
//Using FileOutputStream to specify output file name
FileOutputStream fos = new FileOutputStream(src);
// Writing the values in workbook
workbook.write(fos);
//File close
fos.close();
System.out.println(“Value inserted successfully”);
Here we learn how to read data from the excel using JAVA and Apache POI in selenium wedriver . Reading data from excel is an important part of the selenium to make the Automation keyword framework , Hybrid Frame work and Data driven framework to read the data and pass the values in functions. To read the data from excel we need the Input/Output functions of file stream.
Now lets start with some of the key points to read the data from excel
Poi For Xlsx Read In Selenium For Mac Osx
1 ) Apache POI library is required –Complete guide to add the Apache POI library to Eclipse IDE .
Lets have a brief overview of the Apache POI library : Apache POI library contains the interfaces, classes and methods to read,write, update and create the MS-office files. As a part of MS-Excel Apache POI supports both excel format “XSLX” and “XLS”.
What is XLSX format and XLS ?
XLS format : All the Ms-excel files that are below the version of 97 supports “XLS ” version . These files ends with the extension “XLS “.
XLSX format : All the MS- Excel files that are version of 97 and above supports “XLSX” version.These files ends with the extension “XLSX “.
| Interface | XLSX Class | XLS Class |
|---|---|---|
| Workbook represents workbook | XSSFWorkbook | HSSFWorkbook |
| Sheet represents Sheet | XSSFSheet | HSSFSheet |
| Row represents Row | XSSFRow | HSSFRow |
| Cell represents Cell | XSSFCell | HSSFCell |
Poi For Xlsx Read In Selenium For Mac 64-bit
Now we we will learn how to read the data from excel using JAVA in selenium. Reading data from excel is the most important part while automation as Automation frameworks read the input data from excel and write the output results in excel file. Lets take the below sample excel sheet we will read in selenium now.
File Name : Read_Write_Excel_Padhle.xlsx
Sheet Name : Padhle_ReadExcel
Pseudo code for reading excel data in JAVA selenium
1) Create the File Class object to get the Excel file.
2) Create the object of FileInputStream Class to read the file . Pass the File as the parameter in FileInputStream Class.
3) Create the WorkBook object depends on the file extension if it is “.XLSX” file create XSSFWorkbook object, if it is “.XLS” create HSSFWorkbook object. Remember FileInputStream class object is passed as the parameter in workbook object.
4) Now create the Sheet class object and get sheet with the help of workbook object via functions getSheet(sheetname) or getSheetAt(int).
5) Find the number of rows via getLastRowNum() – getFirstRowNum().
6 ) Create row class object .
7 ) Read the cell values .
Java Code : How to read excel File data
Console Output
Hope you are now clear how to read the data from excel , If you have any doubt please mention in the below comments.