Read and Write Excel File using Apache POI in Selenium WebDriver with Java

File IO has always been a tricky part for me. In this Selenium tutorial, we are going to learn “How to read and write data from Excel File“. We need a third party API to perform these operations as Selenium WebDriver supports web based automation.

Apache provides mostly used library POI API to read or write an excel file. It is an Open Source. It is used for manipulating Microsoft documents like excel, power point, word files, etc. It is a collection of different Java libraries. POI stands for “Poor Obfuscation Implementation”.

Microsoft Excel in Selenium WebDriver is mostly used to fetch and fill data to excel sheet. Now a days most of the companies don’t use Excel sheets for data driving, there are multiple different tools available in market for this. But some companies strictly uses this method. As we have seen, Apache POI API library is used to connect Excel file to test cases.

So today, we are looking for such a interesting Data Driven Testing from Excel:

You can create maven project for this or you can do it by simple project in eclipse also.

If you are doing by Simple eclipse project then first you need to install jar files of POI library. You can download the latest jars from https://poi.apache.org/download.html. Here, binary files zip file you need to download. After downloading you have to extract it in and after that go to your project-> right click on your Project -> Build Path -> Configure Build Path -> here you will see a window. Click on Libraries tab -> Add External Jars-> fetch those all extracted jars from a folder. Then refresh your project and you are good to go with your project.

There are two formats for Excel file as XLS and XLSX. Apache POI library works well with both formats.

For .xls Extention files HSSF implementation works and for .xlsx Extention files XSSF implementation works well. One excel file contains multiple sheets. We can add or delete that sheet. Sheet is a collection of Rows. A row is a collection of cells. First create an excel file like below:

Here, two columns are there — First is Name and the second one is Password. So we are fetching or reading this data by using code below:

Read Data from Excel File:

package testExcel;

import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRead {

public void ReadFile() {
try {
//Create an object of FileInputStream class to read excel file- you give file location on your machine.
FileInputStream fs = new FileInputStream(“D://..//testfile.xlsx”);

//Create object of XSSFWorkbook class
XSSFWorkbook workbook = new XSSFWorkbook(fs);

//Create obejct of XSSFSheet class and read excel — If by index then use getSheetAt() method or if by name use getSheet() method.
XSSFSheet sheet = workbook.getSheetAt(0);

//get row count by getLastRowNum() method.
int rowcount = sheet.getLastRowNum();

for (int k = 0; k <= rowcount; k++) {
XSSFRow row = sheet.getRow(k);
int cellcount = row.getLastCellNum();

for (int j = 0; j < cellcount; j++) {
String temp = row.getCell(j).getStringCellValue();
System.out.print(temp + ” “);
}
System.out.println();

}
workbook.close();
fs.close();
}
catch(Exception e) {
e.printStackTrace();
}

}
public static void main(String[] args) {
ExcelRead obj = new ExcelRead();
obj.ReadFile();
}

}

Output will be like this:

Write Data to Excel File:

For writing the data in Excel file first, we need file location, file name, sheet name or index, and data to write.

package testExcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelWrite

{
//Write a method containing Filepath,Sheetname, Value to fill
public void writeData(String filePath, String fileName, String sheetName, String[] valueToFill) throws IOException {

//Create an object of File to store file path
File src = new File(filePath + “\” + fileName);

//Load the File
FileInputStream inputStream = new FileInputStream(src);

//Create object of workbook
Workbook wb = null;

//To check file format .xlsx or .xls split the file extension
String extension = fileName.substring(fileName.indexOf(“.”));

//Check condition if the file is xlsx file
if (extension.equals(“.xlsx”)) {
//If file is .xlsx then create object of XSSFWorkbook class
wb = new XSSFWorkbook(inputStream);
}

//Check condition if the file is xls file
else if (extension.equals(“.xls”)) {
//If file is .xls then create object of HSSFWorkbook class
wb = new HSSFWorkbook(inputStream);
}

//Read excel sheet by sheet name or you can check it by index as getSheetAt(index) method
Sheet sheet = wb.getSheet(sheetName);

//count no. of rows in excel file
int rowCount = sheet.getLastRowNum() — sheet.getFirstRowNum();

//Get the first row from the sheet
Row row1 = sheet.getRow(0);

//Append new row at last of sheet
Row newRow = sheet.createRow(rowCount + 1);

for (int j = 0; j < row1.getLastCellNum(); j++) {

//Fill data in row
Cell cel = newRow.createCell(j);
cel.setCellValue(valueToFill[j]);
}

//Close input stream
inputStream.close();

//Create an object of FileOutputStream class to write data in excel file
FileOutputStream outputStream = new FileOutputStream(src);

//write data in the excel file
wb.write(outputStream);

//close output stream
outputStream.close();

}

public static void main(String[] args) {
String[] valueToWrite = {
“EmailId”,
“Password”
};
ExcelWrite e1 = new ExcelWrite();
try {
e1.writeData(“D:\MyWorkspace\”, “testfile.xlsx”, “Sheet2”, valueToWrite);
}
catch(IOException e) {
System.out.println(e.getMessage());
}

}

}

You will see the output in your excel file as Emaild Password.

Thanks for reading and give suggestions if any in comment box.