java - 使用 Apache POI 自定义交通灯的条件格式
问题描述
我阅读了这个相关的问题并自定义了红绿灯的值。
IconMultiStateFormatting 默认具有以下阈值:
- 如果单元格值大于或等于范围内所有值的 66%,则为绿色。
- 如果单元格值低于但大于或等于该范围内所有值的 33%,则为黄色。
- 如果单元格值低于范围内所有值的 33%,则为红色。
我需要的是交换红灯和绿灯(红色到大量,绿色到较低值)这可能吗?
目前,这是我的代码:
SheetConditionalFormatting sheetCF = currentSheet.getSheetConditionalFormatting()
ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(IconMultiStateFormatting.IconSet.GYR_3_TRAFFIC_LIGHTS)
rule.getMultiStateFormatting().setIconOnly(false)
ConditionalFormattingThreshold[] thresholds = rule.getMultiStateFormatting().getThresholds()
if (thresholds.length == 3) {
(0..2).each { i ->
ConditionalFormattingThreshold threshold = thresholds[i]
println("-------------------- $i : ${threshold.getRangeType()}") // 4 - percent
println("-------------------- $i : ${threshold.getValue()}")
println("-------------------- $i : ${threshold.getFormula()}") // null
threshold.setRangeType(ConditionalFormattingThreshold.RangeType.PERCENT)
switch (i) {
case 0: // RED LIGHT
threshold.setValue(50.0)
break
case 1: // YELLOW LIGHT
threshold.setValue(20.0)
break
case 2: // GREEN LIGHT
threshold.setValue(0.0)
break
}
}
}
ConditionalFormattingRule [] cfRules = [ rule ]
CellRangeAddress [] regions = [ CellRangeAddress.valueOf("F2:F$lastRow") ]
sheetCF.addConditionalFormatting(regions, cfRules)
在上面的这段代码中,我假装变绿 <= 20%; 黄色 20%-50%;红色 > 50%。
解决方案
你会如何设置这个Excel
?我看到的唯一方法是使用相反的图标顺序。IconMultiStateFormatting也提供了这一点。
完整示例:
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 = null;
for (int r = 0; r < 10; r++) {
cell = sheet.createRow(r).createCell(0);
cell.setCellValue(r+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
// default = green if >= 66%; yellow if < 66% and >= 33%, red if < 33%
// changing the thresholds to green if >= 50%; yellow if < 50% and >= 20%, red if < 20%
if (i == 0) {
threshold.setValue(0d);
} else if (i == 1) {
threshold.setValue(20d);
} else if (i == 2) {
threshold.setValue(50d);
}
}
// set reversed icon order since you wants red if >= 50%; yellow if < 50% and >= 20%, green if < 20%
iconMultiStateFormatting.setReversed(true);
}
ConditionalFormattingRule [] cfRules = {rule};
CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A10")};
sheetCF.addConditionalFormatting(regions, cfRules);
FileOutputStream fileOut = new FileOutputStream("ConditionalFormattingIconSet.xlsx");
workbook.write(fileOut);
fileOut.close();
}
}