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!!
QA Automation Expert 