src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java

   1 /* ====================================================================
   2    Licensed to the Apache Software Foundation (ASF) under one or more
   3    contributor license agreements.  See the NOTICE file distributed with
   4    this work for additional information regarding copyright ownership.
   5    The ASF licenses this file to You under the Apache License, Version 2.0
   6    (the "License"); you may not use this file except in compliance with
   7    the License.  You may obtain a copy of the License at
   8
   9        http://www.apache.org/licenses/LICENSE-2.0
  10
  11    Unless required by applicable law or agreed to in writing, software
  12    distributed under the License is distributed on an "AS IS" BASIS,
  13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14    See the License for the specific language governing permissions and
  15    limitations under the License.
  16 ==================================================================== */
  17
  18 package org.apache.poi.hssf.usermodel;
  19
  20 import org.apache.poi.hssf.model.Sheet;
  21 import org.apache.poi.hssf.record.CFRuleRecord;
  22 import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate;
  23 import org.apache.poi.hssf.util.Region;
  24
  25 /**
  26  * The 'Conditional Formatting' facet of <tt>HSSFSheet</tt>
  27  *
  28  * @author Dmitriy Kumshayev
  29  */
  30 public final class HSSFSheetConditionalFormatting {
  31
  32         private final HSSFWorkbook _workbook;
  33         private final Sheet _sheet;
  34
  35         /* package */ HSSFSheetConditionalFormatting(HSSFWorkbook workbook, Sheet sheet) {
  36                 _workbook = workbook;
  37                 _sheet = sheet;
  38         }
  39
  40         /**
  41          * A factory method allowing to create a conditional formatting rule
  42          * with a cell comparison operator<p/>
  43          * TODO - formulas containing cell references are currently not parsed properly
  44          *
  45          * @param comparisonOperation - a constant value from
  46          *               <tt>{@link HSSFConditionalFormattingRule.ComparisonOperator}</tt>: <p>
  47          * <ul>
  48          *               <li>BETWEEN</li>
  49          *               <li>NOT_BETWEEN</li>
  50          *               <li>EQUAL</li>
  51          *               <li>NOT_EQUAL</li>
  52          *               <li>GT</li>
  53          *               <li>LT</li>
  54          *               <li>GE</li>
  55          *               <li>LE</li>
  56          * </ul>
  57          * </p>
  58          * @param formula1 - formula for the valued, compared with the cell
  59          * @param formula2 - second formula (only used with
  60          * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN}) and
  61          * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN} operations)
  62          */
  63         public HSSFConditionalFormattingRule createConditionalFormattingRule(
  64                         byte comparisonOperation,
  65                         String formula1,
  66                         String formula2) {
  67
  68                 HSSFWorkbook wb = _workbook;
  69                 CFRuleRecord rr = CFRuleRecord.create(wb, comparisonOperation, formula1, formula2);
  70                 return new HSSFConditionalFormattingRule(wb, rr);
  71         }
  72
  73         /**
  74          * A factory method allowing to create a conditional formatting rule with a formula.<br>
  75          *
  76          * The formatting rules are applied by Excel when the value of the formula not equal to 0.<p/>
  77          * TODO - formulas containing cell references are currently not parsed properly
  78          * @param formula - formula for the valued, compared with the cell
  79          */
  80         public HSSFConditionalFormattingRule createConditionalFormattingRule(String formula) {
  81                 HSSFWorkbook wb = _workbook;
  82                 CFRuleRecord rr = CFRuleRecord.create(wb, formula);
  83                 return new HSSFConditionalFormattingRule(wb, rr);
  84         }
  85
  86         /**
  87          * Adds a copy of HSSFConditionalFormatting object to the sheet
  88          * <p>This method could be used to copy HSSFConditionalFormatting object
  89          * from one sheet to another. For example:
  90          * <pre>
  91          * HSSFConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
  92          * newSheet.addConditionalFormatting(cf);
  93          * </pre>
  94          *
  95          * @param cf HSSFConditionalFormatting object
  96          * @return index of the new Conditional Formatting object
  97          */
  98         public int addConditionalFormatting( HSSFConditionalFormatting cf ) {
  99                 CFRecordsAggregate cfraClone = cf.getCFRecordsAggregate().cloneCFAggregate();
 100
 101                 return _sheet.addConditionalFormatting(cfraClone);
 102         }
 103
 104         /**
 105          * Allows to add a new Conditional Formatting set to the sheet.
 106          *
 107          * @param regions - list of rectangular regions to apply conditional formatting rules
 108          * @param cfRules - set of up to three conditional formatting rules
 109          *
 110          * @return index of the newly created Conditional Formatting object
 111          */
 112
 113         public int addConditionalFormatting(Region[] regions, HSSFConditionalFormattingRule[] cfRules) {
 114                 if (regions == null) {
 115                         throw new IllegalArgumentException("regions must not be null");
 116                 }
 117                 if (cfRules == null) {
 118                         throw new IllegalArgumentException("cfRules must not be null");
 119                 }
 120                 if (cfRules.length == 0) {
 121                         throw new IllegalArgumentException("cfRules must not be empty");
 122                 }
 123                 if (cfRules.length > 3) {
 124                         throw new IllegalArgumentException("Number of rules must not exceed 3");
 125                 }
 126
 127                 CFRuleRecord[] rules = new CFRuleRecord[cfRules.length];
 128                 for (int i = 0; i != cfRules.length; i++) {
 129                         rules[i] = cfRules[i].getCfRuleRecord();
 130                 }
 131                 CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules);
 132                 return _sheet.addConditionalFormatting(cfra);
 133         }
 134
 135         public int addConditionalFormatting(Region[] regions,
 136                         HSSFConditionalFormattingRule rule1)
 137         {
 138                 return addConditionalFormatting(regions,
 139                                 new HSSFConditionalFormattingRule[]
 140                                 {
 141                                         rule1
 142                                 });
 143         }
 144
 145         public int addConditionalFormatting(Region[] regions,
 146                         HSSFConditionalFormattingRule rule1,
 147                         HSSFConditionalFormattingRule rule2)
 148         {
 149                 return addConditionalFormatting(regions,
 150                                 new HSSFConditionalFormattingRule[]
 151                                 {
 152                                                 rule1, rule2
 153                                 });
 154         }
 155
 156         public int addConditionalFormatting(Region[] regions,
 157                         HSSFConditionalFormattingRule rule1,
 158                         HSSFConditionalFormattingRule rule2,
 159                         HSSFConditionalFormattingRule rule3)
 160         {
 161                 return addConditionalFormatting(regions,
 162                                 new HSSFConditionalFormattingRule[]
 163                                 {
 164                                                 rule1, rule2, rule3
 165                                 });
 166         }
 167
 168         /**
 169         * gets Conditional Formatting object at a particular index
 170         *
 171         * @param index
 172         *                       of the Conditional Formatting object to fetch
 173         * @return Conditional Formatting object
 174         */
 175         public HSSFConditionalFormatting getConditionalFormattingAt(int index) {
 176                 CFRecordsAggregate cf = _sheet.getCFRecordsAggregateAt(index);
 177                 if (cf == null) {
 178                         return null;
 179                 }
 180                 return new HSSFConditionalFormatting(_workbook, cf);
 181         }
 182
 183         /**
 184         * @return number of Conditional Formatting objects of the sheet
 185         */
 186         public int getNumConditionalFormattings() {
 187                 return _sheet.getNumConditionalFormattings();
 188         }
 189
 190         /**
 191         * removes a Conditional Formatting object by index
 192         * @param index of a Conditional Formatting object to remove
 193         */
 194         public void removeConditionalFormatting(int index) {
 195                 _sheet.removeConditionalFormatting(index);
 196         }
 197 }