How to manage Excel rows pragmatically?
The Row interface provides us with the ability to work with rows in the Excel sheet. Below two classes implement this interface:
How to read a specific cell value?
Now that we are familiar with the different classes and the method provided by the Apache POI library, lets try to combine them in a code snippet, where we try to read the Address of the student in the first row in our sample Excel. The Address is present in cell number 5 of the row.
Note: Index starts from zero for both the row and cell.
You can use the below code snippet to print the address as highlighted in the above using the methods explained above –
When we run the above program, we will get the output as follows:
The highlighted area shows the address of the first student that is printed using the code.
Now that we have understood how to read a particular cell value, we will now take a look at how to read the complete data from the Excel File.
How to manage Excel cells?
The Cell interface helps us in accessing the cells of a particular row. There are two classes that implement this interface and we can use for reading/writing data into the cell:
Now that we have gone through the details of the Apache POI library, lets try to use it to read and write into Excel Files using Selenium WebDriver.
Handling excel files Using Apache POI in Selenium WebDriver. As we all know Selenium supports only Web browser automation. We need to get the help of third party API like Apache POI to handle (read and write) excel files using Selenium WebDriver.
Download the jar files using the above mentioned link. All jar files will come in a zip file. Extract it and Add all jar files in the Project.
Note: How to add Jar files – Select Project and Right click on the Project – Go to ‘Build path’ – Go to ‘Configure build path’ – Click on ‘lib’ section – Add external jar
Workbook: HSSFWorkbook and XSSFWorkbook classes implement this interfaceHSSFWorkbook: It is a class represents XLS fileXSSFWorkbook: It is a class represents XLSX fileSheet: HSSFWorkbook and XSSFWorkbook classes implement this interfaceHSSFSheet: It is a class represents a sheet in a XLS fileXSSFSheet: It is a class represents a sheet in a XLSX fileRow: HSSFWorkbook and XSSFWorkbook classes implement this interfaceHSSFRow: It is a class represents a row in a sheet of XLS fileXSSFRow: It is a class represents a row in a sheet of XLSX fileCell: HSSFWorkbook and XSSFWorkbook classes implement this interfaceHSSFCell: It is a class represents a cell in a row of XLS fileXSSFCell: It is a class represents a cell in a row of XLSX file
Apache POI is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program. It is a popular API that allows to work around excel files using Java Programs. In short, you can read and write MS Excel files using Java. Apache POI is your Java Excel solution. You’d use HSSF if you needed to read or write an Excel file using Java (XLS). You’d use XSSF if you need to read or write an OOXML Excel file using Java (XLSX). It has many predefined methods, classes, and interfaces.