How to get Function expression Apache POI

Question

I'm using Apache POI Library, how can I read the function expression from the excel. I need to read both the value 15 and the expression which has been used to calculate it, =5+10. Does Apache POI Support this functionality?

Answer 1

Using current apache poi 4.1.2 the following code prints all cell contents dependent on the CellType of the cells. For formula cells the formula gets printed as well as the cached formula result.

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellReference;

class ReadExcelFormulas {

 static void printCellContent(Cell cell, CellType type) {
  switch (type) {
   case STRING:
    System.out.println("String: " + cell.getRichStringCellValue().getString());
    break;
   case NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
     System.out.println("Date: " + cell.getDateCellValue());
    } else {
     System.out.println("Number: " + cell.getNumericCellValue());
    }
    break;
   case BOOLEAN:
    System.out.println("Boolean: " + cell.getBooleanCellValue());
    break;
   case FORMULA:
    System.out.print("Formula: " + cell.getCellFormula());
    System.out.print(", Formula result is ");
    printCellContent(cell, cell.getCachedFormulaResultType());
    break;
   case BLANK:
    System.out.println("Blank cell.");
    break;
   default:
    System.out.println("This should not occur.");
  }
 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("Excel.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
    System.out.print(cellRef.formatAsString());
    System.out.print(" - ");
    printCellContent(cell, cell.getCellType());
   }
  }

  workbook.close();
 }
}

Having the Excel.xlsx the same as in your screen shot, the result will be:

A1 - Number: 1.0
B1 - Number: 2.0
C1 - Formula: SUM(A1:B1), Formula result is Number: 3.0
A2 - Number: 6.0
B2 - Formula: 5+10, Formula result is Number: 15.0