How to Read Excel File in Selenium with Apache POI(with Latest Version)[Download Code]

✅ Join us — https://sendfox.com/thetestingacademy

In this video, We are going to learn How to Read Excel File in Selenium with Apache POI

The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML).

To read or write an Excel, 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.

✅ Download Code — https://scrolltest.com/automation/day24
✅http://poi.apache.org
✅https://mvnrepository.com/

package com.scrolltest;
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;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
public class ExcelDemo {
    public static void main(String[] args) {
        WebDriver driver = new FirefoxDriver( );
        readFile( );
        String[] recordToAdd = {"Aman","LeadSDET"};
        writeExcel(recordToAdd);
        driver.quit();

    }
    private static void writeExcel(String[] recordToAdd) {
        File file = new File("src/main/java/com/scrolltest/TestDATA.xlsx");
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            Workbook testDataWorkBook1 = new XSSFWorkbook(inputStream);
            Sheet testDataSheet1 = testDataWorkBook1.getSheet("Sheet1");
            int rowCount1 = testDataSheet1.getLastRowNum()-testDataSheet1.getFirstRowNum();
            Row row = testDataSheet1.getRow(0);
            Row newRow = testDataSheet1.createRow(rowCount1+1);
            for(int j = 0; j < row.getLastCellNum(); j++){
                //Fill data in row
                Cell cell = newRow.createCell(j);
                cell.setCellValue(recordToAdd[j]);
            }
            inputStream.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace( );
        } catch (IOException e) {
            e.printStackTrace( );
        }
    }
    private static void readFile() {
        try {
            File file = new File("src/main/java/com/scrolltest/TestDATA.xlsx");
            FileInputStream inputStream = new FileInputStream(file);
            Workbook testDataWorkBook = new XSSFWorkbook(inputStream);
            Sheet testDataSheet = testDataWorkBook.getSheet("Sheet1");
            int rowCount = testDataSheet.getLastRowNum( ) - testDataSheet.getFirstRowNum( );
            for (int i = 0; i < rowCount + 1; i++) {
                Row row = testDataSheet.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( );
            }
            inputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace( );
        } catch (IOException e) {
            e.printStackTrace( );
        }

    }
}