Read and Write Data from Excel Sheet using Apache POI

Table of contents

Reading Time: 3 minutes

Data driven testing refers to storing the test data in some external file rather than hard coding it in the test scripts. Storing data in an external file makes it easy to manage scripts easily and update data whenever needed without affecting the scripts.So in this blog, We read and write the data from Excel sheet in Selenium.

What is Apache POI?

Apache POI is basically an open source Java library developed by Apache which provides an API for read and write data in Excel sheet using Java programs. It has classes and methods to decode the user input data or a file into Excel file. So for data driven testing using Selenium Web Driver, we use it for reading data stored in excel sheets. It can also be used if you want to write any data to an excel file in your scripts.

Go to Apache POI page https://mvnrepository.com/artifact/org.apache.poi/poi select “The latest Version dependency ” and Download the latest version dependency.

Apache POI Maven dependency

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
</dependency>

Now you copy the dependency and paste into pom.xml file in Intellij.

Load the Apache POI dependency in Intellij pom file

How to Read/Write data using Apache POI?

Now we will be seeing how to write the code in selenium for reading data from an excel sheet and how you can write data to excel sheet.

Workbook

This is the interface of all classes that create or maintain Excel workbooks. The two classes that implement this interface are as follows −

HSSFWorkbook − This class has methods to read and write Excel files in .xls format.
XSSFWorkbook − This class has methods to read and write Excel files in .xls or .xlsx format.

Read data from Excel file

Now, let’s see the complete code to read an excel file using the POI API.

package New;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;


public class ReadData
{
    @Test
    public void excel() throws EncryptedDocumentException, IOException {
        //get the excel sheet file location
        FileInputStream fis = new FileInputStream("src//test//java//sheet.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        //  XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFSheet sheet = workbook.getSheet("StudentDetails");
        //get the total row count in the excel sheet
        Iterator<Row> iterator = sheet.rowIterator();
        int cellIndex = 0;
        String description = null;
        while (iterator.hasNext()) {
            Row row = iterator.next();
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                Cell cell = row.getCell(i);
                //print the cell value
                System.out.println(i + " " +cell);

            }
        }
    }

After running the above program, the output looks like

This image has an empty alt attribute; its file name is screenshot-from-2021-03-10-20-15-56.png

Write data to Excel file

Now, let’s see the complete code to write data from java file to an excel file using the POI API.


package New;

import java.io.File;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

public class WriteData {
    // any exceptions need to be caught
    @Test
    public void Write() throws Exception
    {
        // workbook object 
        XSSFWorkbook workbook = new XSSFWorkbook();
        // spreadsheet object 
        XSSFSheet spreadsheet
                = workbook.createSheet(" Employee Data ");
        // creating a row object 
        XSSFRow row;
        // This data needs to be written (Object[]) 
        Map<String, Object[]> studentData
                = new TreeMap<String, Object[]>();

        studentData.put("1", new Object[] { "Employee Id", "NAME", "POST" });
        
        studentData.put("2", new Object[] { "1301", "Prajjawal", "Intern" });
        studentData.put("3", new Object[] { "1302", "Nitish", "Software Consultant" });
        studentData.put("4", new Object[] { "1303", "Aditi", "QA Engineer" });
        studentData.put("5", new Object[] { "1303", "Ayush", "System Engineer" });
        studentData.put("6", new Object[] { "1304", "Abhishek", "Intern" });

        Set<String> keyid = studentData.keySet();

        int rowid = 0;
        // writing the data into the sheets...
        for (String key : keyid) {
            row = spreadsheet.createRow(rowid++);
            Object[] objectArr = studentData.get(key);
            int cellid = 0;

            for (Object obj : objectArr) {
                Cell cell = row.createCell(cellid++);
                cell.setCellValue((String)obj);
                System.out.println(cellid +" "+cell);
            }
        }
        // .xlsx is the format for Excel Sheets... 
        // writing the workbook into the file... 
        FileOutputStream out = new FileOutputStream(
                new File("src//test//java//Write.xlsx"));
        workbook.write(out);

        out.close();
    }
}