poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r944869 [1/2] - in /poi/trunk: src/documentation/content/xdocs/ src/java/org/apache/poi/hssf/usermodel/ src/java/org/apache/poi/ss/usermodel/ src/ooxml/java/org/apache/poi/xssf/usermodel/ src/ooxml/testcases/org/apache/poi/xssf/usermodel/ s...
Date Sun, 16 May 2010 15:49:22 GMT
Author: yegor
Date: Sun May 16 15:49:21 2010
New Revision: 944869

URL: http://svn.apache.org/viewvc?rev=944869&view=rev
Log:
support for data validation for OOXML, see Bugzilla 49244

Added:
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidation.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java
    poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java
    poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataValidation.java
    poi/trunk/test-data/spreadsheet/DataValidations-49244.xlsx   (with props)
Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sun May 16 15:49:21 2010
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.7-SNAPSHOT" date="2010-??-??">
+           <action dev="POI-DEVELOPERS" type="add">49244 -Support for data validation for OOXML format</action>
            <action dev="POI-DEVELOPERS" type="add">49066 - Worksheet/cell formatting, with view and HTML converter</action>
            <action dev="POI-DEVELOPERS" type="fix">49020 - Workaround Excel outputting invalid XML in button definitions by not closing BR tags</action>
            <action dev="POI-DEVELOPERS" type="fix">49050 - Improve performance of AbstractEscherHolderRecord when there are lots of Continue Records</action>

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java Sun May 16 15:49:21 2010
@@ -26,68 +26,14 @@ import org.apache.poi.hssf.record.formul
 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));
+	}	
 }

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java Sun May 16 15:49:21 2010
@@ -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.CellRangeA
  * 
  * @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(),

Added: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java Sun May 16 15:49:21 2010
@@ -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); 
+	}
+}

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Sun May 16 15:49:21 2010
@@ -51,15 +51,17 @@ import org.apache.poi.hssf.record.formul
 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 
      * 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 
         }
         return result;
     }
+
+	public DataValidationHelper getDataValidationHelper() {
+		return new HSSFDataValidationHelper(this);
+	}
+    
+    
 }

Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidation.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidation.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidation.java Sun May 16 15:49:21 2010
@@ -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();
+
+}

Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java Sun May 16 15:49:21 2010
@@ -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
+			}
+		}
+	}
+}

Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java Sun May 16 15:49:21 2010
@@ -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);
+}

Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java Sun May 16 15:49:21 2010
@@ -798,4 +798,12 @@ public interface Sheet extends Iterable<
      * @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);
 }

Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java (added)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java Sun May 16 15:49:21 2010
@@ -0,0 +1,244 @@
+/**
+ * 
+ */
+package org.apache.poi.xssf.usermodel;
+
+import java.util.HashMap;
+import java.util.Map;
+
+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.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationErrorStyle;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator.Enum;
+
+/**
+ * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
+ *
+ */
+public class XSSFDataValidation implements DataValidation {
+	private CTDataValidation ctDdataValidation;
+	private XSSFDataValidationConstraint validationConstraint;
+	private CellRangeAddressList regions;
+
+    static Map<Integer,STDataValidationOperator.Enum> operatorTypeMappings = new HashMap<Integer,STDataValidationOperator.Enum>();
+	static Map<STDataValidationOperator.Enum,Integer> operatorTypeReverseMappings = new HashMap<STDataValidationOperator.Enum,Integer>();
+	static Map<Integer,STDataValidationType.Enum> validationTypeMappings = new HashMap<Integer,STDataValidationType.Enum>();
+	static Map<STDataValidationType.Enum,Integer> validationTypeReverseMappings = new HashMap<STDataValidationType.Enum,Integer>();
+    static Map<Integer,STDataValidationErrorStyle.Enum> errorStyleMappings = new HashMap<Integer,STDataValidationErrorStyle.Enum>();
+    static {
+		errorStyleMappings.put(DataValidation.ErrorStyle.INFO, STDataValidationErrorStyle.INFORMATION);
+		errorStyleMappings.put(DataValidation.ErrorStyle.STOP, STDataValidationErrorStyle.STOP);
+		errorStyleMappings.put(DataValidation.ErrorStyle.WARNING, STDataValidationErrorStyle.WARNING);
+    }
+	
+    
+	static {
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.BETWEEN,STDataValidationOperator.BETWEEN);
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_BETWEEN,STDataValidationOperator.NOT_BETWEEN);
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.EQUAL,STDataValidationOperator.EQUAL);
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_EQUAL,STDataValidationOperator.NOT_EQUAL);
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_THAN,STDataValidationOperator.GREATER_THAN);    	
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,STDataValidationOperator.GREATER_THAN_OR_EQUAL);
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_THAN,STDataValidationOperator.LESS_THAN);    	
+		operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,STDataValidationOperator.LESS_THAN_OR_EQUAL);
+		
+		for( Map.Entry<Integer,STDataValidationOperator.Enum> entry : operatorTypeMappings.entrySet() ) {
+			operatorTypeReverseMappings.put(entry.getValue(),entry.getKey());
+		}
+	}
+
+	static {
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.FORMULA,STDataValidationType.CUSTOM);
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.DATE,STDataValidationType.DATE);
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.DECIMAL,STDataValidationType.DECIMAL);    	
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.LIST,STDataValidationType.LIST); 
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.ANY,STDataValidationType.NONE);
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.TEXT_LENGTH,STDataValidationType.TEXT_LENGTH);
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.TIME,STDataValidationType.TIME);  
+		validationTypeMappings.put(DataValidationConstraint.ValidationType.INTEGER,STDataValidationType.WHOLE);
+		
+		for( Map.Entry<Integer,STDataValidationType.Enum> entry : validationTypeMappings.entrySet() ) {
+			validationTypeReverseMappings.put(entry.getValue(),entry.getKey());
+		}
+	}
+
+	
+	XSSFDataValidation(CellRangeAddressList regions,CTDataValidation ctDataValidation) {
+		super();
+		this.validationConstraint = getConstraint(ctDataValidation);
+		this.ctDdataValidation = ctDataValidation;
+		this.regions = regions;
+		this.ctDdataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
+		this.ctDdataValidation.setAllowBlank(true);
+	}	
+
+	public XSSFDataValidation(XSSFDataValidationConstraint constraint,CellRangeAddressList regions,CTDataValidation ctDataValidation) {
+		super();
+		this.validationConstraint = constraint;
+		this.ctDdataValidation = ctDataValidation;
+		this.regions = regions;
+		this.ctDdataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
+		this.ctDdataValidation.setAllowBlank(true);
+	}
+ 
+	CTDataValidation getCtDdataValidation() {
+		return ctDdataValidation;
+	}
+
+
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#createErrorBox(java.lang.String, java.lang.String)
+	 */
+	public void createErrorBox(String title, String text) {
+		ctDdataValidation.setErrorTitle(title);
+		ctDdataValidation.setError(text);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#createPromptBox(java.lang.String, java.lang.String)
+	 */
+	public void createPromptBox(String title, String text) {
+		ctDdataValidation.setPromptTitle(title);
+		ctDdataValidation.setPrompt(text);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getEmptyCellAllowed()
+	 */
+	public boolean getEmptyCellAllowed() {
+		return ctDdataValidation.getAllowBlank();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorBoxText()
+	 */
+	public String getErrorBoxText() {
+		return ctDdataValidation.getError();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorBoxTitle()
+	 */
+	public String getErrorBoxTitle() {
+		return ctDdataValidation.getErrorTitle();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorStyle()
+	 */
+	public int getErrorStyle() {
+		return ctDdataValidation.getErrorStyle().intValue();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getPromptBoxText()
+	 */
+	public String getPromptBoxText() {
+		return ctDdataValidation.getPrompt();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getPromptBoxTitle()
+	 */
+	public String getPromptBoxTitle() {
+		return ctDdataValidation.getPromptTitle();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getShowErrorBox()
+	 */
+	public boolean getShowErrorBox() {
+		return ctDdataValidation.getShowErrorMessage();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getShowPromptBox()
+	 */
+	public boolean getShowPromptBox() {
+		return ctDdataValidation.getShowInputMessage();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getSuppressDropDownArrow()
+	 */
+	public boolean getSuppressDropDownArrow() {
+		return !ctDdataValidation.getShowDropDown();
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#getValidationConstraint()
+	 */
+	public DataValidationConstraint getValidationConstraint() {
+		return validationConstraint;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#setEmptyCellAllowed(boolean)
+	 */
+	public void setEmptyCellAllowed(boolean allowed) {
+		ctDdataValidation.setAllowBlank(allowed);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#setErrorStyle(int)
+	 */
+	public void setErrorStyle(int errorStyle) {
+		ctDdataValidation.setErrorStyle(errorStyleMappings.get(errorStyle));
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#setShowErrorBox(boolean)
+	 */
+	public void setShowErrorBox(boolean show) {
+		ctDdataValidation.setShowErrorMessage(show);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#setShowPromptBox(boolean)
+	 */
+	public void setShowPromptBox(boolean show) {
+		ctDdataValidation.setShowInputMessage(show);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidation#setSuppressDropDownArrow(boolean)
+	 */
+	public void setSuppressDropDownArrow(boolean suppress) {
+		if (validationConstraint.getValidationType()==ValidationType.LIST) {
+			ctDdataValidation.setShowDropDown(!suppress);
+		}
+	}
+
+	public CellRangeAddressList getRegions() {
+		return regions;
+	}
+	
+	public String prettyPrint() {
+		StringBuilder builder = new StringBuilder();
+		for(CellRangeAddress address : regions.getCellRangeAddresses()) {
+			builder.append(address.formatAsString());
+		}
+		builder.append(" => ");
+		builder.append(this.validationConstraint.prettyPrint());	
+		return builder.toString();
+	}
+	
+    private XSSFDataValidationConstraint getConstraint(CTDataValidation ctDataValidation) {
+    	XSSFDataValidationConstraint constraint = null;
+    	String formula1 = ctDataValidation.getFormula1();
+    	String formula2 = ctDataValidation.getFormula2();
+    	Enum operator = ctDataValidation.getOperator();
+    	org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType.Enum type = ctDataValidation.getType();
+		Integer validationType = XSSFDataValidation.validationTypeReverseMappings.get(type);
+		Integer operatorType = XSSFDataValidation.operatorTypeReverseMappings.get(operator);
+		constraint = new XSSFDataValidationConstraint(validationType,operatorType, formula1,formula2);
+    	return constraint;
+    }
+}

Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java (added)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java Sun May 16 15:49:21 2010
@@ -0,0 +1,194 @@
+/**
+ * 
+ */
+package org.apache.poi.xssf.usermodel;
+
+import java.util.Arrays;
+
+import org.apache.poi.ss.usermodel.DataValidationConstraint;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator.Enum;
+
+/**
+ * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
+ *
+ */
+public class XSSFDataValidationConstraint implements DataValidationConstraint {
+	private String formula1;
+	private String formula2;
+	private int validationType = -1;
+	private int operator = -1;
+	private String[] explicitListOfValues;
+
+	public XSSFDataValidationConstraint(String[] explicitListOfValues) {
+		if( explicitListOfValues==null || explicitListOfValues.length==0) {
+			throw new IllegalArgumentException("List validation with explicit values must specify at least one value");
+		}
+		this.validationType = ValidationType.LIST;
+		setExplicitListValues(explicitListOfValues);
+		
+		validate();
+	}
+	
+	public XSSFDataValidationConstraint(int validationType,String formula1) {
+		super();
+		setFormula1(formula1);
+		this.validationType = validationType;
+		validate();
+	}
+
+
+
+	public XSSFDataValidationConstraint(int validationType, int operator,String formula1) {
+		super();
+		setFormula1(formula1);
+		this.validationType = validationType;
+		this.operator = operator;
+		validate();
+	}
+
+	public XSSFDataValidationConstraint(int validationType, int operator,String formula1, String formula2) {
+		super();
+		setFormula1(formula1);
+		setFormula2(formula2);
+		this.validationType = validationType;
+		this.operator = operator;
+		
+		validate();
+		
+		//FIXME: Need to confirm if this is not a formula.
+		if( ValidationType.LIST==validationType) {
+			explicitListOfValues = formula1.split(",");
+		}
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getExplicitListValues()
+	 */
+	public String[] getExplicitListValues() {
+		return explicitListOfValues;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getFormula1()
+	 */
+	public String getFormula1() {
+		return formula1;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getFormula2()
+	 */
+	public String getFormula2() {
+		return formula2;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getOperator()
+	 */
+	public int getOperator() {
+		return operator;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getValidationType()
+	 */
+	public int getValidationType() {
+		return validationType;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setExplicitListValues(java.lang.String[])
+	 */
+	public void setExplicitListValues(String[] explicitListValues) {
+		this.explicitListOfValues = explicitListValues;
+		if( explicitListOfValues!=null && explicitListOfValues.length > 0 ) {
+			StringBuilder builder = new StringBuilder("\"");
+			for (int i = 0; i < explicitListValues.length; i++) {
+				String string = explicitListValues[i];
+				if( builder.length() > 1) {
+					builder.append(",");
+				}
+				builder.append(string);
+			}
+			builder.append("\"");
+			setFormula1(builder.toString());			
+		}
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setFormula1(java.lang.String)
+	 */
+	public void setFormula1(String formula1) {
+		this.formula1 = removeLeadingEquals(formula1);
+	}
+
+	protected String removeLeadingEquals(String formula1) {
+		return isFormulaEmpty(formula1) ? formula1 : formula1.charAt(0)=='=' ? formula1.substring(1) : formula1;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setFormula2(java.lang.String)
+	 */
+	public void setFormula2(String formula2) {
+		this.formula2 = removeLeadingEquals(formula2);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setOperator(int)
+	 */
+	public void setOperator(int operator) {
+		this.operator = operator;
+	}
+
+	public void validate() {
+		if (validationType==ValidationType.ANY) {
+			return;
+		}
+		
+		if (validationType==ValidationType.LIST ) {
+			if (isFormulaEmpty(formula1)) {
+				throw new IllegalArgumentException("A valid formula or a list of values must be specified for list validation.");
+			}
+		} else  {
+			if( isFormulaEmpty(formula1) ) {
+				throw new IllegalArgumentException("Formula is not specified. Formula is required for all validation types except explicit list validation.");
+			}
+			
+			if( validationType!= ValidationType.FORMULA ) {
+				if (operator==-1) {
+					throw new IllegalArgumentException("This validation type requires an operator to be specified.");
+				} else if (( operator==OperatorType.BETWEEN || operator==OperatorType.NOT_BETWEEN) && isFormulaEmpty(formula2)) {
+					throw new IllegalArgumentException("Between and not between comparisons require two formulae to be specified.");
+				}
+			}
+		}
+	}
+
+	protected boolean isFormulaEmpty(String formula1) {
+		return formula1 == null || formula1.trim().length()==0;
+	}
+	
+	public String prettyPrint() {
+		StringBuilder builder = new StringBuilder();
+		STDataValidationType.Enum vt = XSSFDataValidation.validationTypeMappings.get(validationType);
+		Enum ot = XSSFDataValidation.operatorTypeMappings.get(operator);
+		builder.append(vt);
+		builder.append(' ');
+		if (validationType!=ValidationType.ANY) {
+			if (validationType != ValidationType.LIST && validationType != ValidationType.ANY && validationType != ValidationType.FORMULA) {
+				builder.append(",").append(ot).append(", ");
+			}
+			final String QUOTE = "";
+			if (validationType == ValidationType.LIST && explicitListOfValues != null) {
+				builder.append(QUOTE).append(Arrays.asList(explicitListOfValues)).append(QUOTE).append(' ');
+			} else {
+				builder.append(QUOTE).append(formula1).append(QUOTE).append(' ');
+			}
+			if (formula2 != null) {
+				builder.append(QUOTE).append(formula2).append(QUOTE).append(' ');
+			}
+		}
+		return builder.toString();
+	}
+}

Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java (added)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java Sun May 16 15:49:21 2010
@@ -0,0 +1,155 @@
+/**
+ * 
+ */
+package org.apache.poi.xssf.usermodel;
+
+import java.util.ArrayList;
+import java.util.List;
+
+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.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
+
+/**
+ * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
+ *
+ */
+public class XSSFDataValidationHelper implements DataValidationHelper {
+	private XSSFSheet xssfSheet;
+	
+    
+    public XSSFDataValidationHelper(XSSFSheet xssfSheet) {
+		super();
+		this.xssfSheet = xssfSheet;
+	}
+
+	/* (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 new XSSFDataValidationConstraint(ValidationType.DATE, operatorType,formula1, formula2);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createDecimalConstraint(int, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2) {
+		return new XSSFDataValidationConstraint(ValidationType.DECIMAL, operatorType,formula1, formula2);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createExplicitListConstraint(java.lang.String[])
+	 */
+	public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) {
+		return new XSSFDataValidationConstraint(listOfValues);
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createFormulaListConstraint(java.lang.String)
+	 */
+	public DataValidationConstraint createFormulaListConstraint(String listFormula) {
+		return new XSSFDataValidationConstraint(ValidationType.LIST, listFormula);
+	}
+
+	
+	
+	public DataValidationConstraint createNumericConstraint(int validationType, int operatorType, String formula1, String formula2) {
+		if( validationType==ValidationType.INTEGER) {
+			return createIntegerConstraint(operatorType, formula1, formula2);
+		} else if ( validationType==ValidationType.DECIMAL) {
+			return createDecimalConstraint(operatorType, formula1, formula2);
+		} else if ( validationType==ValidationType.TEXT_LENGTH) {
+			return createTextLengthConstraint(operatorType, formula1, formula2);
+		}
+		return null;
+	}
+
+	/* (non-Javadoc)
+	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createIntegerConstraint(int, java.lang.String, java.lang.String)
+	 */
+	public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) {
+		return new XSSFDataValidationConstraint(ValidationType.INTEGER, 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 new XSSFDataValidationConstraint(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 new XSSFDataValidationConstraint(ValidationType.TIME, operatorType,formula1,formula2);
+	}
+
+	public DataValidationConstraint createCustomConstraint(String formula) {
+		return new XSSFDataValidationConstraint(ValidationType.FORMULA, 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) {
+		XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
+		CTDataValidation newDataValidation = CTDataValidation.Factory.newInstance();
+
+		int validationType = constraint.getValidationType();
+		switch(validationType) {
+			case DataValidationConstraint.ValidationType.LIST:
+		    	newDataValidation.setType(STDataValidationType.LIST);
+				newDataValidation.setFormula1(constraint.getFormula1());				
+		    	break;
+			case DataValidationConstraint.ValidationType.ANY:				
+				newDataValidation.setType(STDataValidationType.NONE);				
+				break;
+			case DataValidationConstraint.ValidationType.TEXT_LENGTH:
+				newDataValidation.setType(STDataValidationType.TEXT_LENGTH);
+				break;				
+			case DataValidationConstraint.ValidationType.DATE:
+				newDataValidation.setType(STDataValidationType.DATE);
+				break;				
+			case DataValidationConstraint.ValidationType.INTEGER:
+				newDataValidation.setType(STDataValidationType.WHOLE);
+				break;				
+			case DataValidationConstraint.ValidationType.DECIMAL:
+				newDataValidation.setType(STDataValidationType.DECIMAL);
+				break;				
+			case DataValidationConstraint.ValidationType.TIME:
+				newDataValidation.setType(STDataValidationType.TIME);
+				break;
+			case DataValidationConstraint.ValidationType.FORMULA:
+				newDataValidation.setType(STDataValidationType.CUSTOM);
+				break;
+			default:
+				newDataValidation.setType(STDataValidationType.NONE);				
+		}
+		
+		if (validationType!=ValidationType.ANY && validationType!=ValidationType.LIST) {
+			newDataValidation.setOperator(XSSFDataValidation.operatorTypeMappings.get(constraint.getOperator()));			
+			if (constraint.getFormula1() != null) {
+				newDataValidation.setFormula1(constraint.getFormula1());
+			}
+			if (constraint.getFormula2() != null) {
+				newDataValidation.setFormula2(constraint.getFormula2());
+			}
+		}
+		
+		CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
+		List<String> sqref = new ArrayList<String>();
+		for (int i = 0; i < cellRangeAddresses.length; i++) {
+			CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
+			sqref.add(cellRangeAddress.formatAsString());
+		}
+		newDataValidation.setSqref(sqref);
+		
+		return new XSSFDataValidation(dataValidationConstraint,cellRangeAddressList,newDataValidation);
+	}
+}

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=944869&r1=944868&r2=944869&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Sun May 16 15:49:21 2010
@@ -42,11 +42,14 @@ import org.apache.poi.ss.SpreadsheetVers
 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.Footer;
 import org.apache.poi.ss.usermodel.Header;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressList;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.SSCellRange;
 import org.apache.poi.util.Internal;
@@ -58,7 +61,41 @@ import org.apache.poi.xssf.usermodel.hel
 import org.apache.xmlbeans.XmlException;
 import org.apache.xmlbeans.XmlOptions;
 import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBreak;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidations;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDrawing;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHeaderFooter;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOutlinePr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageBreak;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageMargins;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetUpPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPane;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPrintOptions;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSelection;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetData;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPaneState;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument;
 
 /**
  * High level representation of a SpreadsheetML worksheet.
@@ -82,6 +119,7 @@ public class XSSFSheet extends POIXMLDoc
     private CommentsTable sheetComments;
     private Map<Integer, XSSFCell> sharedFormulas;
     private List<CellRangeAddress> arrayFormulas;
+    private XSSFDataValidationHelper dataValidationHelper;    
 
     /**
      * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
@@ -90,6 +128,7 @@ public class XSSFSheet extends POIXMLDoc
      */
     protected XSSFSheet() {
         super();
+        dataValidationHelper = new XSSFDataValidationHelper(this);
         onDocumentCreate();
     }
 
@@ -102,6 +141,7 @@ public class XSSFSheet extends POIXMLDoc
      */
     protected XSSFSheet(PackagePart part, PackageRelationship rel) {
         super(part, rel);
+        dataValidationHelper = new XSSFDataValidationHelper(this);
     }
 
     /**
@@ -2794,4 +2834,49 @@ public class XSSFSheet extends POIXMLDoc
         String ref = ((XSSFCell)cell).getCTCell().getR();
         throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
     }
+
+
+	public DataValidationHelper getDataValidationHelper() {
+		return dataValidationHelper;
+	}
+    
+    public List<XSSFDataValidation> getDataValidations() {
+    	List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
+    	CTDataValidations dataValidations = this.worksheet.getDataValidations();
+    	if( dataValidations!=null && dataValidations.getCount() > 0 ) {
+    		CTDataValidation[] dataValidationList = dataValidations.getDataValidationArray();
+    		for (CTDataValidation ctDataValidation : dataValidationList) {
+    			CellRangeAddressList addressList = new CellRangeAddressList();
+    			
+    			@SuppressWarnings("unchecked")
+    			List<String> sqref = ctDataValidation.getSqref();
+    			for (String stRef : sqref) {
+    				String[] regions = stRef.split(" ");
+    				for (int i = 0; i < regions.length; i++) {
+						String[] parts = regions[i].split(":");
+						CellReference begin = new CellReference(parts[0]);
+						CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
+						CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
+						addressList.addCellRangeAddress(cellRangeAddress);
+					}
+				}
+				XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
+				xssfValidations.add(xssfDataValidation);
+			}
+    	}
+    	return xssfValidations;
+    }
+
+	public void addValidationData(DataValidation dataValidation) {
+		XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation;		
+		CTDataValidations dataValidations = worksheet.getDataValidations();
+		if( dataValidations==null ) {
+			dataValidations = worksheet.addNewDataValidations();
+		}
+		int currentCount = dataValidations.getDataValidationArray().length;
+        CTDataValidation newval = dataValidations.addNewDataValidation();
+		newval.set(xssfDataValidation.getCtDdataValidation());
+		dataValidations.setCount(currentCount + 1);
+
+	}
 }

Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java?rev=944869&view=auto
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java (added)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java Sun May 16 15:49:21 2010
@@ -0,0 +1,242 @@
+/* ====================================================================
+   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.math.BigDecimal;
+import java.util.List;
+
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.xssf.XSSFITestDataProvider;
+import org.apache.poi.xssf.XSSFTestDataSamples;
+
+public class TestXSSFDataValidation extends BaseTestDataValidation {
+
+    public TestXSSFDataValidation(){
+        super(XSSFITestDataProvider.instance);
+    }
+
+	public void testAddValidations() throws Exception {
+		XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("DataValidations-49244.xlsx");
+		Sheet sheet = workbook.getSheetAt(0);
+		List<XSSFDataValidation> dataValidations = ((XSSFSheet)sheet).getDataValidations();
+		
+/**
+ * 		For each validation type, there are two cells with the same validation. This tests
+ * 		application of a single validation definition to multiple cells.
+ * 		
+ * 		For list ( 3 validations for explicit and 3 for formula )
+ * 			- one validation that allows blank. 
+ * 			- one that does not allow blank.
+ * 			- one that does not show the drop down arrow.
+ * 		= 2
+ * 
+ * 		For number validations ( integer/decimal and text length ) with 8 different types of operators.
+ *		= 50  
+ * 
+ * 		= 52 ( Total )
+ */
+		assertEquals(52,dataValidations.size());
+
+		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
+		int[] validationTypes = new int[]{ValidationType.INTEGER,ValidationType.DECIMAL,ValidationType.TEXT_LENGTH};
+		
+		int[] singleOperandOperatorTypes = new int[]{
+				OperatorType.LESS_THAN,OperatorType.LESS_OR_EQUAL,
+				OperatorType.GREATER_THAN,OperatorType.GREATER_OR_EQUAL,
+				OperatorType.EQUAL,OperatorType.NOT_EQUAL
+				} ;
+		int[] doubleOperandOperatorTypes = new int[]{
+				OperatorType.BETWEEN,OperatorType.NOT_BETWEEN
+		};
+		
+		BigDecimal value  = new BigDecimal("10"),value2 = new BigDecimal("20");
+		BigDecimal dvalue = new BigDecimal("10.001"),dvalue2 = new BigDecimal("19.999");
+		final int lastRow = sheet.getLastRowNum();
+		int offset = lastRow + 3;
+		
+		int lastKnownNumValidations = dataValidations.size();
+		
+		Row row = sheet.createRow(offset++);
+		Cell cell = row.createCell(0);
+		DataValidationConstraint explicitListValidation = dataValidationHelper.createExplicitListConstraint(new String[]{"MA","MI","CA"});
+		CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
+		cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
+		DataValidation dataValidation = dataValidationHelper.createValidation(explicitListValidation, cellRangeAddressList);
+		setOtherValidationParameters(dataValidation);
+		sheet.addValidationData(dataValidation);
+		lastKnownNumValidations++;
+		
+		row = sheet.createRow(offset++);
+		cell = row.createCell(0);
+
+		cellRangeAddressList = new CellRangeAddressList();
+		cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
+		
+		Cell firstCell =  row.createCell(1);firstCell.setCellValue("UT");
+		Cell secondCell = row.createCell(2);secondCell.setCellValue("MN");
+		Cell thirdCell  = row.createCell(3);thirdCell.setCellValue("IL");
+		
+		int rowNum = row.getRowNum() + 1;
+		String listFormula = new StringBuilder("$B$").append(rowNum).append(":").append("$D$").append(rowNum).toString();
+		DataValidationConstraint formulaListValidation = dataValidationHelper.createFormulaListConstraint(listFormula);
+		
+		dataValidation = dataValidationHelper.createValidation(formulaListValidation, cellRangeAddressList);
+		setOtherValidationParameters(dataValidation);
+		sheet.addValidationData(dataValidation);
+		lastKnownNumValidations++;
+		
+		offset++;
+		offset++;
+		
+		for (int i = 0; i < validationTypes.length; i++) {
+			int validationType = validationTypes[i];
+			offset = offset + 2;
+			final Row row0 = sheet.createRow(offset++);			
+			Cell cell_10 = row0.createCell(0);
+			cell_10.setCellValue(validationType==ValidationType.DECIMAL ? "Decimal " : validationType==ValidationType.INTEGER ? "Integer" : "Text Length");
+			offset++;
+			for (int j = 0; j < singleOperandOperatorTypes.length; j++) {
+				int operatorType = singleOperandOperatorTypes[j];
+				final Row row1 = sheet.createRow(offset++);
+				
+				//For Integer (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
+				final Row row2 = i==0 && j < 2 ? sheet.createRow(offset++) : null;
+				
+				cell_10 = row1.createCell(0);
+				cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());				
+				Cell cell_11 = row1.createCell(1);
+				Cell cell_21 = row1.createCell(2);
+				Cell cell_22 = i==0 && j < 2 ? row2.createCell(2) : null;
+				
+				Cell cell_13 = row1.createCell(3);
+				
+				
+				cell_13.setCellType(Cell.CELL_TYPE_NUMERIC);				
+				cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
+
+				
+				//First create value based validation;
+				DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, value.toString(), null);
+				cellRangeAddressList = new CellRangeAddressList();
+				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(),cell_11.getRowIndex(),cell_11.getColumnIndex(),cell_11.getColumnIndex()));
+				DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+				setOtherValidationParameters(validation);
+				sheet.addValidationData(validation);
+				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
+				
+				//Now create real formula based validation.
+				String formula1 = new CellReference(cell_13.getRowIndex(),cell_13.getColumnIndex()).formatAsString();
+				constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, formula1, null);
+				if (i==0 && j==0) {
+					cellRangeAddressList = new CellRangeAddressList();
+					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
+					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+					setOtherValidationParameters(validation);
+					sheet.addValidationData(validation);
+					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
+					
+					cellRangeAddressList = new CellRangeAddressList();
+					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
+					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+					setOtherValidationParameters( validation);
+					sheet.addValidationData(validation);
+					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
+				} else if(i==0 && j==1 ){
+					cellRangeAddressList = new CellRangeAddressList();					
+					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
+					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
+					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+					setOtherValidationParameters( validation);
+					sheet.addValidationData(validation);
+					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
+				} else {
+					cellRangeAddressList = new CellRangeAddressList();
+					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
+					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+					setOtherValidationParameters(validation);
+					sheet.addValidationData(validation);
+					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
+				}
+			}
+
+			for (int j = 0; j < doubleOperandOperatorTypes.length; j++) {
+				int operatorType = doubleOperandOperatorTypes[j];
+				final Row row1 = sheet.createRow(offset++);
+				
+				cell_10 = row1.createCell(0);
+				cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());				
+				
+				Cell cell_11 = row1.createCell(1);
+				Cell cell_21 = row1.createCell(2);
+				
+				Cell cell_13 = row1.createCell(3);
+				Cell cell_14 = row1.createCell(4);
+				
+				
+				String value1String = validationType==ValidationType.DECIMAL ? dvalue.toString() : value.toString();
+				cell_13.setCellType(Cell.CELL_TYPE_NUMERIC);				
+				cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
+
+				String value2String = validationType==ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
+				cell_14.setCellType(Cell.CELL_TYPE_NUMERIC);
+				cell_14.setCellValue(validationType==ValidationType.DECIMAL ? dvalue2.doubleValue() : value2.intValue());
+				
+				
+				//First create value based validation;
+				DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, value1String, value2String);
+				cellRangeAddressList = new CellRangeAddressList();
+				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(),cell_11.getRowIndex(),cell_11.getColumnIndex(),cell_11.getColumnIndex()));
+				DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+				setOtherValidationParameters(validation);
+				sheet.addValidationData(validation);
+				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
+				
+				
+				//Now create real formula based validation.
+				String formula1 = new CellReference(cell_13.getRowIndex(),cell_13.getColumnIndex()).formatAsString();
+				String formula2 = new CellReference(cell_14.getRowIndex(),cell_14.getColumnIndex()).formatAsString();
+				constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, formula1, formula2);
+				cellRangeAddressList = new CellRangeAddressList();
+				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(),cell_21.getRowIndex(),cell_21.getColumnIndex(),cell_21.getColumnIndex()));
+				validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
+				
+				setOtherValidationParameters(validation);
+				sheet.addValidationData(validation);	
+				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
+			}
+		}
+
+		workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook);
+		Sheet sheetAt = workbook.getSheetAt(0);
+		assertEquals(lastKnownNumValidations,((XSSFSheet)sheetAt).getDataValidations().size());
+	}
+
+	protected void setOtherValidationParameters(DataValidation validation) {
+		boolean yesNo = true;
+		validation.setEmptyCellAllowed(yesNo);
+		validation.setShowErrorBox(yesNo);
+		validation.setShowPromptBox(yesNo);
+		validation.createErrorBox("Error Message Title", "Error Message");
+		validation.createPromptBox("Prompt", "Enter some value");
+		validation.setSuppressDropDownArrow(yesNo);
+	}
+}



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message