File IO is an important part of any software process. We always create a file, open it and update files or sometimes delete the files on our computers. Same we do in the Selenium Automation case. To do this we need a process to manipulate files with Selenium. For us, Java provides different classes for File Manipulation with Selenium. In this article, we will learn how to read and write on an Excel file with the help of the Java IO package and Apache POI library.
Exporting Excel
How to handle excel file using POI
Apache provides a famous library POI to read or write an Excel. This library is used to read and write both XLS and XLSX file format of Excel. An HSSF implementation is provided by the POI library to read XLS files.
What is POI?
POI is a JAR file that helps us to create a coding environment in the IDE to read the data from the excel sheet and writing to the excel sheet.
How to Set up POI JAR File in my project?
To set up POI Jar File in the project you just need to create the dependency in the pom.xml file. Below is the dependency definition:
Maven:
You can simply download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file under Binary Distribution
Unzip the downloaded file and add all below jars to the classpath of your project.
ooxml-lib folder
lib folder
Interfaces inside the POI
- Workbook– This interface is implemented to instantiate different excel file representation (xls or xlsx).
- Sheet– This interface is implemented to read the sheet inside the workbook of the different types of excel sheet (xls or xlsx).
- Row– This interface is used to identify the row inside the sheet of the different types of excel sheet (xls or xlsx).
- Cell– This interface is implemented to identify the corresponding cell of the given row of the different types of excel sheet (xls or xlsx).
Classes inside the POI
- XSSFWorkbook- This class representation will implement Workbook interfaces for the XLSX file.
- HSSFWorkbook- This class representation will implement the Workbook interface for the XLS file.
- XSSFSheet- This class representing a Sheet interface for the XLSX file.
- HSSFSheet- This class representing a Sheet interface for the XLS file.
- XSSFRow- This class representing a Row interface for the XLSX file.
- HSSFRow- This class representing a Row interface for the XLS file.
- XSSFCell- This class representing a Cell interface for the XLSX file.
- Sheet: XSSFSheet and HSSFSheet classes implement this interface.
- Row: XSSFRow and HSSFRow classes implement this interface.
- Cell: XSSFCell and HSSFCell classes implement this interface.
Implementation of the POI Jar File to read test data from the Excel Sheet?
Consider below Excel file
Read data from Excel file
Step 1: Create a Package excelExportStep 2: Create a Class ReadExcelSheetData
package excelExport;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelSheetData {
public static void main(String[] args) throws IOException {
String path = "C:\\Users\\Desktop\\ExportExcel.xlsx";
FileInputStream fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
System.out.println("Last row- "+lastRow);
for(int i=0; i<=lastRow; i++){
Row row = sheet.getRow(i);
int lastCell = row.getLastCellNum();
for(int j=0; j<lastCell; j++){
Cell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.println(value);
}
System.out.println();
}
}
}
Here is the console output:
Write data on Excel file
Advantages of writing data to the Excel Sheet
- It is the ease in handover test data to the client
- Have a checklist of pass and failure status against each scenario mentioned in the excel sheet
- Ease of maintenance
The Hierarchical flow of POI to write data to the Excel Sheet.
Below are the steps to the instantiation and calling of the methods and interfaces
- FileInputStream
- Workbook -> XSSFWorkbook or HSSFWorkbook
- Sheet
- Row
- Cell
- FileOutputStream
How to implement POI JAR File to write data to the Excel Sheet
Below code will implement all those Interfaces and classes to write data to the sheet. Below sample code create a cell and write cell heading at 0th row and 2nd column.
public static void writeDataToExcel() throws IOException {
String path = "C:\\Users\\Desktop\\ExportExcel.xlsx ";
FileInputStream fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.createCell(2);
cell.setCellValue("Age");
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
}
In the above code, we are taking a file by defining its path through FileInputStream and we created the instance of Workbook and XSSFWorkbook. We use createCell() method first to assign the cell and then we write data by using setCellValue() method.
Write data to Excel file
Step 1: Create a Package excelExportStep 2: Create a Class WriteExcelData
package excelExport;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelData {
public static void main(String[] args) throws IOException {
String path = "C:\\Users\\SidduRohan\\Desktop\\ExportExcel.xlsx";
FileInputStream fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
for(int i=1; i<=lastRow; i++){
Row row = sheet.getRow(i);
Cell cell = row.createCell(2);
cell.setCellValue("32");
}
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
}
}
After running the above program, the output looks like
Conclusion:
- To read excel file by Java IO operation we need to use Apache POI Jar.
- XLSX and XLS files are two kinds of a workbook in Excel file
- POI has different Interfaces like Workbook, Sheet, Row, Cell.
These interfaces are implemented by corresponding XLS (HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell) and XLSX (XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell) file manipulation classes.
