Apache POI is a popular open-source Java library that provides programmers with APIs for creating, modifying, and editing MS Office files. Excel is very excellent at calculating formulas. And perhaps most Excel documents have formulas embedded. Therefore, it’s trivial that on a fine day, you have to deal with formulas when reading and writing Excel documents from Java. The good news is that the Apache POI library provides excellent support for working with formulas in Excel.
Creating Formula in Excel using Java
We will use the Apache POI library in the project to write this article to share with you my experience in dealing with formulas in Excel files.
Below is the implementation of the above topic:
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
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 GFG {
public static void main(String[] args) throws Exception
{
// Naming WorkBook
String excelfilename = "GeeksForGeeks.xlsx" ;
// Creating Workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Creating Spreadsheet
XSSFSheet spreadsheet
= workbook.createSheet( "formula" );
// Create a row object using XSSFRow for creating
// Row
XSSFRow row;
// Create a column object using XSSFCell for
// creating Cell
XSSFCell cell;
// Creating row and start from 0 index value
row = spreadsheet.createRow( 1 );
// Creating column and start from 0 index value
cell = row.createCell( 1 );
// set Value of cell to apply formula
cell.setCellValue( "Value of X = " );
cell = row.createCell( 2 );
cell.setCellValue( 5.0 );
row = spreadsheet.createRow( 2 );
cell = row.createCell( 1 );
cell.setCellValue( "Value of Y = " );
cell = row.createCell( 2 );
cell.setCellValue( 10 );
row = spreadsheet.createRow( 3 );
cell = row.createCell( 1 );
cell.setCellValue( "SUM = " );
cell = row.createCell( 2 );
// Creating SUM formula
cell.setCellFormula( "SUM(C2:C3)" );
cell = row.createCell( 3 );
cell.setCellValue( "SUM(C2:C3)" );
row = spreadsheet.createRow( 4 );
cell = row.createCell( 1 );
cell.setCellValue( "POWER =" );
cell = row.createCell( 2 );
// Create POWER formula
cell.setCellFormula( "POWER(C2,C3)" );
cell = row.createCell( 3 );
cell.setCellValue( "POWER(C2,C3)" );
row = spreadsheet.createRow( 5 );
cell = row.createCell( 1 );
cell.setCellValue( "MAX = " );
cell = row.createCell( 2 );
// Creating MAX formula
cell.setCellFormula( "MAX(C2,C3)" );
cell = row.createCell( 3 );
cell.setCellValue( "MAX(C2,C3)" );
row = spreadsheet.createRow( 6 );
cell = row.createCell( 1 );
cell.setCellValue( "ABS Value = " );
cell = row.createCell( 2 );
// Creating Absolute number formula
cell.setCellFormula( "ABS(C2)" );
cell = row.createCell( 3 );
cell.setCellValue( "ABS(C2)" );
row = spreadsheet.createRow( 7 );
cell = row.createCell( 1 );
cell.setCellValue( "PRODUCT = " );
cell = row.createCell( 2 );
// Creating SQRT formula
cell.setCellFormula( "PRODUCT(C2,C3)" );
cell = row.createCell( 3 );
cell.setCellValue( "PRODUCT(C2,C3)" );
// This is a helpful wrapper around looping over
// allcells, and calling evaluateFormulaCell on each
// one.
workbook.getCreationHelper()
.createFormulaEvaluator()
.evaluateAll();
// To make Auto size column
spreadsheet.autoSizeColumn( 1 );
spreadsheet.autoSizeColumn( 2 );
spreadsheet.autoSizeColumn( 3 );
try {
// Place the output file in default location and
// also kept in try catch block
FileOutputStream outputfile
= new FileOutputStream(excelfilename);
// Write to workbook
workbook.write(outputfile);
// Close the output file
outputfile.close();
// Display message for console window when
// program is successfully executed
System.out.println(excelfilename
+ " is written successfully" );
}
catch (FileNotFoundException e) {
// Display error message for console window when
// program is not successfully executed
System.out.println( "ERROR!! " + e.getMessage());
}
finally {
workbook.close();
}
}
} |
Output: (On the console window)
1. When the program is successfully executed.
GeeksForGeeks.xlsx is written successfully
2. When the program is not successfully executed.
ERROR!! GeeksForGeeks.xlsx
(The process cannot access the file because it is being used by another process)
Output: Workbook(excel file)