Site icon Learn Automation

How To Read Write Excel in Selenium

Read Write excel in Selenium

Hello Friends, In this post we will see How to read write excel in Selenium using Apache POI.

We know  Selenium only support web application so to read write excel in selenium we need to take help of third party tool or API like Apache POI or JExcel.

Here we will use Apache POI API to read write excel in selenium.

What is Apache POI?

Apache POI is a Java API which is the collection of different java libraries which give us facility to read, write and manipulate different Microsoft files like excel sheet, word files, power-point etc.

Apache POI in Selenium

Apache POI in Selenium is used when we developed data driven framework then it is used to read write excel in Selenium. It provided a set of Interfaces and classes which are used to reading/writing test data from excel.

In below image we can see those interfaces and classes.

Classes and Interfaces in POI

There are two type of excel file extension- .xls and .xlsx

Apache POI supports both type of excel file format.

Following is a list of POI libraries which have different Interfaces and Classes

Workbook: It is an interface which is implemented by HSSFWorkbook and XSSFWorkbook.

HSSFWorkbook: It is a class which implements Workbook interface and represents .xls file format.

XSSFWorkbook: It is a class which implements Workbook interface and represents .xlsx file format.

Sheet: It is an interface which is implemented by HSSFSheet and XSSFSheet.

HSSFSheet: It is a class which implements Sheet interface and represents sheet .xls file.

XSSFWorkbook: It is a class which implements Sheet interface and represents sheet in .xlsx file.

Row: It is an interface which is implemented by HSSFRow and XSSFRow.

HSSFRow: It is a class which implements Row interface and represents row in the sheet of  .xls file.

XSSFRow: It is a class which implements Row interface and represents row in the sheet of .xlsx file.

Cell: It is an interface which is implemented by HSSFCell and XSSFCell.

HSSFCell: It is a class which implements Cell interface and represents cell in the row  of  .xls file.

XSSFCell: It is a class which implements Row interface and represents cell in the row of .xlsx file.


Read Excel File Using For Loop in Selenium

There are different steps we need to follow.

Step-1:  Download apache POI jar files

Go to site of Apache POI and click on Download

 

Step-2:  Extract the ZIP file. See in below image. Import all jar files into your selenium project.

If you don’t know how to add jars in your selenium project. Refer below link.

Must Read: How to import Jar files in Selenium Project

 

Note: If you are using Maven project in your selenium framework then no need to follow step 2. You have to add maven dependency into your pom.xml file of maven project.

In below image you can see my excel file extension and excel data which I am going to read.

 

 

Code To Read Excel using For loop in Selenium

package exceloperations;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadData {
    
    public static void main(String[] args) throws IOException {
        
        
        String excelpath = ".\\datafiles\\EmployeeDetails.xlsx";
        FileInputStream inputstream = new FileInputStream(excelpath);
        
        
        XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
        XSSFSheet sheet = workbook.getSheet("Employee");
        
        // Using for loop
        
        int rows = sheet.getLastRowNum();
        int cols= sheet.getRow(1).getLastCellNum();
        
        for(int r=0; r<rows; r++) {
            
            XSSFRow row=sheet.getRow(r);
            
            for(int c=0; c< cols; c++) {
                
                XSSFCell cell=row.getCell(c);
                switch(cell.getCellType())
                {
                case STRING: System.out.println(cell.getStringCellValue()); break;
                case NUMERIC: System.out.println(cell.getNumericCellValue()); break;
                case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break;
                
                }
                
            }
            
            System.out.println();
        }
        
    }

}

Screenshot of above code:

 

 


Code for Read Excel File using Iterator in Selenium

package exceloperations;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadDataIterator {
    
    public static void main(String[] args) throws IOException {
    
    String excelpath = ".\\datafiles\\EmployeeDetails.xlsx";
    FileInputStream inputstream = new FileInputStream(excelpath);
            
    XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
    XSSFSheet sheet = workbook.getSheet("Employee");
    
    //// Using Iterator method
    
    Iterator iterator = sheet.iterator();
    
    while(iterator.hasNext()){
        
        XSSFRow row =(XSSFRow)iterator.next();
        
        Iterator cellIterator= row.cellIterator();
        
        while(cellIterator.hasNext()) {
            
            XSSFCell cell= (XSSFCell)cellIterator.next();
            
            switch(cell.getCellType())
            {
            case STRING: System.out.println(cell.getStringCellValue()); break;
            case NUMERIC: System.out.println(cell.getNumericCellValue()); break;
            case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break;
            
            }
            System.out.println();
        }
        
    }

}

}



How To Write data into Excel sheet in Selenium

Before writing data in excel we need to create a new workbook, then create a sheet then create rows inside the sheet and create cell in each rows.

So structure is like this-

Workbook –>Sheet –> Row –> Cell

Before writing some data into excel we need to prepare data those we are going to write. So here we are using Java collection to store the data and same data we will write into excel sheet.

Code for Write data into Excel in Selenium

package exceloperations;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteDataExcelFile {
    
    public static void main(String[] args) {
        
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Emp Info");
        
        Object empdata[][] = {  {"Emp ID", "Name", "Job"},
                                {101, "Ajeet", "Engineer"},
                                {102, "Amit", "Lead"},
                                {103,"Kedar", "Manager"}
                                        
                             };
        
        // Now Above data we will write into excel sheet
        
        //Using for loop
        int rows = empdata.length;
        int cells = empdata[0].length;
        
        System.out.println(rows);  //4
        System.out.println(cells); //3
        
        for(int r=0; r<rows; r++) {
            
            XSSFRow row=sheet.createRow(r);
            
            for(int c=0; c<cells; c++) {
                
                XSSFCell cell=row.createCell(c);
                Object value=empdata[r];
                
                if(value instanceof String) {
                    cell.setCellValue((String)value);
                    

                if(value instanceof Integer) {
                    cell.setCellValue((Integer)value);
                        

                if(value instanceof Boolean) {
                    cell.setCellValue((Boolean)value);
                        
                    
                }
                
            }
            
        }
        
    }

}

Code Explanation:

  1. We have created an empty workbook. Then created a sheet using createSheet() inside the workbook.
  2. We have written all data into Object two dimensional array which we want to write into excel.
  3. We have created multiple rows and columns using createRow() and createCell() method in the sheet.
  4. Now using for loop we have fetched all object array data. instanceof is known as comparison operator which is used to compare object type. Its return either true or false.
  5. Create a filepath and open an outputstream to write the data into excel. Write the workbook into file system.
  6. Close the outputstream.
  7. Print data writing is successful.

Summary

I hope you enjoyed this tutorial and learnt something new.

Final word, Bookmark this post  “How to read write excel in Selenium” for future reference.

If you have other questions or feedback, the comment section is yours. Don’t forget to leave a comment below!

 

Exit mobile version