Apache POI-条件格式-需要为规则和格式设置不同的单元格范围

Question

`   XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "5");

    PatternFormatting patternFmt = rule1.createPatternFormatting();
    patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);

    sheetCF.addConditionalFormatting(addressList.getCellRangeAddresses(), rule1); //when rule1 is met, same cell is colored yellow

但我想要的是当规则1满足时，然后给不同的单元格区域着色。

在poi中这可能吗？怎么可能？

Answer 1

Excel提供基于公式的条件格式规则。

如果$c1中的值是数值且大于5，则公式=and(ISNUMBER($c1),$c1>5)返回true。如果此公式应用于G1:L1000范围，则如果相应行的列C中的值满足该连接，则此范围中的每个单元格都为true。这是因为列C是使用公式中的$C来确定的。但是行号不是固定的，因此是相对的。

使用Apache POI的示例：

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 main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet("new sheet");
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("AND(ISNUMBER($C1), $C1>5)");
  PatternFormatting fill = rule.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.YELLOW.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

  ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("G1:L1000")};

  sheetCF.addConditionalFormatting(regions, cfRules);

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

 }
}