New Excel Reader

To monitor, assess, and validate website functionality, automation testers must automate a sizable number of end-user actions. This blog post will concentrate on automating one such user action—using Java to read data from an Excel file. Here, used the Apache POI module to develop a different straightforward method for reading data from an Excel sheet in the Cucumber-Selenium BDD framework.

Open-source Java library Apache POI is frequently used to generate and manage files based on Microsoft Office. This library is used to read and write Excel files in the XLS and XLSX formats. The POI JAR file facilitates the creation of an IDE coding environment for reading data from and writing to an excel sheet.

For this Excel Reader Script, used the following interfaces.

• Workbook- This interface is used to create various Excel file representations (xls or xlsx).

• Sheet- This interface is used to read various Excel sheet formats (xls or xlsx) that are contained within a workbook.

• Row - The row inside the sheet of the various types of Excel sheets is identified using this interface.

• Cell - This interface is used to locate the appropriate cell in a given row of an Excel sheet of a particular type (xls or xlsx).

Classes

• XSSFWorkbook-Workbook interfaces for the XLSX file will be implemented by the class representation .

• XSSFSheet-This class represents the XLSX file's Sheet interface.

Methods

• getFirstRowNum - Gets the first row on the sheet

• getRow(int rowno) - Returns the logical row (not physical) 0-based.

• row.getRowNum - Gets row number this row represents

• hasNext() - true if the iteration has more elements

• next - the next element in the iteration

• rowIterator - Returns an iterator of the physical rows

• cellIterator - Cell iterator of the physically defined cells.

• getStringCellValue - the value of the cell as a string

How to use this script in your Maven Project

Before use this script, make sure add Apache Poi dependencies in Pom.Xml.

Sample Project structure used for this script

This is the sample Excel Sheet used for this project

Code Sample to call this Script

- the string variable excelpath where you can specify the path of your excelfile.

This is the Excel Reader code to read data from Excel sheet

This is one method of reading data from Excel files using Apache POI .

Use this script in your work and let me know what you think.

Happy Learning!