POI-XSSF: read formatted value from formula cells cached value

2
votes

In my excel sheet many cells contain formulas, i don't want to recalculate these formulas when i read the excel with Apache POI.

The way i do that:

if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {

    //System.out.println("Formula is " + cell.getCellFormula());
    switch(cell.getCachedFormulaResultType()) {
        case XSSFCell.CELL_TYPE_NUMERIC:

            System.out.print(cell.getNumericCellValue() +" ");

            break;
        case XSSFCell.CELL_TYPE_STRING:
            System.out.print(cell.getRichStringCellValue()+" ");
            break;
    }
}

This helps me in getting the raw value in the cell. For example if the cell has value 19.5%, this will give me 0.195456 . I want to get the formatted value.

One way to get the formatted value is:

DataFormatter formatter = new DataFormatter();

System.out.print(formatter.formatCellValue(cell));

This works well for regular cells, but for cells with formulas, this actually gets the formula string and displays it, i.e., it does not get the cached value and formats it, rather it just returns the formula string.

Is there a way to format a value after retrieving it from CELL_TYPE_FORMULA

2
javaexcelapache-poixssf

2 Answers

5
votes

It's possible to directly format the cached value of a cell without using an evaluator. It's useful in case of values that cannot be recalculated because of third party plugin or unavailable external Data in the cell formula.

This code can be used to do that:

final DataFormatter dataFormatter = new DataFormatter();

final CellStyle cellStyle = cell.getCellStyle();
final String formtatedValue = dataFormatter.formatRawCellContents(cell.getNumericCellValue(), cellStyle.getDataFormat(), cellStyle.getDataFormatString());
System.out.println(formattedValue);

A formatter is still used but the method formatRawCellContents is called to manually format the cell cached value with its style.

3
votes

It should work if you pass in a FormulaEvaluator in the call to formatCellValue, i.e.

DataFormatter formatter = new DataFormatter();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
System.out.print(formatter.formatCellValue(cell, evaluator));