java使用基于单元公式结果的POI集合单元样式

Question

我需要一些关于根据单元格值设置单元格样式的帮助

用于填充单元格的代码

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

然后，如果字段超过某个值，我需要给它上色。现在我只有交替的颜色：

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

我不知道如何获得单元格值。使用getNumericValue返回0

Answer 1

ApachePOI存储公式，但它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.

必须让Apache POI单独计算公式

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;
}