http://poi.apache.org/: Apache POI - Java API To Access Microsoft Format Files (Apache Software Foundation) 
/* ====================================================================
   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.hssf.usermodel;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.NumberPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.StringPtg;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.util.LocaleUtil;


Data Validation Constraint

/**
 * Data Validation Constraint
 */
public class DVConstraint implements DataValidationConstraint {
	/* package */ static final class FormulaPair {

		private final Ptg[] _formula1;
		private final Ptg[] _formula2;

		FormulaPair(Ptg[] formula1, Ptg[] formula2) {
			_formula1 = (formula1 == null) ? null : formula1.clone();
			_formula2 = (formula2 == null) ? null : formula2.clone();
		}
		public Ptg[] getFormula1() {
			return _formula1;
		}
		public Ptg[] getFormula2() {
			return _formula2;
		}
		
	}
	
	private final int _validationType;
	private int _operator;
	private String[] _explicitListValues;
	
	private String _formula1;
	private String _formula2;
	private Double _value1;
	private Double _value2;

	
	private DVConstraint(int validationType, int comparisonOperator, String formulaA,
			String formulaB, Double value1, Double value2, String[] explicitListValues) {
		_validationType = validationType;
		_operator = comparisonOperator;
		_formula1 = formulaA;
		_formula2 = formulaB;
		_value1 = value1;
		_value2 = value2;
		_explicitListValues = (explicitListValues == null) ? null : explicitListValues.clone();
	}
	
	
	
Creates a list constraint

/**
	 * Creates a list constraint
	 */
	private DVConstraint(String listFormula, String[] explicitListValues) {
		this(ValidationType.LIST, OperatorType.IGNORED,
			listFormula, null, null, null, explicitListValues);
	}

	
Creates a number based data validation constraint. The text values entered for expr1 and expr2
can be either standard Excel formulas or formatted number values. If the expression starts 
with '=' it is parsed as a formula, otherwise it is parsed as a formatted number. 

Params:
/**
	 * Creates a number based data validation constraint. The text values entered for expr1 and expr2
	 * can be either standard Excel formulas or formatted number values. If the expression starts 
	 * with '=' it is parsed as a formula, otherwise it is parsed as a formatted number. 
	 * 
	 * @param validationType one of {@link org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType#ANY},
     * {@link org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType#DECIMAL},
     * {@link org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType#INTEGER},
     * {@link org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType#TEXT_LENGTH}
	 * @param comparisonOperator any constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum
	 * @param expr1 date formula (when first char is '=') or formatted number value
	 * @param expr2 date formula (when first char is '=') or formatted number value
	 */
	public static DVConstraint createNumericConstraint(int validationType, int comparisonOperator, 
			String expr1, String expr2) {
		switch (validationType) {
			case ValidationType.ANY:
				if (expr1 != null || expr2 != null) {
					throw new IllegalArgumentException("expr1 and expr2 must be null for validation type 'any'");
				}
				break;
			case ValidationType.DECIMAL:
			case ValidationType.INTEGER:
			case ValidationType.TEXT_LENGTH:
				if (expr1 == null) {
					throw new IllegalArgumentException("expr1 must be supplied");
				}
				OperatorType.validateSecondArg(comparisonOperator, expr2);
				break;
			default:
				throw new IllegalArgumentException("Validation Type ("
						+ validationType + ") not supported with this method");
		}
		// formula1 and value1 are mutually exclusive
		String formula1 = getFormulaFromTextExpression(expr1);
		Double value1 = formula1 == null ? convertNumber(expr1) : null;
		// formula2 and value2 are mutually exclusive
		String formula2 = getFormulaFromTextExpression(expr2);
		Double value2 = formula2 == null ? convertNumber(expr2) : null;
		return new DVConstraint(validationType, comparisonOperator, formula1, formula2, value1, value2, null);
	}

	public static DVConstraint createFormulaListConstraint(String listFormula) {
		return new DVConstraint(listFormula, null);
	}
	public static DVConstraint createExplicitListConstraint(String[] explicitListValues) {
		return new DVConstraint(null, explicitListValues);
	}
	
	
	
Creates a time based data validation constraint. The text values entered for expr1 and expr2
can be either standard Excel formulas or formatted time values. If the expression starts 
with '=' it is parsed as a formula, otherwise it is parsed as a formatted time.  To parse 
formatted times, two formats are supported:  "HH:MM" or "HH:MM:SS".  This is contrary to 
Excel which uses the default time format from the OS.

Params:
  • comparisonOperator – constant from OperatorType enum
  • expr1 – date formula (when first char is '=') or formatted time value
  • expr2 – date formula (when first char is '=') or formatted time value
/**
	 * Creates a time based data validation constraint. The text values entered for expr1 and expr2
	 * can be either standard Excel formulas or formatted time values. If the expression starts 
	 * with '=' it is parsed as a formula, otherwise it is parsed as a formatted time.  To parse 
	 * formatted times, two formats are supported:  "HH:MM" or "HH:MM:SS".  This is contrary to 
	 * Excel which uses the default time format from the OS.
	 * 
	 * @param comparisonOperator constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum
	 * @param expr1 date formula (when first char is '=') or formatted time value
	 * @param expr2 date formula (when first char is '=') or formatted time value
	 */
	public static DVConstraint createTimeConstraint(int comparisonOperator, String expr1, String expr2) {
		if (expr1 == null) {
			throw new IllegalArgumentException("expr1 must be supplied");
		}
		OperatorType.validateSecondArg(comparisonOperator, expr1);
		
		// formula1 and value1 are mutually exclusive
		String formula1 = getFormulaFromTextExpression(expr1);
		Double value1 = formula1 == null ? convertTime(expr1) : null;
		// formula2 and value2 are mutually exclusive
		String formula2 = getFormulaFromTextExpression(expr2);
		Double value2 = formula2 == null ? convertTime(expr2) : null;
		return new DVConstraint(ValidationType.TIME, comparisonOperator, formula1, formula2, value1, value2, null);
	}
	
	
Creates a date based data validation constraint. The text values entered for expr1 and expr2
can be either standard Excel formulas or formatted date values. If the expression starts 
with '=' it is parsed as a formula, otherwise it is parsed as a formatted date (Excel uses 
the same convention).  To parse formatted dates, a date format needs to be specified.  This
is contrary to Excel which uses the default short date format from the OS.

Params:
  • comparisonOperator – constant from OperatorType enum
  • expr1 – date formula (when first char is '=') or formatted date value
  • expr2 – date formula (when first char is '=') or formatted date value
  • dateFormat – ignored if both expr1 and expr2 are formulas.  Default value is "YYYY/MM/DD"
otherwise any other valid argument for SimpleDateFormat can be used
See Also:
/**
	 * Creates a date based data validation constraint. The text values entered for expr1 and expr2
	 * can be either standard Excel formulas or formatted date values. If the expression starts 
	 * with '=' it is parsed as a formula, otherwise it is parsed as a formatted date (Excel uses 
	 * the same convention).  To parse formatted dates, a date format needs to be specified.  This
	 * is contrary to Excel which uses the default short date format from the OS.
	 * 
	 * @param comparisonOperator constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum
	 * @param expr1 date formula (when first char is '=') or formatted date value
	 * @param expr2 date formula (when first char is '=') or formatted date value
	 * @param dateFormat ignored if both expr1 and expr2 are formulas.  Default value is "YYYY/MM/DD"
	 * otherwise any other valid argument for <tt>SimpleDateFormat</tt> can be used
	 * @see <a href='http://java.sun.com/j2se/1.5.0/docs/api/java/text/DateFormat.html'>SimpleDateFormat</a>
	 */
	public static DVConstraint createDateConstraint(int comparisonOperator, String expr1, String expr2, String dateFormat) {
		if (expr1 == null) {
			throw new IllegalArgumentException("expr1 must be supplied");
		}
		OperatorType.validateSecondArg(comparisonOperator, expr2);
		SimpleDateFormat df = null;
		if (dateFormat != null) {
		    df = new SimpleDateFormat(dateFormat, LocaleUtil.getUserLocale());
		    df.setTimeZone(LocaleUtil.getUserTimeZone());
		}
		
		// formula1 and value1 are mutually exclusive
		String formula1 = getFormulaFromTextExpression(expr1);
		Double value1 = formula1 == null ? convertDate(expr1, df) : null;
		// formula2 and value2 are mutually exclusive
		String formula2 = getFormulaFromTextExpression(expr2);
		Double value2 = formula2 == null ? convertDate(expr2, df) : null;
		return new DVConstraint(ValidationType.DATE, comparisonOperator, formula1, formula2, value1, value2, null);
	}
	
	
Distinguishes formula expressions from simple value expressions.  This logic is only 
required by a few factory methods in this class that create data validation constraints
from more or less the same parameters that would have been entered in the Excel UI.  The
data validation dialog box uses the convention that formulas begin with '='.  Other methods
in this class follow the POI convention (formulas and values are distinct), so the '=' 
convention is not used there.
 

Params:
  • textExpr – a formula or value expression
Returns:all text after '=' if textExpr begins with '='. Otherwise null if textExpr does not begin with '='
/**
	 * Distinguishes formula expressions from simple value expressions.  This logic is only 
	 * required by a few factory methods in this class that create data validation constraints
	 * from more or less the same parameters that would have been entered in the Excel UI.  The
	 * data validation dialog box uses the convention that formulas begin with '='.  Other methods
	 * in this class follow the POI convention (formulas and values are distinct), so the '=' 
	 * convention is not used there.
	 *  
	 * @param textExpr a formula or value expression
	 * @return all text after '=' if textExpr begins with '='. Otherwise <code>null</code> if textExpr does not begin with '='
	 */
	private static String getFormulaFromTextExpression(String textExpr) {
		if (textExpr == null) {
			return null;
		}
		if (textExpr.length() < 1) {
			throw new IllegalArgumentException("Empty string is not a valid formula/value expression");
		}
		if (textExpr.charAt(0) == '=') {
			return textExpr.substring(1);
		}
		return null;
	}


	
Returns:null if numberStr is null
/**
	 * @return <code>null</code> if numberStr is <code>null</code>
	 */
	private static Double convertNumber(String numberStr) {
		if (numberStr == null) {
			return null;
		}
		try {
			return Double.valueOf(numberStr);
		} catch (NumberFormatException e) {
			throw new RuntimeException("The supplied text '" + numberStr 
					+ "' could not be parsed as a number");
		}
	}

	
Returns:null if timeStr is null
/**
	 * @return <code>null</code> if timeStr is <code>null</code>
	 */
	private static Double convertTime(String timeStr) {
		if (timeStr == null) {
			return null;
		}
		return Double.valueOf(HSSFDateUtil.convertTime(timeStr));
	}
	
Params:
  • dateFormat – pass null for default YYYYMMDD
Returns:null if timeStr is null
/**
	 * @param dateFormat pass <code>null</code> for default YYYYMMDD
	 * @return <code>null</code> if timeStr is <code>null</code>
	 */
	private static Double convertDate(String dateStr, SimpleDateFormat dateFormat) {
		if (dateStr == null) {
			return null;
		}
		Date dateVal; 
		if (dateFormat == null) {
			dateVal = HSSFDateUtil.parseYYYYMMDDDate(dateStr);
		} else {
			try {
				dateVal = dateFormat.parse(dateStr);
			} catch (ParseException e) {
				throw new RuntimeException("Failed to parse date '" + dateStr 
						+ "' using specified format '" + dateFormat + "'", e);
			}
		}
		return Double.valueOf(HSSFDateUtil.getExcelDate(dateVal));
	}

	public static DVConstraint createCustomFormulaConstraint(String formula) {
		if (formula == null) {
			throw new IllegalArgumentException("formula must be supplied");
		}
		return new DVConstraint(ValidationType.FORMULA, OperatorType.IGNORED, formula, null, null, null, null);
	}
	
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getValidationType()
	 */
	public int getValidationType() {
		return _validationType;
	}
	
Convenience method

Returns:true if this constraint is a 'list' validation
/**
	 * Convenience method
	 * @return <code>true</code> if this constraint is a 'list' validation
	 */
	public boolean isListValidationType() {
		return _validationType == ValidationType.LIST;
	}
	
Convenience method

Returns:true if this constraint is a 'list' validation with explicit values
/**
	 * Convenience method
	 * @return <code>true</code> if this constraint is a 'list' validation with explicit values
	 */
	public boolean isExplicitList() {
		return _validationType == ValidationType.LIST && _explicitListValues != null;
	}
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getOperator()
	 */
	public int getOperator() {
		return _operator;
	}
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setOperator(int)
	 */
	public void setOperator(int operator) {
		_operator = operator;
	}
	
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getExplicitListValues()
	 */
	public String[] getExplicitListValues() {
		return _explicitListValues;
	}
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setExplicitListValues(java.lang.String[])
	 */
	public void setExplicitListValues(String[] explicitListValues) {
		if (_validationType != ValidationType.LIST) {
			throw new RuntimeException("Cannot setExplicitListValues on non-list constraint");
		}
		_formula1 = null;
		_explicitListValues = explicitListValues;
	}

	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula1()
	 */
	public String getFormula1() {
		return _formula1;
	}
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula1(java.lang.String)
	 */
	public void setFormula1(String formula1) {
		_value1 = null;
		_explicitListValues = null;
		_formula1 = formula1;
	}

	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula2()
	 */
	public String getFormula2() {
		return _formula2;
	}
	/* (non-Javadoc)
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula2(java.lang.String)
	 */
	public void setFormula2(String formula2) {
		_value2 = null;
		_formula2 = formula2;
	}

	
Returns:the numeric value for expression 1. May be null
/**
	 * @return the numeric value for expression 1. May be <code>null</code>
	 */
	public Double getValue1() {
		return _value1;
	}
	
Sets a numeric value for expression 1.

/**
	 * Sets a numeric value for expression 1.
	 */
	public void setValue1(double value1) {
		_formula1 = null;
		_value1 = Double.valueOf(value1);
	}

	
Returns:the numeric value for expression 2. May be null
/**
	 * @return the numeric value for expression 2. May be <code>null</code>
	 */
	public Double getValue2() {
		return _value2;
	}
	
Sets a numeric value for expression 2.

/**
	 * Sets a numeric value for expression 2.
	 */
	public void setValue2(double value2) {
		_formula2 = null;
		_value2 = Double.valueOf(value2);
	}
	
	
Returns:both parsed formulas (for expression 1 and 2).
/**
	 * @return both parsed formulas (for expression 1 and 2). 
	 */
	/* package */ FormulaPair createFormulas(HSSFSheet sheet) {
		Ptg[] formula1;
		Ptg[] formula2;
		if (isListValidationType()) {
			formula1 = createListFormula(sheet);
			formula2 = Ptg.EMPTY_PTG_ARRAY;
		} else {
			formula1 = convertDoubleFormula(_formula1, _value1, sheet);
			formula2 = convertDoubleFormula(_formula2, _value2, sheet);
		}
		return new FormulaPair(formula1, formula2);
	}

    @SuppressWarnings("resource")
    private Ptg[] createListFormula(HSSFSheet sheet) {

		if (_explicitListValues == null) {
            HSSFWorkbook wb = sheet.getWorkbook();
            // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
			return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet));
			// To do: Excel places restrictions on the available operations within a list formula.
			// Some things like union and intersection are not allowed.
		}
		// explicit list was provided
		StringBuilder sb = new StringBuilder(_explicitListValues.length * 16);
		for (int i = 0; i < _explicitListValues.length; i++) {
			if (i > 0) {
				sb.append('\0'); // list delimiter is the nul char
			}
			sb.append(_explicitListValues[i]);
		
		}
		return new Ptg[] { new StringPtg(sb.toString()), };
	}

	
Returns:The parsed token array representing the formula or value specified. 
Empty array if both formula and value are null
/**
	 * @return The parsed token array representing the formula or value specified. 
	 * Empty array if both formula and value are <code>null</code>
	 */
    @SuppressWarnings("resource")
	private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) {
		if (formula == null) {
			if (value == null) {
				return Ptg.EMPTY_PTG_ARRAY;
			}
			return new Ptg[] { new NumberPtg(value.doubleValue()), };
		}
		if (value != null) {
			throw new IllegalStateException("Both formula and value cannot be present");
		}
        HSSFWorkbook wb = sheet.getWorkbook();
		return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet));
	}	

    static DVConstraint createDVConstraint(DVRecord dvRecord, FormulaRenderingWorkbook book) {
        switch (dvRecord.getDataType()) {
        case ValidationType.ANY:
            return new DVConstraint(ValidationType.ANY, dvRecord.getConditionOperator(), null, null, null, null, null);
        case ValidationType.INTEGER:
        case ValidationType.DECIMAL:
        case ValidationType.DATE:
        case ValidationType.TIME:
        case ValidationType.TEXT_LENGTH:
            FormulaValuePair pair1 = toFormulaString(dvRecord.getFormula1(), book);
            FormulaValuePair pair2 = toFormulaString(dvRecord.getFormula2(), book);
            return new DVConstraint(dvRecord.getDataType(), dvRecord.getConditionOperator(), pair1.formula(),
                    pair2.formula(), pair1.value(), pair2.value(), null);
        case ValidationType.LIST:
            if (dvRecord.getListExplicitFormula()) {
                String values = toFormulaString(dvRecord.getFormula1(), book).string();
                if (values.startsWith("\"")) {
                    values = values.substring(1);
                }
                if (values.endsWith("\"")) {
                    values = values.substring(0, values.length() - 1);
                }
                String[] explicitListValues = values.split(Pattern.quote("\0"));
                return createExplicitListConstraint(explicitListValues);
            } else {
                String listFormula = toFormulaString(dvRecord.getFormula1(), book).string();
                return createFormulaListConstraint(listFormula);
            }
        case ValidationType.FORMULA:
            return createCustomFormulaConstraint(toFormulaString(dvRecord.getFormula1(), book).string());
        default:
            throw new UnsupportedOperationException("validationType="+dvRecord.getDataType());
        }
    }

    private static class FormulaValuePair {
        private String _formula;
        private String _value;

        public String formula() {
            return _formula;
        }

        public Double value() {
            if (_value == null) {
                return null;
            }
            return Double.valueOf(_value);
        }

        public String string() {
            if (_formula != null) {
                return _formula;
            }
            if (_value != null) {
                return _value;
            }
            return null;
        }
    }

    private static FormulaValuePair toFormulaString(Ptg[] ptgs, FormulaRenderingWorkbook book) {
        FormulaValuePair pair = new FormulaValuePair();
        if (ptgs != null && ptgs.length > 0) {
            String string = FormulaRenderer.toFormulaString(book, ptgs);
            if (ptgs.length == 1 && ptgs[0].getClass() == NumberPtg.class) {
                pair._value = string;
            } else {
                pair._formula = string;
            }
        }
        return pair;
    }
}