Java Create Formula Excel Cells using Apache POI

In this Java Apache POI tutorial we learn how to add a formula Excel cell to an existing Excel file in Java using the Apache POI library.

Table of contents

Add Apache POI dependencies to Java project
Sample Excel File to Add Formular Cell
How to Set Formular to an Excel Cell in Java
Step by Step How to Add Formular Cell to Existing Excel File in Java

Add Apache POI dependencies to Java project

If you use Gradle build project, add the following dependencies to the build.gradle file.

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

If you use Maven build project, add the following dependencies to the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Sample Excel File to Add Formular Cell

For example, we have an Excel file with the data as the following screenshot and located at D:\SimpleSolution\Data\Sample.xlsx

Java Create Formula Excel Cells using Apache POI

How to Set Formular to an Excel Cell in Java

In Java, by using the Apache POI library we can set formula for a specified Excel cell using the Cell.setCellFormula(String formula) method as Java code below.

cell.setCellFormula("SUM(B1:B3)");

Step by Step How to Add Formular Cell to Existing Excel File in Java

With a given Excel file we can follow these step to add a new formular cell using the Apache POI API.

Step 1: use the WorkbookFactory.create(InputStream inp) method to open an Excel Workbook using FileInputStream.
Step 2: use the Workbook.getSheetAt(int index) method to get the Sheet object.
Step 3: use the Sheet.getRow(int rownum) method to get Row object by row index value.
Step 4: use the Row.createCell(int column) method to instantiate a new Cell object.
Step 5: use Cell.setCellFormula(String formula) method to set formula to specified Cell object.
Step 6: use the Workbook.write(OutputStream stream) method to write the updated Excel file using FileOutputStream.

AddFormulaCellExample1.java

import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStream;

public class AddFormulaCellExample1 {
    public static void main(String... args) {
        String filePath = "D:\\SimpleSolution\\Data\\Sample.xlsx";
        InputStream inputStream = null;
        Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            workbook = WorkbookFactory.create(inputStream);

            // Get first sheet of Excel file
            Sheet sheet = workbook.getSheetAt(0);

            // Get row
            Row row = sheet.getRow(3);
            // Create a new cell
            Cell cell = row.createCell(1);
            // Set formula cell value
            cell.setCellFormula("SUM(B1:B3)");

            // Write updated Excel file
            outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
                workbook.close();
                outputStream.close();
            }catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }
}