You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2010/05/16 17:49:22 UTC
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...
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