Java Create Formula Excel Cells using Apache POI

Tags: Apache POI Excel

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

  1. Add Apache POI dependencies to Java project
  2. Sample Excel File to Add Formular Cell
  3. How to Set Formular to an Excel Cell in Java
  4. 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();
            }
        }
    }
}

Execute the Java program we have the updated Excel file with a formula cell as the screenshot below.

Java Create Formula Excel Cells using Apache POI

Happy Coding 😊

Java Add Rows to Existing Excel File using Apache POI

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Java Read Password Protected Excel File using Apache POI

Java Apache POI Tutorial

Spring Boot Download Excel File Export from MySQL Database

Spring Boot Web Application Download Excel File

Java Read Excel File using Apache POI

Java Read Excel Workbook from File using Apache POI

Java Read Excel Workbook from InputStream using Apache POI

Java Create Excel File .xlsx using Apache POI

Java Add Sheet to Existing Excel File using Apache POI

Java Remove Sheet from Existing Excel File using Apache POI