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”);
Poi For Xlsx Read In Selenium For Mac Os
Let’s see how to Read excel files using Apache POI in Selenium WebDriver: Step 1– Download Apache POI jar file Step 2– Add download jar files to your project build path. Select Project and Right click on the Project – Go to ‘Build path’ – Go to ‘Configure build path’ – Click on ‘lib’ section – Add external jar. To Read or Write From Excel,Apache Community provides a very widely used library POI. This library have features to read and write both XLS and XLSX file format of Excel. HSSF implementation is provided by POI library to read 'XLS' Files and XSSF implementation of POI library to read 'XLSX' File,Lets go to Deep Die.
Poi For Xlsx Read In Selenium For Mac 64-bit
- Handling excel files Using Apache POI in Selenium WebDriver. As we all know Selenium supports only Web browser automation. We need to get the help of third party API like Apache POI to handle (read and write) excel files using Selenium WebDriver.
- 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.
- Reading Specific value from excel using Apache POI in Selenium WebDriver Ankur Jain 0 Comment AutomationFramework ExcelFile Utility As we all are aware of reading and writing data from excel file is one of the most important aspects that we need to take care while designing our automation testing framework especially while using a data-driven.