Excel file are also used to perform various stats in which formula plays are very important role. So when youa re creating a excel file using the POI library and you want to add formula’s to particular Cells, how do we do that?
Let us see how we can add Formula to excel using Apache POI.
Add Formula to Cell
Now we will add a formula to already existing example that we had created in “Write Excel File in Java using Apache POI”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
package com.kscodes.sampleproject; import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class FormulaInExcelUsingPOI { public static void main(String[] args) { // Create instance of the class and call the writeExcelFile() FormulaInExcelUsingPOI formulaInExcelUsingPOI = new FormulaInExcelUsingPOI(); formulaInExcelUsingPOI.writeExcelFile("C:\\kscodes\\POIFormulaExample.xlsx"); } public void writeExcelFile(String fileName) { XSSFWorkbook workbook = null; FileOutputStream fileOutputStream = null; try { int rowNum = 0; fileOutputStream = new FileOutputStream(new File(fileName)); // Create a Workbook workbook = new XSSFWorkbook(); // Create an Empty Sheet XSSFSheet sheet = workbook.createSheet("Employee Details - POIFormulaExample"); // Create a Row and write the Row Header details Row row = sheet.createRow(rowNum++); List<String> rowHeaders = getRowHeaders(); int cellNum = 0; for (String rowHeader : rowHeaders) { Cell cell = row.createCell(cellNum); cell.setCellValue(rowHeader); cellNum++; } // Get Data and Write into the Excel List<Employee> employeeDetails = getEmployeeDetails(); for (Employee employee : employeeDetails) { Row detailsRow = sheet.createRow(rowNum++); cellNum = 0; writeIntoCell(detailsRow, employee.getName(), cellNum++); writeIntoCell(detailsRow, employee.getYearsOfExp(), cellNum++); writeIntoCell(detailsRow, employee.getSalary(), cellNum++); } // Now Add Formula to Calculate the Total Salary Row totalSalRow = sheet.createRow(rowNum++); Cell cell = totalSalRow.createCell(0); cell.setCellValue("Total"); cell = totalSalRow.createCell(2); cell.setCellFormula("SUM(C2:C4)"); workbook.write(fileOutputStream); System.out.println("Excel File created and written !!!!"); } catch (Exception e) { System.out.println("An Exception occured while writing Excel" + e); } finally { try { if (fileOutputStream != null) { fileOutputStream.close(); } if (workbook != null) { workbook.close(); } } catch (Exception e) { } } } private List<Employee> getEmployeeDetails() { List<Employee> employeeDetails = new ArrayList<>(); employeeDetails.add(new Employee("John Doe", 4500,4)); employeeDetails.add(new Employee("Steve Smith", 7500, 6)); employeeDetails.add(new Employee("Mr Junior", 2500,1)); return employeeDetails; } private List<String> getRowHeaders() { List<String> rowHeaders = new ArrayList<>(); rowHeaders.add("Name"); rowHeaders.add("Years Of Exp"); rowHeaders.add("Salary"); return rowHeaders; } private void writeIntoCell(Row row, Object value, int cellNum) { Cell cell = row.createCell(cellNum++); if (value instanceof String) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue((String) value); } else if (value instanceof Long) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue((Long) value); } else if (value instanceof Integer) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue((Integer) value); } } } |
Output
