Working with formulas in excel using Apache POI in java

Java Apache POI tutorial:

    Read excel files in java using POI Write excel files in java using POI Working with formula in excel using POI How to set style in excel using POI
In this post, we will see how to set and evaluate formulas in excel using Apache POI.

Set formula for a cell:

For setting a formula for cell, you need to use following code:
 // Setting cell formula and cell type
 Cell cell = row.createCell(2);
 cell.setCellFormula("SUM(C2:C5)");
 cell.setCellType(Cell.CELL_TYPE_FORMULA);
We will use same code which we have used to write CountriesDetails.xlsx in previous post and add row for total population and evaluate it using formula.

Java program:

package org.arpit.java2blog;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

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 WriteExcelMain {

 public static void main(String[] args) throws IOException {
  writeFileUsingPOI();
 }

 public static void writeFileUsingPOI() throws IOException 
 {
  //create blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook(); 

  //Create a blank sheet
  XSSFSheet sheet = workbook.createSheet("Country");

  ArrayList<Object[]> data=new ArrayList<Object[]>();
  data.add(new String[]{"Country","Capital","Population"});
  data.add(new Object[]{"India","Delhi",10000});
  data.add(new Object[]{"France","Paris",40000});
  data.add(new Object[]{"Germany","Berlin",20000});
  data.add(new Object[]{"England","London",30000});


  //Iterate over data and write to sheet
  int rownum = 0;
  for (Object[] countries : data)
  {
   Row row = sheet.createRow(rownum++);

   int cellnum = 0;
   for (Object obj : countries)
   {
    Cell cell = row.createCell(cellnum++);
    if(obj instanceof String)
     cell.setCellValue((String)obj);
    else if(obj instanceof Double)
     cell.setCellValue((Double)obj);
    else if(obj instanceof Integer)
     cell.setCellValue((Integer)obj);
   }
  }
  Row rowGap = sheet.createRow(rownum++);
  Row row = sheet.createRow(rownum++);
  Cell cellTotal = row.createCell(0);
  cellTotal.setCellValue("Total Population");
  
  // Setting cell formula and cell type
  Cell cell = row.createCell(2);
  cell.setCellFormula("SUM(C2:C5)");
  cell.setCellType(Cell.CELL_TYPE_FORMULA);
  try
  {
   //Write the workbook to the file system
   FileOutputStream out = new FileOutputStream(new File("CountriesDetails.xlsx"));
   workbook.write(out);
   out.close();
   System.out.println("CountriesDetails.xlsx has been created successfully");
  } 
  catch (Exception e) 
  {
   e.printStackTrace();
  }
  finally {
   workbook.close();
  }
 }


}

When you run above program, you will get following output:
CountriesDetails.xlsx has been created successfully

Lets see content of CountriesDetails.xlsx now.

We have calculated total population using sum(c2:c5) in c7.

Formula evaluators:

When you update any cell which has impact on formula, you need to reevaluate the cell.
Apache poi API has provided FormulaEvalutor class to evaluate formula.

It has four method to evaluate formula:
1) public void evaluateAll()
It evaluates all cell which presents in workbook and it recalculates all formula and save the result and formula cells remains formula cells too. If cell do not have formula, it remains the same.

2) CellValue evaluate()
It evaluates formula cell and returns its cell value, it does not change original content of cell.

3) int evaluateFormulaCell(Cell cell)
It evaluates formula cell and returns its cell type. It saves calculated value. Cell remains as formula cell.

4) Cell evaluateInCell(Cell cell)
It evaluates formulas and return Cell object . It updates formula cell with latest value and cell will no longer formula cell.

Example for evaluate method:

 package org.arpit.java2blog;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadWriteExcelMain {

 public static void main(String[] args) throws IOException {
  readFileUsingPOI();
 }

 public static void readFileUsingPOI() throws IOException 
 {
  ClassLoader classLoader = ReadWriteExcelMain.class.getClassLoader();
  String excelFilePath = "CountriesDetails.xlsx";
  FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile()));

  Workbook workbook = new XSSFWorkbook(inputStream);
  Sheet sheet = workbook.getSheetAt(0);
  
  // Creating formula evaluator object
  FormulaEvaluator formulaEval = workbook.getCreationHelper().createFormulaEvaluator();
  
  Iterator<Row> iterator = sheet.iterator();

  while (iterator.hasNext()) {
   Row nextRow = iterator.next();
   Iterator<Cell> cellIterator = nextRow.cellIterator();

   while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
     System.out.print(cell.getStringCellValue());
     break;
    case Cell.CELL_TYPE_NUMERIC:
     System.out.print(cell.getNumericCellValue());
     break;
    case Cell.CELL_TYPE_BOOLEAN:
     System.out.print(cell.getBooleanCellValue());
     break;
    case Cell.CELL_TYPE_FORMULA:
    {
     // Evaluting cell
      CellValue c=formulaEval.evaluate(cell);
     System.out.println(c.getNumberValue());
    }
    }
    System.out.print(" | ");
   }
   System.out.println();
  }

  workbook.close();
  inputStream.close();
 }
}

When you run above file, you will get following output:
Country | Capital | Population | 
India | Delhi | 10000.0 | 
France | Paris | 40000.0 | 
Germany | Berlin | 20000.0 | 
England | London | 30000.0 | 

Total Population | 100000.0
 | 

Written by Arpit:

If you have read the post and liked it. Please connect with me on Facebook | Twitter | Google Plus

 

Java tutorial for beginners Copyright © 2012