Open In App

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

Last Updated : 12 May, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

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 https://mvnrepository.com/ 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.

Code

Java




package GFG_Maven.GFG_MAven;
  
import org.testng.annotations.Test;
import java.io.FileInputStream;
import java.io.IOException;
  
import java.util.Iterator;
  
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
  
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
public class Geeks {
    @Test
    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 = rIterator.next();
                  
                  // For each row, iterate through all the columns
                Iterator<Cell> Cell = row.cellIterator();
                   
                while (Cell.hasNext()) 
                {
                    Cell cell = Cell.next();
                      
                      // Check the cell type
                    switch(cell.getCellType())
                    {
                    case STRING:
                        System.out.print(cell.getStringCellValue());
                        break;
                          
                    case NUMERIC:
                        System.out.print(cell.getNumericCellValue()); 
                        break;
                          
                    case FORMULA:
                        System.out.print(cell.getNumericCellValue());
                        break;
                    }
                    System.out.print("|");
                }
                System.out.println();              
            }
            wb.close();
            file.close();
    }
}


Output

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

Output

 

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.

case FORMULA:
       System.out.print(cell.getNumericCellValue());



Similar Reads

How to Create Formula Cell in Excel Sheet using Java and Apache POI?
In the previous article, we have seen how to read data from the formula cell, here we are going to create the formula cell using Apache POI. [caption width="800"] [/caption] In this article, we are creating an Excel file with three columns consisting of values and the last column is the formula cell which is calculated from the other cells by defin
2 min read
How to Read Data from Password Protected Excel using Java and Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs
2 min read
Opening Existing Excel sheet in Java using Apache POI
Apache POI is a powerful API by which one can read, write and modify any Microsoft document like powerpoint, world, or excel. Apache POI have different classes and method to work upon different MS Office Document. POIFS -It's Stand for "Poor Obfuscation Implementation File System". This component is the basic factor of all other POI elements. It is
2 min read
Java Program to Draw a Shape in Excel Sheet using Apache POI
Apache POI supports customized printing by allowing users to select a range of cells in order to configure the desired print area in a worksheet using Java Program. The top-most shape is the patriarch. This is not visible on the sheet at all. To start drawing you need to call createPatriarch on the HSSFSheet class. Let's take an example to Create a
4 min read
How to Write Data from Excel File into a HashMap using Java and Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs
3 min read
Reading and Writing Data to Excel File in Java using Apache POI
In Java, reading an Excel file is not similar to reading a Word file because of cells in an Excel file. JDK does not provide a direct API to read data from Excel files for which we have to toggle to a third-party library that is Apache POI. Apache POI is an open-source java library designed for reading and writing Microsoft documents in order to cr
5 min read
How to Write Data from HashMap to Excel using Java in Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs
3 min read
How to Fill Background Color of Cells in Excel using Java and Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs
3 min read
Working with Microsoft Excel Using Apache POI and JExcel API with a Maven Project in Java
In the software industry, information needs to be portable and hence any valid data is available in XLS and XLSX formats i.e. excel formats. In order to communicate between excel formats and java, we need to use Apache POI and JExcel APIs. Let us see in this article how to use them via a sample maven project. Necessary dependencies to be used for u
7 min read
Creating Sheets in Excel File in Java using Apache POI
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs
3 min read
Practice Tags :