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/20 04:49:12 UTC
svn commit: r1691866 - in /poi/trunk/src:
examples/src/org/apache/poi/ss/examples/ java/org/apache/poi/hssf/record/
java/org/apache/poi/hssf/record/cf/ java/org/apache/poi/hssf/usermodel/
java/org/apache/poi/ss/usermodel/ ooxml/java/org/apache/poi/xssf...
Author: nick
Date: Mon Jul 20 02:49:12 2015
New Revision: 1691866
URL: http://svn.apache.org/r1691866
Log:
#58130 Support for creating CF Color Scale Formattings
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/cf/ColorGradientThreshold.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ExtendedColor.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.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=1691866&r1=1691865&r2=1691866&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 Mon Jul 20 02:49:12 2015
@@ -56,8 +56,9 @@ public class ConditionalFormats {
shadeAlt(wb.createSheet("Shade Alt"));
shadeBands(wb.createSheet("Shade Bands"));
iconSets(wb.createSheet("Icon Sets"));
+ colourScales(wb.createSheet("Colour Scales"));
- // TODO Add colour scales, data bars etc, see bug #58130
+ // TODO Add data bars, see bug #58130
// Write the output to a file
String file = "cf-poi.xls";
@@ -439,7 +440,7 @@ public class ConditionalFormats {
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") };
@@ -476,4 +477,68 @@ public class ConditionalFormats {
im3.getThresholds()[2].setValue(7d);
sheetCF.addConditionalFormatting(regions, rule3);
}
+
+ /**
+ * Color Scales / Colour Scales / Colour Gradients allow you shade the
+ * background colour of the cell based on the values, eg from Red to
+ * Yellow to Green.
+ */
+ static void colourScales(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue("Colour Scales");
+ Row r = sheet.createRow(1);
+ r.createCell(0).setCellValue("Red-Yellow-Green");
+ for (int i=1; i<=7; i++) {
+ r.createCell(i).setCellValue((i-1)*5);
+ }
+ r = sheet.createRow(2);
+ r.createCell(0).setCellValue("Red-White-Blue");
+ for (int i=1; i<=9; i++) {
+ r.createCell(i).setCellValue((i-1)*5);
+ }
+ r = sheet.createRow(3);
+ r.createCell(0).setCellValue("Blue-Green");
+ for (int i=1; i<=16; i++) {
+ r.createCell(i).setCellValue((i-1));
+ }
+ sheet.setColumnWidth(0, 5000);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") };
+ ConditionalFormattingRule rule1 =
+ sheetCF.createConditionalFormattingColorScaleRule();
+ ColorScaleFormatting cs1 = rule1.getColorScaleFormatting();
+ cs1.getThresholds()[0].setRangeType(RangeType.MIN);
+ cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
+ cs1.getThresholds()[1].setValue(50d);
+ cs1.getThresholds()[2].setRangeType(RangeType.MAX);
+ ((ExtendedColor)cs1.getColors()[0]).setARGBHex("FFF8696B");
+ ((ExtendedColor)cs1.getColors()[1]).setARGBHex("FFFFEB84");
+ ((ExtendedColor)cs1.getColors()[2]).setARGBHex("FF63BE7B");
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") };
+ ConditionalFormattingRule rule2 =
+ sheetCF.createConditionalFormattingColorScaleRule();
+ ColorScaleFormatting cs2 = rule2.getColorScaleFormatting();
+ cs2.getThresholds()[0].setRangeType(RangeType.MIN);
+ cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
+ cs2.getThresholds()[1].setValue(50d);
+ cs2.getThresholds()[2].setRangeType(RangeType.MAX);
+ ((ExtendedColor)cs2.getColors()[0]).setARGBHex("FFF8696B");
+ ((ExtendedColor)cs2.getColors()[1]).setARGBHex("FFFCFCFF");
+ ((ExtendedColor)cs2.getColors()[2]).setARGBHex("FF5A8AC6");
+ sheetCF.addConditionalFormatting(regions, rule2);
+
+ regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") };
+ ConditionalFormattingRule rule3=
+ sheetCF.createConditionalFormattingColorScaleRule();
+ ColorScaleFormatting cs3 = rule3.getColorScaleFormatting();
+ cs3.setNumControlPoints(2);
+ cs3.getThresholds()[0].setRangeType(RangeType.MIN);
+ cs3.getThresholds()[1].setRangeType(RangeType.MAX);
+ ((ExtendedColor)cs3.getColors()[0]).setARGBHex("FF5A8AC6");
+ ((ExtendedColor)cs3.getColors()[1]).setARGBHex("FF63BE7B");
+ 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=1691866&r1=1691865&r2=1691866&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 Mon Jul 20 02:49:12 2015
@@ -20,9 +20,11 @@ package org.apache.poi.hssf.record;
import java.util.Arrays;
import org.apache.poi.hssf.record.cf.ColorGradientFormatting;
+import org.apache.poi.hssf.record.cf.ColorGradientThreshold;
import org.apache.poi.hssf.record.cf.IconMultiStateFormatting;
import org.apache.poi.hssf.record.cf.IconMultiStateThreshold;
import org.apache.poi.hssf.record.cf.Threshold;
+import org.apache.poi.hssf.record.common.ExtendedColor;
import org.apache.poi.hssf.record.common.FtrHeader;
import org.apache.poi.hssf.record.common.FutureRecord;
import org.apache.poi.hssf.usermodel.HSSFSheet;
@@ -129,14 +131,34 @@ public final class CFRule12Record extend
ts[i] = new IconMultiStateThreshold();
}
- CFRule12Record r = new CFRule12Record(CONDITION_TYPE_COLOR_SCALE,
+ CFRule12Record r = new CFRule12Record(CONDITION_TYPE_ICON_SET,
ComparisonOperator.NO_COMPARISON);
IconMultiStateFormatting imf = r.createMultiStateFormatting();
imf.setIconSet(iconSet);
imf.setThresholds(ts);
return r;
}
- // TODO Static creators for the other record types
+ /**
+ * Creates a new Color Scale / Color Gradient formatting
+ */
+ public static CFRule12Record createColorScale(HSSFSheet sheet) {
+ int numPoints = 3;
+ ExtendedColor[] colors = new ExtendedColor[numPoints];
+ ColorGradientThreshold[] ts = new ColorGradientThreshold[numPoints];
+ for (int i=0; i<ts.length; i++) {
+ ts[i] = new ColorGradientThreshold();
+ colors[i] = new ExtendedColor();
+ }
+
+ CFRule12Record r = new CFRule12Record(CONDITION_TYPE_COLOR_SCALE,
+ ComparisonOperator.NO_COMPARISON);
+ ColorGradientFormatting cgf = r.createColorGradientFormatting();
+ cgf.setNumControlPoints(numPoints);
+ cgf.setThresholds(ts);
+ cgf.setColors(colors);
+ return r;
+ }
+ // TODO Static creators for Data Bars
public CFRule12Record(RecordInputStream in) {
futureHeader = new FtrHeader(in);
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/cf/ColorGradientThreshold.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/cf/ColorGradientThreshold.java?rev=1691866&r1=1691865&r2=1691866&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/cf/ColorGradientThreshold.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/cf/ColorGradientThreshold.java Mon Jul 20 02:49:12 2015
@@ -32,7 +32,7 @@ public final class ColorGradientThreshol
position = 0d;
}
- /** Creates new Ico Multi-State Threshold */
+ /** Creates new Color Gradient Threshold */
public ColorGradientThreshold(LittleEndianInput in) {
super(in);
position = in.readDouble();
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=1691866&r1=1691865&r2=1691866&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 Mon Jul 20 02:49:12 2015
@@ -106,7 +106,36 @@ public final class HSSFSheetConditionalF
return new HSSFConditionalFormattingRule(_sheet, rr);
}
- // TODO Support types beyond CELL_VALUE_IS and FORMULA and ICONs
+ /**
+ * Create a Color Scale / Color Gradient conditional formatting rule.
+ * <p>The thresholds and colours for it will be created, but will be
+ * empty and require configuring with
+ * {@link HSSFConditionalFormattingRule#getColorScaleFormatting()}
+ * then
+ * {@link HSSFColorScaleFormatting#getThresholds()}
+ * and
+ * {@link HSSFColorScaleFormatting#getColors()}
+ */
+ public HSSFConditionalFormattingRule createConditionalFormattingColorScaleRule() {
+ CFRule12Record rr = CFRule12Record.createColorScale(_sheet);
+ return new HSSFConditionalFormattingRule(_sheet, rr);
+ }
+
+ /**
+ * Create a Databar conditional formatting rule.
+ * <p>The thresholds and colour for it will be created, but will be
+ * empty and require configuring with
+ * {@link HSSFConditionalFormattingRule#getDataBarFormatting()}
+ * then
+ * {@link HSSFDataBarFormatting#getMinThreshold()}
+ * and
+ * {@link HSSFDataBarFormatting#getMaxThreshold()}
+ * and
+ * {@link HSSFDataBarFormatting#getColor()}
+ */
+ public HSSFConditionalFormattingRule createConditionalFormattingDataBarRule() {
+ throw new IllegalStateException("Not Implemented Yet!"); // TODO Implement
+ }
/**
* Adds a copy of HSSFConditionalFormatting object to the sheet
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/ExtendedColor.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ExtendedColor.java?rev=1691866&r1=1691865&r2=1691866&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ExtendedColor.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ExtendedColor.java Mon Jul 20 02:49:12 2015
@@ -79,7 +79,7 @@ public abstract class ExtendedColor impl
*/
public abstract void setRGB(byte[] rgb);
- protected byte[] getRGBOrARGB() {
+ protected byte[] getRGBOrARGB() {
byte[] rgb = null;
if (isIndexed() && getIndex() > 0) {
@@ -97,7 +97,7 @@ public abstract class ExtendedColor impl
// Grab the colour
rgb = getStoredRBG();
return rgb;
- }
+ }
/**
* Standard Red Green Blue ctColor value (RGB) with applied tint.
@@ -138,6 +138,23 @@ public abstract class ExtendedColor impl
}
return sb.toString().toUpperCase();
}
+
+ /**
+ * Sets the ARGB value from hex format, eg FF0077FF.
+ * Only works for regular (non-indexed) colours
+ */
+ public void setARGBHex(String argb) {
+ if (argb.length() == 6 || argb.length() == 8) {
+ byte[] rgb = new byte[argb.length()/2];
+ for (int i=0; i<rgb.length; i++) {
+ String part = argb.substring(i*2,(i+1)*2);
+ rgb[i] = (byte)Integer.parseInt(part, 16);
+ }
+ setRGB(rgb);
+ } else {
+ throw new IllegalArgumentException("Must be of the form 112233 or FFEEDDCC");
+ }
+ }
private static byte applyTint(int lum, double tint){
if(tint > 0){
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java?rev=1691866&r1=1691865&r2=1691866&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java Mon Jul 20 02:49:12 2015
@@ -146,7 +146,31 @@ public interface SheetConditionalFormatt
*/
ConditionalFormattingRule createConditionalFormattingRule(IconSet iconSet);
- // TODO Support types beyond CELL_VALUE_IS and FORMULA and ICONs
+ /**
+ * Create a Color Scale / Color Gradient conditional formatting rule.
+ * <p>The thresholds and colours for it will be created, but will be
+ * empty and require configuring with
+ * {@link ConditionalFormattingRule#getColorScaleFormatting()}
+ * then
+ * {@link ColorScaleFormatting#getThresholds()}
+ * and
+ * {@link ColorScaleFormatting#getColors()}
+ */
+ ConditionalFormattingRule createConditionalFormattingColorScaleRule();
+
+ /**
+ * Create a Databar conditional formatting rule.
+ * <p>The thresholds and colour for it will be created, but will be
+ * empty and require configuring with
+ * {@link ConditionalFormattingRule#getDataBarFormatting()}
+ * then
+ * {@link DataBarFormatting#getMinThreshold()}
+ * and
+ * {@link DataBarFormatting#getMaxThreshold()}
+ * and
+ * {@link DataBarFormatting#getColor()}
+ */
+ ConditionalFormattingRule createConditionalFormattingDataBarRule();
/**
* Gets Conditional Formatting object at a particular index
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java?rev=1691866&r1=1691865&r2=1691866&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java Mon Jul 20 02:49:12 2015
@@ -138,7 +138,41 @@ public class XSSFSheetConditionalFormatt
return rule;
}
- // TODO Support types beyond CELL_VALUE_IS and FORMULA and ICONs
+ /**
+ * Create a Color Scale / Color Gradient conditional formatting rule.
+ * <p>The thresholds and colours for it will be created, but will be
+ * empty and require configuring with
+ * {@link XSSFConditionalFormattingRule#getColorScaleFormatting()}
+ * then
+ * {@link XSSFColorScaleFormatting#getThresholds()}
+ * and
+ * {@link XSSFColorScaleFormatting#getColors()}
+ */
+ public XSSFConditionalFormattingRule createConditionalFormattingColorScaleRule() {
+ XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
+
+ // Have it setup, with suitable defaults
+ rule.createColorScaleFormatting();
+
+ // All done!
+ return rule;
+ }
+
+ /**
+ * Create a Databar conditional formatting rule.
+ * <p>The thresholds and colour for it will be created, but will be
+ * empty and require configuring with
+ * {@link XSSFConditionalFormattingRule#getDataBarFormatting()}
+ * then
+ * {@link XSSFDataBarFormatting#getMinThreshold()}
+ * and
+ * {@link XSSFDataBarFormatting#getMaxThreshold()}
+ * and
+ * {@link XSSFDataBarFormatting#getColor()}
+ */
+ public XSSFConditionalFormattingRule createConditionalFormattingDataBarRule() {
+ throw new IllegalStateException("Not Implemented Yet!"); // TODO Implement
+ }
@SuppressWarnings("deprecation")
public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
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=1691866&r1=1691865&r2=1691866&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 Mon Jul 20 02:49:12 2015
@@ -1048,6 +1048,7 @@ public abstract class BaseTestConditiona
ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
assertEquals(1, cf.getNumberOfRules());
rule1 = cf.getRule(0);
+ assertEquals(ConditionType.ICON_SET, rule1.getConditionTypeType());
iconFmt = rule1.getMultiStateFormatting();
assertEquals(IconSet.GYRB_4_TRAFFIC_LIGHTS, iconFmt.getIconSet());
@@ -1066,8 +1067,49 @@ public abstract class BaseTestConditiona
}
public void testCreateColorScaleFormatting() {
- // TODO Implement then test
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+ ConditionalFormattingRule rule1 =
+ sheetCF.createConditionalFormattingColorScaleRule();
+ ColorScaleFormatting clrFmt = rule1.getColorScaleFormatting();
+
+ assertEquals(3, clrFmt.getNumControlPoints());
+ assertEquals(3, clrFmt.getColors().length);
+ assertEquals(3, clrFmt.getThresholds().length);
+
+ clrFmt.getThresholds()[0].setRangeType(RangeType.MIN);
+ clrFmt.getThresholds()[1].setRangeType(RangeType.NUMBER);
+ clrFmt.getThresholds()[1].setValue(10d);
+ clrFmt.getThresholds()[2].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);
+ clrFmt = rule1.getColorScaleFormatting();
+ assertEquals(ConditionType.COLOR_SCALE, rule1.getConditionTypeType());
+
+ assertEquals(3, clrFmt.getNumControlPoints());
+ assertEquals(3, clrFmt.getColors().length);
+ assertEquals(3, clrFmt.getThresholds().length);
+
+ assertEquals(RangeType.MIN, clrFmt.getThresholds()[0].getRangeType());
+ assertEquals(RangeType.NUMBER, clrFmt.getThresholds()[1].getRangeType());
+ assertEquals(RangeType.MAX, clrFmt.getThresholds()[2].getRangeType());
+ assertEquals(null, clrFmt.getThresholds()[0].getValue());
+ assertEquals(10d, clrFmt.getThresholds()[1].getValue());
+ assertEquals(null, clrFmt.getThresholds()[2].getValue());
}
+
public void testCreateDataBarFormatting() {
// TODO Implement then test
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org