POI设置Excel条件格式 仅对值匹配所在行生效的实现方法

问题原因 #

之前的实现无法全量生效，核心是三个问题：

• 条件格式应用范围只绑定了第2行的C2、E2单元格，规则自然只在第2行生效
• 尝试使用的$A$、C$属于非法单元格引用格式，缺少行号标识，Excel无法解析，导致规则失效
• 原有代码存在逻辑bug：创建第三行数据时调用createRow(1)，行索引和第二行重复，会直接覆盖已创建的第二行数据。

实现原理 #

完全不需要逐行循环绑定规则，利用Excel条件格式的混合引用+区域批量绑定特性即可一次性完成全量配置，性能足以支撑1万行甚至更大数据量：

• 公式中使用$A2这类混合引用：$A锁定判断列固定为A列，行号2使用相对引用，当规则应用到第N行单元格时，Excel会自动偏移计算对应行A列的值，无需代码动态拼接行号
• 规则应用范围直接设置为所有数据行的目标列区间，一次绑定即可覆盖全部数据行
• 不同Lot Type对应不同高亮列的场景，只需要为每个Lot Type创建1条规则，指定对应高亮列的全量数据区间即可，总共仅需创建和下拉选项数量一致的规则（40条左右），无行级遍历开销。

可直接运行的代码示例 #

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");

// 创建表头（行索引0，对应Excel第1行）
XSSFRow headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Lot Type");
headerRow.createCell(1).setCellValue("Lot Size");
headerRow.createCell(2).setCellValue("Square Footage");
headerRow.createCell(3).setCellValue("Heating/Cooling");
headerRow.createCell(4).setCellValue("Extras");

// 创建示例数据行，注意行索引不能重复
XSSFRow row2 = sheet.createRow(1);
row2.createCell(0).setCellValue("Residential");
row2.createCell(1).setCellValue("8000");
row2.createCell(2).setCellValue("1200");
row2.createCell(3).setCellValue("Yes");
row2.createCell(4).setCellValue("None");

XSSFRow row3 = sheet.createRow(2);
row3.createCell(0).setCellValue("Industrial");
row3.createCell(1).setCellValue("12000");
row3.createCell(2).setCellValue("8000");
row3.createCell(3).setCellValue("");
row3.createCell(4).setCellValue("");

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
int totalDataRows = 10000;
// Excel行号从1开始，表头占第1行，数据最后一行对应Excel行号为1+totalDataRows
int lastExcelRowNum = 1 + totalDataRows;

// 规则1：A列值为Residential时，高亮对应行的C、E列
ConditionalFormattingRule residentialRule = sheetCF.createConditionalFormattingRule("$A2=\"Residential\"");
PatternFormatting residentialFill = residentialRule.createPatternFormatting();
residentialFill.setFillBackgroundColor(IndexedColors.YELLOW.index);
residentialFill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] residentialRegions = new CellRangeAddress[]{
    CellRangeAddress.valueOf("C2:C" + lastExcelRowNum),
    CellRangeAddress.valueOf("E2:E" + lastExcelRowNum)
};

// 规则2：A列值为Industrial时，高亮对应行的B、D列，其他Lot Type规则按相同逻辑新增即可
ConditionalFormattingRule industrialRule = sheetCF.createConditionalFormattingRule("$A2=\"Industrial\"");
PatternFormatting industrialFill = industrialRule.createPatternFormatting();
industrialFill.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
industrialFill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] industrialRegions = new CellRangeAddress[]{
    CellRangeAddress.valueOf("B2:B" + lastExcelRowNum),
    CellRangeAddress.valueOf("D2:D" + lastExcelRowNum)
};

// 批量添加所有规则，无逐行循环
sheetCF.addConditionalFormatting(residentialRegions, residentialRule);
sheetCF.addConditionalFormatting(industrialRegions, industrialRule);

return wb;

注意事项 #

• 公式里的起始行号必须和应用范围的起始行号保持一致，比如应用范围从第2行开始，公式里的相对行号就写2，否则会出现判断行错位的问题
• 不要使用缺少行/列标识的非法引用（如$A$、C$），这类写法Excel无法识别，会直接导致规则失效
• 调用createRow时传入的行索引必须唯一，重复传入相同索引会覆盖之前创建的行数据

内容的提问来源于stack exchange，提问作者SamR