How to Calculate Excel Formulas in Java application?

Microsoft Excel’s powerful formula capabilities are essential for performing complex calculations, data analysis, and creating dynamic reports. But what if you could automate these formulaic operations using a Java library? That’s where Apache POI comes into play. In this blog post, we’ll show how to use the Apache POI library to calculate Excel Formulas from within your Java application and save the workbook as XLSX file.

Understanding Formulas and Calculations

Formulas are at the heart of Excel’s functionality. They allow you to perform mathematical operations, manipulate data, and create relationships between different cells. Apache POI empowers Java developers to generate and manipulate these formulas programmatically, enabling automation and customization.

Setting Up Your Project

Before we dive into the specifics of formulas, let’s set up our project to work with Apache POI. You’ll need to include the appropriate POI libraries in your Java project. You can download the libraries from the Apache POI website or add them through a dependency management tool like Maven or Gradle. Check our Apache POI API Overview article for further information on setting up the development environment for your project.

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

Creating Basic Formulas

Let’s start with a simple example. Imagine you have an Excel sheet with two columns containing numbers. You want to calculate the sum of these numbers in a third column. Here’s how you can achieve this using Apache POI:

Import the Necessary Classes

Start by importing the required classes from the Apache POI library, such as Workbook, Sheet, and Row.

Access the Desired Cells

Use the getCell() method to access the cells containing the numbers you want to sum.

Create a Formula

Utilize the createFormula() method to generate the formula. In this case, it will be something like SUM(A2:B2).

Set the Formula to the Target Cell

Assign the formula to the cell where you want the result to appear using the setCellFormula() method.

Evaluate the Formula

After setting the formula, use the evaluateFormulaCell() method to calculate and populate the result.

Excel Formulas using Apache POI Java Library

Let’s have a look at the following code sample which uses:

ADD formula to add two values in a cell
POWER formula to calculate the POWER of a value in a cell
MAX formula to get the maximum value of a cell
FACT formula to calculate the factorial of a value in a cell
SQRT formula to calculate the Square Root of a value in a cell

The following code sample shows the use of these Excel formulas from within your Java application.

XSSFWorkbook wb = new XSSFWorkbook(); 
XSSFSheet spreadsheet = wb("formula");
XSSFRow row = spreadsheet.createRow(1);
XSSFCell cell = row.createCell(1);
cell.setCellValue("A = ");
cell = row.createCell(2);
cell.setCellValue(2);
row = spreadsheet.createRow(2);
cell = row.createCell(1);
cell.setCellValue("B = ");
cell = row.createCell(2);
cell.setCellValue(4);
row = spreadsheet.createRow(3);
cell = row.createCell(1);
cell.setCellValue("Total = ");
cell = row.createCell(2);
// Create SUM formula
cell.setCellFormula("SUM(C2:C3)");
cell = row.createCell(3);
cell.setCellValue("SUM(C2:C3)");
row = spreadsheet.createRow(4);
cell = row.createCell(1);
cell.setCellValue("POWER =");
cell=row.createCell(2);
// Create POWER formula
cell.setCellFormula("POWER(C2,C3)");
cell = row.createCell(3);
cell.setCellValue("POWER(C2,C3)");
row = spreadsheet.createRow(5);
cell = row.createCell(1);
cell.setCellValue("MAX = ");
cell = row.createCell(2);
// Create MAX formula
cell.setCellFormula("MAX(C2,C3)");
cell = row.createCell(3);
cell.setCellValue("MAX(C2,C3)");
row = spreadsheet.createRow(6);
cell = row.createCell(1);
cell.setCellValue("FACT = ");
cell = row.createCell(2);
// Create FACT formula
cell.setCellFormula("FACT(C3)");
cell = row.createCell(3);
cell.setCellValue("FACT(C3)");
row = spreadsheet.createRow(7);
cell = row.createCell(1);
cell.setCellValue("SQRT = ");
cell = row.createCell(2);
// Create SQRT formula
cell.setCellFormula("SQRT(C5)");
cell = row.createCell(3);
cell.setCellValue("SQRT(C5)");
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
FileOutputStream out = new FileOutputStream("formula.xlsx");
wb.write(out);
out.close();
System.out.println("fromula.xlsx written successfully");

Conclusion

Apache POI unlocks the potential to automate and customize Excel formulas and calculations through Java code. Whether you’re building financial reports, data analysis tools, or any application that involves Excel manipulation, Apache POI’s formula capabilities are a powerful addition to your toolkit. By understanding how to create, evaluate, and manage formulas using Apache POI, you can streamline your workflows, enhance accuracy, and save valuable time.