How to Create a Formula in Excel using Java?
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:
Java
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)
