根据单元格公式结果使用POI设置单元格样式 [英] Using POI Set Cell Style Based on Cell Formula Result

问题描述

在基于单元格值设置单元格样式方面，我需要一些帮助.

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

我无法弄清楚如何获取单元格值.使用getNumericValue返回0.

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

推荐答案

Apache POI存储该公式，但它

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

Excel文件格式(.xls和.xlsx)均存储缓存"的文件格式.每个公式的结果以及公式本身.这意味着当打开文件时，可以快速显示该文件，而无需花费很长时间来计算所有公式结果.这也意味着，当通过Apache POI读取文件时，结果也很快对您可用！

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!

在使用Apache POI对公式单元本身或它们所依赖的公式单元进行更改之后，通常应执行公式评估，以使这些缓存"到单元格中.结果已更新.通常在完成所有更改之后，但在将文件写出之前，完成此操作.

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分别评估公式.

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

这篇关于根据单元格公式结果使用POI设置单元格样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！