support for data validation for OOXML, see Bugzilla 49244

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@944869 13f79535-47bb-0310-9956-ffa450edef68

8 files changed, 588 insertions, 217 deletions

diff --git a/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java b/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
index b28a21786d..5e56acffa9 100644
--- a/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
+++ b/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
@@ -26,68 +26,14 @@ import org.apache.poi.hssf.record.formula.NumberPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.hssf.record.formula.StringPtg;
 import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.usermodel.DataValidationConstraint;
 
 /**
  * 
  * @author Josh Micich
  */
-public class DVConstraint {
-	/**
-	 * ValidationType enum
-	 */
-	public static final class ValidationType {
-		private ValidationType() {
-			// no instances of this class
-		}
-		/** 'Any value' type - value not restricted */
-		public static final int ANY         = 0x00;
-		/** Integer ('Whole number') type */
-		public static final int INTEGER     = 0x01;
-		/** Decimal type */
-		public static final int DECIMAL     = 0x02;
-		/** List type ( combo box type ) */
-		public static final int LIST        = 0x03;
-		/** Date type */
-		public static final int DATE        = 0x04;
-		/** Time type */
-		public static final int TIME        = 0x05;
-		/** String length type */
-		public static final int TEXT_LENGTH = 0x06;
-		/** Formula ( 'Custom' ) type */
-		public static final int FORMULA     = 0x07;
-	}
-	/**
-	 * Condition operator enum
-	 */
-	public static final class OperatorType {
-		private OperatorType() {
-			// no instances of this class
-		}
-
-		public static final int BETWEEN = 0x00;
-		public static final int NOT_BETWEEN = 0x01;
-		public static final int EQUAL = 0x02;
-		public static final int NOT_EQUAL = 0x03;
-		public static final int GREATER_THAN = 0x04;
-		public static final int LESS_THAN = 0x05;
-		public static final int GREATER_OR_EQUAL = 0x06;
-		public static final int LESS_OR_EQUAL = 0x07;
-		/** default value to supply when the operator type is not used */
-		public static final int IGNORED = BETWEEN;
-		
-		/* package */ static void validateSecondArg(int comparisonOperator, String paramValue) {
-			switch (comparisonOperator) {
-				case BETWEEN:
-				case NOT_BETWEEN:
-					if (paramValue == null) {
-						throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
-					}
-				// all other operators don't need second arg
-			}
-		}
-	}
-	
-	/* package */ static final class FormulaPair {
+public class DVConstraint implements DataValidationConstraint {
+	/* package */ public static final class FormulaPair {
 
 		private final Ptg[] _formula1;
 		private final Ptg[] _formula2;
@@ -211,8 +157,8 @@ public class DVConstraint {
 		String formula2 = getFormulaFromTextExpression(expr2);
 		Double value2 = formula2 == null ? convertTime(expr2) : null;
 		return new DVConstraint(VT.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 
@@ -321,65 +267,8 @@ public class DVConstraint {
 		return new DVConstraint(VT.FORMULA, OperatorType.IGNORED, formula, null, null, null, null);
 	}
 	
-	/**
-	 * @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);
-	}
-
-	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
-		StringBuffer sb = new StringBuffer(_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()), };
-	}
-
-	/**
-	 * @return The parsed token array representing the formula or value specified. 
-	 * Empty array if both formula and value are <code>null</code>
-	 */
-	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));
-	}
-	
-	
-	/**
-	 * @return data validation type of this constraint
-	 * @see ValidationType
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getValidationType()
 	 */
 	public int getValidationType() {
 		return _validationType;
@@ -398,24 +287,28 @@ public class DVConstraint {
 	public boolean isExplicitList() {
 		return _validationType == VT.LIST && _explicitListValues != null;
 	}
-	/**
-	 * @return the operator used for this constraint
-	 * @see OperatorType
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getOperator()
 	 */
 	public int getOperator() {
 		return _operator;
 	}
-	/**
-	 * Sets the comparison operator for this constraint
-	 * @see OperatorType
+	/* (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 != VT.LIST) {
 			throw new RuntimeException("Cannot setExplicitListValues on non-list constraint");
@@ -424,14 +317,14 @@ public class DVConstraint {
 		_explicitListValues = explicitListValues;
 	}
 
-	/**
-	 * @return the formula for expression 1. May be <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula1()
 	 */
 	public String getFormula1() {
 		return _formula1;
 	}
-	/**
-	 * Sets a formula for expression 1.
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula1(java.lang.String)
 	 */
 	public void setFormula1(String formula1) {
 		_value1 = null;
@@ -439,14 +332,14 @@ public class DVConstraint {
 		_formula1 = formula1;
 	}
 
-	/**
-	 * @return the formula for expression 2. May be <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula2()
 	 */
 	public String getFormula2() {
 		return _formula2;
 	}
-	/**
-	 * Sets a formula for expression 2.
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula2(java.lang.String)
 	 */
 	public void setFormula2(String formula2) {
 		_value2 = null;
@@ -480,4 +373,59 @@ public class DVConstraint {
 		_formula2 = null;
 		_value2 = new Double(value2);
 	}
+	
+	/**
+	 * @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);
+	}
+
+	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
+		StringBuffer sb = new StringBuffer(_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()), };
+	}
+
+	/**
+	 * @return The parsed token array representing the formula or value specified. 
+	 * Empty array if both formula and value are <code>null</code>
+	 */
+	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));
+	}	
 }
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java
index 0704fd5cb3..9ea6038d0b 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java
@@ -19,6 +19,9 @@ package org.apache.poi.hssf.usermodel;
 
 import org.apache.poi.hssf.record.DVRecord;
 import org.apache.poi.hssf.usermodel.DVConstraint.FormulaPair;
+import org.apache.poi.ss.usermodel.DataValidation;
+import org.apache.poi.ss.usermodel.DataValidationConstraint;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
 import org.apache.poi.ss.util.CellRangeAddressList;
 
 /**
@@ -26,19 +29,7 @@ import org.apache.poi.ss.util.CellRangeAddressList;
  * 
  * @author Dragos Buleandra (dragos.buleandra@trade2b.ro)
  */
-public final class HSSFDataValidation {
-	/**
-	 * Error style constants for error box
-	 */
-	public static final class ErrorStyle {
-    	/** STOP style */
-    	public static final int STOP    = 0x00;
-    	/** WARNING style */
-    	public static final int WARNING = 0x01;
-    	/** INFO style */
-    	public static final int INFO    = 0x02;
-	}    
-
+public final class HSSFDataValidation implements DataValidation {
 	private String _prompt_title;
 	private String _prompt_text;
 	private String _error_title;
@@ -49,7 +40,7 @@ public final class HSSFDataValidation {
 	private boolean _suppress_dropdown_arrow = false;
 	private boolean _showPromptBox = true;
 	private boolean _showErrorBox = true;
-	private final CellRangeAddressList _regions;
+	private CellRangeAddressList _regions;
 	private DVConstraint _constraint;
 
 	/**
@@ -57,119 +48,106 @@ public final class HSSFDataValidation {
 	 * applied
 	 * @param constraint 
 	 */
-	public HSSFDataValidation(CellRangeAddressList regions, DVConstraint constraint) {
+	public HSSFDataValidation(CellRangeAddressList regions, DataValidationConstraint constraint) {
 		_regions = regions;
-		_constraint = constraint;
+		
+		//FIXME: This cast can be avoided.
+		_constraint = (DVConstraint)constraint;
 	}
 
 
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getConstraint()
+	 */
+	public DataValidationConstraint getValidationConstraint() {
+		return _constraint;
+	}
+
 	public DVConstraint getConstraint() {
 		return _constraint;
 	}
+	
+	public CellRangeAddressList getRegions() {
+		return _regions;
+	}
 
-	/**
-	 * Sets the error style for error box
-	 * @see ErrorStyle
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#setErrorStyle(int)
 	 */
 	public void setErrorStyle(int error_style) {
 		_errorStyle = error_style;
 	}
 
-	/**
-	 * @return the error style of error box
-	 * @see ErrorStyle
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorStyle()
 	 */
 	public int getErrorStyle() {
 		return _errorStyle;
 	}
 
-	/**
-	 * Sets if this object allows empty as a valid value
-	 * 
-	 * @param allowed <code>true</code> if this object should treats empty as valid value , <code>false</code>
-	 *            otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#setEmptyCellAllowed(boolean)
 	 */
 	public void setEmptyCellAllowed(boolean allowed) {
 		_emptyCellAllowed = allowed;
 	}
 
-	/**
-	 * Retrieve the settings for empty cells allowed
-	 * 
-	 * @return True if this object should treats empty as valid value , false
-	 *         otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getEmptyCellAllowed()
 	 */
 	public boolean getEmptyCellAllowed() {
 		return _emptyCellAllowed;
 	}
 
-	/**
-	 * Useful for list validation objects .
-	 * 
-	 * @param suppress
-	 *            True if a list should display the values into a drop down list ,
-	 *            false otherwise . In other words , if a list should display
-	 *            the arrow sign on its right side
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#setSuppressDropDownArrow(boolean)
 	 */
 	public void setSuppressDropDownArrow(boolean suppress) {
 		_suppress_dropdown_arrow = suppress;
 	}
 
-	/**
-	 * Useful only list validation objects . This method always returns false if
-	 * the object isn't a list validation object
-	 * 
-	 * @return <code>true</code> if a list should display the values into a drop down list ,
-	 *         <code>false</code> otherwise .
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getSuppressDropDownArrow()
 	 */
 	public boolean getSuppressDropDownArrow() {
-		if (_constraint.isListValidationType()) {
+		if (_constraint.getValidationType()==ValidationType.LIST) {
 			return _suppress_dropdown_arrow;
 		}
 		return false;
 	}
 
-	/**
-	 * Sets the behaviour when a cell which belongs to this object is selected
-	 * 
-	 * @param show <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#setShowPromptBox(boolean)
 	 */
 	public void setShowPromptBox(boolean show) {
 		_showPromptBox = show;
 	}
 
-	/**
-	 * @return <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getShowPromptBox()
 	 */
 	public boolean getShowPromptBox() {
 		return _showPromptBox;
 	}
 
-	/**
-	 * Sets the behaviour when an invalid value is entered
-	 * 
-	 * @param show <code>true</code> if an error box should be displayed , <code>false</code> otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#setShowErrorBox(boolean)
 	 */
 	public void setShowErrorBox(boolean show) {
 		_showErrorBox = show;
 	}
 
-	/**
-	 * @return <code>true</code> if an error box should be displayed , <code>false</code> otherwise
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getShowErrorBox()
 	 */
 	public boolean getShowErrorBox() {
 		return _showErrorBox;
 	}
 
 
-	/**
-	 * Sets the title and text for the prompt box . Prompt box is displayed when
-	 * the user selects a cell which belongs to this validation object . In
-	 * order for a prompt box to be displayed you should also use method
-	 * setShowPromptBox( boolean show )
-	 * 
-	 * @param title The prompt box's title
-	 * @param text The prompt box's text
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#createPromptBox(java.lang.String, java.lang.String)
 	 */
 	public void createPromptBox(String title, String text) {
 		_prompt_title = title;
@@ -177,28 +155,22 @@ public final class HSSFDataValidation {
 		this.setShowPromptBox(true);
 	}
 
-	/**
-	 * @return Prompt box's title or <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getPromptBoxTitle()
 	 */
 	public String getPromptBoxTitle() {
 		return _prompt_title;
 	}
 
-	/**
-	 * @return Prompt box's text or <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getPromptBoxText()
 	 */
 	public String getPromptBoxText() {
 		return _prompt_text;
 	}
 
-	/**
-	 * Sets the title and text for the error box . Error box is displayed when
-	 * the user enters an invalid value int o a cell which belongs to this
-	 * validation object . In order for an error box to be displayed you should
-	 * also use method setShowErrorBox( boolean show )
-	 * 
-	 * @param title The error box's title
-	 * @param text The error box's text
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#createErrorBox(java.lang.String, java.lang.String)
 	 */
 	public void createErrorBox(String title, String text) {
 		_error_title = title;
@@ -206,15 +178,15 @@ public final class HSSFDataValidation {
 		this.setShowErrorBox(true);
 	}
 
-	/**
-	 * @return Error box's title or <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxTitle()
 	 */
 	public String getErrorBoxTitle() {
 		return _error_title;
 	}
 
-	/**
-	 * @return Error box's text or <code>null</code>
+	/* (non-Javadoc)
+	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxText()
 	 */
 	public String getErrorBoxText() {
 		return _error_text;
@@ -227,7 +199,7 @@ public final class HSSFDataValidation {
 		return new DVRecord(_constraint.getValidationType(),
 				_constraint.getOperator(),
 				_errorStyle, _emptyCellAllowed, getSuppressDropDownArrow(),
-				_constraint.isExplicitList(),
+				_constraint.getValidationType()==ValidationType.LIST && _constraint.getExplicitListValues()!=null,
 				_showPromptBox, _prompt_title, _prompt_text,
 				_showErrorBox, _error_title, _error_text,
 				fp.getFormula1(), fp.getFormula2(),
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java
new file mode 100644
index 0000000000..5418f5d3c6
--- /dev/null
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java
@@ -0,0 +1,118 @@
+/**
+ * 
+ */
+package org.apache.poi.hssf.usermodel;
+
+import org.apache.poi.ss.usermodel.DataValidation;
+import org.apache.poi.ss.usermodel.DataValidationConstraint;
+import org.apache.poi.ss.usermodel.DataValidationHelper;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+/**
+ * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
+ * 
+ */
+public class HSSFDataValidationHelper implements DataValidationHelper {
+	@SuppressWarnings("unused")
+	private HSSFSheet sheet;
+	
+	public HSSFDataValidationHelper(HSSFSheet sheet) {
+		super();
+		this.sheet = sheet;
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createDateConstraint
+	 * (int, java.lang.String, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2, String dateFormat) {
+		return DVConstraint.createDateConstraint(operatorType, formula1, formula2, dateFormat);
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createExplicitListConstraint
+	 * (java.lang.String[])
+	 */
+	public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) {
+		return DVConstraint.createExplicitListConstraint(listOfValues);
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createFormulaListConstraint
+	 * (java.lang.String)
+	 */
+	public DataValidationConstraint createFormulaListConstraint(String listFormula) {
+		return DVConstraint.createFormulaListConstraint(listFormula);
+	}
+
+	
+	
+	public DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2) {
+		return DVConstraint.createNumericConstraint(validationType, operatorType, formula1, formula2);
+	}
+
+	public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) {
+		return DVConstraint.createNumericConstraint(ValidationType.INTEGER, operatorType, formula1, formula2);
+	}
+	
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createNumericConstraint
+	 * (int, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2) {
+		return DVConstraint.createNumericConstraint(ValidationType.DECIMAL, operatorType, formula1, formula2);
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createTextLengthConstraint
+	 * (int, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2) {
+		return DVConstraint.createNumericConstraint(ValidationType.TEXT_LENGTH, operatorType, formula1, formula2);
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createTimeConstraint
+	 * (int, java.lang.String, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2) {
+		return DVConstraint.createTimeConstraint(operatorType, formula1, formula2);
+	}
+
+	
+	
+	public DataValidationConstraint createCustomConstraint(String formula) {
+		return DVConstraint.createCustomFormulaConstraint(formula);
+	}
+
+	/*
+	 * (non-Javadoc)
+	 * 
+	 * @see
+	 * org.apache.poi.ss.usermodel.DataValidationHelper#createValidation(org
+	 * .apache.poi.ss.usermodel.DataValidationConstraint,
+	 * org.apache.poi.ss.util.CellRangeAddressList)
+	 */
+	public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
+		return new HSSFDataValidation(cellRangeAddressList, constraint); 
+	}
+}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
index 75e48cfa0f..8ceb1df433 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
@@ -51,15 +51,17 @@ import org.apache.poi.hssf.record.formula.FormulaShifter;
 import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.hssf.util.PaneInformation;
 import org.apache.poi.hssf.util.Region;
+import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.formula.FormulaType;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellRange;
 import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DataValidation;
+import org.apache.poi.ss.usermodel.DataValidationHelper;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.SSCellRange;
-import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.util.POILogFactory;
 import org.apache.poi.util.POILogger;
 
@@ -373,13 +375,14 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
      * Creates a data validation object
      * @param dataValidation The Data validation object settings
      */
-    public void addValidationData(HSSFDataValidation dataValidation) {
+    public void addValidationData(DataValidation dataValidation) {
        if (dataValidation == null) {
            throw new IllegalArgumentException("objValidation must not be null");
        }
+       HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation;
        DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
 
-       DVRecord dvRecord = dataValidation.createDVRecord(this);
+       DVRecord dvRecord = hssfDataValidation.createDVRecord(this);
        dvt.addDataValidation(dvRecord);
     }
 
@@ -1997,4 +2000,10 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
         }
         return result;
     }
+
+	public DataValidationHelper getDataValidationHelper() {
+		return new HSSFDataValidationHelper(this);
+	}
+    
+    
 }
diff --git a/src/java/org/apache/poi/ss/usermodel/DataValidation.java b/src/java/org/apache/poi/ss/usermodel/DataValidation.java
new file mode 100644
index 0000000000..c80ed6a744
--- /dev/null
+++ b/src/java/org/apache/poi/ss/usermodel/DataValidation.java
@@ -0,0 +1,152 @@
+/* ====================================================================
+   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.ss.usermodel;
+
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+
+public interface DataValidation {
+	/**
+	 * Error style constants for error box
+	 */
+	public static final class ErrorStyle {
+    	/** STOP style */
+    	public static final int STOP    = 0x00;
+    	/** WARNING style */
+    	public static final int WARNING = 0x01;
+    	/** INFO style */
+    	public static final int INFO    = 0x02;
+	}    
+
+	public abstract DataValidationConstraint getValidationConstraint();
+
+	/**
+	 * Sets the error style for error box
+	 * @see ErrorStyle
+	 */
+	public abstract void setErrorStyle(int error_style);
+
+	/**o
+	 * @return the error style of error box
+	 * @see ErrorStyle
+	 */
+	public abstract int getErrorStyle();
+
+	/**
+	 * Sets if this object allows empty as a valid value
+	 * 
+	 * @param allowed <code>true</code> if this object should treats empty as valid value , <code>false</code>
+	 *            otherwise
+	 */
+	public abstract void setEmptyCellAllowed(boolean allowed);
+
+	/**
+	 * Retrieve the settings for empty cells allowed
+	 * 
+	 * @return True if this object should treats empty as valid value , false
+	 *         otherwise
+	 */
+	public abstract boolean getEmptyCellAllowed();
+
+	/**
+	 * Useful for list validation objects .
+	 * 
+	 * @param suppress
+	 *            True if a list should display the values into a drop down list ,
+	 *            false otherwise . In other words , if a list should display
+	 *            the arrow sign on its right side
+	 */
+	public abstract void setSuppressDropDownArrow(boolean suppress);
+
+	/**
+	 * Useful only list validation objects . This method always returns false if
+	 * the object isn't a list validation object
+	 * 
+	 * @return <code>true</code> if a list should display the values into a drop down list ,
+	 *         <code>false</code> otherwise .
+	 */
+	public abstract boolean getSuppressDropDownArrow();
+
+	/**
+	 * Sets the behaviour when a cell which belongs to this object is selected
+	 * 
+	 * @param show <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
+	 */
+	public abstract void setShowPromptBox(boolean show);
+
+	/**
+	 * @return <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
+	 */
+	public abstract boolean getShowPromptBox();
+
+	/**
+	 * Sets the behaviour when an invalid value is entered
+	 * 
+	 * @param show <code>true</code> if an error box should be displayed , <code>false</code> otherwise
+	 */
+	public abstract void setShowErrorBox(boolean show);
+
+	/**
+	 * @return <code>true</code> if an error box should be displayed , <code>false</code> otherwise
+	 */
+	public abstract boolean getShowErrorBox();
+
+	/**
+	 * Sets the title and text for the prompt box . Prompt box is displayed when
+	 * the user selects a cell which belongs to this validation object . In
+	 * order for a prompt box to be displayed you should also use method
+	 * setShowPromptBox( boolean show )
+	 * 
+	 * @param title The prompt box's title
+	 * @param text The prompt box's text
+	 */
+	public abstract void createPromptBox(String title, String text);
+
+	/**
+	 * @return Prompt box's title or <code>null</code>
+	 */
+	public abstract String getPromptBoxTitle();
+
+	/**
+	 * @return Prompt box's text or <code>null</code>
+	 */
+	public abstract String getPromptBoxText();
+
+	/**
+	 * Sets the title and text for the error box . Error box is displayed when
+	 * the user enters an invalid value int o a cell which belongs to this
+	 * validation object . In order for an error box to be displayed you should
+	 * also use method setShowErrorBox( boolean show )
+	 * 
+	 * @param title The error box's title
+	 * @param text The error box's text
+	 */
+	public abstract void createErrorBox(String title, String text);
+
+	/**
+	 * @return Error box's title or <code>null</code>
+	 */
+	public abstract String getErrorBoxTitle();
+
+	/**
+	 * @return Error box's text or <code>null</code>
+	 */
+	public abstract String getErrorBoxText();
+
+	public abstract CellRangeAddressList getRegions();
+
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java b/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java
new file mode 100644
index 0000000000..e3d2c376db
--- /dev/null
+++ b/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java
@@ -0,0 +1,118 @@
+/* ====================================================================
+   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.ss.usermodel;
+
+
+public interface DataValidationConstraint {
+
+	/**
+	 * @return data validation type of this constraint
+	 * @see ValidationType
+	 */
+	public abstract int getValidationType();
+
+	/**
+	 * @return the operator used for this constraint
+	 * @see OperatorType
+	 */
+	public abstract int getOperator();
+
+	/**
+	 * Sets the comparison operator for this constraint
+	 * @see OperatorType
+	 */
+	public abstract void setOperator(int operator);
+
+	public abstract String[] getExplicitListValues();
+
+	public abstract void setExplicitListValues(String[] explicitListValues);
+
+	/**
+	 * @return the formula for expression 1. May be <code>null</code>
+	 */
+	public abstract String getFormula1();
+
+	/**
+	 * Sets a formula for expression 1.
+	 */
+	public abstract void setFormula1(String formula1);
+
+	/**
+	 * @return the formula for expression 2. May be <code>null</code>
+	 */
+	public abstract String getFormula2();
+
+	/**
+	 * Sets a formula for expression 2.
+	 */
+	public abstract void setFormula2(String formula2);
+	
+	/**
+	 * ValidationType enum
+	 */
+	public static final class ValidationType {
+		private ValidationType() {
+			// no instances of this class
+		}
+		/** 'Any value' type - value not restricted */
+		public static final int ANY         = 0x00;
+		/** Integer ('Whole number') type */
+		public static final int INTEGER     = 0x01;
+		/** Decimal type */
+		public static final int DECIMAL     = 0x02;
+		/** List type ( combo box type ) */
+		public static final int LIST        = 0x03;
+		/** Date type */
+		public static final int DATE        = 0x04;
+		/** Time type */
+		public static final int TIME        = 0x05;
+		/** String length type */
+		public static final int TEXT_LENGTH = 0x06;
+		/** Formula ( 'Custom' ) type */
+		public static final int FORMULA     = 0x07;
+	}
+	/**
+	 * Condition operator enum
+	 */
+	public static final class OperatorType {
+		private OperatorType() {
+			// no instances of this class
+		}
+
+		public static final int BETWEEN = 0x00;
+		public static final int NOT_BETWEEN = 0x01;
+		public static final int EQUAL = 0x02;
+		public static final int NOT_EQUAL = 0x03;
+		public static final int GREATER_THAN = 0x04;
+		public static final int LESS_THAN = 0x05;
+		public static final int GREATER_OR_EQUAL = 0x06;
+		public static final int LESS_OR_EQUAL = 0x07;
+		/** default value to supply when the operator type is not used */
+		public static final int IGNORED = BETWEEN;
+		
+		/* package */ public static void validateSecondArg(int comparisonOperator, String paramValue) {
+			switch (comparisonOperator) {
+				case BETWEEN:
+				case NOT_BETWEEN:
+					if (paramValue == null) {
+						throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
+					}
+				// all other operators don't need second arg
+			}
+		}
+	}
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java b/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java
new file mode 100644
index 0000000000..2e749467e4
--- /dev/null
+++ b/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java
@@ -0,0 +1,46 @@
+/* ====================================================================
+   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.ss.usermodel;
+
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+/**
+ * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
+ * 
+ */
+public interface DataValidationHelper {
+	
+	DataValidationConstraint createFormulaListConstraint(String listFormula);
+
+	DataValidationConstraint createExplicitListConstraint(String[] listOfValues);
+
+	DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2);
+	
+	DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2);
+	
+	DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2);
+	
+	DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2);
+	
+	DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2,String dateFormat);
+	
+	DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2);
+	
+	DataValidationConstraint createCustomConstraint(String formula);
+	
+	DataValidation createValidation(DataValidationConstraint constraint,CellRangeAddressList cellRangeAddressList);
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java
index 0841559438..9bef5269df 100644
--- a/src/java/org/apache/poi/ss/usermodel/Sheet.java
+++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java
@@ -798,4 +798,12 @@ public interface Sheet extends Iterable<Row> {
      * @return the {@link CellRange} of cells affected by this change
      */
     CellRange<? extends Cell> removeArrayFormula(Cell cell);
+    
+    public DataValidationHelper getDataValidationHelper();
+
+	/**
+	 * Creates a data validation object
+	 * @param dataValidation The Data validation object settings
+	 */
+	public void addValidationData(DataValidation dataValidation);
 }