Quick introduction to Apache POI

This chapter shows how to apply different formula on cells. In excel formula use pass locations or values dynamically. Desired result is shown once formula is executed.

• References: single cell & area, 2D & 3D, relative & absolute
• Literals: number, text, boolean, error and array
• Operators: arithmetic and logical, some region operators
• Built-in functions: there are over 350 recognised, 280 others
• Add-in functions: Analysis Toolpack offers 24 of them
• Array Formulas: these are used through Sheet.setArrayFormula() and Sheet.removeArrayFormula()

• Manipulating table formulas (In Excel, formulas that look like "{=...}" with curly brackets as opposed to "=...")
• Region operators: union, intersection
• Parsing of previously uncalled add-in functions
• Preservation of whitespace in formulas (when POI manipulates them)

Table shows list of basic formula:

Supported function list can be found here 

Example code to show the use of formulas. We are using version 3.11 of Apache POI 

package com.sc;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;



/**
 * Created by SubjectCoach.com on 3/12/2015.
 * POI version 3.11
 */
public class FormulaTest
{
    public static void main(String[] args)throws Exception
    {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("FormulaTestSheet");
        XSSFRow row = sheet.createRow(1);
        XSSFCell cell = row.createCell(1);
        cell.setCellValue("Number1 is" );
        cell = row.createCell(2);
        cell.setCellValue(13);
        row = sheet.createRow(2);
        cell = row.createCell(1);
        cell.setCellValue("Number2 is");
        cell = row.createCell(2);
        cell.setCellValue(39);
        row = sheet.createRow(3);
        cell = row.createCell(1);
        cell.setCellValue("Division result");
        cell = row.createCell(2);
        // lets add a multiplication formula
        cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("C3/C2" );
        cell = row.createCell(3);
        cell.setCellValue("Divide C3 by C2");
        row = sheet.createRow(4);
        cell = row.createCell(1);
        cell.setCellValue("Square Root result");
        cell=row.createCell(2);
        // POWER formula
        cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("SQRT(C3)");
        cell = row.createCell(3);
        cell.setCellValue("SQUARE ROOT(C3)");
        workbook.getCreationHelper()
                .createFormulaEvaluator()
                .evaluateAll();
        try {
            FileOutputStream out = new FileOutputStream(
                    new File("C:/poi/formulaexample.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("Formula test workbook sheet processed ");
        }
        catch(Exception e) {
            System.out.print(e.getMessage());
        }
    }
}

As usual we will save this program in FormulaTest.java file, compile and thereafter execute it

Output will be like this