Условное форматирование выделяет только недопустимые ячейки

Я создаю ниже правило условного форматирования в Apache POI.

Проблема

Ожидал:

• Формула должна выделять только недопустимые значения в столбце «J».

Действительный:

• Но все значения всего столбца «J» выделяются, даже для допустимых значений.

Формула

String kkf = "IF(AND(NOT(ISBLANK(J4)),ISERROR(SUM(MATCH(FILTERXML(\"<t><s>\"&SUBSTITUTE(J4,\",\",\"</s><s>\")&\"</s></t>\",\"//s\"),FILTERXML(\"<t><s>\"&SUBSTITUTE(PossibleValues!$D$2,\",\",\"</s><s>\")&\"</s></t>\",\"//s\"),0))),\"\"),TRUE())";

Полный код

import java.util.ArrayList;

import org.apache.commons.io.FileUtils;
import org.json.CDL;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import  java.io.*;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import  org.apache.poi.hssf.usermodel.HSSFRow;

public class Red {
    public static void main(String[] args) throws Exception{

String str = "{\"rulesetgroupname\":\"Coastal122\",\"downloadtype\":\"Template\",\"rulesetid\":\"638a0ac02944f902b1eebab3\",\"rulesetversion\":\"0.1\",\"rulegroupid\":\"638a09f28bd88c16a76fa599\",\"product\":\"CAT\",\"ruletype\":\"ERWR\",\"dataobjects\":\"Location 24,Class Codes 24,BPP Covg 24,Building Covg 24,Building 24,Policy 24\",\"ruletemplateversion\":\"1\",\"ruleTemplaeResponseId\":\"638a09f28bd88c16a76fa599\",\"dataobject\":\"Location 24\",\"rulegroup\":\"CAT Testing122\",\"metadataheaders\":[\"Rule Name\",\"Rule Label\",\"Modified By\",\"Modified At\",\"Rule ID\",\"Rule Status (Add/Change/Expire/Verify)\",\"Effective Date [MM/DD/YYYY]\",\"Expiration Date [MM/DD/YYYY]\",\"Has Rule Changed?\"],\"conditionheaders\":[{\"label\":\"State\",\"type\":\"Collection\",\"name\":\"State\",\"dataobject\":\"Location 24\",\"values\":\"NC,SC,TX,VA,MA,GA,AL,IL,DE,NY,MS,NH,RI,ME,FL,MN,CT,NJ,MD\"},{\"label\":\"Counties\",\"type\":\"Collection\",\"name\":\"Counties\",\"dataobject\":\"Location 24\",\"values\":\"\"},{\"label\":\"Zip Codes\",\"type\":\"Collection\",\"name\":\"ZipCodes\",\"dataobject\":\"Location 24\",\"values\":\"\"}],\"outcomeheaders\":[{\"label\":\"Output\",\"type\":\"String\",\"name\":\"RuleCode\",\"dataobject\":\"Location 24\"}]}";

        JSONObject output;
        try {
            output = new JSONObject(str);

          
            
            String filename = "D:\\project\\excel\\color.xlsx" ;
            

            XSSFWorkbook workbook = new XSSFWorkbook();
            String rulegroup = output.getString("rulegroup");
            String downloadtype = output.getString("downloadtype");
            XSSFSheet sheet = workbook.createSheet("Ruleset");

            sheet.setDefaultColumnWidth(10);

            ArrayList<String> headers = new ArrayList<String>();
            ArrayList<String> nameheaders = new ArrayList<String>();

            // Top Element Font

            Font font31 = workbook.createFont();
            font31.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            font31.setBold(true);
            
            XSSFCellStyle style12 = workbook.createCellStyle();
            style12.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
            style12.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style12.setAlignment(HorizontalAlignment.CENTER);
            style12.setVerticalAlignment(VerticalAlignment.CENTER);
            style12.setBorderTop(BorderStyle.MEDIUM);
            style12.setBorderBottom(BorderStyle.MEDIUM);
            style12.setBorderLeft(BorderStyle.MEDIUM);
            style12.setBorderRight(BorderStyle.MEDIUM);
            style12.setWrapText(true);
            style12.setFont(font31);
            
            XSSFCellStyle style13 = workbook.createCellStyle();
            style13.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex());
            style13.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style13.setAlignment(HorizontalAlignment.CENTER);
            style13.setVerticalAlignment(VerticalAlignment.CENTER);
            style13.setBorderTop(BorderStyle.MEDIUM);
            style13.setBorderBottom(BorderStyle.MEDIUM);
            style13.setBorderLeft(BorderStyle.MEDIUM);
            style13.setBorderRight(BorderStyle.MEDIUM);
            style13.setWrapText(true);
            style13.setFont(font31);

            XSSFCellStyle style14 = workbook.createCellStyle();
            Font font32 = workbook.createFont();
            font32.setBold(true);
            font32.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            style14.setBorderTop(BorderStyle.MEDIUM);
            style14.setBorderBottom(BorderStyle.MEDIUM);
            style14.setBorderLeft(BorderStyle.MEDIUM);
            style14.setBorderRight(BorderStyle.MEDIUM);
            style14.setAlignment(HorizontalAlignment.CENTER);
            style14.setVerticalAlignment(VerticalAlignment.CENTER);
            style14.setWrapText(true);
            style14.setFont(font32);
            // Get Top elements

            String product = output.getString("product");
            String ruletype = output.getString("ruletype");
            String dataobject = output.getString("dataobjects");
            String rulesetid = output.getString("rulesetid");
            String rulesetversion = output.getString("rulesetversion");
            String rulegroupid = output.getString("rulegroupid");
            String ruletemplateversion = output.getString("ruletemplateversion");
            String rulesetgroupname = output.getString("rulesetgroupname");

            XSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell(0).setCellValue("Product/Process");
            rowhead.getCell(0).setCellStyle(style14);
            rowhead.createCell(1).setCellValue(product);
            rowhead.getCell(1).setCellStyle(style13);
            rowhead.createCell(2).setCellValue("RuleType");
            rowhead.getCell(2).setCellStyle(style14);
            rowhead.createCell(3).setCellValue(ruletype);
            rowhead.getCell(3).setCellStyle(style13);
            rowhead.createCell(4).setCellValue("DataObject");
            rowhead.getCell(4).setCellStyle(style14);
            rowhead.createCell(5).setCellValue(dataobject);
            rowhead.getCell(5).setCellStyle(style13);
            rowhead.createCell(6).setCellValue("RuleTemplate");
            rowhead.getCell(6).setCellStyle(style14);
            rowhead.createCell(7).setCellValue(rulegroup);
            rowhead.getCell(7).setCellStyle(style13);
            rowhead.createCell(8).setCellValue("RulesetID");
            rowhead.getCell(8).setCellStyle(style14);
            rowhead.createCell(9).setCellValue(rulesetid);
            rowhead.getCell(9).setCellStyle(style13);
            rowhead.createCell(10).setCellValue("RuleSetVersion");
            rowhead.getCell(10).setCellStyle(style14);
            rowhead.createCell(11).setCellValue(rulesetversion);
            rowhead.getCell(11).setCellStyle(style13);
            rowhead.createCell(12).setCellValue("RuleTemplateID");
            rowhead.getCell(12).setCellStyle(style14);
            rowhead.createCell(13).setCellValue(rulegroupid);
            rowhead.getCell(13).setCellStyle(style13);
            rowhead.createCell(14).setCellValue("RuleTemplateVersion");
            rowhead.getCell(14).setCellStyle(style14);
            rowhead.createCell(15).setCellValue(ruletemplateversion);
            rowhead.getCell(15).setCellStyle(style13);
            rowhead.createCell(16).setCellValue("RuleSetGroupName");
            rowhead.getCell(16).setCellStyle(style14);
            rowhead.createCell(17).setCellValue(rulesetgroupname);
            rowhead.getCell(17).setCellStyle(style12);

            rowhead.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));

            JSONArray metadataheader = output.getJSONArray("metadataheaders");
            JSONArray conditionheader = output.getJSONArray("conditionheaders");
            JSONArray outcomeheader = output.getJSONArray("outcomeheaders");

            Integer metheadcount = metadataheader.length();
            Integer conheadcount = conditionheader.length();
            Integer outheadcount = outcomeheader.length();

            metadataheader.getString(0);

            for (int i = 0; i < metadataheader.length(); i++) {
                headers.add(metadataheader.getString(i));
                nameheaders.add(metadataheader.getString(i));
            }

            for (int i = 0; i < conditionheader.length(); i++) {

                JSONObject json = conditionheader.getJSONObject(i);
                String conname = json.getString("label");
                String contype = json.getString("type");
                String dataobject1 = json.getString("dataobject");
                contype = "[" + contype + "]";
                String fnl = dataobject1 + "." + conname + " " + contype;

                headers.add(fnl);

                nameheaders.add(conname);

            }

            for (int i = 0; i < outcomeheader.length(); i++) {

                JSONObject json = outcomeheader.getJSONObject(i);
//              if (json.isEmpty()) {
//                  throw new Exception("Outcome Attributes filed should not be empty");
//              } 
                String conname = json.getString("label");
                String contype = json.getString("type");
                String dataobject1 = json.getString("dataobject");
                contype = "[" + contype + "]";
                String fnl = dataobject1 + "." + conname + " " + contype;
                headers.add(fnl);

                nameheaders.add(conname);

            }

            // Merging Region
            // HSSFRow merge = sheet.createRow((short)1);
            Row row = sheet.createRow(1);
            Cell cell = row.createCell(0);
            cell.setCellValue("MetaData");

            // Merging cells by providing cell index
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, metheadcount - 1));
            Font font3 = workbook.createFont();
            font3.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            font3.setBold(true);

            XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setWrapText(true);

            /*
             * style.setBorderTop(BorderStyle.MEDIUM);
             * style.setBorderBottom(BorderStyle.MEDIUM);
             * style.setBorderLeft(BorderStyle.MEDIUM);
             * style.setBorderRight(BorderStyle.MEDIUM);
             */
            style.setFont(font3);
            cell.setCellStyle(style);

            // style.setFont(headerFont);
            // style.setFillForegroundColor(IndexedColors.BLACK.getIndex());

            Cell cell1 = row.createCell(metheadcount);
            cell1.setCellValue("Conditional Attributes");

            if (conheadcount > 1) {
                sheet.addMergedRegion(new CellRangeAddress(1, 1, metheadcount, metheadcount + conheadcount - 1));
            }

            XSSFCellStyle style1 = workbook.createCellStyle();
            Font font1 = workbook.createFont();
            font1.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            font1.setBold(true);
            style1.setFont(font1);
            style1.setWrapText(true);
            style1.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());
            style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style1.setAlignment(HorizontalAlignment.CENTER);
            style1.setVerticalAlignment(VerticalAlignment.CENTER);
            /*
             * style1.setBorderTop(BorderStyle.MEDIUM);
             * style1.setBorderBottom(BorderStyle.MEDIUM);
             * style1.setBorderLeft(BorderStyle.MEDIUM);
             * style1.setBorderRight(BorderStyle.MEDIUM);
             */
            cell1.setCellStyle(style1);

            Cell cell2 = row.createCell(metheadcount + conheadcount);
            cell2.setCellValue("Outcome Attributes");
            if (outheadcount > 1) {
                sheet.addMergedRegion(new CellRangeAddress(1, 1, metheadcount + conheadcount,
                        metheadcount + conheadcount + outheadcount - 1));
            }
            XSSFCellStyle style2 = workbook.createCellStyle();
            Font font2 = workbook.createFont();
            font2.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            font2.setBold(true);

            style2.setFont(font2);
            style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
            style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style2.setAlignment(HorizontalAlignment.CENTER);
            style2.setVerticalAlignment(VerticalAlignment.CENTER);
            style2.setWrapText(true);
            /*
             * style2.setBorderTop(BorderStyle.MEDIUM);
             * style2.setBorderBottom(BorderStyle.MEDIUM);
             * style2.setBorderLeft(BorderStyle.MEDIUM);
             * style2.setBorderRight(BorderStyle.MEDIUM);
             */
            cell2.setCellStyle(style2);

            row.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));

            /*
             * HSSFCellStyle style22 = workbook.createCellStyle(); Font font22 =
             * workbook.createFont();
             * font22.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
             * font22.setBold(true); style22.setFont(font22);
             * style22.setAlignment(HorizontalAlignment.CENTER);
             */

            XSSFCellStyle style22 = workbook.createCellStyle();
            Font font22 = workbook.createFont();
            font22.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            font22.setBold(true);
            style22.setFont(font22);
            style22.setBorderTop(BorderStyle.MEDIUM);
            style22.setBorderBottom(BorderStyle.MEDIUM);
            style22.setBorderLeft(BorderStyle.MEDIUM);
            style22.setBorderRight(BorderStyle.MEDIUM);
            style22.setWrapText(true);
            // style22.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            // style22.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style22.setAlignment(HorizontalAlignment.CENTER);

            XSSFRow rowheader = sheet.createRow((short) 2);

            for (int z = 0; z < headers.size(); z++) {
                rowheader.createCell(z).setCellValue(headers.get(z));
                String metaDataCellValue = rowheader.getCell(z).getStringCellValue();
                if (metaDataCellValue.equals("Rule Name") || metaDataCellValue.equals("Modified By") ||
                        metaDataCellValue.equals("Modified At") || metaDataCellValue.equals("Rule ID")) {
                    rowheader.getCell(z).setCellStyle(style13);
                } else {
                    rowheader.getCell(z).setCellStyle(style22);
                }
                

            }

            rowheader.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));

            // Get Details

            DataFormatter formatter = new DataFormatter();

            if (downloadtype.equalsIgnoreCase("rulesets")) {

                JSONArray values = output.getJSONArray("values");
                if (!values.isEmpty()) {

                    short valcell = 3;

                    for (int val = 0; val < values.length(); val++) {

                        XSSFRow rowheader2 = sheet.createRow((short) valcell + val);

                        try {
                            JSONObject firstvals = values.getJSONObject(val);

                            JSONArray methead1 = firstvals.optJSONArray("metadata");
                            JSONArray conhead1 = firstvals.optJSONArray("condition");
                            JSONArray outhead1 = firstvals.optJSONArray("outcome");

                            for (int i = 0; i < methead1.length(); i++) {
                                JSONObject json = methead1.getJSONObject(i);
                                String val23 = json.get("value").toString();
                                String name = "";
                                if (!json.isNull("label")) {
                                    name = (json.getString("label"));
                                } else {
                                    name = (json.getString("name"));
                                }
                                int index = nameheaders.indexOf(name);
                                rowheader2.createCell(index).setCellValue(val23);
                            }

                            for (int y = 0; y < conhead1.length(); y++) {
                                JSONObject json = conhead1.getJSONObject(y);
                                String val23 = json.get("value").toString();

                                String name = "";
                                if (!json.isNull("label")) {
                                    name = (json.getString("label"));
                                } else {
                                    name = (json.getString("name"));
                                }

                                String oper = (json.getString("operator"));
                                

                                if (oper.equalsIgnoreCase("not in")) {

                                    val23 = "NOT IN(" + val23 + ")";

                                } else if (oper.equalsIgnoreCase("neq")) {

                                    val23 = "NOT EQUALS(" + val23 + ")";
                                } else if (oper.equalsIgnoreCase(">") || oper.equalsIgnoreCase("GT") ) {

                                    val23 =  ">" + val23;
                                    
                                } else if (oper.equalsIgnoreCase("> = ") || oper.equalsIgnoreCase("GTE")) {

                                    val23 =  "> = " + val23;
                                    
                                    
                                    
                                }else if (oper.equalsIgnoreCase("<") || oper.equalsIgnoreCase("LT")) {

                                    val23 =  "<" + val23;
                                    
                                    
                                }
                                else if ( oper.equalsIgnoreCase("< = ") || oper.equalsIgnoreCase("LTE")) {

                                    val23 =  "< = " + val23;
                                    
                                }

                                int index = nameheaders.indexOf(name);
                                if (index > 0) {
                                    rowheader2.createCell(index).setCellValue(val23);
                                }
                            }

                            for (int za = 0; za < outhead1.length(); za++) {
                                JSONObject json = outhead1.getJSONObject(za);
                                String val23 = json.get("value").toString();

                                String name = "";
                                if (!json.isNull("label")) {
                                    name = (json.getString("label"));
                                } else {
                                    name = (json.getString("name"));
                                }

                                int index = nameheaders.indexOf(name);

                                if (index > 0) {
                                    rowheader2.createCell(index).setCellValue(val23);
                                }

                            }
                        } catch (JSONException e) {
                            e.printStackTrace();
                            String error = e.getMessage();
                            throw new Exception(error);
                        } catch (Exception e) {
                            // String error = e.getMessage();
                            e.printStackTrace();
                            throw new Exception(e.getMessage());
                        }

                    }

                } //

            }


            

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateAll();

            // Duplicate Highlighter

            String lcollett = "";
            String csv1 = "";
            SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
            int s1 = sheet.getNumMergedRegions();

            for (int i = 0; i < s1; i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                int colIndex = region.getFirstColumn();
                int rowNum = region.getFirstRow();
                String val1 = formatter.formatCellValue(sheet.getRow(rowNum).getCell(colIndex));
                if (val1.contains("MetaData")) {
                    int fcol = sheet.getMergedRegion(i).getFirstColumn();
                    int lcol = sheet.getMergedRegion(i).getLastColumn();
                    lcollett = CellReference.convertNumToColString(lcol);
                    for (i = fcol; i <= lcol; i++) {
                        sheet.autoSizeColumn(i);
                    }

                }

            }

            for (int i = 0; i < s1; i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                int colIndex = region.getFirstColumn();
                int rowNum = region.getFirstRow();
                String val1 = formatter.formatCellValue(sheet.getRow(rowNum).getCell(colIndex));
                ArrayList<String> xsdf = new ArrayList<String>();
                if (val1.contains("Conditional")) {
                    int fcol = sheet.getMergedRegion(i).getFirstColumn();
                    int lcol = sheet.getMergedRegion(i).getLastColumn();
                    String fcollett = "";
                    lcollett = CellReference.convertNumToColString(lcol);
                    for (i = fcol; i <= lcol; i++) {
                        fcollett = CellReference.convertNumToColString(i);
                        if (i == lcol) {
                            xsdf.add("$" + fcollett + "$4:$" + fcollett + "$10000,\"*\"&$" + fcollett + "4&\"*\"");
                        } else {
                            xsdf.add("$" + fcollett + "$4:$" + fcollett + "$10000,$" + fcollett + "4");
                        }

                        sheet.autoSizeColumn(i);

                    }

                    csv1 = String.join(",", xsdf);
                    csv1 = "COUNTIFS(" + csv1 + ")>1";
                }

            }

            for (int i = 0; i < s1; i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                int colIndex = region.getFirstColumn();
                int rowNum = region.getFirstRow();
                String val1 = formatter.formatCellValue(sheet.getRow(rowNum).getCell(colIndex));
                if (val1.contains("Outcome")) {
                    int fcol = sheet.getMergedRegion(i).getFirstColumn();
                    int lcol = sheet.getMergedRegion(i).getLastColumn();
                    lcollett = CellReference.convertNumToColString(lcol);
                    for (i = fcol; i <= lcol; i++) {
                        sheet.autoSizeColumn(i);
                    }

                }

            }

            ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(csv1);
            org.apache.poi.ss.usermodel.FontFormatting font = rule1.createFontFormatting();
            font.setFontStyle(false, true);
            font.setFontColorIndex(IndexedColors.BLUE.index);
            String cellrangeadd = "A4:" + lcollett + "10000";
            CellRangeAddress[] regions = new CellRangeAddress[] { CellRangeAddress.valueOf(cellrangeadd) };
            sheetCF.addConditionalFormatting(regions, rule1);
            
            // Sheet 3
            
            SheetConditionalFormatting sheetCFa = sheet.getSheetConditionalFormatting();
            
            XSSFSheet sheet21 = workbook.createSheet("PossibleValues");

            sheet21.setColumnWidth(0, 20 * 256);
            sheet21.setColumnWidth(1, 50 * 256);
            
            XSSFRow rowhead33 = sheet21.createRow((short) 0);

            rowhead33.createCell(0).setCellValue("DataObject");
            rowhead33.getCell(0).setCellStyle(style14);
            rowhead33.createCell(1).setCellValue("AttributeName");
            rowhead33.getCell(1).setCellStyle(style14);
            rowhead33.createCell(2).setCellValue("DataType");
            rowhead33.getCell(2).setCellStyle(style14);
            rowhead33.createCell(3).setCellValue("PossibleValues");
            rowhead33.getCell(3).setCellStyle(style14);

            
            int attcell=1;
            ArrayList<String> xkdf = new ArrayList<String>();
            for (int i = 0; i < conditionheader.length(); i++) {

                JSONObject json = conditionheader.getJSONObject(i);
                String conname = json.getString("label");
                String contype = json.getString("type");
                String dataobject1 = json.getString("dataobject");
                String pv = json.getString("values");
            String  contypea = "[" + contype + "]";
            String fnl = dataobject1 + "." + conname + " " + contypea;
            
                xkdf.add(fnl);
                

                XSSFRow rowhead334 = sheet21.createRow((short) attcell + i);
                     
                rowhead334.createCell(0).setCellValue(dataobject1);
                rowhead334.createCell(1).setCellValue(conname);
                rowhead334.createCell(2).setCellValue(contype);
                rowhead334.createCell(3).setCellValue(pv);


            }
            
    
            String kkf = "IF(AND(NOT(ISBLANK(J4)),ISERROR(SUM(MATCH(FILTERXML(\"<t><s>\"&SUBSTITUTE(J4,\",\",\"</s><s>\")&\"</s></t>\",\"//s\"),FILTERXML(\"<t><s>\"&SUBSTITUTE(PossibleValues!$D$2,\",\",\"</s><s>\")&\"</s></t>\",\"//s\"),0))),\"\"),TRUE())";
            ConditionalFormattingRule rule13 = sheetCF.createConditionalFormattingRule(kkf);
            org.apache.poi.ss.usermodel.FontFormatting font535 = rule13.createFontFormatting();
            font535.setFontStyle(false, true);
            font535.setFontColorIndex(IndexedColors.RED.index);
            
            String cellrangeadd2 = "J4:J10000";
            CellRangeAddress[] regionssd = new CellRangeAddress[] { CellRangeAddress.valueOf(cellrangeadd2) };
            sheetCF.addConditionalFormatting(regionssd, rule13);

              FileOutputStream fileOut = new FileOutputStream(filename);
              workbook.write(fileOut); 
              fileOut.close(); 
              workbook.close();
  
            System.out.println("Your excel file has been generated!");   

          
        } catch (JSONException e) {
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
    }

}

Требуется ручная постобработка в Excel

Теперь я сделал следующие шаги

1. Добавьте три значения в столбец «J» с именем «Штат». Все ячейки подсвечиваются, см. изображение ниже:

2. Нажмите «Условное форматирование» > «Управление правилами» > «Выбрать правило» (красный формат) > «Изменить правила» > «ОК» > «Применить» > «ОК».

После этого действия значения столбца подсвечиваются правильно.

Вопрос

Есть ли что-то, что я пропустил в своем Java-коде? Как избежать этой ручной работы в Excel?