In this post, let us look at how to create a formula cell in an excel sheet using Apache POI.
Here, we will create an Excel file with three columns of values, and the last column will be the formula cell, which will be calculated from the other cells by defining a specific formula.
We will create the formula cell by Multiplying all of the other data in the cells.
Apache POI library in Java:
ApachePOI is an abbreviation for Poor Obfuscation Implementation, a Java API for reading and writing Microsoft documents. It includes the Wordbook, Sheet, Row, and Cell classes and interfaces. Apache POI may be used to access files with the ‘xlsx’ extension as well as files with the ‘xlsx’ extension.
Apache POI is a free and open-source Java library for creating and manipulating Microsoft Office file formats. The following file formats should be able to be created, modified, and displayed/read using POI. For example, because java does not include built-in support for interacting with Excel files, we must search for open-source APIs to complete the job.
Classes Used:
XSSFWorkbook: XSSFWorkbook. It is a class that represents both high-level and low-level Excel file formats. It is part of the org. apache. xssf.
XSSFSheet:
public class XSSFSheet extends POIXMLDocumentPart implements Sheet
It is a High-level representation of a SpreadsheetML worksheet. Sheets are the central structures within a workbook and are where the majority of the spreadsheet work is done. The worksheet, which is displayed as a grid of cells, is the most used type of sheet. Text, numbers, dates, and formulas can all be entered into worksheet cells. Formatting cells is also possible.
FileOutputStream:
FileOutputStream(File file) class creates a file output stream to write to the file provided File object.
FileInputStream:
A FileInputStream reads bytes from a file in the file system. The files that are available are determined by the host environment. FileInputStream is intended for reading raw bytes streams such as image data. Consider using FileReader to read character streams.
Pre-requisites:
We required the following software on your computers to work with these.
- Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
- Create a Maven project.
- Add apache poi dependency.
Creating Formula Cell in Excel Sheet using Java and Apache POI
Here we use STS(Spring Tool Suite) to create a formula cell in an excel sheet
You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.
STS:
Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.
Note:
If you are using MyEclipse then create a maven project.
STS directly imports all the classes from maven if we just add dependencies.
Step#1: Open STS(SpringToolSuite) and go to file, create a spring stater project say CreateFormulaCell in a package com.sheetstips.
Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.
Step#3: Open the pom.xml file and add the following apache poi dependency.
https://mvnrepository.com/
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.12</version> </dependency> <dependency> <groupId>org.apache.poi</ groupId> <artifactId>poi-ooxml</ artifactId> <version>3.12</version> </dependency>
Step#4: Create a new class say CreatingFormulaCell in the src/main/java folder with the same package com.sheetstips.
Step#5: Write the below code in the above CreatingFormulaCell class
Approach:
- Import Excel interface using the import keyword.
- Import all the required libraries using import keyword.
- Import generic java libraries using the import keyword
- Import File libraries using the import keyword
- Import apache poi using the import keyword
- Inside the Main driver method, Create a new HSSFWorkbook object.
- Create a new worksheet in the above workbook object by passing the sheet name as an argument to the createSheet() function(Sheet1 by default).
- Store it in another variable.
- Create the first row using the createRow() function(row numbers starts from 0 index).
- Set the cell values to the first column(A1) of the above corresponding first row using the setCellValue() function by passing the cell value as an argument to it.
- Similarly set the 2nd and 3rd column cell values(B1, C1) of the first row.
- Create the 4th cell which will be calculated from the other cells by defining a specific formula.
- Here we are multiplying all the other cell values and storing it as a new cell
- Create an object for the FileOutputStream by passing the output excel file path as an argument to it to place the output file in that location
- Write the data which is added in the above steps i.e write the outputFile to the workbook Object using the write() function
- Close the above-opened output file using the close() function.
- The Exit of the Program.
Below is the implementation:
package com.sheetstips;
// Import Excel interface using the import keyword
// Import generic java libraries using the import keyword
// Import File libraries using the import keyword
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
// Import apache poi using the import keyword
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;
public class CreatingFormulaCell {
// Main driver method
public static void main(String[] args) throws Exception
{
// Create a new HSSFWorkbook object.
XSSFWorkbook workbookObj=new XSSFWorkbook();
// Create a new worksheet in the above workbook object by passing
// the sheet name as an argument to the createSheet() function(Sheet1 by default)
// Store it in another variable
XSSFSheet worksheet=workbookObj.createSheet("FormulaCell");
// Create the first row using the createRow() function(row numbers starts from 0 index)
XSSFRow row=worksheet.createRow(0);
// Set the cell values to the first column(A1) of the above corresponding first row
// using the setCellValue() function b passing the cell value as an argument to it.
row.createCell(0).setCellValue(2);
// Similarly set the 2nd and 3rd column cell values(B1, C1) of the first row
row.createCell(1).setCellValue(5);
row.createCell(2).setCellValue(3);
// Create the 4th cell which will be calculated from the other cells by
// defining a specific formula.
// Here we are multiplying all the other cell values and storing it as a new cell
row.createCell(3).setCellFormula("A1*B1*C1");
// Create an object for the FileOutputStream by passing the output excel file
// path as an argument to it to place the output file in that location
FileOutputStream outputFile = new FileOutputStream("FormulaCellExcel.xlsx");
// Write the data which is added in the above steps
// i.e write the outputFile to the workbook Object using the write() function
workbookObj.write(outputFile);
// Close the above opened outputfile using the close() function
outputFile.close();
System.out.println("The new FormulaCellExcel.xlsx file is created Sucessfully!!");
}
}
Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App
Output:
The new FormulaCellExcel.xlsx file is created Sucessfully!!
