/* | |
* ==================================================================== | |
* Licensed to the Apache Software Foundation (ASF) under one or more | |
* contributor license agreements. See the NOTICE file distributed with | |
* this work for additional information regarding copyright ownership. | |
* The ASF licenses this file to You under the Apache License, Version 2.0 | |
* (the "License"); you may not use this file except in compliance with | |
* the License. You may obtain a copy of the License at | |
* | |
* http://www.apache.org/licenses/LICENSE-2.0 | |
* | |
* Unless required by applicable law or agreed to in writing, software | |
* distributed under the License is distributed on an "AS IS" BASIS, | |
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
* See the License for the specific language governing permissions and | |
* limitations under the License. | |
* ==================================================================== | |
*/ | |
package org.apache.poi.ss.examples; | |
import org.apache.poi.hssf.usermodel.*; | |
import org.apache.poi.ss.usermodel.*; | |
import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType; | |
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet; | |
import org.apache.poi.ss.util.CellRangeAddress; | |
import org.apache.poi.xssf.usermodel.XSSFWorkbook; | |
import java.io.FileOutputStream; | |
import java.io.IOException; | |
/** | |
* Excel Conditional Formatting -- Examples | |
* | |
* <p> | |
* Partly based on the code snippets from | |
* http://www.contextures.com/xlcondformat03.html | |
* </p> | |
*/ | |
public class ConditionalFormats { | |
public static void main(String[] args) throws IOException { | |
Workbook wb; | |
if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); | |
else wb = new XSSFWorkbook(); | |
sameCell(wb.createSheet("Same Cell")); | |
multiCell(wb.createSheet("MultiCell")); | |
overlapping(wb.createSheet("Overlapping")); | |
errors(wb.createSheet("Errors")); | |
hideDupplicates(wb.createSheet("Hide Dups")); | |
formatDuplicates(wb.createSheet("Duplicates")); | |
inList(wb.createSheet("In List")); | |
expiry(wb.createSheet("Expiry")); | |
shadeAlt(wb.createSheet("Shade Alt")); | |
shadeBands(wb.createSheet("Shade Bands")); | |
iconSets(wb.createSheet("Icon Sets")); | |
// TODO Add colour scales, data bars etc, see bug #58130 | |
// Write the output to a file | |
String file = "cf-poi.xls"; | |
if(wb instanceof XSSFWorkbook) file += "x"; | |
FileOutputStream out = new FileOutputStream(file); | |
wb.write(out); | |
out.close(); | |
System.out.println("Generated: " + file); | |
} | |
/** | |
* Highlight cells based on their values | |
*/ | |
static void sameCell(Sheet sheet) { | |
sheet.createRow(0).createCell(0).setCellValue(84); | |
sheet.createRow(1).createCell(0).setCellValue(74); | |
sheet.createRow(2).createCell(0).setCellValue(50); | |
sheet.createRow(3).createCell(0).setCellValue(51); | |
sheet.createRow(4).createCell(0).setCellValue(49); | |
sheet.createRow(5).createCell(0).setCellValue(41); | |
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); | |
// Condition 1: Cell Value Is greater than 70 (Blue Fill) | |
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70"); | |
PatternFormatting fill1 = rule1.createPatternFormatting(); | |
fill1.setFillBackgroundColor(IndexedColors.BLUE.index); | |
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); | |
// Condition 2: Cell Value Is less than 50 (Green Fill) | |
ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50"); | |
PatternFormatting fill2 = rule2.createPatternFormatting(); | |
fill2.setFillBackgroundColor(IndexedColors.GREEN.index); | |
fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); | |
CellRangeAddress[] regions = { | |
CellRangeAddress.valueOf("A1:A6") | |
}; | |
sheetCF.addConditionalFormatting(regions, rule1, rule2); | |
sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)"); | |
sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)"); | |
} | |
/** | |
* Highlight multiple cells based on a formula | |
*/ | |
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)"); | |
} | |
/** | |
* Multiple conditional formatting rules can apply to | |
* one cell, some combining, some beating others. | |
* Done in order of the rules added to the | |
* SheetConditionalFormatting object | |
*/ | |
static void overlapping(Sheet sheet) { | |
for (int i=0; i<40; i++) { | |
int rn = i+1; | |
Row r = sheet.createRow(i); | |
r.createCell(0).setCellValue("This is row " + rn + " (" + i + ")"); | |
String str = ""; | |
if (rn%2 == 0) str = str + "even "; | |
if (rn%3 == 0) str = str + "x3 "; | |
if (rn%5 == 0) str = str + "x5 "; | |
if (rn%10 == 0) str = str + "x10 "; | |
if (str.length() == 0) str = "nothing special..."; | |
r.createCell(1).setCellValue("It is " + str); | |
} | |
sheet.autoSizeColumn(0); | |
sheet.autoSizeColumn(1); | |
sheet.getRow(1).createCell(3).setCellValue("Even rows are blue"); | |
sheet.getRow(2).createCell(3).setCellValue("Multiples of 3 have a grey background"); | |
sheet.getRow(4).createCell(3).setCellValue("Multiples of 5 are bold"); | |
sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)"); | |
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); | |
// Condition 1: Row divides by 10, red (will beat #1) | |
ConditionalFormattingRule rule1 = | |
sheetCF.createConditionalFormattingRule("MOD(ROW(),10)=0"); | |
FontFormatting font1 = rule1.createFontFormatting(); | |
font1.setFontColorIndex(IndexedColors.RED.index); | |
// Condition 2: Row is even, blue | |
ConditionalFormattingRule rule2 = | |
sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0"); | |
FontFormatting font2 = rule2.createFontFormatting(); | |
font2.setFontColorIndex(IndexedColors.BLUE.index); | |
// Condition 3: Row divides by 5, bold | |
ConditionalFormattingRule rule3 = | |
sheetCF.createConditionalFormattingRule("MOD(ROW(),5)=0"); | |
FontFormatting font3 = rule3.createFontFormatting(); | |
font3.setFontStyle(false, true); | |
// Condition 4: Row divides by 3, grey background | |
ConditionalFormattingRule rule4 = | |
sheetCF.createConditionalFormattingRule("MOD(ROW(),3)=0"); | |
PatternFormatting fill4 = rule4.createPatternFormatting(); | |
fill4.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index); | |
fill4.setFillPattern(PatternFormatting.SOLID_FOREGROUND); | |
// Apply | |
CellRangeAddress[] regions = { | |
CellRangeAddress.valueOf("A1:F41") | |
}; | |
sheetCF.addConditionalFormatting(regions, rule1); | |
sheetCF.addConditionalFormatting(regions, rule2); | |
sheetCF.addConditionalFormatting(regions, rule3); | |
sheetCF.addConditionalFormatting(regions, rule4); | |
} | |
/** | |
* 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. | |
*/ | |
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)"); | |
} | |
/** | |
* 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. | |
*/ | |
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)"); | |
} | |
/** | |
* Use Excel conditional formatting to highlight duplicate entries in a column. | |
*/ | |
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)"); | |
} | |
/** | |
* Use Excel conditional formatting to highlight items that are in a list on the worksheet. | |
*/ | |
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"); | |
} | |
/** | |
* 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. | |
*/ | |
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"); | |
} | |
/** | |
* Use Excel conditional formatting to shade alternating rows on the worksheet | |
*/ | |
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)"); | |
} | |
/** | |
* 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. | |
*/ | |
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)"); | |
} | |
/** | |
* Icon Sets / Multi-States allow you to have icons shown which vary | |
* based on the values, eg Red traffic light / Yellow traffic light / | |
* Green traffic light | |
*/ | |
static void iconSets(Sheet sheet) { | |
sheet.createRow(0).createCell(0).setCellValue("Icon Sets"); | |
Row r = sheet.createRow(1); | |
r.createCell(0).setCellValue("Reds"); | |
r.createCell(1).setCellValue(0); | |
r.createCell(2).setCellValue(0); | |
r.createCell(3).setCellValue(0); | |
r = sheet.createRow(2); | |
r.createCell(0).setCellValue("Yellows"); | |
r.createCell(1).setCellValue(5); | |
r.createCell(2).setCellValue(5); | |
r.createCell(3).setCellValue(5); | |
r = sheet.createRow(3); | |
r.createCell(0).setCellValue("Greens"); | |
r.createCell(1).setCellValue(10); | |
r.createCell(2).setCellValue(10); | |
r.createCell(3).setCellValue(10); | |
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); | |
CellRangeAddress[] regions = { CellRangeAddress.valueOf("B1:B4") }; | |
ConditionalFormattingRule rule1 = | |
sheetCF.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS); | |
IconMultiStateFormatting im1 = rule1.getMultiStateFormatting(); | |
im1.getThresholds()[0].setRangeType(RangeType.MIN); | |
im1.getThresholds()[1].setRangeType(RangeType.PERCENT); | |
im1.getThresholds()[1].setValue(33d); | |
im1.getThresholds()[2].setRangeType(RangeType.MAX); | |
sheetCF.addConditionalFormatting(regions, rule1); | |
regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C1:C4") }; | |
ConditionalFormattingRule rule2 = | |
sheetCF.createConditionalFormattingRule(IconSet.GYR_3_FLAGS); | |
IconMultiStateFormatting im2 = rule1.getMultiStateFormatting(); | |
im2.getThresholds()[0].setRangeType(RangeType.PERCENT); | |
im2.getThresholds()[0].setValue(0d); | |
im2.getThresholds()[1].setRangeType(RangeType.PERCENT); | |
im2.getThresholds()[1].setValue(33d); | |
im2.getThresholds()[2].setRangeType(RangeType.PERCENT); | |
im2.getThresholds()[2].setValue(67d); | |
sheetCF.addConditionalFormatting(regions, rule2); | |
regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D1:D4") }; | |
ConditionalFormattingRule rule3 = | |
sheetCF.createConditionalFormattingRule(IconSet.GYR_3_SYMBOLS_CIRCLE); | |
IconMultiStateFormatting im3 = rule1.getMultiStateFormatting(); | |
im3.setIconOnly(true); | |
im3.getThresholds()[0].setRangeType(RangeType.MIN); | |
im3.getThresholds()[1].setRangeType(RangeType.NUMBER); | |
im3.getThresholds()[1].setValue(3d); | |
im3.getThresholds()[2].setRangeType(RangeType.NUMBER); | |
im3.getThresholds()[2].setValue(7d); | |
sheetCF.addConditionalFormatting(regions, rule3); | |
} | |
} |