Java tutorial
/* * ==================================================================== * 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.HashMap; import java.util.Map; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType; import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet; import org.apache.poi.xssf.model.StylesTable; import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** * XSSF support for Conditional Formatting rules */ public class XSSFConditionalFormattingRule implements ConditionalFormattingRule { private final CTCfRule _cfRule; private XSSFSheet _sh; private static Map<STCfType.Enum, ConditionType> typeLookup = new HashMap<>(); private static Map<STCfType.Enum, ConditionFilterType> filterTypeLookup = new HashMap<>(); static { typeLookup.put(STCfType.CELL_IS, ConditionType.CELL_VALUE_IS); typeLookup.put(STCfType.EXPRESSION, ConditionType.FORMULA); typeLookup.put(STCfType.COLOR_SCALE, ConditionType.COLOR_SCALE); typeLookup.put(STCfType.DATA_BAR, ConditionType.DATA_BAR); typeLookup.put(STCfType.ICON_SET, ConditionType.ICON_SET); // These are all subtypes of Filter, we think... typeLookup.put(STCfType.TOP_10, ConditionType.FILTER); typeLookup.put(STCfType.UNIQUE_VALUES, ConditionType.FILTER); typeLookup.put(STCfType.DUPLICATE_VALUES, ConditionType.FILTER); typeLookup.put(STCfType.CONTAINS_TEXT, ConditionType.FILTER); typeLookup.put(STCfType.NOT_CONTAINS_TEXT, ConditionType.FILTER); typeLookup.put(STCfType.BEGINS_WITH, ConditionType.FILTER); typeLookup.put(STCfType.ENDS_WITH, ConditionType.FILTER); typeLookup.put(STCfType.CONTAINS_BLANKS, ConditionType.FILTER); typeLookup.put(STCfType.NOT_CONTAINS_BLANKS, ConditionType.FILTER); typeLookup.put(STCfType.CONTAINS_ERRORS, ConditionType.FILTER); typeLookup.put(STCfType.NOT_CONTAINS_ERRORS, ConditionType.FILTER); typeLookup.put(STCfType.TIME_PERIOD, ConditionType.FILTER); typeLookup.put(STCfType.ABOVE_AVERAGE, ConditionType.FILTER); filterTypeLookup.put(STCfType.TOP_10, ConditionFilterType.TOP_10); filterTypeLookup.put(STCfType.UNIQUE_VALUES, ConditionFilterType.UNIQUE_VALUES); filterTypeLookup.put(STCfType.DUPLICATE_VALUES, ConditionFilterType.DUPLICATE_VALUES); filterTypeLookup.put(STCfType.CONTAINS_TEXT, ConditionFilterType.CONTAINS_TEXT); filterTypeLookup.put(STCfType.NOT_CONTAINS_TEXT, ConditionFilterType.NOT_CONTAINS_TEXT); filterTypeLookup.put(STCfType.BEGINS_WITH, ConditionFilterType.BEGINS_WITH); filterTypeLookup.put(STCfType.ENDS_WITH, ConditionFilterType.ENDS_WITH); filterTypeLookup.put(STCfType.CONTAINS_BLANKS, ConditionFilterType.CONTAINS_BLANKS); filterTypeLookup.put(STCfType.NOT_CONTAINS_BLANKS, ConditionFilterType.NOT_CONTAINS_BLANKS); filterTypeLookup.put(STCfType.CONTAINS_ERRORS, ConditionFilterType.CONTAINS_ERRORS); filterTypeLookup.put(STCfType.NOT_CONTAINS_ERRORS, ConditionFilterType.NOT_CONTAINS_ERRORS); filterTypeLookup.put(STCfType.TIME_PERIOD, ConditionFilterType.TIME_PERIOD); filterTypeLookup.put(STCfType.ABOVE_AVERAGE, ConditionFilterType.ABOVE_AVERAGE); } /** * NOTE: does not set priority, so this assumes the rule will not be added to the sheet yet * @param sh */ /*package*/ XSSFConditionalFormattingRule(XSSFSheet sh) { _cfRule = CTCfRule.Factory.newInstance(); _sh = sh; } /*package*/ XSSFConditionalFormattingRule(XSSFSheet sh, CTCfRule cfRule) { _cfRule = cfRule; _sh = sh; } /*package*/ CTCfRule getCTCfRule() { return _cfRule; } /*package*/ CTDxf getDxf(boolean create) { StylesTable styles = _sh.getWorkbook().getStylesSource(); CTDxf dxf = null; if (styles._getDXfsSize() > 0 && _cfRule.isSetDxfId()) { int dxfId = (int) _cfRule.getDxfId(); dxf = styles.getDxfAt(dxfId); } if (create && dxf == null) { dxf = CTDxf.Factory.newInstance(); int dxfId = styles.putDxf(dxf); _cfRule.setDxfId(dxfId - 1); } return dxf; } public int getPriority() { final int priority = _cfRule.getPriority(); // priorities start at 1, if it is less, it is undefined, use definition order in caller return priority >= 1 ? priority : 0; } public boolean getStopIfTrue() { return _cfRule.getStopIfTrue(); } /** * Create a new border formatting structure if it does not exist, * otherwise just return existing object. * * @return - border formatting object, never returns <code>null</code>. */ public XSSFBorderFormatting createBorderFormatting() { CTDxf dxf = getDxf(true); CTBorder border; if (!dxf.isSetBorder()) { border = dxf.addNewBorder(); } else { border = dxf.getBorder(); } return new XSSFBorderFormatting(border, _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * @return - border formatting object if defined, <code>null</code> otherwise */ public XSSFBorderFormatting getBorderFormatting() { CTDxf dxf = getDxf(false); if (dxf == null || !dxf.isSetBorder()) return null; return new XSSFBorderFormatting(dxf.getBorder(), _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * Create a new font formatting structure if it does not exist, * otherwise just return existing object. * * @return - font formatting object, never returns <code>null</code>. */ public XSSFFontFormatting createFontFormatting() { CTDxf dxf = getDxf(true); CTFont font; if (!dxf.isSetFont()) { font = dxf.addNewFont(); } else { font = dxf.getFont(); } return new XSSFFontFormatting(font, _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * @return - font formatting object if defined, <code>null</code> otherwise */ public XSSFFontFormatting getFontFormatting() { CTDxf dxf = getDxf(false); if (dxf == null || !dxf.isSetFont()) return null; return new XSSFFontFormatting(dxf.getFont(), _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * Create a new pattern formatting structure if it does not exist, * otherwise just return existing object. * * @return - pattern formatting object, never returns <code>null</code>. */ public XSSFPatternFormatting createPatternFormatting() { CTDxf dxf = getDxf(true); CTFill fill; if (!dxf.isSetFill()) { fill = dxf.addNewFill(); } else { fill = dxf.getFill(); } return new XSSFPatternFormatting(fill, _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * @return - pattern formatting object if defined, <code>null</code> otherwise */ public XSSFPatternFormatting getPatternFormatting() { CTDxf dxf = getDxf(false); if (dxf == null || !dxf.isSetFill()) return null; return new XSSFPatternFormatting(dxf.getFill(), _sh.getWorkbook().getStylesSource().getIndexedColors()); } /** * * @param color * @return data bar formatting */ public XSSFDataBarFormatting createDataBarFormatting(XSSFColor color) { // Is it already there? if (_cfRule.isSetDataBar() && _cfRule.getType() == STCfType.DATA_BAR) return getDataBarFormatting(); // Mark it as being a Data Bar _cfRule.setType(STCfType.DATA_BAR); // Ensure the right element CTDataBar bar = null; if (_cfRule.isSetDataBar()) { bar = _cfRule.getDataBar(); } else { bar = _cfRule.addNewDataBar(); } // Set the color bar.setColor(color.getCTColor()); // Add the default thresholds CTCfvo min = bar.addNewCfvo(); min.setType(STCfvoType.Enum.forString(RangeType.MIN.name)); CTCfvo max = bar.addNewCfvo(); max.setType(STCfvoType.Enum.forString(RangeType.MAX.name)); // Wrap and return return new XSSFDataBarFormatting(bar, _sh.getWorkbook().getStylesSource().getIndexedColors()); } public XSSFDataBarFormatting getDataBarFormatting() { if (_cfRule.isSetDataBar()) { CTDataBar bar = _cfRule.getDataBar(); return new XSSFDataBarFormatting(bar, _sh.getWorkbook().getStylesSource().getIndexedColors()); } else { return null; } } public XSSFIconMultiStateFormatting createMultiStateFormatting(IconSet iconSet) { // Is it already there? if (_cfRule.isSetIconSet() && _cfRule.getType() == STCfType.ICON_SET) return getMultiStateFormatting(); // Mark it as being an Icon Set _cfRule.setType(STCfType.ICON_SET); // Ensure the right element CTIconSet icons = null; if (_cfRule.isSetIconSet()) { icons = _cfRule.getIconSet(); } else { icons = _cfRule.addNewIconSet(); } // Set the type of the icon set if (iconSet.name != null) { STIconSetType.Enum xIconSet = STIconSetType.Enum.forString(iconSet.name); icons.setIconSet(xIconSet); } // Add a default set of thresholds int jump = 100 / iconSet.num; STCfvoType.Enum type = STCfvoType.Enum.forString(RangeType.PERCENT.name); for (int i = 0; i < iconSet.num; i++) { CTCfvo cfvo = icons.addNewCfvo(); cfvo.setType(type); cfvo.setVal(Integer.toString(i * jump)); } // Wrap and return return new XSSFIconMultiStateFormatting(icons); } public XSSFIconMultiStateFormatting getMultiStateFormatting() { if (_cfRule.isSetIconSet()) { CTIconSet icons = _cfRule.getIconSet(); return new XSSFIconMultiStateFormatting(icons); } else { return null; } } public XSSFColorScaleFormatting createColorScaleFormatting() { // Is it already there? if (_cfRule.isSetColorScale() && _cfRule.getType() == STCfType.COLOR_SCALE) return getColorScaleFormatting(); // Mark it as being a Color Scale _cfRule.setType(STCfType.COLOR_SCALE); // Ensure the right element CTColorScale scale = null; if (_cfRule.isSetColorScale()) { scale = _cfRule.getColorScale(); } else { scale = _cfRule.addNewColorScale(); } // Add a default set of thresholds and colors if (scale.sizeOfCfvoArray() == 0) { CTCfvo cfvo; cfvo = scale.addNewCfvo(); cfvo.setType(STCfvoType.Enum.forString(RangeType.MIN.name)); cfvo = scale.addNewCfvo(); cfvo.setType(STCfvoType.Enum.forString(RangeType.PERCENTILE.name)); cfvo.setVal("50"); cfvo = scale.addNewCfvo(); cfvo.setType(STCfvoType.Enum.forString(RangeType.MAX.name)); for (int i = 0; i < 3; i++) { scale.addNewColor(); } } // Wrap and return return new XSSFColorScaleFormatting(scale, _sh.getWorkbook().getStylesSource().getIndexedColors()); } public XSSFColorScaleFormatting getColorScaleFormatting() { if (_cfRule.isSetColorScale()) { CTColorScale scale = _cfRule.getColorScale(); return new XSSFColorScaleFormatting(scale, _sh.getWorkbook().getStylesSource().getIndexedColors()); } else { return null; } } /** * Return the number format from the dxf style record if present, null if not * @see org.apache.poi.ss.usermodel.ConditionalFormattingRule#getNumberFormat() */ public ExcelNumberFormat getNumberFormat() { CTDxf dxf = getDxf(false); if (dxf == null || !dxf.isSetNumFmt()) return null; CTNumFmt numFmt = dxf.getNumFmt(); return new ExcelNumberFormat((int) numFmt.getNumFmtId(), numFmt.getFormatCode()); } /** * Type of conditional formatting rule. */ @Override public ConditionType getConditionType() { return typeLookup.get(_cfRule.getType()); } /** * Will return null if {@link #getConditionType()} != {@link ConditionType#FILTER} * @see org.apache.poi.ss.usermodel.ConditionalFormattingRule#getConditionFilterType() */ public ConditionFilterType getConditionFilterType() { return filterTypeLookup.get(_cfRule.getType()); } public ConditionFilterData getFilterConfiguration() { return new XSSFConditionFilterData(_cfRule); } /** * The comparison function used when the type of conditional formatting is set to * {@link ConditionType#CELL_VALUE_IS} * <p> * MUST be a constant from {@link org.apache.poi.ss.usermodel.ComparisonOperator} * </p> * * @return the conditional format operator */ @Override public byte getComparisonOperation() { STConditionalFormattingOperator.Enum op = _cfRule.getOperator(); if (op == null) return ComparisonOperator.NO_COMPARISON; switch (op.intValue()) { case STConditionalFormattingOperator.INT_LESS_THAN: return ComparisonOperator.LT; case STConditionalFormattingOperator.INT_LESS_THAN_OR_EQUAL: return ComparisonOperator.LE; case STConditionalFormattingOperator.INT_GREATER_THAN: return ComparisonOperator.GT; case STConditionalFormattingOperator.INT_GREATER_THAN_OR_EQUAL: return ComparisonOperator.GE; case STConditionalFormattingOperator.INT_EQUAL: return ComparisonOperator.EQUAL; case STConditionalFormattingOperator.INT_NOT_EQUAL: return ComparisonOperator.NOT_EQUAL; case STConditionalFormattingOperator.INT_BETWEEN: return ComparisonOperator.BETWEEN; case STConditionalFormattingOperator.INT_NOT_BETWEEN: return ComparisonOperator.NOT_BETWEEN; } return ComparisonOperator.NO_COMPARISON; } /** * The formula used to evaluate the first operand for the conditional formatting rule. * <p> * If the condition type is {@link ConditionType#CELL_VALUE_IS}, * this field is the first operand of the comparison. * If type is {@link ConditionType#FORMULA}, this formula is used * to determine if the conditional formatting is applied. * </p> * <p> * If comparison type is {@link ConditionType#FORMULA} the formula MUST be a Boolean function * </p> * * @return the first formula */ public String getFormula1() { return _cfRule.sizeOfFormulaArray() > 0 ? _cfRule.getFormulaArray(0) : null; } /** * The formula used to evaluate the second operand of the comparison when * comparison type is {@link ConditionType#CELL_VALUE_IS} and operator * is either {@link org.apache.poi.ss.usermodel.ComparisonOperator#BETWEEN} or {@link org.apache.poi.ss.usermodel.ComparisonOperator#NOT_BETWEEN} * * @return the second formula */ public String getFormula2() { return _cfRule.sizeOfFormulaArray() == 2 ? _cfRule.getFormulaArray(1) : null; } public String getText() { return _cfRule.getText(); } /** * Conditional format rules don't define stripes, so always 0 * @see org.apache.poi.ss.usermodel.DifferentialStyleProvider#getStripeSize() */ public int getStripeSize() { return 0; } }