使用色阶规则的XSSFheetConditionalFormatting不会格式化使用java中的poi生成的excel中的数据

Question

我正在尝试使用XSSFWorkbook功能在java中通过poi（4.0.1）生成的excel中创建色阶条件格式。尽管我尝试了许多在线可用的示例代码来创建红黄绿色阶格式，但我没有看到数据格式有任何变化。我正在粘贴我目前正在处理的代码。有人能帮助找到问题所在吗？

        applyColorScale(cf, "J2:J21", "00FF0000", "0000FFFF", "0000FF00");
        applyColorScale(XSSFSheetConditionalFormatting cf, String region, String... colors) {
            XSSFConditionalFormattingRule cr = cf.createConditionalFormattingColorScaleRule();
            XSSFColorScaleFormatting color = cr.createColorScaleFormatting();
            int steps = 100 / (colors.length - 1);
            for (int i = 0; i < colors.length; i++) {
                XSSFConditionalFormattingThreshold th = color.createThreshold();
                if (i == 0) {
                    th.setRangeType(XSSFConditionalFormattingThreshold.RangeType.MIN);
                } else if (i == colors.length - 1) {
                    th.setRangeType(XSSFConditionalFormattingThreshold.RangeType.MAX);
                } else {
                    th.setRangeType(XSSFConditionalFormattingThreshold.RangeType.PERCENTILE);
                }
                th.setValue((double)steps*i);
                XSSFColor clr = color.createColor();
                clr.setARGBHex(colors[i]);
            }
            CellRangeAddress[] regions = {CellRangeAddress.valueOf(region)};
            cf.addConditionalFormatting(regions, cr);
        }

Answer 1

如果您查看条件格式，您会发现一个链接

请参阅ConditionalFormats.java中有关Excel条件格式的更多示例

下面的例子是：

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

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

import java.io.FileOutputStream;

public class ConditionalFormatting {

 public static void applyColorScale(SheetConditionalFormatting sheetCF, String region, String... colors) {
  CellRangeAddress[] regions = { CellRangeAddress.valueOf(region) };
  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingColorScaleRule();
  ColorScaleFormatting csf = rule.getColorScaleFormatting();
  if (colors.length == 3) {
   csf.getThresholds()[0].setRangeType(ConditionalFormattingThreshold.RangeType.MIN);
   csf.getThresholds()[1].setRangeType(ConditionalFormattingThreshold.RangeType.PERCENTILE);
   csf.getThresholds()[1].setValue(50d);
   csf.getThresholds()[2].setRangeType(ConditionalFormattingThreshold.RangeType.MAX);
   for (int c = 0; c < 3; c++) {
    ((ExtendedColor)csf.getColors()[c]).setARGBHex(colors[c]);
   }
  } else if (colors.length == 2) {
   csf.setNumControlPoints(2);
   csf.getThresholds()[0].setRangeType(ConditionalFormattingThreshold.RangeType.MIN);
   csf.getThresholds()[1].setRangeType(ConditionalFormattingThreshold.RangeType.MAX);
   for (int c = 0; c < 2; c++) {
    ((ExtendedColor)csf.getColors()[c]).setARGBHex(colors[c]);
   }
  }

  sheetCF.addConditionalFormatting(regions, rule);
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();

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

  for (int r = 1; r < 20; r++) {
   sheet.createRow(r).createCell(9).setCellFormula("RANDBETWEEN(-100, 100)");
   sheet.getRow(r).createCell(11).setCellFormula("RANDBETWEEN(-100, 100)");
  }

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  applyColorScale(sheetCF, "J2:J21", "FFFF0000", "FFFFFF00", "FF00FF00");
  applyColorScale(sheetCF, "L2:L21", "FFFF0000", "FF00FF00");

  FileOutputStream out = new FileOutputStream("ConditionalFormatting.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

代码的主要区别：您创建的XSSFColor没有在任何地方使用。仅限范围类型。百分数需要一个值。ARGB中的Alpha应该是完全不透明的，而不是完全不透明的。黄色是FFFF00