You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2015/07/18 06:55:27 UTC
svn commit: r1691679 - in /poi/trunk/src:
examples/src/org/apache/poi/ss/examples/ java/org/apache/poi/hssf/record/
java/org/apache/poi/hssf/record/aggregates/
java/org/apache/poi/hssf/record/cf/ java/org/apache/poi/hssf/usermodel/
ooxml/java/org/apach...
Author: nick
Date: Sat Jul 18 04:55:27 2015
New Revision: 1691679
URL: http://svn.apache.org/r1691679
Log:
#58130 Mostly there with CF Icon sets
Modified:
poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java
poi/trunk/src/java/org/apache/poi/hssf/record/CFRule12Record.java
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java
poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java
Modified: poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java (original)
+++ poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java Sat Jul 18 04:55:27 2015
@@ -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
*
* <p>
- * 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
* </p>
- *
- * @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);
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/CFRule12Record.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/CFRule12Record.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/CFRule12Record.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/CFRule12Record.java Sat Jul 18 04:55:27 2015
@@ -129,8 +129,9 @@ public final class CFRule12Record extend
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
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ConditionalFormattingTable.java Sat Jul 18 04:55:27 2015
@@ -61,6 +61,7 @@ public final class ConditionalFormatting
* @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;
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java Sat Jul 18 04:55:27 2015
@@ -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);
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java Sat Jul 18 04:55:27 2015
@@ -77,9 +77,6 @@ public final class HSSFConditionalFormat
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");
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFIconMultiStateFormatting.java Sat Jul 18 04:55:27 2015
@@ -58,7 +58,7 @@ public final class HSSFIconMultiStateFor
iconFormatting.setReversed(reversed);
}
- public ConditionalFormattingThreshold[] getThresholds() {
+ public HSSFConditionalFormattingThreshold[] getThresholds() {
Threshold[] t = iconFormatting.getThresholds();
HSSFConditionalFormattingThreshold[] ht = new HSSFConditionalFormattingThreshold[t.length];
for (int i=0; i<t.length; i++) {
@@ -74,4 +74,8 @@ public final class HSSFIconMultiStateFor
}
iconFormatting.setThresholds(t);
}
+
+ public HSSFConditionalFormattingThreshold createThreshold() {
+ return new HSSFConditionalFormattingThreshold(new Threshold(), sheet);
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingThreshold.java Sat Jul 18 04:55:27 2015
@@ -28,9 +28,17 @@ import org.openxmlformats.schemas.spread
*/
public class XSSFConditionalFormattingThreshold implements org.apache.poi.ss.usermodel.ConditionalFormattingThreshold {
private CTCfvo cfvo;
+
+ protected XSSFConditionalFormattingThreshold(CTCfvo cfvo) {
+ this.cfvo = cfvo;
+ }
+
+ protected CTCfvo getCTCfvo() {
+ return cfvo;
+ }
public RangeType getRangeType() {
- return RangeType.valueOf(cfvo.getType().toString());
+ return RangeType.byName(cfvo.getType().toString());
}
public void setRangeType(RangeType type) {
STCfvoType.Enum xtype = STCfvoType.Enum.forString(type.name);
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFIconMultiStateFormatting.java Sat Jul 18 04:55:27 2015
@@ -20,6 +20,7 @@ package org.apache.poi.xssf.usermodel;
import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfvo;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTIconSet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STIconSetType;
@@ -35,9 +36,8 @@ public class XSSFIconMultiStateFormattin
}
public IconSet getIconSet() {
- return IconSet.valueOf(
- _iconset.getIconSet().toString()
- );
+ String set = _iconset.getIconSet().toString();
+ return IconSet.byName(set);
}
public void setIconSet(IconSet set) {
STIconSetType.Enum xIconSet = STIconSetType.Enum.forString(set.name);
@@ -62,11 +62,24 @@ public class XSSFIconMultiStateFormattin
_iconset.setReverse(reversed);
}
+ @SuppressWarnings("deprecation")
public XSSFConditionalFormattingThreshold[] getThresholds() {
- // TODO Implement
- return null;
+ CTCfvo[] cfvos = _iconset.getCfvoArray();
+ XSSFConditionalFormattingThreshold[] t =
+ new XSSFConditionalFormattingThreshold[cfvos.length];
+ for (int i=0; i<cfvos.length; i++) {
+ t[i] = new XSSFConditionalFormattingThreshold(cfvos[i]);
+ }
+ return t;
}
public void setThresholds(ConditionalFormattingThreshold[] thresholds) {
- // TODO Implement
+ CTCfvo[] cfvos = new CTCfvo[thresholds.length];
+ for (int i=0; i<thresholds.length; i++) {
+ cfvos[i] = ((XSSFConditionalFormattingThreshold)thresholds[i]).getCTCfvo();
+ }
+ _iconset.setCfvoArray(cfvos);
+ }
+ public XSSFConditionalFormattingThreshold createThreshold() {
+ return new XSSFConditionalFormattingThreshold(_iconset.addNewCfvo());
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/TestCFRuleRecord.java Sat Jul 18 04:55:27 2015
@@ -32,6 +32,8 @@ import org.apache.poi.hssf.util.HSSFColo
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefNPtg;
import org.apache.poi.ss.formula.ptg.RefPtg;
+import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
+import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.util.LittleEndian;
/**
@@ -119,6 +121,46 @@ public final class TestCFRuleRecord exte
}
}
+ public void testCreateIconCFRule12Record() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ HSSFSheet sheet = workbook.createSheet();
+ CFRule12Record record = CFRule12Record.create(sheet, IconSet.GREY_5_ARROWS);
+ record.getMultiStateFormatting().getThresholds()[1].setType(RangeType.PERCENT.id);
+ record.getMultiStateFormatting().getThresholds()[1].setValue(10d);
+ record.getMultiStateFormatting().getThresholds()[2].setType(RangeType.NUMBER.id);
+ record.getMultiStateFormatting().getThresholds()[2].setValue(-4d);
+
+ // Check it
+ testCFRule12Record(record);
+ assertEquals(IconSet.GREY_5_ARROWS, record.getMultiStateFormatting().getIconSet());
+ assertEquals(5, record.getMultiStateFormatting().getThresholds().length);
+
+ // Serialize
+ byte [] serializedRecord = record.serialize();
+
+ // Strip header
+ byte [] recordData = new byte[serializedRecord.length-4];
+ System.arraycopy(serializedRecord, 4, recordData, 0, recordData.length);
+
+ // Deserialize
+ record = new CFRule12Record(TestcaseRecordInputStream.create(CFRule12Record.sid, recordData));
+
+ // Check it has the icon, and the right number of thresholds
+ assertEquals(IconSet.GREY_5_ARROWS, record.getMultiStateFormatting().getIconSet());
+ assertEquals(5, record.getMultiStateFormatting().getThresholds().length);
+
+ // Serialize again
+ byte[] output = record.serialize();
+
+ // Compare
+ assertEquals("Output size", recordData.length+4, output.length); //includes sid+recordlength
+
+ for (int i = 0; i < recordData.length;i++)
+ {
+ assertEquals("CFRule12Record doesn't match", recordData[i], output[i+4]);
+ }
+ }
+
private void testCFRuleRecord(CFRuleRecord record) {
testCFRuleBase(record);
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java?rev=1691679&r1=1691678&r2=1691679&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java Sat Jul 18 04:55:27 2015
@@ -24,6 +24,7 @@ import junit.framework.TestCase;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.ss.util.CellRangeAddress;
@@ -546,6 +547,7 @@ public abstract class BaseTestConditiona
ConditionalFormatting cf = null;
ConditionalFormattingRule cr = null;
IconMultiStateFormatting icon = null;
+ ConditionalFormattingThreshold th = null;
// Sanity check data
assertEquals("Values", s.getRow(0).getCell(0).toString());
@@ -572,7 +574,7 @@ public abstract class BaseTestConditiona
type == ConditionType.FORMULA) {
fCF++;
} else {
- // TODO Detect Ext ones
+ // TODO Properly detect Ext ones from the xml
fCF12++;
}
}
@@ -599,7 +601,7 @@ public abstract class BaseTestConditiona
// When it matches:
// Sets the font colour to dark green
// Sets the background colour to lighter green
- // TODO Should the colours be slightly different between formats?
+ // TODO Should the colours be slightly different between formats? Would CFEX support help for HSSF?
if (cr instanceof HSSFConditionalFormattingRule) {
assertColour("0:8080:0", cr.getFontFormatting().getFontColor());
assertColour("CCCC:FFFF:CCCC", cr.getPatternFormatting().getFillBackgroundColorColor());
@@ -623,7 +625,7 @@ public abstract class BaseTestConditiona
// When it matches:
// Sets the font colour to dark red
// Sets the background colour to lighter red
- // TODO Should the colours be slightly different between formats?
+ // TODO Should the colours be slightly different between formats? Would CFEX support help for HSSF?
if (cr instanceof HSSFConditionalFormattingRule) {
assertColour("8080:0:8080", cr.getFontFormatting().getFontColor());
assertColour("FFFF:9999:CCCC", cr.getPatternFormatting().getFillBackgroundColorColor());
@@ -641,13 +643,34 @@ public abstract class BaseTestConditiona
assertEquals(1, cf.getNumberOfRules());
cr = cf.getRule(0);
assertEquals(ConditionType.DATA_BAR, cr.getConditionTypeType());
- // TODO Support then check the rest
+ // TODO Support Data Bars, then check the rest of this rule
// Colours R->G - 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());
- // Icons : Default - Column H
+ 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, 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 BaseTestConditiona
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 BaseTestConditiona
// 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<vals.length; i++) {
+ Double v = vals[i];
+ ConditionalFormattingThreshold th = icon.getThresholds()[i];
+ assertEquals(RangeType.PERCENT, th.getRangeType());
+ assertEquals(v, th.getValue());
+ assertEquals(null, th.getFormula());
+ }
+ }
public void testCreateFontFormatting() {
Workbook workbook = _testDataProvider.createWorkbook();
@@ -859,8 +990,55 @@ public abstract class BaseTestConditiona
assertEquals(BorderFormatting.BORDER_HAIR, r1fp.getBorderRight());
}
- public void testCreateIconFormatting() {
- // TODO Implement for XSSF, then test here
+ // TODO Fix this test to work for HSSF
+ public void DISABLEDtestCreateIconFormatting() {
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+ ConditionalFormattingRule rule1 =
+ sheetCF.createConditionalFormattingRule(IconSet.GYRB_4_TRAFFIC_LIGHTS);
+ IconMultiStateFormatting iconFmt = rule1.getMultiStateFormatting();
+
+ assertEquals(IconSet.GYRB_4_TRAFFIC_LIGHTS, iconFmt.getIconSet());
+ assertEquals(4, iconFmt.getThresholds().length);
+ assertEquals(false, iconFmt.isIconOnly());
+ assertEquals(false, iconFmt.isReversed());
+
+ iconFmt.setIconOnly(true);
+ iconFmt.getThresholds()[0].setRangeType(RangeType.MIN);
+ iconFmt.getThresholds()[1].setRangeType(RangeType.NUMBER);
+ iconFmt.getThresholds()[1].setValue(10d);
+ iconFmt.getThresholds()[2].setRangeType(RangeType.PERCENT);
+ iconFmt.getThresholds()[2].setValue(75d);
+ iconFmt.getThresholds()[3].setRangeType(RangeType.MAX);
+
+ CellRangeAddress [] regions = { CellRangeAddress.valueOf("A1:A5") };
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ // Save, re-load and re-check
+ workbook = _testDataProvider.writeOutAndReadBack(workbook);
+ sheetCF = sheet.getSheetConditionalFormatting();
+ assertEquals(1, sheetCF.getNumConditionalFormattings());
+
+ ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
+ assertEquals(1, cf.getNumberOfRules());
+ rule1 = cf.getRule(0);
+ iconFmt = rule1.getMultiStateFormatting();
+
+ assertEquals(IconSet.GYRB_4_TRAFFIC_LIGHTS, iconFmt.getIconSet());
+ assertEquals(4, iconFmt.getThresholds().length);
+ assertEquals(true, iconFmt.isIconOnly());
+ assertEquals(false, iconFmt.isReversed());
+
+ assertEquals(RangeType.MIN, iconFmt.getThresholds()[0].getRangeType());
+ assertEquals(RangeType.NUMBER, iconFmt.getThresholds()[1].getRangeType());
+ assertEquals(RangeType.PERCENT,iconFmt.getThresholds()[2].getRangeType());
+ assertEquals(RangeType.MAX, iconFmt.getThresholds()[3].getRangeType());
+ assertEquals(null, iconFmt.getThresholds()[0].getValue());
+ assertEquals(10d, iconFmt.getThresholds()[1].getValue());
+ assertEquals(75d, iconFmt.getThresholds()[2].getValue());
+ assertEquals(null, iconFmt.getThresholds()[3].getValue());
}
public void testBug55380() {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org