Using POI Set Cell Style Based on Cell Formula Res

Question

I need some help on setting the cell style base on the cell value.

The code used to populate cell.

String totalvariationweightv1 = "J" + (x+1);
String totalvariationweightv2 = "L" + (x+1);
cell85014.setCellType(Cell.CELL_TYPE_FORMULA);
cell85014.setCellFormula("SUM(((" + totalvariationweightv2 + "-" + totalvariationweightv1 + ")/" + totalvariationweightv1 + ")*100)");

Then I need to color the field if it exceeds a certain value. Right now I just have alternating colors:

cell85014.setCellStyle((x%2)==0?stylefloatGray:stylefloat);

I cannot figure out how to get the cell value. Using getNumericValue returns 0.

Answer 1

Apache POI stores the formula, but it doesn't evaluate it automatically.

The Excel file format (both .xls and .xlsx) stores a "cached" result for every formula along with the formula itself. This means that when the file is opened, it can be quickly displayed, without needing to spend a long time calculating all of the formula results. It also means that when reading a file through Apache POI, the result is quickly available to you too!

After making changes with Apache POI to either Formula Cells themselves, or those that they depend on, you should normally perform a Formula Evaluation to have these "cached" results updated. This is normally done after all changes have been performed, but before you write the file out.

You must tell Apache POI to evaluate the formula separately.

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

// Set your cell formula here

switch (evaluator.evaluateFormulaCell(cell85014)) {
    case Cell.CELL_TYPE_NUMERIC:
        double x = cell85014.getNumericCellValue();
        // Set cell style here, based on numeric value,
        // as you already are doing in your code.
        // Watch out for floating point inaccuracies!
        break;
    default:
        System.err.println("Unexpected result type!");
        break;
}