Partner – Moderne – NPI EA (cat=Spring Boot)
announcement - icon

Refactor Java code safely — and automatically — with OpenRewrite.

Refactoring big codebases by hand is slow, risky, and easy to put off. That’s where OpenRewrite comes in. The open-source framework for large-scale, automated code transformations helps teams modernize safely and consistently.

Each month, the creators and maintainers of OpenRewrite at Moderne run live, hands-on training sessions — one for newcomers and one for experienced users. You’ll see how recipes work, how to apply them across projects, and how to modernize code with confidence.

Join the next session, bring your questions, and learn how to automate the kind of work that usually eats your sprint time.

Course – Black Friday 2026 – NPI EA – main – before_post_content
announcement - icon

Yes, we're now running our Black Friday Sale. All Courses are 33% off until 1st December, 2026 at 17:00 Romania time:

>> EXPLORE ACCESS NOW

Course – Black Friday 2026 – NPI – main – before_post_content
announcement - icon

Yes, we're now running our Black Friday Sale. All Courses are 33% off until 1st December, 2026 at 17:00 Romania time:

>> EXPLORE ACCESS NOW

1. Overview

In this quick tutorial, we’re going to talk about how we can set formulas in a Microsoft Excel spreadsheet using Apache POI through a simple example.

2. Apache POI

Apache POI is a popular open-source Java library that provides programmers with APIs to create, modify, and display MS Office files.

It uses Workbook to represent an Excel file and its elements. A Cell in an Excel file can have different types such as FORMULA.

In order to see Apache POI in action, we’re going to set a formula to subtract the sum of the values in columns A and B in an Excel file. The linked file contains the data below:

Sample Excel File

3. Dependencies

First, we need to add the POI dependency to our project pom.xml file. To work with Excel 2007+ workbooks, we should use poi-ooxml:

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

Note that for earlier versions of Excel, we should use the poi dependency instead.

4. Cell Lookup

To begin, let’s first open our file and construct the appropriate workbook:

FileInputStream inputStream = new FileInputStream(new File(fileLocation));
XSSFWorkbook excel = new XSSFWorkbook(inputStream);

Then, we need to create or look up the cell we’re going be using. Using the data shared earlier, we want to edit cell C1.

That’s on the first sheet and the first row, and we can ask POI for the first blank column:

XSSFSheet sheet = excel.getSheetAt(0);
int lastCellNum = sheet.getRow(0).getLastCellNum();
XSSFCell formulaCell = sheet.getRow(0).createCell(lastCellNum + 1);

5. Formulas

Next, we want to set a formula on the cell we’ve looked up.

As stated earlier, let’s subtract the sum of column B from the sum of column A. In Excel, this would be:

=SUM(A:A)-SUM(B:B)

And we can write that into our formulaCell with the setCellFormula method:

formulaCell.setCellFormula("SUM(A:A)-SUM(B:B)");

Now, this won’t evaluate the formula. To do that, we’ll need to use POI’s XSSFFormulaEvaluator:

XSSFFormulaEvaluator formulaEvaluator = 
  excel.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateFormulaCell(formulaCell);

The result will be set in the first Cell of the next empty column:Sample Excel File2

As we can see, the result is calculated and saved in the first cell of column C. Also the formula is shown in the formula bar.

Note that the FormulaEvaluator class provides us with other methods to evaluate FORMULA in Excel workbooks, like evaluateAll, which will loop over all cells and evaluate them.

6. Conclusion

In this tutorial, we showed how to set a formula on a cell in an Excel file in Java using the Apache POI API.

The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.
Partner – Moderne – NPI EA (tag=Refactoring)
announcement - icon

Modern Java teams move fast — but codebases don’t always keep up. Frameworks change, dependencies drift, and tech debt builds until it starts to drag on delivery. OpenRewrite was built to fix that: an open-source refactoring engine that automates repetitive code changes while keeping developer intent intact.

The monthly training series, led by the creators and maintainers of OpenRewrite at Moderne, walks through real-world migrations and modernization patterns. Whether you’re new to recipes or ready to write your own, you’ll learn practical ways to refactor safely and at scale.

If you’ve ever wished refactoring felt as natural — and as fast — as writing code, this is a good place to start.

Course – Black Friday 2026 – NPI EA – main – after_post_content

announcement - icon

Yes, we're now running our Black Friday Sale. All Courses are 33% off until 1st December, 2026 at 17:00 Romania time:

>> EXPLORE ACCESS NOW

Course – Black Friday 2026 – NPI – main – after_post_content

announcement - icon

Yes, we're now running our Black Friday Sale. All Courses are 33% off until 1st December, 2026 at 17:00 Romania time:

>> EXPLORE ACCESS NOW