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/16 21:34:06 UTC
svn commit: r1691434 - in /poi/trunk/src/java/org/apache/poi:
hssf/record/cf/ hssf/usermodel/ ss/usermodel/
Author: nick
Date: Thu Jul 16 19:34:06 2015
New Revision: 1691434
URL: http://svn.apache.org/r1691434
Log:
Start on conditional formatting thresholds
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingThreshold.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingThreshold.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/cf/IconMultiStateFormatting.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/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/IconMultiStateFormatting.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/cf/IconMultiStateFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/cf/IconMultiStateFormatting.java?rev=1691434&r1=1691433&r2=1691434&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/cf/IconMultiStateFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/cf/IconMultiStateFormatting.java Thu Jul 16 19:34:06 2015
@@ -20,7 +20,6 @@ package org.apache.poi.hssf.record.cf;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.util.BitField;
import org.apache.poi.util.BitFieldFactory;
-import org.apache.poi.util.HexDump;
import org.apache.poi.util.LittleEndianInput;
import org.apache.poi.util.LittleEndianOutput;
import org.apache.poi.util.POILogFactory;
@@ -34,7 +33,7 @@ public final class IconMultiStateFormatt
private IconSet iconSet;
private byte options;
- private byte[] states; // TODO Decode
+ private Threshold[] thresholds;
private static BitField iconOnly = BitFieldFactory.getInstance(0x01);
private static BitField reversed = BitFieldFactory.getInstance(0x04);
@@ -42,7 +41,7 @@ public final class IconMultiStateFormatt
public IconMultiStateFormatting() {
iconSet = IconSet.GYR_3_TRAFFIC_LIGHTS;
options = 0;
- states = new byte[0];
+ thresholds = new Threshold[iconSet.num];
}
public IconMultiStateFormatting(LittleEndianInput in) {
in.readShort(); // Ignored
@@ -54,9 +53,11 @@ public final class IconMultiStateFormatt
log.log(POILogger.WARN, "Inconsistent Icon Set defintion, found " + iconSet + " but defined as " + num + " entries");
}
options = in.readByte();
- // TODO Decode
- states = new byte[in.available()];
- in.readFully(states);
+
+ thresholds = new Threshold[iconSet.num];
+ for (int i=0; i<thresholds.length; i++) {
+ thresholds[i] = new Threshold(in);
+ }
}
public IconSet getIconSet() {
@@ -66,6 +67,13 @@ public final class IconMultiStateFormatt
this.iconSet = set;
}
+ public Threshold[] getThresholds() {
+ return thresholds;
+ }
+ public void setThresholds(Threshold[] thresholds) {
+ this.thresholds = thresholds;
+ }
+
public boolean isIconOnly() {
return getOptionFlag(iconOnly);
}
@@ -94,7 +102,9 @@ public final class IconMultiStateFormatt
buffer.append(" .icon_set = ").append(iconSet).append("\n");
buffer.append(" .icon_only= ").append(isIconOnly()).append("\n");
buffer.append(" .reversed = ").append(isReversed()).append("\n");
- buffer.append(" .states = ").append(HexDump.toHex(states)).append("\n");
+ for (Threshold t : thresholds) {
+ buffer.append(t.toString());
+ }
buffer.append(" [/Icon Formatting]\n");
return buffer.toString();
}
@@ -103,13 +113,17 @@ public final class IconMultiStateFormatt
IconMultiStateFormatting rec = new IconMultiStateFormatting();
rec.iconSet = iconSet;
rec.options = options;
- rec.states = new byte[states.length];
- System.arraycopy(states, 0, rec.states, 0, states.length);
+ rec.thresholds = new Threshold[thresholds.length];
+ System.arraycopy(thresholds, 0, rec.thresholds, 0, thresholds.length);
return rec;
}
public int getDataLength() {
- return 6 + states.length;
+ int len = 6;
+ for (Threshold t : thresholds) {
+ len += t.getDataLength();
+ }
+ return len;
}
public void serialize(LittleEndianOutput out) {
@@ -118,6 +132,8 @@ public final class IconMultiStateFormatt
out.writeByte(iconSet.num);
out.writeByte(iconSet.id);
out.writeByte(options);
- out.write(states);
+ for (Threshold t : thresholds) {
+ t.serialize(out);
+ }
}
}
Added: 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=1691434&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/cf/Threshold.java Thu Jul 16 19:34:06 2015
@@ -0,0 +1,142 @@
+/* ====================================================================
+ 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.hssf.record.cf;
+
+import org.apache.poi.ss.formula.Formula;
+import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
+import org.apache.poi.util.LittleEndianInput;
+import org.apache.poi.util.LittleEndianOutput;
+
+/**
+ * Threshold / value for changes in Conditional Formatting
+ */
+public final class Threshold {
+ /**
+ * Cell values that are equal to the threshold value do not pass the threshold
+ */
+ public static final byte EQUALS_EXCLUDE = 0;
+ /**
+ * Cell values that are equal to the threshold value pass the threshold.
+ */
+ public static final byte EQUALS_INCLUDE = 1;
+
+ private byte type;
+ private Formula formula;
+ private Double value;
+ private byte equals;
+
+ public Threshold() {
+ type = (byte)RangeType.NUMBER.id;
+ formula = null; // TODO SHould this be empty instead?
+ value = 0d;
+ }
+
+ /** Creates new Threshold */
+ public Threshold(LittleEndianInput in) {
+ type = in.readByte();
+ short formuaLen = in.readShort();
+ if (formuaLen > 0) {
+ formula = Formula.read(formuaLen, in);
+ }
+ // Value is only there for non-formula, non min/max thresholds
+ if (formula == null && type != RangeType.MIN.id &&
+ type != RangeType.MAX.id) {
+ value = in.readDouble();
+ }
+ equals = in.readByte();
+ // Reserved, 4 bytes, all 0
+ in.readInt();
+ }
+
+ public byte getType() {
+ return type;
+ }
+ public void setType(byte type) {
+ this.type = type;
+ }
+
+ public Formula getFormula() {
+ return formula;
+ }
+ public void setFormula(Formula formula) {
+ this.formula = formula;
+ }
+
+ public Double getValue() {
+ return value;
+ }
+ public void setValue(Double value) {
+ this.value = value;
+ }
+
+ public byte getEquals() {
+ return equals;
+ }
+ public void setEquals(byte equals) {
+ this.equals = equals;
+ }
+
+ public int getDataLength() {
+ int len = 1;
+ if (formula != null) {
+ len += formula.getEncodedSize();
+ } else {
+ len += 2;
+ }
+ if (value != null) {
+ len += 8;
+ }
+ len += 5;
+ return len;
+ }
+
+
+ public String toString() {
+ StringBuffer buffer = new StringBuffer();
+ buffer.append(" [CF Threshold]\n");
+ buffer.append(" .type = ").append(Integer.toHexString(type)).append("\n");
+ // TODO Output the formula better
+ buffer.append(" .formula = ").append(formula).append("\n");
+ buffer.append(" .value = ").append(value).append("\n");
+ buffer.append(" [/CF Threshold]\n");
+ return buffer.toString();
+ }
+
+ public Object clone() {
+ Threshold rec = new Threshold();
+ rec.type = type;
+ rec.formula = formula;
+ rec.value = value;
+ rec.equals = equals;
+ return rec;
+ }
+
+ public void serialize(LittleEndianOutput out) {
+ out.writeByte(type);
+ if (formula == null) {
+ out.writeShort(0);
+ } else {
+ formula.serialize(out);
+ }
+ if (value != null) {
+ out.writeDouble(value);
+ }
+ out.writeByte(equals);
+ out.writeInt(0); // Reserved
+ }
+}
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=1691434&r1=1691433&r2=1691434&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 Thu Jul 16 19:34:06 2015
@@ -80,8 +80,6 @@ public final class HSSFConditionalFormat
// TODO Should this be assigning unique IDs to the rules
// as they get added to the file?
- // TODO Support types beyond CELL_VALUE_IS and FORMULA
-
HSSFConditionalFormatting(HSSFWorkbook workbook, CFRecordsAggregate cfAggregate) {
if(workbook == null) {
throw new IllegalArgumentException("workbook must not be null");
@@ -112,10 +110,11 @@ public final class HSSFConditionalFormat
/**
* Replaces an existing Conditional Formatting rule at position idx.
- * Excel allows to create up to 3 Conditional Formatting rules.
+ * Older versions of Excel only allow up to 3 Conditional Formatting rules,
+ * and will ignore rules beyond that, while newer versions are fine.
* This method can be useful to modify existing Conditional Formatting rules.
*
- * @param idx position of the rule. Should be between 0 and 2.
+ * @param idx position of the rule. Should be between 0 and 2 for older Excel versions
* @param cfRule - Conditional Formatting rule
*/
public void setRule(int idx, HSSFConditionalFormattingRule cfRule) {
Added: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingThreshold.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingThreshold.java?rev=1691434&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingThreshold.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingThreshold.java Thu Jul 16 19:34:06 2015
@@ -0,0 +1,57 @@
+/* ====================================================================
+ 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.hssf.usermodel;
+
+import org.apache.poi.hssf.record.cf.Threshold;
+import org.apache.poi.ss.formula.Formula;
+
+/**
+ * High level representation for Icon / Multi-State / Databar /
+ * Colour Scale change thresholds
+ */
+public final class HSSFConditionalFormattingThreshold implements org.apache.poi.ss.usermodel.ConditionalFormattingThreshold {
+ private final Threshold threshold;
+
+ protected HSSFConditionalFormattingThreshold(Threshold threshold) {
+ this.threshold = threshold;
+ }
+ protected Threshold getThreshold() {
+ return threshold;
+ }
+
+ public RangeType getRangeType() {
+ return RangeType.byId(threshold.getType());
+ }
+ public void setRangeType(RangeType type) {
+ threshold.setType((byte)type.id);
+ }
+
+ public Formula getFormula() {
+ return threshold.getFormula();
+ }
+ public void setFormula(Formula formula) {
+ threshold.setFormula(formula);
+ }
+
+ public Double getValue() {
+ return threshold.getValue();
+ }
+ public void setValue(Double value) {
+ threshold.setValue(value);
+ }
+}
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=1691434&r1=1691433&r2=1691434&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 Thu Jul 16 19:34:06 2015
@@ -19,6 +19,8 @@ package org.apache.poi.hssf.usermodel;
import org.apache.poi.hssf.record.CFRule12Record;
import org.apache.poi.hssf.record.cf.IconMultiStateFormatting;
+import org.apache.poi.hssf.record.cf.Threshold;
+import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold;
/**
* High level representation for Icon / Multi-State Formatting
@@ -53,4 +55,21 @@ public final class HSSFIconMultiStateFor
public void setReversed(boolean reversed) {
iconFormatting.setReversed(reversed);
}
+
+ public ConditionalFormattingThreshold[] getThresholds() {
+ Threshold[] t = iconFormatting.getThresholds();
+ HSSFConditionalFormattingThreshold[] ht = new HSSFConditionalFormattingThreshold[t.length];
+ for (int i=0; i<t.length; i++) {
+ ht[i] = new HSSFConditionalFormattingThreshold(t[i]);
+ }
+ return ht;
+ }
+
+ public void setThresholds(ConditionalFormattingThreshold[] thresholds) {
+ Threshold[] t = new Threshold[thresholds.length];
+ for (int i=0; i<t.length; i++) {
+ t[i] = ((HSSFConditionalFormattingThreshold)thresholds[i]).getThreshold();
+ }
+ iconFormatting.setThresholds(t);
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java?rev=1691434&r1=1691433&r2=1691434&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java Thu Jul 16 19:34:06 2015
@@ -97,6 +97,8 @@ public final class HSSFSheetConditionalF
return new HSSFConditionalFormattingRule(wb, rr);
}
+ // TODO Support types beyond CELL_VALUE_IS and FORMULA
+
/**
* A factory method allowing the creation of conditional formatting
* rules using an Icon Set / Multi-State formatting/
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingThreshold.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingThreshold.java?rev=1691434&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingThreshold.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingThreshold.java Thu Jul 16 19:34:06 2015
@@ -0,0 +1,105 @@
+/*
+ * ====================================================================
+ * 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.usermodel;
+
+import org.apache.poi.ss.formula.Formula;
+
+/**
+ * The Threshold / CFVO / Conditional Formatting Value Object.
+ * <p>This defines how to calculate the ranges for a conditional
+ * formatting rule, eg which values get a Green Traffic Light
+ * icon and which Yellow or Red.</p>
+ */
+public interface ConditionalFormattingThreshold {
+ public enum RangeType {
+ /** Number / Parameter */
+ NUMBER(1, "num"),
+ /** The minimum value from the range */
+ MIN(2, "min"),
+ /** The maximum value from the range */
+ MAX(3, "max"),
+ /** Percent of the way from the mi to the max value in the range */
+ PERCENT(4, "percent"),
+ /** The minimum value of the cell that is in X percentile of the range */
+ PERCENTILE(5, "percentile"),
+ UNALLOCATED(6, null),
+ /** Formula result */
+ FORMULA(7, "formula");
+
+ /** Numeric ID of the type */
+ public int id;
+ /** Name (system) of the type */
+ public final String name;
+
+ public String toString() {
+ return id + " - " + name;
+ }
+
+ public static RangeType byId(int id) {
+ return values()[id-1]; // 1-based IDs
+ }
+
+ private RangeType(int id, String name) {
+ this.id = id; this.name = name;
+ }
+ }
+
+ /**
+ * Get the Range Type used
+ */
+ RangeType getRangeType();
+
+ /**
+ * Changes the Range Type used
+ *
+ * <p>If you change the range type, you need to
+ * ensure that the Formula and Value parameters
+ * are compatible with it before saving</p>
+ */
+ void setRangeType(RangeType type);
+
+ /**
+ * Formula to use to calculate the threshold,
+ * or <code>null</code> if no formula
+ */
+ Formula getFormula();
+
+ /**
+ * Sets the formula used to calculate the threshold,
+ * or unsets it if <code>null</code> is given.
+ */
+ void setFormula(Formula formula);
+
+ /**
+ * Gets the value used for the threshold, or
+ * <code>null</code> if there isn't one.
+ */
+ Double getValue();
+
+ /**
+ * Sets the value used for the threshold.
+ * <p>If the type is {@link RangeType#PERCENT} or
+ * {@link RangeType#PERCENTILE} it must be between 0 and 100.
+ * <p>If the type is {@link RangeType#MIN} or {@link RangeType#MAX}
+ * or {@link RangeType#FORMULA} it shouldn't be set.
+ * <p>Use <code>null</code> to unset
+ */
+ void setValue(Double value);
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/IconMultiStateFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/IconMultiStateFormatting.java?rev=1691434&r1=1691433&r2=1691434&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/IconMultiStateFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/IconMultiStateFormatting.java Thu Jul 16 19:34:06 2015
@@ -104,5 +104,13 @@ public interface IconMultiStateFormattin
boolean isReversed();
void setReversed(boolean reversed);
- // TODO States
+ /**
+ * Gets the list of thresholds
+ */
+ ConditionalFormattingThreshold[] getThresholds();
+ /**
+ * Sets the of thresholds. The number must match
+ * {@link IconSet#num} for the current {@link #getIconSet()}
+ */
+ void setThresholds(ConditionalFormattingThreshold[] thresholds);
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org