How to perform read & write operations in excel file using Selenium WebDriver?

To perform read & write operation using Selenium web driver, Apache provides a POI library. to download that POI library in the form of Jar file navigate to this URL "http://poi.apache.org/download.html"

Now, understand the classes and Interfaces available in POI library to achieve the objective of read & write operation.

Interfaces

1. Workbook
2. Sheet
3. Row
4. Cell

Classes which implements the above interfaces

1. HSSFWorkbook
2. HSSFSheet
3. HSSFRow
4. HSSFCell

The above mentioned classes we use in our code in case if you have an excel file saved with .xls extension. But in case if the extension is .xlsx then below classes will implement the above mentioned interfaces.

1. XSSFWorkbook
2. XSSFSheet
3. XSSFRow
4. XSSFCell

I have created a below excel file i.e. "SampleStudentData.xlsx" to perform read & write operation in it. As you noticed that the extension of this file is .xlsx so in this case we will use XSSF... class which will implement the above interfaces.

In the above excel file I have provided the different types of data like String, Numeric and Boolean format. The intention is so that you understand that how would you deal with such data if it is in different formats. The below code you will find in my code which will extract the type of data containing in each cell.

CellType cell_type=sheet.getRow(r).getCell(c).getCellType();

Find the below code for Read&Write Data from Excel File:

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Scanner;

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

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

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.xssf.usermodel.XSSFWorkbook;

public class ExcelConnection {

public static Workbook workbook;

public static Sheet sheet;

public static Row row;

public static Cell cell;

public static FileInputStream fin;

public static FileOutputStream fout;

public static int totNoOfRows;

public static int totNoOfCols;

public static String strName;

public static String strRollNo;

public static String strSubject;

public static String strCurrentStatus;

public static boolean blnFeeStatus;

public static Scanner scan;

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

{

fin=new FileInputStream("D:/SampleStudentData.xlsx");

workbook=new XSSFWorkbook(fin);

sheet=workbook.getSheet("Sheet1");

totNoOfRows=sheet.getPhysicalNumberOfRows();

System.out.println("Total No of rows available i.e. :"+ totNoOfRows);

row=sheet.getRow(0);

totNoOfCols=row.getLastCellNum();

System.out.println("Total no of columns available i.e. :"+ totNoOfCols);

System.out.println();

for(int r=0;r<=totNoOfRows-1;r++)

{

for(int c=0;c<=totNoOfCols-1;c++)

{

CellType cell_type=sheet.getRow(r).getCell(c).getCellType();

if(cell_type==CellType.STRING)

{

System.out.print(sheet.getRow(r).getCell(c).getStringCellValue() +"   ");

}

else if(cell_type==CellType.NUMERIC)

{

System.out.print(sheet.getRow(r).getCell(c).getNumericCellValue()+"   ");

}

else if(cell_type==CellType.BOOLEAN)

{

System.out.print(sheet.getRow(r).getCell(c).getBooleanCellValue()+"   ");

}

}

System.out.println();

}

System.out.println("Enter how many records you want to add in the existing file");

int noOfAdditions;

scan=new Scanner(System.in);

noOfAdditions=scan.nextInt();

for(int i=totNoOfRows;i<=noOfAdditions;i++)

{

enterData();

row=sheet.createRow(i);

for(int j=0;j<totNoOfCols;j++)

{

cell=row.createCell(j);

switch(j)

{

case 0:

cell=row.createCell(j);

cell.setCellValue(i);

break;

case 1:

cell=row.createCell(j);

cell.setCellValue(strName);

break;

case 2:

cell=row.createCell(j);

cell.setCellValue(strRollNo);

break;

case 3: 

cell=row.createCell(j);

cell.setCellValue(strSubject);

break;

case 4: 

cell=row.createCell(j);

cell.setCellValue(strCurrentStatus);

break;

case 5:

cell=row.createCell(j);

cell.setCellValue(blnFeeStatus);

break;

default:

}

}

}

fin.close();

fout=new FileOutputStream("d:/SampleStudentData.xlsx");

workbook.write(fout);

System.out.println("Data Saved successfully");

fout.close();

}

public static void enterData() 

{

scan=new Scanner(System.in);

System.out.println("Enter name of the Student");

strName=scan.nextLine();

System.out.println("Enter rollno of the Student");

strRollNo=scan.nextLine();

System.out.println("Enter subject of the Student");

strSubject=scan.nextLine();

System.out.println("Enter current status of the Student");

strCurrentStatus=scan.nextLine();

System.out.println("Enter fee status of the Student");

blnFeeStatus=scan.nextBoolean();

}

}

Output: