英文:
Customize Traffic Lights' Conditional Formatting with 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%。
英文:
I read this related question and customized the values of the traffic lights.
> The IconMultiStateFormatting has following thresholds per default:
>
> * If the cell value is greater than or equal 66% of all the values in the range, then green.
> * If the cell value is lower but greater than or equal 33% of all the values in the range, then yellow.
> * If the cell value is lower than 33% of all the values in the range, then red.
What I need is to swap red and green lights (red to large amounts and green to lower values) Is that possible?
Currently, this is my code:
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)
In this code above I'd pretended get green <= 20%; yellow 20%-50%; red > 50%.
答案1
得分: 0
如何在 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()); // 默认
System.out.println(i + " : " + threshold.getValue()); // 默认
// 默认 = 绿色如果 >= 66%; 黄色如果 < 66% 且 >= 33%; 红色如果 < 33%
// 更改阈值,绿色如果 >= 50%; 黄色如果 < 50% 且 >= 20%; 红色如果 < 20%
if (i == 0) {
threshold.setValue(0d);
} else if (i == 1) {
threshold.setValue(20d);
} else if (i == 2) {
threshold.setValue(50d);
}
}
// 设置倒序图标顺序,因为您希望红色如果 >= 50%; 黄色如果 < 50% 且 >= 20%; 绿色如果 < 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();
}
}
英文:
How would you set this in Excel
? The only way I see is using a reversed icon order. IconMultiStateFormatting provides this too.
Complete example:
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();
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论