Я создаю ниже правило условного форматирования в Apache POI.
Ожидал:
Действительный:
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();
}
}
}
Теперь я сделал следующие шаги
После этого действия значения столбца подсвечиваются правильно.
Есть ли что-то, что я пропустил в своем Java-коде? Как избежать этой ручной работы в Excel?
Невозможно воспроизвести что-либо в excel с этой информацией. Однако вы можете захотеть оценить формулы перед записью в файл: workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
@XtremeBaumer Я обновил сообщение и добавил информацию, связанную с XML. Позвольте мне знать, если это помогает
@XtremeBaumer Кстати, формула оценки не помогла.
Я твердо убежден, что вы либо опускаете важную информацию, либо формула не работает. Если вы не можете предоставить воспроизводимый пример, я не вижу, как мы можем вам помочь.
@XtremeBaumer Я добавил полный код для справки со всеми снимками.
Вы проверили, применяется ли формула ко всем строкам столбца J?
Попробуйте заменить вашу формулу на: String kkf = "IF(AND(NOT(ISBLANK(J4)),NOT(ISNUMBER(FIND(J4,PossibleValues!$D$2)))),TRUE,FALSE)";
Это работает для меня (но чувствительно к регистру)
@PhilippeFery Спасибо за ответ .. это сработало .. но одна загвоздка в том, что предоставленная вами формула будет обрабатывать одно значение за раз. Если я ввожу их через запятую, например (FL, MD), это также выделяется. Ожидается также обработка значений, разделенных запятыми.
Хорошо, я посмотрю на это
Следует упомянуть связанный с этим ваш вопрос для условного форматирования значений, разделенных запятыми, особенно если это требование здесь.
Проблема связана с функцией 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
. Но, конечно, это не гарантируется во всех случаях. Таким образом, чтобы быть на стороне спасения во всех случаях, должна быть предоставлена одна из двух возможностей:
Office Open XML
в 2006 г. (Office 2007)Office Open XML
в 2006 году (Office 2007).Для выполнения последнего Apache POI далеко. Он даже не поддерживает все функции Office 2007 до сих пор.
Префикс см. также Что означают _xlfn и _xlpm в excel?
@Axel Большое спасибо .. Наконец-то нашел решение, а также спасибо за очень подробное объяснение.
Если использование VBA является для вас приемлемым решением, то возможным решением будет создание файла .xlsm, содержащего функции, необходимые для условного форматирования.
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
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
Надеюсь, это поможет
Вам нужно будет уточнить часть
it should highlight the selected cells in column 'J' but the entire column 'J' is getting highlighted
. На скриншоте снова и снова показаны одни и те же значения, поэтому неудивительно, что все они выделены красным шрифтом. И из того, что я могу сказать, это не проблема с диапазоном ячеек, который вы выделяете. Таким образом, вы также должны уточнить, в чем на самом деле проблема. Также некоторые необработанные данные помогут воспроизвести проблему.