How to read and write from Excel File in Selenium : Apache POI and JXL

Java provides different API to Read and Write a File with help of Selenium. In this Post, we will learn how can we read and write from Excel file by using Java IO package,JXL and Apache POI library.
            If your are using any Automation Framework you must know About Read and Write fro Excel, We have to use This Concept while working in Data Driven Framework .

Step to Read Data from Excel Using Apache POI 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 .......

• For user who is using Maven Project can copy Dependency from here 

    org.apache.poi
    poi
    3.17

       

• For user Who is using Simple Java Project Can know How To Download Apache POI Jar and Add To JAVA Build Path 

Types of  Files in MS-Excel 

• .XLS File
• .XLSX File

Types of Classes and InterFace in Apache POI 

Apache Interface and Classes

 Read Data from Excel File

• Go To Java Project
• Right Click on Java Project
• Create a New Folder with Name DataFromExcel
• Copy Any Blank Excel File from Your PC and Paste Inside Folder 'DataFromExcel'
• Open Excel File 
• Enter The Excel Sheet Name 'LoginDataSheet'
• Enter Any Website Login Credential Whom you want To Read

LoginDataSheet

SheetName

  

package com.abof.scripts;  // As Per Your Java Project Name

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

    
    public void readExcelFile(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 workbook = 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

    workbook = 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

       workbook = new HSSFWorkbook(inputStream);

    }

    //Read sheet inside the workbook by its name

    Sheet sheet = workbook.getSheet(sheetName);

    //Find number of rows in excel file

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

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

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

        Row row = sheet.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()+"|| ");

        }
    }
    }
    

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

    public static void main(String [] args) throws IOException{

    //Create an object of ReadQaMantraExcelFile class

    ReadQaMantraExcelFile objExcelFile = new ReadQaMantraExcelFile ();

    //Prepare the path of excel file

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

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

    objExcelFile.readExcelFile(filePath,"data.xlsx","LoginDataSheet");

    }

}

 Write Data To Excel File

package com.abof.scripts;

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 WriteqamantraExcelFile {
               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 qamantraWorkbook = 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

        qamantraWorkbook = 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

            qamantraWorkbook = new HSSFWorkbook(inputStream);

        }

        

    //Read excel sheet by sheet name    

    Sheet sheet = qamantraWorkbook.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

    qamantraWorkbook.write(outputStream);

    //close output stream

    outputStream.close();
    
    }

    
    public static void main(String args []) throws IOException{

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

        String[] valueToWrite = {"abc@gmail.com","Pass@123"};

        //Create an object of current class

   WriteqamantraExcelFile objExcelFile = new WriteqamantraExcelFile();

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

        objExcelFile.writeExcel(System.getProperty("user.dir")+"\\src\\testData","data.xlsx","LoginDataSheet",valueToWrite);

    }

}