How To Read an Excel File Using Apache POI In Selenium WebDriver

Apache provides a library POI using which read and write of an excel can be performed in Selenium WebDriver. Using it the read and write operation of both xls and xlsx file formats can be done. So first you need to download the Apache POI and configure it (jar files) in your eclipse. Implement the following steps to read an excel file:

LEARN SPRING FRAMEWORK AND ANGULARJS FROM SCRATCH.

1) Launch eclipse, create a project, a package, and a class inside this package.

2) Add all the jar files of Apache POI to your project through java build path.

3) Add all the jar files inside Apache poi folder.

a

4) Then go to lib folder under the Apache poi folder and all the jar files present inside it.

a

5) Then go to ooxml-lib folder and all the jar files present inside it.

a

6) Now you can proceed with your script. Go to the created class and add the below given lines of code. But before this you need to create an excel sheet in any of your local drive folder and insert some data inside it. Name the sheet as Data and rename the sheet inside this file as “Input”. Save this excel sheet in .xlsx format.

a

7) Now go to your script and add these lines of code :

FileInputStream fis=new FileInputStream(“D:\\Selenium\\Data.xlsx”);
Workbook wb=WorkbookFactory.create(fis);
Sheet sh=wb.getSheet(“Input”);

Here the first line takes the path where you have created your excel sheet and then pass it in the workbook object. It will create a pipeline between the sheet and the eclipse. Then we can use the getSheet method which is present inside the Workbook class where we have to pass the sheet name. Here the sheet name is “Input”.

a

8) Now you can just give the row number and the cell number in the methods of the respective cell which value you want to display.

Row row=sh.getRow(2);
Cell cell=row.getCell(1);
System.out.println(cell);

Or you can first pass the cell value to a String variable first and then display it.

String cellval=cell.getStringCellValue();
System.out.println(cellval);

Now you run your code. According to our script and excel file we will get the result as :

NYC

NYC

Two times the result is displayed because we have given it two times in print statement. The overall code will look like :

package home;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class readingFromExcel {
static int rowcount=0;
public static void main(String[] args) throws IOException, InvalidFormatException{
FileInputStream fis=new FileInputStream(“D:\\Selenium\\Data.xlsx”);
Workbook wb=WorkbookFactory.create(fis);
Sheet sh=wb.getSheet(“Input”);
Row row=sh.getRow(2);
Cell cell=row.getCell(1);
System.out.println(cell);
String cellval=cell.getStringCellValue();
System.out.println(cellval);
}}

And the result get displayed when you run this :

a

That’s all in reading a value from an excel sheet. We will learn how to write in an excel sheet in the next post…!!!

Add Comment