path: root/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java

/*
 *  ====================================================================
 *    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.xssf.usermodel;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.ExtendedColor;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeUtil;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCfType;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STConditionalFormattingOperator;

/**
 * XSSF Conditional Formattings
 */
public class XSSFSheetConditionalFormatting implements SheetConditionalFormatting {
    /** Office 2010 Conditional Formatting extensions namespace */
    protected static final String CF_EXT_2009_NS_X14 = "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main";

    private final XSSFSheet _sheet;

    /* package */ XSSFSheetConditionalFormatting(XSSFSheet sheet) {
        _sheet = sheet;
    }

    /**
     * A factory method allowing to create a conditional formatting rule
     * with a cell comparison operator<p>
     * TODO - formulas containing cell references are currently not parsed properly
     *
     * @param comparisonOperation - a constant value from
     *       {@code {@link org.apache.poi.hssf.record.CFRuleBase.ComparisonOperator}}:
     * <ul>
     *       <li>BETWEEN</li>
     *       <li>NOT_BETWEEN</li>
     *       <li>EQUAL</li>
     *       <li>NOT_EQUAL</li>
     *       <li>GT</li>
     *       <li>LT</li>
     *       <li>GE</li>
     *       <li>LE</li>
     * </ul>
     *
     * @param formula1 - formula for the valued, compared with the cell
     * @param formula2 - second formula (only used with
     * {@link org.apache.poi.ss.usermodel.ComparisonOperator#BETWEEN}) and
     * {@link org.apache.poi.ss.usermodel.ComparisonOperator#NOT_BETWEEN} operations)
     */
    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingRule(
            byte comparisonOperation,
            String formula1,
            String formula2) {

        XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
        CTCfRule cfRule = rule.getCTCfRule();
        cfRule.addFormula(formula1);
        if(formula2 != null) cfRule.addFormula(formula2);
        cfRule.setType(STCfType.CELL_IS);
        STConditionalFormattingOperator.Enum operator;
        switch (comparisonOperation){
            case ComparisonOperator.BETWEEN: operator = STConditionalFormattingOperator.BETWEEN; break;
            case ComparisonOperator.NOT_BETWEEN: operator = STConditionalFormattingOperator.NOT_BETWEEN; break;
            case ComparisonOperator.LT: operator = STConditionalFormattingOperator.LESS_THAN; break;
            case ComparisonOperator.LE: operator = STConditionalFormattingOperator.LESS_THAN_OR_EQUAL; break;
            case ComparisonOperator.GT: operator = STConditionalFormattingOperator.GREATER_THAN; break;
            case ComparisonOperator.GE: operator = STConditionalFormattingOperator.GREATER_THAN_OR_EQUAL; break;
            case ComparisonOperator.EQUAL: operator = STConditionalFormattingOperator.EQUAL; break;
            case ComparisonOperator.NOT_EQUAL: operator = STConditionalFormattingOperator.NOT_EQUAL; break;
            default: throw new IllegalArgumentException("Unknown comparison operator: " + comparisonOperation);
        }
        cfRule.setOperator(operator);

        return rule;
    }

    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingRule(
            byte comparisonOperation,
            String formula) {

        return createConditionalFormattingRule(comparisonOperation, formula, null);
    }

    /**
     * A factory method allowing to create a conditional formatting rule with a formula.<br>
     *
     * @param formula - formula for the valued, compared with the cell
     */
    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingRule(String formula) {
        XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
        CTCfRule cfRule = rule.getCTCfRule();
        cfRule.addFormula(formula);
        cfRule.setType(STCfType.EXPRESSION);
        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()}
     */
    public XSSFConditionalFormattingRule createConditionalFormattingRule(XSSFColor color) {
        XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);

        // Have it setup, with suitable defaults
        rule.createDataBarFormatting(color);

        // All done!
        return rule;
    }
    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingRule(ExtendedColor color) {
        return createConditionalFormattingRule((XSSFColor)color);
    }

    /**
     * A factory method allowing the creation of conditional formatting
     *  rules using an Icon Set / Multi-State formatting.
     * The thresholds for it will be created, but will be empty
     *  and require configuring with
     *  {@link XSSFConditionalFormattingRule#getMultiStateFormatting()}
     *  then
     *  {@link XSSFIconMultiStateFormatting#getThresholds()}
     */
    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingRule(IconSet iconSet) {
        XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);

        // Have it setup, with suitable defaults
        rule.createMultiStateFormatting(iconSet);

        // All done!
        return rule;
    }

    /**
     * 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()}
     */
    @Override
    public XSSFConditionalFormattingRule createConditionalFormattingColorScaleRule() {
        XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);

        // Have it setup, with suitable defaults
        rule.createColorScaleFormatting();

        // All done!
        return rule;
    }

    @Override
    public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
        if (regions == null) {
            throw new IllegalArgumentException("regions must not be null");
        }
        for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);

        if (cfRules == null) {
            throw new IllegalArgumentException("cfRules must not be null");
        }
        if (cfRules.length == 0) {
            throw new IllegalArgumentException("cfRules must not be empty");
        }

        CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
        CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
        List<String> refs = new ArrayList<>();
        for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
        cf.setSqref(refs);

        int priority = 1;
        for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){
            priority += c.sizeOfCfRuleArray();
        }

        for(ConditionalFormattingRule rule : cfRules){
            XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule;
            xRule.getCTCfRule().setPriority(priority++);
            cf.addNewCfRule().set(xRule.getCTCfRule());
        }
        return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1;
    }

    @Override
    public int addConditionalFormatting(CellRangeAddress[] regions,
                                        ConditionalFormattingRule rule1)
    {
        return addConditionalFormatting(regions,
                rule1 == null ? null : new XSSFConditionalFormattingRule[] {
                        (XSSFConditionalFormattingRule)rule1
                });
    }

    @Override
    public int addConditionalFormatting(CellRangeAddress[] regions,
                                        ConditionalFormattingRule rule1, ConditionalFormattingRule rule2)
    {
        return addConditionalFormatting(regions,
                rule1 == null ? null : new XSSFConditionalFormattingRule[] {
                        (XSSFConditionalFormattingRule)rule1,
                        (XSSFConditionalFormattingRule)rule2
                });
    }

    /**
     * Adds a copy of HSSFConditionalFormatting object to the sheet
     * <p>This method could be used to copy HSSFConditionalFormatting object
     * from one sheet to another. For example:
     * <pre>
     * HSSFConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
     * newSheet.addConditionalFormatting(cf);
     * </pre>
     *
     * @param cf HSSFConditionalFormatting object
     * @return index of the new Conditional Formatting object
     */
    @Override
    public int addConditionalFormatting( ConditionalFormatting cf ) {
        XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
        CTWorksheet sh = _sheet.getCTWorksheet();
        sh.addNewConditionalFormatting().set(xcf.getCTConditionalFormatting().copy());
        return sh.sizeOfConditionalFormattingArray() - 1;
    }

    /**
     * gets Conditional Formatting object at a particular index
     *
     * @param index
     *          of the Conditional Formatting object to fetch
     * @return Conditional Formatting object
     */
    @Override
    public XSSFConditionalFormatting getConditionalFormattingAt(int index) {
        checkIndex(index);
        CTConditionalFormatting cf = _sheet.getCTWorksheet().getConditionalFormattingArray(index);
        return new XSSFConditionalFormatting(_sheet, cf);
    }

    /**
     * @return number of Conditional Formatting objects of the sheet
     */
    @Override
    public int getNumConditionalFormattings() {
        return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray();
    }

    /**
     * removes a Conditional Formatting object by index
     * @param index of a Conditional Formatting object to remove
     */
    @Override
    public void removeConditionalFormatting(int index) {
        checkIndex(index);
        _sheet.getCTWorksheet().removeConditionalFormatting(index);
    }

    private void checkIndex(int index) {
        int cnt = getNumConditionalFormattings();
        if (index < 0 || index >= cnt) {
            throw new IllegalArgumentException("Specified CF index " + index
                    + " is outside the allowable range (0.." + (cnt - 1) + ")");
        }
    }

}