Open In App

How to Read Data From Formula Cell in Excel Sheet using Selenium and Apache POI?

Last Updated : 12 May, 2022
Like Article

The Apache POI is one of the most popular java libraries for selenium data-driven testing. It is mainly used for handling all types of Microsoft documents such as MS Word, and MS Excel. MS word and others. Apache POI is an open-source library and POI stands for “Poor Obfuscation Implementation”.

Getting Started with Apache POI

We need to download the Apache POI to get started with the Apache POI library.

  • Create the Maven project and install the Selenium driver  

Geeks, for creating a maven project with selenium check this article How to Create a Selenium Maven Project with Eclipse to Open Chrome Browser?

  • Add the Apache POI dependency mvn repository 
  • Go to and search for Apache POI and select the latest stable version, also install the poi-OOXML dependency.



  • Copy the dependency and paste it into the pom.xml file in the maven project.


Creating Excel Sheet for Testing

Now create the Excel sheet with two columns Salary and bonus and calculate the Total using the Formula. If we try to read the data from the Formula table we usually occur errors. So we try to read the data in the formula data type itself.

Excel Sheet


Save the Excel File and paste the location in the file input stream in the code.



package GFG_Maven.GFG_MAven;
import org.testng.annotations.Test;
import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Geeks {
    public void geekforgeeks() throws IOException{
          FileInputStream file = new FileInputStream("C:\\Users\\ADMIN\\Desktop\\geeks.xlsx");
            XSSFWorkbook wb=new XSSFWorkbook(file);
            XSSFSheet sheet=wb.getSheet("Sheet1");
            // Iterate each row one by one
            Iterator<Row> rIterator = sheet.iterator();
            while (rIterator.hasNext()) 
                Row row =;
                  // For each row, iterate through all the columns
                Iterator<Cell> Cell = row.cellIterator();
                while (Cell.hasNext()) 
                    Cell cell =;
                      // Check the cell type
                    case STRING:
                    case NUMERIC:
                    case FORMULA:


After Executing the program, we will get the all data from Excel.



Code Explanation

  • Creating the file input stream paste the location of the Excel file.

FileInputStream file = new FileInputStream(“file location”);

  • Now open the file in the XSSFworkbook and open get the sheet1.
  • Get the number of rows and columns.
  • Iterate and get each cell value.
  • Check the type of data in the switch case.
  • Here we are given the FORMULA type for reading the cell value in the Formula column.


Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads