List of usage examples for org.apache.poi.ss.usermodel SheetConditionalFormatting addConditionalFormatting
int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules);
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to shade alternating rows on the worksheet *///from ww w. j a va 2 s .c o m static void shadeAlt(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * You can use Excel conditional formatting to shade bands of rows on the worksheet. * In this example, 3 rows are shaded light grey, and 3 are left with no shading. * In the MOD function, the total number of rows in the set of banded rows (6) is entered. */// w w w .ja va 2 s .c o m static void shadeBands(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),6)<2 (Light Grey Fill)"); }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
public void extendCellRangesForConditionalFormattings() throws Exception { if (workbook instanceof SXSSFWorkbook) { warn("Cannot extend cell ranges for conditional formats in the memory the saving mode (use of the streaming-workbook)."); } else {// w w w.j a v a 2 s. c om int firstDataRowIndex = firstRowIsHeader ? rowStartIndex + 1 : rowStartIndex; info("Extending cell ranges for conditional formats. Use formats from row: " + firstDataRowIndex); if (getLastRowNum() > 0 && getLastRowNum() > firstDataRowIndex) { SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting(); if (debug) { debug("#### Conditional formattings before:"); debug(logoutSheetConditionalFormatting(scf)); } ConditionalFormatting lastCf = null; for (Integer cellColumnIndex : usedCellColumnIndexes) { if (debug) { debug("extendCellRangesForConditionalFormattings: check format for cell index=" + cellColumnIndex); } find(scf, firstDataRowIndex, cellColumnIndex); // currentCf and currentCfIndex will be set here if (currentCf != null && currentCf != lastCf) { if (debug) { debug("extendCellRangesForConditionalFormattings: found format for cell index=" + cellColumnIndex); } lastCf = currentCf; CellRangeAddress[] ranges = currentCf.getFormattingRanges(); for (int i = 0; i < ranges.length; i++) { CellRangeAddress address = ranges[i]; CellRangeAddress extendedAddress = new CellRangeAddress(address.getFirstRow(), getLastRowNum(), address.getFirstColumn(), address.getLastColumn()); ranges[i] = extendedAddress; } if (debug) { debug("extendCellRangesForConditionalFormattings: extend ranges to=" + firstDataRowIndex + ":" + getLastRowNum() + " -> " + getRangesAsString(ranges)); } int numRulesTotal = currentCf.getNumberOfRules(); if (numRulesTotal > 0) { int chunks = numRulesTotal / maxRuleChunkSize; int restChunkSize = numRulesTotal % maxRuleChunkSize; int currentSize = 0; for (int c = 0; c <= chunks; c++) { if (c < chunks) { // all not-last chunks have the max chunk size currentSize = maxRuleChunkSize; } else { // the last chunk contains the rest currentSize = restChunkSize; } if (currentSize > 0) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[currentSize]; for (int i = 0; i < currentSize; i++) { int ruleIndex = i + (maxRuleChunkSize * c); // current pointer within a chunk + chunk offset rules[i] = currentCf.getRule(ruleIndex); if (debug) { debug("extendCellRangesForConditionalFormattings: add ranges: " + getRangesAsString(ranges) + " rule #" + ruleIndex + " =" + describeRule(rules[i])); } } scf.addConditionalFormatting(ranges, rules); } } if (debug) { debug("extendCellRangesForConditionalFormattings: remove template format at index:" + currentCfIndex); } scf.removeConditionalFormatting(currentCfIndex); } } } if (debug) { debug("#### Conditional formattings after:"); debug(logoutSheetConditionalFormatting(scf)); } } } }
From source file:net.ceos.project.poi.annotated.core.ConditionalFormattingHandler.java
License:Apache License
/** * Apply the conditional formatting according the values defined at the * respective annotation. Is only available at the declared object, in * another words, at the linked {@link Sheet} with the object. * /*from ww w .java2s. c o m*/ * @param configCriteria * the {@link XConfigCriteria} * @param conditionalFomat * the {@link XlsConditionalFormat} * @throws ConfigurationException */ protected static void applyCondition(XConfigCriteria configCriteria, XlsConditionalFormat conditionalFomat) throws ConfigurationException { // Define a Conditional Formatting rule, which triggers formatting // according the developer definition and applies patternFormatting SheetConditionalFormatting sheet = configCriteria.getSheet().getSheetConditionalFormatting(); /* apply all rules defined */ int i = 0; ConditionalFormattingRule[] rules = new ConditionalFormattingRule[conditionalFomat.rules().length]; XlsConditionalFormatRules[] rulesAnnotated = conditionalFomat.rules(); for (XlsConditionalFormatRules rule : rulesAnnotated) { ConditionalFormattingRule setRule = sheet.createConditionalFormattingRule(rule.operator(), rule.formula1(), StringUtils.isNotBlank(rule.formula2()) ? rule.formula2() : null); CellStyle decorator = null; try { decorator = configCriteria.getCellStyle(conditionalFomat.decorator()); } catch (ElementException e) { throw new ConfigurationException(ExceptionMessage.CONFIGURATION_DECORATOR_MISSING.getMessage(), e); } /* add FontFormatting */ FontFormatting fontFormat = setRule.createFontFormatting(); Font f = configCriteria.getWorkbook().getFontAt(decorator.getFontIndex()); fontFormat.setFontStyle(f.getItalic(), f.getBold()); fontFormat.setFontColorIndex(f.getColor()); fontFormat.setUnderlineType(f.getUnderline()); /* add BorderFormatting */ BorderFormatting borderFormat = setRule.createBorderFormatting(); borderFormat.setBorderBottom(decorator.getBorderBottom()); borderFormat.setBorderTop(decorator.getBorderTop()); borderFormat.setBorderLeft(decorator.getBorderLeft()); borderFormat.setBorderRight(decorator.getBorderRight()); borderFormat.setBottomBorderColor(decorator.getBottomBorderColor()); borderFormat.setTopBorderColor(decorator.getTopBorderColor()); borderFormat.setLeftBorderColor(decorator.getLeftBorderColor()); borderFormat.setRightBorderColor(decorator.getRightBorderColor()); /* add PatternFormatting */ PatternFormatting patternFormat = setRule.createPatternFormatting(); patternFormat.setFillBackgroundColor(decorator.getFillForegroundColor()); /* join rule */ rules[i++] = setRule; } /* Define a region */ CellRangeAddress[] regions = { CellRangeAddress.valueOf(CellFormulaConverter .calculateRangeAddressFromTemplate(configCriteria, conditionalFomat.rangeAddress())) }; /* Apply Conditional Formatting rule defined above to the regions */ sheet.addConditionalFormatting(regions, rules); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Highlight multiple cells based on a formula */// w ww. java 2 s. com static void multiCell(Sheet sheet) { // header row Row row0 = sheet.createRow(0); row0.createCell(0).setCellValue("Units"); row0.createCell(1).setCellValue("Cost"); row0.createCell(2).setCellValue("Total"); Row row1 = sheet.createRow(1); row1.createCell(0).setCellValue(71); row1.createCell(1).setCellValue(29); row1.createCell(2).setCellValue(2059); Row row2 = sheet.createRow(2); row2.createCell(0).setCellValue(85); row2.createCell(1).setCellValue(29); row2.createCell(2).setCellValue(2059); Row row3 = sheet.createRow(3); row3.createCell(0).setCellValue(71); row3.createCell(1).setCellValue(29); row3.createCell(2).setCellValue(2059); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =$B2>75 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75 (Blue Fill)"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to check for errors, and change the font * colour to match the cell colour. In this example, if formula result is * #DIV/0! then it will have white font colour. *//*from ww w . j a v a 2 s . c o m*/ static void errors(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(0); sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)"); sheet.createRow(3).createCell(0).setCellValue(0); sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)"); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =ISERROR(C2) (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)"); FontFormatting font = rule1.createFontFormatting(); font.setFontColorIndex(IndexedColors.WHITE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue( "<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)"); sheet.getRow(4).createCell(1).setCellValue( "<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to hide the duplicate values, and make * the list easier to read. In this example, when the table is sorted by * Region, the second (and subsequent) occurences of each region name will * have white font colour.//from w ww. ja v a 2 s . com */ static void hideDupplicates(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("City"); sheet.createRow(1).createCell(0).setCellValue("Boston"); sheet.createRow(2).createCell(0).setCellValue("Boston"); sheet.createRow(3).createCell(0).setCellValue("Chicago"); sheet.createRow(4).createCell(0).setCellValue("Chicago"); sheet.createRow(5).createCell(0).setCellValue("New York"); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1"); FontFormatting font = rule1.createFontFormatting(); font.setFontColorIndex(IndexedColors.WHITE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(1).createCell(1) .setCellValue("<== the second (and subsequent) " + "occurences of each region name will have white font colour. " + "Condition: Formula Is =A2=A1 (White Font)"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight duplicate entries in a * column.//from www . j a v a2 s . c o m */ static void formatDuplicates(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Code"); sheet.createRow(1).createCell(0).setCellValue(4); sheet.createRow(2).createCell(0).setCellValue(3); sheet.createRow(3).createCell(0).setCellValue(6); sheet.createRow(4).createCell(0).setCellValue(3); sheet.createRow(5).createCell(0).setCellValue(5); sheet.createRow(6).createCell(0).setCellValue(8); sheet.createRow(7).createCell(0).setCellValue(0); sheet.createRow(8).createCell(0).setCellValue(2); sheet.createRow(9).createCell(0).setCellValue(8); sheet.createRow(10).createCell(0).setCellValue(6); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight items that are in a list on * the worksheet./*w w w.j a v a 2s . c o m*/ */ static void inList(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Codes"); sheet.createRow(1).createCell(0).setCellValue("AA"); sheet.createRow(2).createCell(0).setCellValue("BB"); sheet.createRow(3).createCell(0).setCellValue("GG"); sheet.createRow(4).createCell(0).setCellValue("AA"); sheet.createRow(5).createCell(0).setCellValue("FF"); sheet.createRow(6).createCell(0).setCellValue("XX"); sheet.createRow(7).createCell(0).setCellValue("CC"); sheet.getRow(0).createCell(2).setCellValue("Valid"); sheet.getRow(1).createCell(2).setCellValue("AA"); sheet.getRow(2).createCell(2).setCellValue("BB"); sheet.getRow(3).createCell(2).setCellValue("CC"); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(3).setCellValue( "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight payments that are due in * the next thirty days. In this example, Due dates are entered in cells * A2:A4.// w w w. j a v a2 s . c o m */ static void expiry(Sheet sheet) { CellStyle style = sheet.getWorkbook().createCellStyle(); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm")); sheet.createRow(0).createCell(0).setCellValue("Date"); sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29"); sheet.createRow(2).createCell(0).setCellFormula("A2+1"); sheet.createRow(3).createCell(0).setCellFormula("A3+1"); for (int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted"); }