From 7a8be4390118aec572f4b87e200e6290a7c9a4b0 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 18 Jul 2015 04:55:27 +0000 Subject: [PATCH] #58130 Mostly there with CF Icon sets git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1691679 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/examples/ConditionalFormats.java | 70 +++++- .../poi/hssf/record/CFRule12Record.java | 5 +- .../ConditionalFormattingTable.java | 1 + .../apache/poi/hssf/record/cf/Threshold.java | 5 +- .../usermodel/HSSFConditionalFormatting.java | 3 - .../HSSFIconMultiStateFormatting.java | 6 +- .../XSSFConditionalFormattingThreshold.java | 10 +- .../XSSFIconMultiStateFormatting.java | 25 +- .../poi/hssf/record/TestCFRuleRecord.java | 42 ++++ .../BaseTestConditionalFormatting.java | 214 ++++++++++++++++-- 10 files changed, 345 insertions(+), 36 deletions(-) diff --git a/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java b/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java index 47f6f05a30..dd1398b860 100644 --- a/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java +++ b/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java @@ -21,6 +21,8 @@ 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; @@ -31,10 +33,9 @@ import java.io.IOException; * Excel Conditional Formatting -- Examples * *

- * Based on the code snippets from http://www.contextures.com/xlcondformat03.html + * Partly based on the code snippets from + * http://www.contextures.com/xlcondformat03.html *

- * - * @author Yegor Kozlov */ public class ConditionalFormats { @@ -54,8 +55,9 @@ public class ConditionalFormats { expiry(wb.createSheet("Expiry")); shadeAlt(wb.createSheet("Shade Alt")); shadeBands(wb.createSheet("Shade Bands")); + iconSets(wb.createSheet("Icon Sets")); - // TODO Add Icons, data bars etc, see bug #58130 + // TODO Add colour scales, data bars etc, see bug #58130 // Write the output to a file String file = "cf-poi.xls"; @@ -414,4 +416,64 @@ public class ConditionalFormats { 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); + } } diff --git a/src/java/org/apache/poi/hssf/record/CFRule12Record.java b/src/java/org/apache/poi/hssf/record/CFRule12Record.java index a44f81cd27..c9d0f174b9 100644 --- a/src/java/org/apache/poi/hssf/record/CFRule12Record.java +++ b/src/java/org/apache/poi/hssf/record/CFRule12Record.java @@ -129,8 +129,9 @@ public final class CFRule12Record extends CFRuleBase implements FutureRecord { CFRule12Record r = new CFRule12Record(CONDITION_TYPE_COLOR_SCALE, ComparisonOperator.NO_COMPARISON); - r.getMultiStateFormatting().setIconSet(iconSet); - r.getMultiStateFormatting().setThresholds(ts); + IconMultiStateFormatting imf = r.createMultiStateFormatting(); + imf.setIconSet(iconSet); + imf.setThresholds(ts); return r; } // TODO Static creators for the other record types diff --git a/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java b/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java index e5ea515e80..e4721ca338 100644 --- a/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java +++ b/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java @@ -61,6 +61,7 @@ public final class ConditionalFormattingTable extends RecordAggregate { * @return index of the newly added CF header aggregate */ public int add(CFRecordsAggregate cfAggregate) { + cfAggregate.getHeader().setID(_cfHeaders.size()); _cfHeaders.add(cfAggregate); return _cfHeaders.size() - 1; } diff --git a/src/java/org/apache/poi/hssf/record/cf/Threshold.java b/src/java/org/apache/poi/hssf/record/cf/Threshold.java index 52b89501de..462027807c 100644 --- a/src/java/org/apache/poi/hssf/record/cf/Threshold.java +++ b/src/java/org/apache/poi/hssf/record/cf/Threshold.java @@ -74,6 +74,9 @@ public final class Threshold { public void setType(byte type) { this.type = type; } + public void setType(int type) { + this.type = (byte)type; + } protected Formula getFormula() { return formula; @@ -129,7 +132,7 @@ public final class Threshold { public void serialize(LittleEndianOutput out) { out.writeByte(type); - if (formula == null) { + if (formula.getTokens().length == 0) { out.writeShort(0); } else { formula.serialize(out); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java index 5c97b68e28..e5792770c0 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java @@ -77,9 +77,6 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { private final HSSFSheet sheet; private final CFRecordsAggregate cfAggregate; - // TODO Should this be assigning unique IDs to the rules - // as they get added to the file? - HSSFConditionalFormatting(HSSFSheet sheet, CFRecordsAggregate cfAggregate) { if(sheet == null) { throw new IllegalArgumentException("sheet must not be null"); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java index f15732c626..e93d5c918f 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java @@ -58,7 +58,7 @@ public final class HSSFIconMultiStateFormatting implements org.apache.poi.ss.use iconFormatting.setReversed(reversed); } - public ConditionalFormattingThreshold[] getThresholds() { + public HSSFConditionalFormattingThreshold[] getThresholds() { Threshold[] t = iconFormatting.getThresholds(); HSSFConditionalFormattingThreshold[] ht = new HSSFConditionalFormattingThreshold[t.length]; for (int i=0; iG - Column F + cf = sheetCF.getConditionalFormattingAt(3); + assertEquals(1, cf.getFormattingRanges().length); + assertEquals("F2:F17", cf.getFormattingRanges()[0].formatAsString()); + + assertEquals(1, cf.getNumberOfRules()); + cr = cf.getRule(0); + assertEquals(ConditionType.COLOR_SCALE, cr.getConditionTypeType()); + // TODO Support Color Scales, then check the rest of this rule + + // Colours BWR - Column G + cf = sheetCF.getConditionalFormattingAt(4); + assertEquals(1, cf.getFormattingRanges().length); + assertEquals("G2:G17", cf.getFormattingRanges()[0].formatAsString()); + + assertEquals(1, cf.getNumberOfRules()); + cr = cf.getRule(0); + assertEquals(ConditionType.COLOR_SCALE, cr.getConditionTypeType()); + // TODO Support Color Scales, then check the rest of this rule + + + // TODO Simplify asserts - // Icons : Default - Column H + // Icons : Default - Column H, percentage thresholds cf = sheetCF.getConditionalFormattingAt(5); assertEquals(1, cf.getFormattingRanges().length); assertEquals("H2:H17", cf.getFormattingRanges()[0].formatAsString()); @@ -658,21 +681,107 @@ public abstract class BaseTestConditionalFormatting extends TestCase { assertEquals(ComparisonOperator.NO_COMPARISON, cr.getComparisonOperation()); assertEquals(null, cr.getFormula1()); assertEquals(null, cr.getFormula2()); - if (cr instanceof HSSFConditionalFormattingRule) { - HSSFConditionalFormattingRule hcr = (HSSFConditionalFormattingRule)cr; - icon = hcr.getMultiStateFormatting(); - assertNotNull(icon); - assertEquals(IconSet.GYR_3_TRAFFIC_LIGHTS, icon.getIconSet()); - assertEquals(false, icon.isIconOnly()); - assertEquals(false, icon.isReversed()); - // TODO Check the rest - } else { - // TODO XSSF Support - } + + icon = cr.getMultiStateFormatting(); + assertNotNull(icon); + assertEquals(IconSet.GYR_3_TRAFFIC_LIGHTS, icon.getIconSet()); + assertEquals(false, icon.isIconOnly()); + assertEquals(false, icon.isReversed()); + + assertNotNull(icon.getThresholds()); + assertEquals(3, icon.getThresholds().length); + th = icon.getThresholds()[0]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(0.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[1]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(33.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[2]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(67.0d, th.getValue()); + assertEquals(null, th.getFormula()); + // Icons : 3 signs - Column I + cf = sheetCF.getConditionalFormattingAt(6); + assertEquals(1, cf.getFormattingRanges().length); + assertEquals("I2:I17", cf.getFormattingRanges()[0].formatAsString()); + + assertEquals(1, cf.getNumberOfRules()); + cr = cf.getRule(0); + assertEquals(ConditionType.ICON_SET, cr.getConditionTypeType()); + assertEquals(ComparisonOperator.NO_COMPARISON, cr.getComparisonOperation()); + assertEquals(null, cr.getFormula1()); + assertEquals(null, cr.getFormula2()); + + icon = cr.getMultiStateFormatting(); + assertNotNull(icon); + assertEquals(IconSet.GYR_3_SHAPES, icon.getIconSet()); + assertEquals(false, icon.isIconOnly()); + assertEquals(false, icon.isReversed()); + + assertNotNull(icon.getThresholds()); + assertEquals(3, icon.getThresholds().length); + th = icon.getThresholds()[0]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(0.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[1]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(33.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[2]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(67.0d, th.getValue()); + assertEquals(null, th.getFormula()); + + // Icons : 3 traffic lights 2 - Column J + cf = sheetCF.getConditionalFormattingAt(7); + assertEquals(1, cf.getFormattingRanges().length); + assertEquals("J2:J17", cf.getFormattingRanges()[0].formatAsString()); + + assertEquals(1, cf.getNumberOfRules()); + cr = cf.getRule(0); + assertEquals(ConditionType.ICON_SET, cr.getConditionTypeType()); + assertEquals(ComparisonOperator.NO_COMPARISON, cr.getComparisonOperation()); + assertEquals(null, cr.getFormula1()); + assertEquals(null, cr.getFormula2()); + + icon = cr.getMultiStateFormatting(); + assertNotNull(icon); + assertEquals(IconSet.GYR_3_TRAFFIC_LIGHTS_BOX, icon.getIconSet()); + assertEquals(false, icon.isIconOnly()); + assertEquals(false, icon.isReversed()); + + assertNotNull(icon.getThresholds()); + assertEquals(3, icon.getThresholds().length); + th = icon.getThresholds()[0]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(0.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[1]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(33.0d, th.getValue()); + assertEquals(null, th.getFormula()); + th = icon.getThresholds()[2]; + assertEquals(RangeType.PERCENT, th.getRangeType()); + assertEquals(67.0d, th.getValue()); + assertEquals(null, th.getFormula()); + + // Icons : 4 traffic lights - Column K + cf = sheetCF.getConditionalFormattingAt(8); + assertEquals(1, cf.getFormattingRanges().length); + assertEquals("K2:K17", cf.getFormattingRanges()[0].formatAsString()); + + assertEquals(1, cf.getNumberOfRules()); + cr = cf.getRule(0); + assertIconSetPercentages(cr, IconSet.GYRB_4_TRAFFIC_LIGHTS, 0d, 25d, 50d, 75d); + + // Icons : 3 symbols - Column L // Icons : 3 flags - Column M // Icons : 3 symbols 2 - Column N @@ -685,6 +794,28 @@ public abstract class BaseTestConditionalFormatting extends TestCase { // Mixed icons - Column U } + private void assertIconSetPercentages(ConditionalFormattingRule cr, IconSet iconset, Double...vals) { + assertEquals(ConditionType.ICON_SET, cr.getConditionTypeType()); + assertEquals(ComparisonOperator.NO_COMPARISON, cr.getComparisonOperation()); + assertEquals(null, cr.getFormula1()); + assertEquals(null, cr.getFormula2()); + + IconMultiStateFormatting icon = cr.getMultiStateFormatting(); + assertNotNull(icon); + assertEquals(iconset, icon.getIconSet()); + assertEquals(false, icon.isIconOnly()); + assertEquals(false, icon.isReversed()); + + assertNotNull(icon.getThresholds()); + assertEquals(vals.length, icon.getThresholds().length); + for (int i=0; i