Read Write data in excel using Java with Apache POI for Selenium

Read & Write Data from Excel File using Java with Apache POI for Selenium Data Driven Framework

=====================================================================

Download and Setup Apache POI
Read Excel file using Apache POI
Write Excel file using Apache POI
Download input.xlsx file – input.xlsx
Download Full Data Driven Frmework project – Excel_Read_Write

=====================================================================

1. Download and Setup Apache POI

=====================================================================

Apache POI:

Apache POI is an API, It is freeware & written in Java, using apache POI able to read/write Excel files because of so many predefined methods, classes, and interfaces.

——————————————————————————–

Two ways to Read and Write excel file in Selenium project using Java:

Read and Write Excel file Using two ways using Jar File or using maven dependency:

Download apache poi jar file add into project libraries.

Using Maven Add poi-ooxml dependency into Maven pom.xml file.

——————————————————————————–

Method 1: Download Apache POI to Read and Write in Excel file:

Go to Apache POI download page to download the Apache POI jar file.

URL – http://poi.apache.org/download.html

Under Available Downloads Click on the latest stable release is Apache POI — link.

URL – https://poi.apache.org/download.html#POI-4.1.0

Under Binary Distribution click on Zip file to Download Apache POI jar file.
Then Click on the first highlighted link at the top of the page.
After file downloading Right click on the Zip file and select “Extract All“.

——————————————————————————–

Add Apache POI Jars into Project Library:

Right, click on the project.
Click on Properties.
Click on Java Build Path.
Select the Libraries Tab.
Click on Add External Jars.
Select Apache POI Jar files.
Click on Apply and Close button.

——————————————————————————–

Method 2: Using poi-ooxml Maven Add poi-ooxml dependency into Maven pom.xml file.

Add Apache POI Dependencies.
Go to below link select version then copy dependency code and paste into pom.xml file.
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml

<dependency>    

     <groupId>org.apache.poi</groupId>    

     <artifactId>poi</artifactId>    

     <version>3.17</version>

</dependency>

<dependency>    

     <groupId>org.apache.poi</groupId>    

     <artifactId>poi-ooxml</artifactId>    

     <version>3.17</version>

</dependency>

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

=====================================================================

2. Read Excel file using Apache POI

=====================================================================

To Read data from excel we will use Java and Apache POI classes.
Below are the classes we will use to read the data from the Excel file.

——————————————————————————–

FileInputStream – A FileInputStream is Use to read data from a file in the form of a sequence of bytes. XSSFWorkbook – HSSF and XSSF provide ways to read spreadsheets create, modify, read and write XLS spreadsheets.
XSSFSheet – Use to create a new sheet.
XSSFRow – Read / Write Row.
XSSFCell – Read / Write Cell using Row and Column Number.

——————————————————————————–

Row index starts from ZERO (0).
Column index starts from ZERO (0).
‘.xlsx’ Extension of excel file from Excel 2007 to till now.

——————————————————————————–

Test data contains numeric, string Data and sometimes combination as well which is totally dynamic.
So we create such library which able to handle dynamic data from excel file.
If we want to read String value you can call getStringCellValue() method
If we want to read Numeric value you can call getNumericCellValue() sometimes which will return a double value which we can typecast into int and then we can use in our test data.

import java.io.*;

// Import packages for Apache POI library. 

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

public class ReadDataFromExcelFile {

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

		// To locate the path of excel file.
		FileInputStream file = new FileInputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

		// Initialize the excel file as a workbook.
		XSSFWorkbook workbook = new XSSFWorkbook(file);

		// initialize the Excel Sheet of the workbook, (0) denotes first sheet of the
		// workbook.
		XSSFSheet sheet = workbook.getSheetAt(0);

		// 0 denotes to first row and first cell of the excel sheet.
		String str = sheet.getRow(0).getCell(0).getStringCellValue();
		System.out.println("String Fetch from Excel:- "+str);

		// 1 denotes to Second row and 0 denotes first cell of the excel sheet.
		Number num = sheet.getRow(1).getCell(0).getNumericCellValue();
		System.out.println("Number Fetch from Excel:- "+num);

		// close the excel file.
		file.close();

	}
}

import java.io.*;

// Import packages for Apache POI library.

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

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

public class ReadDataFromExcelFile {

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

// To locate the path of excel file.

FileInputStream file = new FileInputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

// Initialize the excel file as a workbook.

XSSFWorkbook workbook = new XSSFWorkbook(file);

// initialize the Excel Sheet of the workbook, (0) denotes first sheet of the

// workbook.

XSSFSheet sheet = workbook.getSheetAt(0);

// 0 denotes to first row and first cell of the excel sheet.

String str = sheet.getRow(0).getCell(0).getStringCellValue();

System.out.println("String Fetch from Excel:- "+str);

// 1 denotes to Second row and 0 denotes first cell of the excel sheet.

Number num = sheet.getRow(1).getCell(0).getNumericCellValue();

System.out.println("Number Fetch from Excel:- "+num);

// close the excel file.

file.close();

}

——————————————————————————–

Output:-

String Fetch from Excel:- Username

Number Fetch from Excel:- 123456789

——————————————————————————–

Read Cell Data from Excel:-

=====================================================================

3. Before Write Excel file using Apache POI

=====================================================================

Write data into excel file we will use Java and Apache POI classes.
Below are the classes we will use to Write the data In Excel file Cell.

——————————————————————————–

FileOutputStream – To write the data to file, you have to convert the data into bytes and save it to file.
FileInputStream – A FileInputStream is Use to read data from a file in the form of a sequence of bytes. XSSFWorkbook – HSSF and XSSF provide ways to read spreadsheets create, modify, read and write XLS spreadsheets.
XSSFSheet – Use to create a new sheet.
XSSFRow – Read / Write Row.
XSSFCell – Read / Write Cell using Row and Column Number.

——————————————————————————–

Row index starts from ZERO (0).
Column index starts from ZERO (0).
‘.xlsx’ Extension of excel file from Excel 2007 to Till now.

——————————————————————————–

import java.io.*;

// Import packages for Apache POI library. 

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteDataInExcelFile {

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

		// locate the path of excel.
		FileInputStream fileRead = new FileInputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

		// initialize the excel as a workbook.
		XSSFWorkbook workbook = new XSSFWorkbook(fileRead);

		// 0 denotes to the first sheet of the workbook.
		XSSFSheet sheet = workbook.getSheetAt(0);

		// Cell location where update the data.
		Cell Write = sheet.getRow(0).getCell(0);

		// Value to set into Cell
		Write.setCellValue("String");

		// Read Cell from Excel file
		String str = sheet.getRow(0).getCell(0).getStringCellValue();

		// Print Write cell from Excel file
		System.out.println(str);

		// Define the path of output excel file
		FileOutputStream fileWrite = new FileOutputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

		// Update the output file on defined location.
		workbook.write(fileWrite);

		// Close the Excel file
		fileWrite.close();

	}
}

import java.io.*;

// Import packages for Apache POI library.

import org.apache.poi.ss.usermodel.Cell;

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

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

public class WriteDataInExcelFile {

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

// locate the path of excel.

FileInputStream fileRead = new FileInputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

// initialize the excel as a workbook.

XSSFWorkbook workbook = new XSSFWorkbook(fileRead);

// 0 denotes to the first sheet of the workbook.

XSSFSheet sheet = workbook.getSheetAt(0);

// Cell location where update the data.

Cell Write = sheet.getRow(0).getCell(0);

// Value to set into Cell

Write.setCellValue("String");

// Read Cell from Excel file

String str = sheet.getRow(0).getCell(0).getStringCellValue();

// Print Write cell from Excel file

System.out.println(str);

// Define the path of output excel file

FileOutputStream fileWrite = new FileOutputStream(new File(System.getProperty("user.dir") + "\\input.xlsx"));

// Update the output file on defined location.

workbook.write(fileWrite);

// Close the Excel file

fileWrite.close();

}

——————————————————————————–

After Write Data in Excel File

Package / Project Structure –