自定义交通灯的条件格式化与Apache POI

huangapple go评论60阅读模式
英文:

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 -&gt;
        ConditionalFormattingThreshold threshold = thresholds[i]
        println(&quot;-------------------- $i : ${threshold.getRangeType()}&quot;)   // 4 - percent
        println(&quot;-------------------- $i : ${threshold.getValue()}&quot;)
        println(&quot;-------------------- $i : ${threshold.getFormula()}&quot;)     // 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(&quot;F2:F$lastRow&quot;) ]
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(&quot;Sheet1&quot;);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); 
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); 
Cell cell = null;
for (int r = 0; r &lt; 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 &lt; 3; i++) {
ConditionalFormattingThreshold threshold = thresholds[i];
System.out.println(i + &quot; : &quot; + threshold.getRangeType()); // default 
System.out.println(i + &quot; : &quot; + threshold.getValue()); // default
// default = green if &gt;= 66%; yellow if &lt; 66% and &gt;= 33%, red if &lt; 33%
// changing the thresholds to green if &gt;= 50%; yellow if &lt; 50% and &gt;= 20%, red if &lt; 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 &gt;= 50%; yellow if &lt; 50% and &gt;= 20%, green if &lt; 20%
iconMultiStateFormatting.setReversed(true);
}
ConditionalFormattingRule [] cfRules = {rule};
CellRangeAddress[] regions = {CellRangeAddress.valueOf(&quot;A1:A10&quot;)};
sheetCF.addConditionalFormatting(regions, cfRules);
FileOutputStream fileOut = new FileOutputStream(&quot;ConditionalFormattingIconSet.xlsx&quot;);
workbook.write(fileOut);
fileOut.close();
}
}

huangapple
  • 本文由 发表于 2020年7月30日 00:46:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/63158528.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定