如何用Apache POI实现逗号分隔值不匹配的Excel条件格式？

要实现这个需求，你需要用Excel公式作为条件格式的判断规则，以此处理单个值或逗号分隔多值的校验场景。以下是修改后的完整代码及说明：

String possibleValues = "TX,NC,AZ,CA";
// 构造条件格式用的Excel公式：检查单元格拆分后是否存在不在可选集合中的值
String conditionFormula = "SUMPRODUCT(--(ISNA(MATCH(FILTERXML(\"<t><s>\"&SUBSTITUTE(A4,\",\",\"</s><s>\")&\"</s></t>\",\"//s\"),{\"" + possibleValues.replace(",", "\",\"") + "\"},0))))>0";

ConditionalFormattingRule rule11 = sheetCF.createConditionalFormattingRule(conditionFormula);
org.apache.poi.ss.usermodel.FontFormatting font11 = rule11.createFontFormatting();
font11.setFontStyle(false, true);
font11.setFontColorIndex(IndexedColors.RED.index);
String cellRangeAddr = "A4:A100";
CellRangeAddress[] regions1 = new CellRangeAddress[] { CellRangeAddress.valueOf(cellRangeAddr) };
sheetCF.addConditionalFormatting(regions1, rule11);

公式逻辑说明 #

• SUBSTITUTE(A4,",","</s><s>")：将单元格A4中的逗号替换为XML标签片段，为拆分多值做准备
• FILTERXML(...)：把处理后的字符串解析为XML结构，提取出每个单独的取值
• MATCH(...,{"TX","NC","AZ","CA"},0)：检查每个拆分后的值是否在可选集合内，不在则返回#N/A
• ISNA(...)：将#N/A转为布尔值TRUE，存在的合法值转为FALSE
• --(...)：把布尔值转换为1（对应不合法值）或0（对应合法值）
• SUMPRODUCT(...)：统计不合法值的数量，若结果大于0，说明单元格存在不符合要求的值，触发红色高亮格式

额外处理（可选） #

如果单元格中的逗号分隔值带有多余空格，需要在公式中加入TRIM去除空格，修改后的公式如下：

SUMPRODUCT(--(ISNA(MATCH(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A4,",","</s><s>")&"</s></t>","//s")),{"TX","NC","AZ","CA"},0))))>0

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