Working with Formula Cells in Excel using Apache POI

In this Java Excel tutorial, you will learn how to program with formulas in Excel files.

Excel is very excellent in calculating formulas. And perhaps almost Excel documents have formulas embedded. Therefore, it’s trivial that on a fine day you have to deal with formulas when reading and writing Excel documents from Java. Good news is that the Apache POI library provides excellent support for working with formulas in Excel. I have used it in my project so that I write this article to share with you my experience in dealing with formulas in Excel files.

1. Setting Formula for Cells

Here I show you how simple it is to setting a formula for a cell in an Excel worksheet. Suppose cell is an object of type Cell, thus the code to set a formula to it looks like this:

cell.setCellFormula("SUM(D2:D10)");

That tells Excel sets a formula that calculates and stores sum of the cells in positions from D2 to D10 to this cell. Note that the formula is exact as we type in Excel but without the equal sign (=) at the beginning.

The following line of code sets formula for the cell at the row #3 and column #4 (remember index is 0-based):

sheet.getRow(3).getCell(4).setCellFormula("SUM(D2:D10)");

In the above line, note that you should make sure that the cell at position (3, 4) does exist, otherwise you get a NullPointerException.

In case the column does not exist (but the row does), use the following code:

sheet.getRow(3).createCell(4).setCellFormula("SUM(D2:D10)

In case both the column and row do not exist, use the following code:

sheet.createRow(3).createCell(4).setCellFormula("SUM(D2:D10)");

The following code snippet does the same as the above, but more verbose:

Row row = sheet.createRow(3);
Cell cell = row.createCell(4);
cell.setCellFormula("SUM(D2:D10)");

And let’s look at a sample program that updates formula for an existing Excel document. Suppose we have an Excel document looks like this:

Note that the formula of the cell C9 is SUM(C5:C7). The following program will update this formula to add 10% to the sum (i.e. adding 10% tax):

package net.codejava.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This program demonstrates reading an Excel document, add a formula
 * to the first sheet, and then save the file. 
 * @author www.codejava.net
 *
 */
public class ExcelFormulaUpdateDemo {

	public static void main(String[] args) throws IOException {
		String excelFilePath = "JavaBooks4Beginner.xlsx";
		FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

		Workbook workbook = new XSSFWorkbook(inputStream);
		Sheet sheet = workbook.getSheetAt(0);

		sheet.getRow(8).getCell(2).setCellFormula("SUM(C5:C7) + SUM(C5:C7) * 0.1");
		
		inputStream.close();
		
		FileOutputStream outputStream = new FileOutputStream(excelFilePath);
		workbook.write(outputStream);
		workbook.close();
		outputStream.close();
	}

}

And here’s the result after running the above program:

For setting formula for a cell when creating a new Excel file from scratch, the following program is a good example:

package net.codejava.excel;

import java.io.FileOutputStream;
import java.io.IOException;

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;

/**
 * This program demonstrates creating an Excel document with a formula cell.
 * @author www.codejava.net
 *
 */
public class ExcelFormulaCreateDemo {

	public static void main(String[] args) throws IOException {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("Java Books");
		
		Object[][] bookData = {
				{"Head First Java", "Kathy Serria", 79},
				{"Effective Java", "Joshua Bloch", 36},
				{"Clean Code", "Robert martin", 42},
				{"Thinking in Java", "Bruce Eckel", 35},
		};

		int rowCount = 0;
		
		for (Object[] aBook : bookData) {
			Row row = sheet.createRow(++rowCount);
			
			int columnCount = 0;
			
			for (Object field : aBook) {
				Cell cell = row.createCell(++columnCount);
				if (field instanceof String) {
					cell.setCellValue((String) field);
				} else if (field instanceof Integer) {
					cell.setCellValue((Integer) field);
				}
			}
			
		}
		
		Row rowTotal = sheet.createRow(rowCount + 2);
		Cell cellTotalText = rowTotal.createCell(2);
		cellTotalText.setCellValue("Total:");
		
		Cell cellTotal = rowTotal.createCell(3);
		cellTotal.setCellFormula("SUM(D2:D5)");
		
		try (FileOutputStream outputStream = new FileOutputStream("JavaBooks4BeginnerPrice.xlsx")) {
			workbook.write(outputStream);
			workbook.close();
		}
	}

}

Result:

2. Evaluating Formula Cells

When using Apache POI to update cells on which the formulas dependent, we need to evaluate the formulas to ensure the results are updated, because the Excel file format stores a “cached” result for every formula along with the formula itself.

The Apache POI API provides the FormulaEvaluatorclass that evaluates formula in a cell. The following line obtains an instance of this evaluator:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

This class has 4 different methods for evaluating Excel formulas:

• evaluate(Cell cell): evaluates the formula of a given cell and returns the result in a CellValue object. This method does not modify the contents of the original cell.
• evaluateFormulaCell(Cell cell): evaluates the formula of a given cell and saves the result of the formula. The cell remains a formula cell. This method returns the type of the formula result.
• evaluateInCell(Cell cell): evaluates the formula and saves the formula result back into the cell. The cell is not a formula cell any more. This method returns the cell itself.
• evaluateAll(): loops over all cells in all sheets of the associated workbook and evaluates all formula cells. The results are saved. The formula cells remain as formula cells. The non-formula cells are not affected.

Now, let’s see some examples.

The following code snippet demonstrates evaluating a formula cell using the evaluate() method:

String excelFilePath = "JavaBooks4Beginner.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

CellReference cellReference = new CellReference("C9");
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());

CellValue cellValue = evaluator.evaluate(cell);

switch (cellValue.getCellType()) {
	case Cell.CELL_TYPE_STRING:
		System.out.print(cellValue.getStringValue());
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		System.out.print(cellValue.getBooleanValue());
		break;
	case Cell.CELL_TYPE_NUMERIC:
		System.out.print(cellValue.getNumberValue());
		break;
}


workbook.close();
inputStream.close();

Similarly, the following code snippet shows the usage of the evaluateInCell() method:

Cell cell = row.getCell(cellReference.getCol());

switch (evaluator.evaluateInCell(cell).getCellType()) {
	case Cell.CELL_TYPE_STRING:
		System.out.print(cell.getStringCellValue());
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		System.out.print(cell.getBooleanCellValue());
		break;
	case Cell.CELL_TYPE_NUMERIC:
		System.out.print(cell.getNumericCellValue());
		break;
}

The following code snippet demonstrates the usage of the evaluateFormulaCell() method:

int cellType = evaluator.evaluateFormulaCell(cell);

switch (cellType) {
	case Cell.CELL_TYPE_STRING:
		System.out.print(cell.getStringCellValue());
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		System.out.print(cell.getBooleanCellValue());
		break;
	case Cell.CELL_TYPE_NUMERIC:
		System.out.print(cell.getNumericCellValue());
		break;
}

And evaluate all formula cells:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

evaluator.evaluateAll();

That's some Java code examples for programming with formula cells using Apache POI API. If you want to learn more in-depth about Java programming, this Java Masterclass course is recommended.

Related Java Excel Tutorials:

• How to Write Excel Files in Java using Apache POI
• How to Read Excel Files in Java using Apache POI
• Java Example to Read Password-protected Excel Files Using Apache POI
• Java Example to Update Existing Excel Files Using Apache POI

References:

• Apache POI - Formula Evaluation
• Apache POI - Formula Support

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

ExcelFormulasDemo.zip

[Excel Formulas Java Code Examples]

12 kB