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

Я создаю ниже правило условного форматирования в 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» с именем «Штат». Все ячейки подсвечиваются, см. изображение ниже:

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

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

Вопрос

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

Вам нужно будет уточнить часть it should highlight the selected cells in column 'J' but the entire column 'J' is getting highlighted. На скриншоте снова и снова показаны одни и те же значения, поэтому неудивительно, что все они выделены красным шрифтом. И из того, что я могу сказать, это не проблема с диапазоном ячеек, который вы выделяете. Таким образом, вы также должны уточнить, в чем на самом деле проблема. Также некоторые необработанные данные помогут воспроизвести проблему.

XtremeBaumer 07.12.2022 14:05

Невозможно воспроизвести что-либо в excel с этой информацией. Однако вы можете захотеть оценить формулы перед записью в файл: workbook.getCreationHelper().createFormulaEvaluator().evalua‌​teAll();

XtremeBaumer 07.12.2022 14:33

@XtremeBaumer Я обновил сообщение и добавил информацию, связанную с XML. Позвольте мне знать, если это помогает

Chennai Cheetah 08.12.2022 12:31

@XtremeBaumer Кстати, формула оценки не помогла.

Chennai Cheetah 08.12.2022 12:46

Я твердо убежден, что вы либо опускаете важную информацию, либо формула не работает. Если вы не можете предоставить воспроизводимый пример, я не вижу, как мы можем вам помочь.

XtremeBaumer 08.12.2022 12:52

@XtremeBaumer Я добавил полный код для справки со всеми снимками.

Chennai Cheetah 09.12.2022 05:13

Вы проверили, применяется ли формула ко всем строкам столбца J?

JRichardsz 13.12.2022 05:56

Попробуйте заменить вашу формулу на: String kkf = "IF(AND(NOT(ISBLANK(J4)),NOT(ISNUMBER(FIND(J4,PossibleValue‌​s!$D$2)))),TRUE,FALS‌​E)"; Это работает для меня (но чувствительно к регистру)

Philippe Fery 14.12.2022 13:19

@PhilippeFery Спасибо за ответ .. это сработало .. но одна загвоздка в том, что предоставленная вами формула будет обрабатывать одно значение за раз. Если я ввожу их через запятую, например (FL, MD), это также выделяется. Ожидается также обработка значений, разделенных запятыми.

Chennai Cheetah 14.12.2022 13:39

Хорошо, я посмотрю на это

Philippe Fery 14.12.2022 13:50

Следует упомянуть связанный с этим ваш вопрос для условного форматирования значений, разделенных запятыми, особенно если это требование здесь.

hc_dev 17.12.2022 13:50
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
Как вычислять биты и понимать побитовые операторы в Java - объяснение с примерами
Как вычислять биты и понимать побитовые операторы в Java - объяснение с примерами
В компьютерном программировании биты играют важнейшую роль в представлении и манипулировании данными на двоичном уровне. Побитовые операции...
Поднятие тревоги для долго выполняющихся методов в Spring Boot
Поднятие тревоги для долго выполняющихся методов в Spring Boot
Приходилось ли вам сталкиваться с требованиями, в которых вас могли попросить поднять тревогу или выдать ошибку, когда метод Java занимает больше...
Полный курс Java для разработчиков веб-сайтов и приложений
Полный курс Java для разработчиков веб-сайтов и приложений
Получите сертификат Java Web и Application Developer, используя наш курс.
3
11
169
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Короткий ответ

Проблема связана с функцией FILTERXML, используемой в вашем условном форматировании. Это должно иметь префикс _xlfn в хранилище листов XML. Чтобы заставить его работать, ваша формула должна быть изменена на следующую:

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

Причина

Функция FILTERXML была введена в Excel 2013. Таким образом, она была представлена ​​после того, как Microsoft опубликовала формат файлов Office Open XML для файлов Microsoft Office в 2006 году для Office 2007. Чтобы пометить такие позже введенные функции, Microsoft решила добавить к ним префикс _xlfn в хранилище XML. . После чтения XML графический интерфейс Excel знает, известна ли функция в этой версии Excel или нет. Если да, префикс удаляется, а имя функции локализуется (например, в немецком Excel: на XMLFILTER). Если нет, префикс остается, и пользователь также знает, что эта функция неизвестна в этой версии Excel. См. Выпуск: _xlfn. префикс отображается перед формулой.

Зачем нам нужна версия формулы хранения?

В Apache POI Cell.setCellFormula, а также SheetConditionalFormatting.createConditionalFormattingRule устанавливает строку формулы напрямую в XML. Так что эта строка должна быть именно такой, какой она должна храниться в XML.

Узнайте больше об этом, проверив форматы файлов

Office Open XML файлы, а также *.xlsx файлы — это ZIP-архивы, содержащие XML-файлы и другие файлы в определенной структуре каталогов. Таким образом, можно просто разархивировать файл *.xlsx, чтобы просмотреть его.

Поэтому после создания color.xlsx с помощью кода разархивируйте его и загляните в xl/worksheets/sheet1.xml.

Вы найдете что-то вроде:

<conditionalFormatting sqref = "J4:J10000">
 <cfRule type = "expression" dxfId = "1" priority = "2">
  <formula>
   <![CDATA[ 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()) ]]>
  </formula>
 </cfRule>
</conditionalFormatting>

И это не работает в Excel.

Теперь откройте color.xlsx в Excel и заставьте его работать. Затем сохраните color.xlsx, снова разархивируйте и загляните в xl/worksheets/sheet1.xml.

Вы найдете что-то вроде:

<x14:conditionalFormattings>
 <x14:conditionalFormatting xmlns:xm = "http://schemas.microsoft.com/office/excel/2006/main">
  <x14:cfRule type = "expression" priority = "2" id = "{00000000-000E-0000-0000-000002000000}">
   <xm:f>IF(AND(NOT(ISBLANK(J4)),ISERROR(SUM(MATCH(_xlfn.FILTERXML("<t><s>"&SUBSTITUTE(J4,",","</s><s>")&"</s></t>","//s"),_xlfn.FILTERXML("<t><s>"&SUBSTITUTE(PossibleValues!$D$2,",","</s><s>")&"</s></t>","//s"),0))),""),TRUE())</xm:f>
  ...
  </x14:cfRule>
  ...
 </x14:conditionalFormatting>
</x14:conditionalFormattings>

Это показывает префикс _xlfn перед FILTERXML в строке формулы.

Это также показывает, что используется совершенно другая версия условного форматирования. Но это второстепенная проблема. Версия строки формулы с префиксом также работает как строка формулы в старой версии conditionalFormatting. Но, конечно, это не гарантируется во всех случаях. Таким образом, чтобы быть на стороне спасения во всех случаях, должна быть предоставлена ​​одна из двух возможностей:

  • любой из них избегает использования всех функций Excel, представленных после публикации Office Open XML в 2006 г. (Office 2007)
  • или Apache POI должен полностью поддерживать все изменения, внесенные Microsoft после публикации Office Open XML в 2006 году (Office 2007).

Для выполнения последнего Apache POI далеко. Он даже не поддерживает все функции Office 2007 до сих пор.

Префикс см. также Что означают _xlfn и _xlpm в excel?

hc_dev 17.12.2022 14:11

@Axel Большое спасибо .. Наконец-то нашел решение, а также спасибо за очень подробное объяснение.

Chennai Cheetah 19.12.2022 14:11

Если использование VBA является для вас приемлемым решением, то возможным решением будет создание файла .xlsm, содержащего функции, необходимые для условного форматирования.

  1. Создайте вручную файл color.xlsm
  2. Добавьте 2 пустых листа «Набор правил» и «Возможные значения».
  3. Откройте редактор VBA
  4. В листе «Набор правил» добавьте следующий Sub:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$j$4:$j$1000")) Is Nothing Then
        Highlight Target
    End If
End Sub
  1. Создайте модуль VBA и добавьте следующие Sub...
Sub Highlight(ByVal Cell As Range)
    Dim Entry() As String
    Entry = Split(Cell.Value, ",")     
    Dim StatesString As String
    StatesString = UCase(Worksheets("PossibleValues").Range("D2").Value)
    Dim States() As String
    States = Split(StatesString, ",")

    Dim Matches As Integer
    Matches = 0
    For EntryIdx = 0 To UBound(Entry)
        If (IsInArray(Entry(EntryIdx), States)) Then
            Matches = Matches + 1
        End If
    Next EntryIdx

    If (UBound(Entry) + 1 = Matches) Then
        Cell.Font.Color = vbBlack
    Else
        Cell.Font.Color = vbRed
    End If End Sub

...и следующую функцию:

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
    'DEVELOPER: Ryan Wells (wellsr.com)
    'DESCRIPTION: Function to check if a value is in an array of values
    'INPUT: Pass the function a value to search for and an array of values of any data type.
    'OUTPUT: True if is in array, false otherwise
    Dim element As Variant
    On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function
  1. Наконец, вам нужно будет адаптировать свой код, чтобы открывать этот файл .xlsm, созданный вручную, а не создавать его программно.

Надеюсь, это поможет

Другие вопросы по теме