Selenium Automation Testing using Java: 07/10/24

Reading Data from Excel File Using Apache POI:

To read data from an Excel file, you can use the Apache POI library.

Apache POI provides predefined classes, interfaces, and methods to interact with Excel files.

Download Apache POI.jar :

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

</dependency>

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

<artifactId>poi-ooxml</artifactId>

</dependency>

After adding these dependencies, save the pom.xml file and ensure that poi.jar and poi-ooxml.jar are added to your current project.

Structure of Excel :

An Excel file (workbook) contains worksheets, which in turn contain rows and cells.

Xl -Workbook

worksheets

rows and cells

create "test data.xls" file in ur system:

Create a file named test data.xls with the following structure:

ProductName	Qty	Price
Lux	4	200
Shampoo	5	200
Paste	2	100

Java Code to Read Data from Excel File

package com.birla;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

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

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

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

public class ExcelReader1 {

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

// TODO Auto-generated method stub

// Apache POI:

String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

//create FileInputStream class object

FileInputStream fis = new FileInputStream(xlFile);

//create workbook reference var

Workbook wb = WorkbookFactory.create(fis);

// Workbook - Predefined Interface in POI

//create Sheet reference var and refer Sheet1

Sheet sh = wb.getSheet("Sheet1");

// sheet contains rows and cells

// Rows count

int rowsCnt = sh.getLastRowNum();

System.out.println("rowscnt="+rowsCnt);

// rowscnt=3 But actual = 4 rows - 0,1,2,3

// Get columns count

// sh.getLastCellNum();-- no method available

int colsCnt = sh.getRow(0).getLastCellNum();

System.out.println("colsCnt="+colsCnt);

// columnsCnt=3 -- displays exact columns count

// Read data from row, cell - 0,0 .toString(); ProductName

String data00 = sh.getRow(0).getCell(0).toString();

System.out.println("data00="+data00);//ProductName

// Read data from row, cell - 0,1 --Qty

String data01 = sh.getRow(0).getCell(1).toString();

System.out.println("data01="+data01);

// HW Read data from 0,2 - Price

// Read data from 1,0 -Lux

String data10 = sh.getRow(1).getCell(0).toString();

System.out.println("data10="+data10);

// Read data from 1,1 - 4

String data11 = sh.getRow(1).getCell(1).toString();

System.out.println("data11="+data11);// data11=4.0 ??? data11=4

// Note: add ' single quotes before number in excel file

// '4 == treat as string else number

// Read data from 1,2 cell -200

String data12 = sh.getRow(1).getCell(2).toString();

System.out.println("data12="+data12);// data12=200.0 ?? data12=200

}

HW read all rows data - from 1st column using for loop ?

HW Read all rows data from 2nd column using for loop ?

Develop generic method to read excel file:

getRowsCnt(String xlFile,String sheet)

{

}

Call:

getRowsCnt("xl file name.xlsx", "Sheet1" )

// 3

--------------

//HW Develop some generic method to get columns cnt

getColumnsCnt(String xlFile,String sheet)

{

}

Call:getColumnsCnt("xl file name.xlsx", "Sheet1" );

// 3

Develop below method:

readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo)

{

}

package com.birla;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

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

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

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

public class ExcelReader2 {

// readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo)

public static String readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo) throws EncryptedDocumentException, IOException

{

FileInputStream fis= new FileInputStream(xlFile);

Workbook wb = WorkbookFactory.create(fis);

Sheet sh = wb.getSheet(sheet);

String data = sh.getRow(rowNo).getCell(cellNo).toString();

System.out.println("Data from row="+ rowNo +",cell no="+ cellNo + " is ="+data);

return data;

}

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

// TODO Auto-generated method stub

// Apache POI:

String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

// call readDataFromExcel from sheet1 0, 0

String data00 = readDataFromExcel(xlFile, "Sheet1", 0, 0);

System.out.println("data00="+data00);

// call readDataFromExcel from sheet1 0, 1

String data01 = readDataFromExcel(xlFile, "Sheet1", 0, 1);

System.out.println("data01="+data01);

// call readDataFromExcel from sheet1 0, 2

String data02 =readDataFromExcel(xlFile, "Sheet1", 0, 2);

System.out.println("data02="+data02);

}

o/p:

Data from row=0,cell no=0 is =ProductName

data00=ProductName

Data from row=0,cell no=1 is =Qty

data01=Qty

Data from row=0,cell no=2 is =Price

data02=Price

// HW Read all rows and and all cells Data from excel file ?

// Note: use 2 for loops

// 0,0 0,1 0,2

// 1,0 1,1 1,2

// 2,0 2,1 2,2

---

//HW Develop Generic Method to Get Column Number Based on Column Name

// getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"ProductName") ;

// // 1

// getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"Qty") ; // 2

// getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"Price") ; // 3

// getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"QtyInvlaidColumn") ; // -1, Display proper msg "Given column name is not found in sheet".

HW Store column data in "arrayList" by passing "column name" ?

pass the column name and it should store all the data ?

readAllDataFromExcelBasedOnColumnName( String excelfilename, String sheetname, String ColumnName)

{

}

FAQ What jar file (or) Library file is used to read the data from excel ?

Apache POI .jar file

WAP to check Whether given sheet name is exist in excel (or) not ?

code:

// wb.getSheetIndex

// return index no of given sheet name

// sheet 1 index no =0

// sheet 2 index no = 1

// if sheet name is not there in excel, it returns -1

Method calls:

isSheetExist(xlFile, "CreateUsers");

isSheetExist(xlFile, "Sheet1");

isSheetExist(xlFile, "Sheet2");

isSheetExist(xlFile, "CreateUsersInvalid");

----------------------

package com.birla;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

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

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

public class ExcelReader3 {

// develop a method -isSheetExist(String xlFile, String sheetName ) throws

public static int isSheetExist(String xlFile, String sheetName) throws EncryptedDocumentException, IOException

{

FileInputStream fis= new FileInputStream(xlFile);

Workbook wb = WorkbookFactory.create(fis);

int indexNo = wb.getSheetIndex(sheetName);

System.out.println("Index no of given sheet="+sheetName +"= "+ indexNo);

if(indexNo>=0)

{

System.out.println("Given sheet=" +sheetName+" is available in excel = "+xlFile);

}

else

{

System.out.println("Given sheet=" +sheetName+" is not available in excel = "+xlFile);

}

return indexNo;

}

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

String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\Jul2023WorkSpace\\MavenProjectThroughCommand\\TestData.xlsx";

// check isSheetExist(xlFile, "Sheet1");

int indexNoSheet1 = isSheetExist(xlFile, "Sheet1");

System.out.println("indexNoSheet1="+indexNoSheet1);

// check isSheetExist(xlFile, "Sheet2");// 1

int indexNoSheet2 = isSheetExist(xlFile, "Sheet2");

System.out.println("indexNoSheet2="+indexNoSheet2);

//HW check isSheetExist(xlFile, "CreateUsers");//2

// check isSheetExist(xlFile, "CreateUsersInvalid");// -1

int indexNoCreateUsersInvalid = isSheetExist(xlFile, "CreateUsersInvalid");

System.out.println("indexNoCreateUsersInvalid="+indexNoCreateUsersInvalid);

}

o/p:

Index no of given sheet=Sheet1= 0

Given sheet=Sheet1 is available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoSheet1=0

Index no of given sheet=Sheet2= 1

Given sheet=Sheet2 is available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoSheet2=1

Index no of given sheet=CreateUsersInvalid= -1

Given sheet=CreateUsersInvalid is not available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoCreateUsersInvalid=-1

Other important classes in apache poi:

// Apache POI:

// We have some predefined classes and interfaces and predefined methods which can be used to read excel file

Key Classes in Apache POI

HSSFWorkbook: Predefined class to read data from .xls format.
XSSFWorkbook: Predefined class to read data from .xlsx format.

Key Interfaces in Apache POI

Workbook: Interface representing an Excel workbook.
Sheet: Interface representing a sheet within a workbook.

Read data from .xlsx:

package com.birla;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

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

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

public class ReadDataFromExcelByXssfworkbook {

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

String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

// to read data from .xlsx --> we have to use -XSSFWorkbook

// create obj for -XSSFWorkbook class

FileInputStream fis = new FileInputStream(xlFile);

XSSFWorkbook xssfwb = new XSSFWorkbook(fis);

XSSFSheet xssfsh = xssfwb.getSheet("sheet1");

// read data from row no, cell no = 0,0

String data00= xssfsh.getRow(0).getCell(0).toString();

System.out.println("data00="+data00);

// data00=ProductName

// HW read data from row no, cell no = 0,1

// HW read data from row no, cell no = 0,2

// HW read data from row no, cell no =1,0

// HW read data from row no, cell no =1,1

//HW Get Rows count

//HW Get columns Cnt

}

// HW create new excel file with .xls format and Get Rows count, Get columns Cnt , read data from .xls format file using 'HSSWorkbook' class?

Selenium Automation Testing using Java

Wednesday, July 10, 2024

Reading Data from Excel File Using Apache POI:

Java Code to Read Data from Excel File

Key Classes in Apache POI

Key Interfaces in Apache POI

git commands MCQ

Report Abuse