Excel Formula

vibssingh

Java Excel Tutorial: Creating Excel with Formulas Using Apache POI

Last Updated On

HOME

In the previous tutorial, I have explained How to update data in existing excel in Java. In this Java Excel tutorial, I will explain how to create an Excel with formula in a Java program. Excel is very excellent in calculating formulas. The Apache POI library provides excellent support for working with formulas in Excel.

I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.

If you are using maven, then you need to add below dependency in pom.xml.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.3.0</version>
</dependency>
  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

To know about various Interfaces and Classes in Excel, please refer this link.

Implementation Steps

Step 1 – Create a blank workbook.

XSSFWorkbook workbook = new XSSFWorkbook();

Step 2 – Import XSSFWorkbook from package.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 3 – Create a sheet and pass name of the sheet.

XSSFSheet sheet = workbook.createSheet("Calculate Salary");

Step 4 – Import XSSFSheet from package.

import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 5 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout. I have created object called Header of XSSFRow.

XSSFRow header = sheet.createRow(0);

Step 6 – Below syntax create new cells within the row at index 0 and set a string value for the cell.

header.createCell(0).setCellValue("Employee_Name");

Step 7 – Below syntax creates a cell at index 4 and sets formula for cell.

dataRow.createCell(4).setCellFormula("B2+C2+D2");

Step 8 – The following line of code sets formula for the cell at the row #1 and column #5 (remember index is 0-based):

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

XSSFRow dataRow = sheet.createRow(1);
dataRow.createCell(5).setCellFormula("SUM(B2:C2)");

Step 9 – Write the workbook to a File and close the workbook

try {

			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File("Salary_Slip.xlsx"));
			workbook.write(out);
			out.close();
			System.out.println("Excel written successfully.");

		} catch (IOException e) {
			e.printStackTrace();

		}

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

Let us see a program where I have created a cell which contains the formula.

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;
import java.io.IOException;

public class FormulaExcelDemo {
    
    public static void main(String[] args) {

        // Create object of XSSFWorkbook class
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Create object of XSSFSheet class
        XSSFSheet sheet = workbook.createSheet("Calculate Salary");

        // Create Header row using XSSFRow class
        XSSFRow header = sheet.createRow(0);
        header.createCell(0).setCellValue("Employee_Name");
        header.createCell(1).setCellValue("Base_Salary");
        header.createCell(2).setCellValue("Variable_Pay");
        header.createCell(3).setCellValue("Other_Benefits");
        header.createCell(4).setCellValue("Total Salary");
        header.createCell(5).setCellValue("Base_Variable Salary");

        XSSFRow dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue("George");
        dataRow.createCell(1).setCellValue(5000);
        dataRow.createCell(2).setCellValue(650);
        dataRow.createCell(3).setCellValue(1200);

        // Set formula
        dataRow.createCell(4).setCellFormula("B2+C2+D2");
        dataRow.createCell(5).setCellFormula("SUM(B2:C2)");

        try {

            // Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("Salary_Slip.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully.");

        } catch (IOException e) {
            e.printStackTrace();

        }
    }
}

The output of the above program is

Updated Excel

That’s it! Well Done! Cheers!!

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!