How to add Formulas in Excel in Java

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.0.0</version>
</dependency>
  
  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

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

Below are the steps which explain how to add Formulas in Excel.

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)");

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.

public class FormulaExample {

	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();

		}
	}
}

That’s it! Well Done! Cheers!!

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s