Apache POI-条件格式-需要为规则和格式设置不同的单元格范围 [英] Apache POI - Conditional formatting - need to set different cell range for rule and formatting

问题描述

我正在尝试使用apache poi java创建一个空的excel模板.我需要添加一条规则-当列号为0时.填充3，然后需要用某种颜色突出显示7到12列(作为用户的强制性指示).

I am trying to create an empty excel template using apache poi java. I need to add a rule - when column no. 3 is populated then columns from 7 to 12 need to be highlighted in some color (as a mandatory indicator for the user).

我可以在下面的代码中找到在同一单元格上满足条件时为单元格着色的代码.但是我想在当前单元格满足条件时为不同的单元格上色/设置格式.

I could find below code which colors the cell when the condition is met on the same cell. But I want to color/format different cells when the condition is met on current cell.

`   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

但是我想是当满足rule1时，然后为另一个单元格区域着色.

But I want is when rule1 is met, then color a different cell range.

这在poi中可行吗?

推荐答案

Excel提供基于公式的条件格式设置规则.

Excel provides conditional formatting rules based on formulas.

如果$C1中的值是数字且大于5，则公式=AND(ISNUMBER($C1), $C1>5)返回True.如果将此公式应用于范围G1:L1000，则该范围中的每个单元格都将为true.相应行的C列中的内容满足该条件.这是因为C列在公式中使用$C进行了固定.但是行号不是固定的，相对的也是固定的.

The formula =AND(ISNUMBER($C1), $C1>5) returns True if the value in $C1 is numeric and greater than 5. If this formula is applied to the range G1:L1000, then each cell in this range will be true if the value in column C of the corresponding row fulfills that contition. That is because column C is fixated using $C in the formula. But the row numbers are not fixated and so are relative.

使用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();

 }
}

现在，如果在C列中放置数字或大于5的数字，则G:L列中的单元格将填充为黄色.

Now if you put something in column C what is numeric and greater than 5, the cells in columns G:L will be filled yellow.

这篇关于Apache POI-条件格式-需要为规则和格式设置不同的单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！