Read test data from excel file in selenium

In the selenium data-driven automation framework, the test data is kept in external data sources majorly in excel files. Moreover, we're also required to perform varied operations on different data files like excel, word document, ppt, etc. during our test execution.

Earlier JXL API was used to read and write data from excel files. But it had some limitations as it could only read XLS type of files only and provide a very limited set of operations that can be performed on the file.

With the advent of XLSX and the introduction of later versions of MS Excel, the JXL API poses further limitations. Therefore apache POI API came into existence to handle varied file types and became a widely used API as it eases the user to perform a multitude of operations not only on an excel file but also on word documents, PowerPoint presentation PPT files, etc. Apache POI API is not only limited to reading and writing data to and from excel, but it gives varied operational capabilities to be performed over an excel file like formatting the excel, handling formula in the excel, creating and deleting a new sheet, formatting the cell values, reading and creating a PowerPoint ppt file, performing operations on a word document file,

API components of POI libraries:

HSSF/XSSF: To perform operations on excel files (.xls/.xlsx respectively)
HSLF/XSLF: To perform operations on a PPT file
HWPF/XWPF: To perform operations on word document file
HSMF: To access MS outlook

Download the apache poi library from https://poi.apache.org/download.html
OR
Include the following dependency in the Maven POM XML

Read different types of numeric data from excel datasheet in selenium

An application under test might have different input fields that may take different types of numeric and string inputs from the user. Reading different types of numeric data from excel sheets may pose some challenges while executing an automation test.

The image below is a sample test data sheet containing different types of numeric test data.



Here the mobile number is a 10 digit numeric data,
weight is another numeric data with a decimal value,
similarly, date of birth is a date in excel format,
in the last cell, a formula is applied in the excel sheet to add the weight and age.

So we have varied types of numeric test data that we want to read and use in our AUT. Below code snippet will read the cell values from the excel datasheet and print them in the console:



After executing this code snippet, we'll get below output. As you can see the 10 digit cell number has been completely changed to a different numeral format.
Age 23 is converted into decimal format i.e. 23.0
The date format 02/02/2021 is also changed to 02-Feb-2021
The excel formula gets printed instead of summation value.
This is definitely not the output that we want to use in our AUT as test data.




To counter this, we'll use DataFormatter class under Apache POI libraries. Use DataFormatter class (org.apache.poi.ss.usermodel.DataFormatter) to format the cell values. DataFormatter class provides varied methods to format the excel cell values and can be used to retrieve the data exactly as it appears in Excel.
"formatCellValue(cell)" method will format the cell value to string irrespective of the cell type.



After executing the above code snippet, we'll get the below output in which the numeric values are exactly the same as specified in the excel datasheet.