How to Read/Write Data from Excel File: Selenium POI

Details

File IO is a critical part of any software process. We frequently create a file, open it & update something or delete it in our Computers. Same is the case with Selenium Automation. We need a process to manipulate files with Selenium.

Java provides us different classes for File Manipulation with Selenium. In this tutorial, we are going to learn how can we read and write on Excel file with the help of Java IO package and Apache POI library.

Apache POI in Selenium

The Apache POI in Selenium is a widely used API for selenium data driven testing. It is a POI library written in Java that gives users an API for manipulating Microsoft documents like .xls and .xlsx. Users can easily create, modify and read/write into excel files. POI stands for “Poor Obfuscation Implementation.”

Exporting Excel

How to handle excel file using POI (Maven POM Dependency)

How to export Excel File in Selenium Webdriver using Aapache POI

To Read and Write Excel file in Java, Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.

To read XLS files, an HSSF implementation is provided by POI library.

To read XLSX, XSSF implementation of POI library will be the choice. Let's study these implementations in detail.

If you are using Maven in your project, the Maven dependency will be

How to export Excel File in Selenium Webdriver using Aapache POI 

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>

Or you can simply download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file

How to download Apache POI

When you download the zip file for this jar, you need to unzip it and add these all jars to the class path of your project.

How to download Apache POI

Classes and Interfaces in POI:

Classes and Interfaces in Apache POI
Classes and Interfaces in Apache POI

Following is a list of different Java Interfaces and classes in POI for reading XLS and XLSX file-

Read/Write operation-

For our example, we will consider below given Excel file format

Read/Write Data from Excel File in Selenium Webdriver

Read data from Excel file

Complete Example: Here we are trying to read data from Excel in Selenium: 

package excelExportAndFileIO;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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 ReadgtupapersExcelFile {

    public void readExcel(String filePath,String fileName,String sheetName) throws IOException{

    //Create an object of File class to open xlsx file

    File file =    new File(filePath+"\\"+fileName);

    //Create an object of FileInputStream class to read excel file

    FileInputStream inputStream = new FileInputStream(file);

    Workbook gtupapersWorkbook = null;

    //Find the file extension by splitting file name in substring  and getting only extension name

    String fileExtensionName = fileName.substring(fileName.indexOf("."));

    //Check condition if the file is xlsx file

    if(fileExtensionName.equals(".xlsx")){

    //If it is xlsx file then create object of XSSFWorkbook class

    gtupapersWorkbook = new XSSFWorkbook(inputStream);

    }

    //Check condition if the file is xls file

    else if(fileExtensionName.equals(".xls")){

        //If it is xls file then create object of HSSFWorkbook class

        gtupapersWorkbook = new HSSFWorkbook(inputStream);

    }

    //Read sheet inside the workbook by its name

    Sheet gtupapersSheet = gtupapersWorkbook.getSheet(sheetName);

    //Find number of rows in excel file

    int rowCount = gtupapersSheet.getLastRowNum()-gtupapersSheet.getFirstRowNum();

    //Create a loop over all the rows of excel file to read it

    for (int i = 0; i < rowCount+1; i++) {

        Row row = gtupapersSheet.getRow(i);

        //Create a loop to print cell values in a row

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

            //Print Excel data in console

            System.out.print(row.getCell(j).getStringCellValue()+"|| ");

        }

        System.out.println();
    } 

    }  

    //Main function is calling readExcel function to read data from excel file

    public static void main(String...strings) throws IOException{

    //Create an object of ReadgtupapersExcelFile class

    ReadgtupapersExcelFile objExcelFile = new ReadgtupapersExcelFile();

    //Prepare the path of excel file

    String filePath = System.getProperty("user.dir")+"\\src\\excelExportAndFileIO";

    //Call read file method of the class to read data

    objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelgtupapersDemo");

    }

}

Note: We are not using the Testng framework here. Run the class as Java Application using function read excel in Selenium as shown in above example.

Read/Write Data from Excel File in Selenium Webdriver

Write data on Excel file

Complete Example: Here we are trying to write data from Excel file by adding new row in Excel file 

package excelExportAndFileIO;

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 WritegtupapersExcelFile {

    public void writeExcel(String filePath,String fileName,String sheetName,String[] dataToWrite) throws IOException{

        //Create an object of File class to open xlsx file

        File file =    new File(filePath+"\\"+fileName);

        //Create an object of FileInputStream class to read excel file

        FileInputStream inputStream = new FileInputStream(file);

        Workbook gtupapersWorkbook = null;

        //Find the file extension by splitting  file name in substring and getting only extension name

        String fileExtensionName = fileName.substring(fileName.indexOf("."));

        //Check condition if the file is xlsx file

        if(fileExtensionName.equals(".xlsx")){

        //If it is xlsx file then create object of XSSFWorkbook class

        gtupapersWorkbook = new XSSFWorkbook(inputStream);

        }

        //Check condition if the file is xls file

        else if(fileExtensionName.equals(".xls")){

            //If it is xls file then create object of XSSFWorkbook class

            gtupapersWorkbook = new HSSFWorkbook(inputStream);

        }    

    //Read excel sheet by sheet name    

    Sheet sheet = gtupapersWorkbook.getSheet(sheetName);

    //Get the current count of rows in excel file

    int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();

    //Get the first row from the sheet

    Row row = sheet.getRow(0);

    //Create a new row and append it at last of sheet

    Row newRow = sheet.createRow(rowCount+1);

    //Create a loop over the cell of newly created Row

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

        //Fill data in row

        Cell cell = newRow.createCell(j);

        cell.setCellValue(dataToWrite[j]);

    }

    //Close input stream

    inputStream.close();

    //Create an object of FileOutputStream class to create write data in excel file

    FileOutputStream outputStream = new FileOutputStream(file);

    //write data in the excel file

    gtupapersWorkbook.write(outputStream);

    //close output stream

    outputStream.close();
	
    }

    public static void main(String...strings) throws IOException{

        //Create an array with the data in the same order in which you expect to be filled in excel file

        String[] valueToWrite = {"Mr. E","Noida"};

        //Create an object of current class

        WritegtupapersExcelFile objExcelFile = new WritegtupapersExcelFile();

        //Write the file using file name, sheet name and the data to be filled

        objExcelFile.writeExcel(System.getProperty("user.dir")+"\\src\\excelExportAndFileIO","ExportExcel.xlsx","ExcelgtupapersDemo",valueToWrite);

    }

}

Read/Write Data from Excel File in Selenium Webdriver

Excel Manipulation using JXL API

How to manipulate Excel File using JXL API

JXL is also another famous jar to read Excel file in Java and writing files. Nowadays, POI is used in most of the projects, but before POI, JXL was only Java API for Excel manipulation. It is a very small and simple API for excel reading in Selenium.

TIPS: My suggestion is not to use JXL in any new project because the library is not in active development from 2010 and lack of the feature in compare to POI API.

Download JXL:

If you want to work with JXL, you can download it from this link

https://sourceforge.net/projects/jexcelapi/files/jexcelapi/2.6.12/

How to manipulate Excel File using JXL API

You can also get demo example inside this zipped file for JXL.

Some of the features:

Drawback:

Summary:

 

YOU MIGHT LIKE: