如何根据布尔值在单元格中显示交通灯图标? [英] How to display Traffic Lights icons in a cell according to a boolean?

问题描述

如果 value 为 true ，红色交通灯为 value 为 false，则我需要在单元格中显示绿色的交通信号灯.

I need to display in a cell a green traffic light if value is true and red traffic light is value is false.

我阅读了有关 ConditionalFormattingRule 的一些文档，但我不了解它的工作原理...

I read several documentation about ConditionalFormattingRule but I don't understand how it works...

算法希望

...
Cell cell = sheet.getRow(1).getCell(5)
if (value) {
    cell.setIcon(TRAFFIC_LIGHT_GREEN)
}
else {
    cell.setIcon(TRAFFIC_LIGHT_RED)
}
...

有人可以帮助我理解吗?

Someone can help me to understand that please?

预先感谢

致谢

推荐答案

默认情况下， IconMultiStateFormatting 具有以下阈值:

The IconMultiStateFormatting has following thresholds per default:

1. 如果单元格值大于或等于中所有值的67％范围，然后是绿色.
2. 如果单元格值低于但大于或等于所有单元格的33％值，然后为黄色.
3. 如果单元格值低于该范围内所有值的33％，然后是红色.

如果您需要其他阈值，则必须更改该默认值.

If you need other thresholds, then you have to change that default.

以下代码设置了以下阈值:

Following code sets following thresholds:

1. 如果单元格值大于或等于1，则为绿色.
2. 如果单元格值小于但大于或等于0，则为黄色.
3. 如果单元格值小于0，则为红色.

使用当前 apache poi 4.1.0 的完整示例:

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;

class ConditionalFormattingIconSet {

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

  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");

  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setAlignment(HorizontalAlignment.CENTER); 
  cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); 

  Cell cell = sheet.createRow(0).createCell(0);
  cell.setCellValue(-1);
  cell.setCellStyle(cellStyle);

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(IconMultiStateFormatting.IconSet.GYR_3_TRAFFIC_LIGHTS);

  //rule.getMultiStateFormatting().setIconOnly(true);

  IconMultiStateFormatting iconMultiStateFormatting = rule.getMultiStateFormatting();
  ConditionalFormattingThreshold[] thresholds = iconMultiStateFormatting.getThresholds();
  if (thresholds.length == 3) {
   for (int i = 0; i < 3; i++) {
    ConditionalFormattingThreshold threshold = thresholds[i];
System.out.println(i + " : " + threshold.getRangeType()); // default 
System.out.println(i + " : " + threshold.getValue()); // default
    // changing the thresholds
    if (i == 0) {
     threshold.setValue(0d);
    } else if (i == 1) {
     threshold.setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
     threshold.setValue(0d);
    } else if (i == 2) {
     threshold.setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
     threshold.setValue(1d);
    }
   }
  }

  ConditionalFormattingRule [] cfRules = {rule};

  CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A1")};

  sheetCF.addConditionalFormatting(regions, cfRules);

  FileOutputStream fileOut = new FileOutputStream("ConditionalFormattingIconSet.xlsx");
  workbook.write(fileOut);
  fileOut.close();

 }
}

这篇关于如何根据布尔值在单元格中显示交通灯图标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！