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

问题描述

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

我阅读了一些关于 ConditionalFormattingRule 的文档，但我不明白它是如何工作的...

Algo 希望

<预><代码>...单元格单元格 = sheet.getRow(1).getCell(5)如果(值){cell.setIcon(TRAFFIC_LIGHT_GREEN)}别的 {cell.setIcon(TRAFFIC_LIGHT_RED)}...

有人可以帮我理解吗?

提前致谢，

问候

解决方案

IconMultiStateFormatting 默认有以下阈值:

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

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

以下代码设置以下阈值:

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

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.ss.util.CellRangeAddress；导入 java.io.FileOutputStream;类 ConditionalFormattingIconSet {public static void main(String[] args) 抛出异常 {工作簿工作簿 = new XSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet1");CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(Horizo​​ntalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);单元格单元格 = sheet.createRow(0).createCell(0);cell.setCellValue(-1);cell.setCellStyle(cellStyle);SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();ConditionalFormattingRule 规则 = sheetCF.createConditionalFormattingRule(IconMultiStateFormatting.IconSet.GYR_3_TRAFFIC_LIGHTS);//rule.getMultiStateFormatting().setIconOnly(true);IconMultiStateFormatting iconMultiStateFormatting = rule.getMultiStateFormatting();ConditionalFormattingThreshold[] thresholds = iconMultiStateFormatting.getThresholds();如果(阈值.长度== 3){for (int i = 0; i <3; i++) {ConditionalFormattingThreshold 阈值 = 阈值 [i];System.out.println(i + " : " + threshold.getRangeType());//默认System.out.println(i + " : " + threshold.getValue());//默认//改变阈值如果(我== 0){阈值.setValue(0d);} else if (i == 1) {threshold.setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);阈值.setValue(0d);} else if (i == 2) {threshold.setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);阈值.setValue(1d);}}}ConditionalFormattingRule [] cfRules = {rule};CellRangeAddress[] 区域 = {CellRangeAddress.valueOf("A1:A1")};sheetCF.addConditionalFormatting(regions, cfRules);FileOutputStream fileOut = new FileOutputStream("ConditionalFormattingIconSet.xlsx");workbook.write(fileOut);fileOut.close();}}

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

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

Algo wished

...
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?

Thanks in advance,

Regards

解决方案

The IconMultiStateFormatting has following thresholds per default:

1. If the cell value is greater than or equal 67% of all the values in the range, then green.
2. If the cell value is lower but greater than or equal 33% of all the values in the range, then yellow.
3. If the cell value is lower than 33% of all the values in the range, then red.

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

Following code sets following thresholds:

1. If the cell value is greater than or equal 1 then green.
2. If the cell value is lower but greater than or equal 0, then yellow.
3. If the cell value is lower than 0, then red.

Complete Example using current 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屋！