From 76a0c6378e14eca4cb8eb025ef6bb57849006283 Mon Sep 17 00:00:00 2001 From: Dominik Stadler Date: Mon, 7 Nov 2016 21:32:04 +0000 Subject: [PATCH] Add setFormattingRanges() to interface ConditionalFormatting, closes #42 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1768588 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/HSSFConditionalFormatting.java | 12 ++ .../ss/usermodel/ConditionalFormatting.java | 6 + .../usermodel/XSSFConditionalFormatting.java | 132 +++++++++++------- .../BaseTestConditionalFormatting.java | 90 ++++++++++-- 4 files changed, 178 insertions(+), 62 deletions(-) diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java index fd31c2df92..26bdd6bd95 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java @@ -94,10 +94,17 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { /** * @return array of CellRangeAddresss. never null */ + @Override public CellRangeAddress[] getFormattingRanges() { return cfAggregate.getHeader().getCellRanges(); } + @Override + public void setFormattingRanges( + final CellRangeAddress[] ranges) { + cfAggregate.getHeader().setCellRanges(ranges); + } + /** * Replaces an existing Conditional Formatting rule at position idx. * Older versions of Excel only allow up to 3 Conditional Formatting rules, @@ -111,6 +118,7 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { cfAggregate.setRule(idx, cfRule.getCfRuleRecord()); } + @Override public void setRule(int idx, ConditionalFormattingRule cfRule){ setRule(idx, (HSSFConditionalFormattingRule)cfRule); } @@ -124,6 +132,7 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { cfAggregate.addRule(cfRule.getCfRuleRecord()); } + @Override public void addRule(ConditionalFormattingRule cfRule){ addRule((HSSFConditionalFormattingRule)cfRule); } @@ -131,6 +140,7 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { /** * @return the Conditional Formatting rule at position idx. */ + @Override public HSSFConditionalFormattingRule getRule(int idx) { CFRuleBase ruleRecord = cfAggregate.getRule(idx); return new HSSFConditionalFormattingRule(sheet, ruleRecord); @@ -139,10 +149,12 @@ public final class HSSFConditionalFormatting implements ConditionalFormatting { /** * @return number of Conditional Formatting rules. */ + @Override public int getNumberOfRules() { return cfAggregate.getNumberOfRules(); } + @Override public String toString() { return cfAggregate.toString(); } diff --git a/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java b/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java index f6e8ff441f..09d63a407d 100644 --- a/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java +++ b/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java @@ -80,6 +80,12 @@ public interface ConditionalFormatting { */ CellRangeAddress[] getFormattingRanges(); + /** + * Sets the cell ranges the rule conditional formatting must be applied to. + * @param ranges non-null array of CellRangeAddresss + */ + void setFormattingRanges(CellRangeAddress[] ranges); + /** * Replaces an existing Conditional Formatting rule at position idx. * Excel pre-2007 allows to create up to 3 Conditional Formatting rules, diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java index 53eb41b122..915ba5d4bf 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java @@ -25,6 +25,7 @@ import org.apache.poi.ss.util.CellRangeAddress; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting; import java.util.ArrayList; +import java.util.Collections; /** * @author Yegor Kozlov @@ -33,73 +34,98 @@ public class XSSFConditionalFormatting implements ConditionalFormatting { private final CTConditionalFormatting _cf; private final XSSFSheet _sh; - /*package*/ XSSFConditionalFormatting(XSSFSheet sh){ + /*package*/ XSSFConditionalFormatting(XSSFSheet sh) { _cf = CTConditionalFormatting.Factory.newInstance(); _sh = sh; } - /*package*/ XSSFConditionalFormatting(XSSFSheet sh, CTConditionalFormatting cf){ + /*package*/ XSSFConditionalFormatting( + XSSFSheet sh, CTConditionalFormatting cf) { _cf = cf; _sh = sh; } - /*package*/ CTConditionalFormatting getCTConditionalFormatting(){ + /*package*/ CTConditionalFormatting getCTConditionalFormatting() { return _cf; } /** - * @return array of CellRangeAddresss. Never null - */ - public CellRangeAddress[] getFormattingRanges(){ - ArrayList lst = new ArrayList(); - for (Object stRef : _cf.getSqref()) { - String[] regions = stRef.toString().split(" "); - for (int i = 0; i < regions.length; i++) { - lst.add(CellRangeAddress.valueOf(regions[i])); - } - } - return lst.toArray(new CellRangeAddress[lst.size()]); - } + * @return array of CellRangeAddresss. Never null + */ + @Override + public CellRangeAddress[] getFormattingRanges() { + ArrayList lst = new ArrayList(); + for (Object stRef : _cf.getSqref()) { + String[] regions = stRef.toString().split(" "); + for (final String region : regions) { + lst.add(CellRangeAddress.valueOf(region)); + } + } + return lst.toArray(new CellRangeAddress[lst.size()]); + } - /** - * Replaces an existing Conditional Formatting rule at position idx. - * Excel allows to create up to 3 Conditional Formatting rules. - * This method can be useful to modify existing Conditional Formatting rules. - * - * @param idx position of the rule. Should be between 0 and 2. - * @param cfRule - Conditional Formatting rule - */ - public void setRule(int idx, ConditionalFormattingRule cfRule){ - XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule; - _cf.getCfRuleArray(idx).set(xRule.getCTCfRule()); - } + @Override + public void setFormattingRanges(CellRangeAddress[] ranges) { + if (ranges == null) { + throw new IllegalArgumentException("cellRanges must not be null"); + } + final StringBuilder sb = new StringBuilder(); + boolean first = true; + for (CellRangeAddress range : ranges) { + if (!first) { + sb.append(" "); + } else { + first = false; + } + sb.append(range.formatAsString()); + } + _cf.setSqref(Collections.singletonList(sb.toString())); + } - /** - * Add a Conditional Formatting rule. - * Excel allows to create up to 3 Conditional Formatting rules. - * - * @param cfRule - Conditional Formatting rule - */ - public void addRule(ConditionalFormattingRule cfRule){ - XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule; - _cf.addNewCfRule().set(xRule.getCTCfRule()); - } + /** + * Replaces an existing Conditional Formatting rule at position idx. + * Excel allows to create up to 3 Conditional Formatting rules. + * This method can be useful to modify existing Conditional Formatting rules. + * + * @param idx position of the rule. Should be between 0 and 2. + * @param cfRule - Conditional Formatting rule + */ + @Override + public void setRule(int idx, ConditionalFormattingRule cfRule) { + XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) cfRule; + _cf.getCfRuleArray(idx).set(xRule.getCTCfRule()); + } - /** - * @return the Conditional Formatting rule at position idx. - */ - public XSSFConditionalFormattingRule getRule(int idx){ - return new XSSFConditionalFormattingRule(_sh, _cf.getCfRuleArray(idx)); - } + /** + * Add a Conditional Formatting rule. + * Excel allows to create up to 3 Conditional Formatting rules. + * + * @param cfRule - Conditional Formatting rule + */ + @Override + public void addRule(ConditionalFormattingRule cfRule) { + XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) cfRule; + _cf.addNewCfRule().set(xRule.getCTCfRule()); + } - /** - * @return number of Conditional Formatting rules. - */ - public int getNumberOfRules(){ - return _cf.sizeOfCfRuleArray(); - } - - public String toString() { - return _cf.toString(); - } + /** + * @return the Conditional Formatting rule at position idx. + */ + @Override + public XSSFConditionalFormattingRule getRule(int idx) { + return new XSSFConditionalFormattingRule(_sh, _cf.getCfRuleArray(idx)); + } + + /** + * @return number of Conditional Formatting rules. + */ + @Override + public int getNumberOfRules() { + return _cf.sizeOfCfRuleArray(); + } + + @Override + public String toString() { + return _cf.toString(); + } } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java index 28cfb2cf7b..d5a8124df9 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java @@ -19,15 +19,6 @@ package org.apache.poi.ss.usermodel; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertFalse; -import static org.junit.Assert.assertNotNull; -import static org.junit.Assert.assertNull; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; - -import java.io.IOException; - import org.apache.poi.hssf.usermodel.HSSFConditionalFormatting; import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule; import org.apache.poi.ss.ITestDataProvider; @@ -36,6 +27,10 @@ import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet; import org.apache.poi.ss.util.CellRangeAddress; import org.junit.Test; +import java.io.IOException; + +import static org.junit.Assert.*; + /** * Base tests for Conditional Formatting, for both HSSF and XSSF */ @@ -1271,4 +1266,81 @@ public abstract class BaseTestConditionalFormatting { sheet.getSheetConditionalFormatting().addConditionalFormatting(ranges, rule); wb.close(); } + + @Test + public void testSetCellRangeAddresswithSingleRange() throws Exception { + Workbook wb = _testDataProvider.createWorkbook(); + final Sheet sheet = wb.createSheet("S1"); + final SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting(); + assertEquals(0, cf.getNumConditionalFormattings()); + ConditionalFormattingRule rule1 = cf.createConditionalFormattingRule("$A$1>0"); + cf.addConditionalFormatting(new CellRangeAddress[] { + CellRangeAddress.valueOf("A1:A5") + }, rule1); + + assertEquals(1, cf.getNumConditionalFormattings()); + ConditionalFormatting readCf = cf.getConditionalFormattingAt(0); + CellRangeAddress[] formattingRanges = readCf.getFormattingRanges(); + assertEquals(1, formattingRanges.length); + CellRangeAddress formattingRange = formattingRanges[0]; + assertEquals("A1:A5", formattingRange.formatAsString()); + + readCf.setFormattingRanges(new CellRangeAddress[] { + CellRangeAddress.valueOf("A1:A6") + }); + + readCf = cf.getConditionalFormattingAt(0); + formattingRanges = readCf.getFormattingRanges(); + assertEquals(1, formattingRanges.length); + formattingRange = formattingRanges[0]; + assertEquals("A1:A6", formattingRange.formatAsString()); + } + + @Test + public void testSetCellRangeAddressWithMultipleRanges() throws Exception { + Workbook wb = _testDataProvider.createWorkbook(); + final Sheet sheet = wb.createSheet("S1"); + final SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting(); + assertEquals(0, cf.getNumConditionalFormattings()); + ConditionalFormattingRule rule1 = cf.createConditionalFormattingRule("$A$1>0"); + cf.addConditionalFormatting(new CellRangeAddress[] { + CellRangeAddress.valueOf("A1:A5") + }, rule1); + + assertEquals(1, cf.getNumConditionalFormattings()); + ConditionalFormatting readCf = cf.getConditionalFormattingAt(0); + CellRangeAddress[] formattingRanges = readCf.getFormattingRanges(); + assertEquals(1, formattingRanges.length); + CellRangeAddress formattingRange = formattingRanges[0]; + assertEquals("A1:A5", formattingRange.formatAsString()); + + readCf.setFormattingRanges(new CellRangeAddress[] { + CellRangeAddress.valueOf("A1:A6"), + CellRangeAddress.valueOf("B1:B6") + }); + + readCf = cf.getConditionalFormattingAt(0); + formattingRanges = readCf.getFormattingRanges(); + assertEquals(2, formattingRanges.length); + formattingRange = formattingRanges[0]; + assertEquals("A1:A6", formattingRange.formatAsString()); + formattingRange = formattingRanges[1]; + assertEquals("B1:B6", formattingRange.formatAsString()); + } + + @Test(expected = IllegalArgumentException.class) + public void testSetCellRangeAddressWithNullRanges() throws Exception { + Workbook wb = _testDataProvider.createWorkbook(); + final Sheet sheet = wb.createSheet("S1"); + final SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting(); + assertEquals(0, cf.getNumConditionalFormattings()); + ConditionalFormattingRule rule1 = cf.createConditionalFormattingRule("$A$1>0"); + cf.addConditionalFormatting(new CellRangeAddress[] { + CellRangeAddress.valueOf("A1:A5") + }, rule1); + + assertEquals(1, cf.getNumConditionalFormattings()); + ConditionalFormatting readCf = cf.getConditionalFormattingAt(0); + readCf.setFormattingRanges(null); + } } \ No newline at end of file -- 2.39.5