--- src/java/org/apache/poi/hssf/usermodel/DVConstraint.java (revision 942001) +++ src/java/org/apache/poi/hssf/usermodel/DVConstraint.java (working copy) @@ -26,69 +26,15 @@ 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 class DVConstraint implements DataValidationConstraint { + /* package */ public static final class FormulaPair { - 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 { - private final Ptg[] _formula1; private final Ptg[] _formula2; @@ -211,8 +157,8 @@ 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,66 +267,9 @@ return new DVConstraint(VT.FORMULA, OperatorType.IGNORED, formula, null, null, null, null); } - /** - * @return both parsed formulas (for expression 1 and 2). + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getValidationType() */ - /* 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 null - */ - 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 - */ public int getValidationType() { return _validationType; } @@ -398,24 +287,28 @@ 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 @@ _explicitListValues = explicitListValues; } - /** - * @return the formula for expression 1. May be null + /* (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 @@ _formula1 = formula1; } - /** - * @return the formula for expression 2. May be null + /* (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 @@ _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 null + */ + 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)); + } } --- src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java (revision 942001) +++ src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java (working copy) @@ -19,6 +19,9 @@ 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 @@ * * @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 @@ private boolean _suppress_dropdown_arrow = false; private boolean _showPromptBox = true; private boolean _showErrorBox = true; - private final CellRangeAddressList _regions; + public final CellRangeAddressList _regions; private DVConstraint _constraint; /** @@ -57,119 +48,106 @@ * 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 true if this object should treats empty as valid value , false - * 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 true if a list should display the values into a drop down list , - * false 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 true if an prompt box should be displayed , false otherwise + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidation#setShowPromptBox(boolean) */ public void setShowPromptBox(boolean show) { _showPromptBox = show; } - /** - * @return true if an prompt box should be displayed , false 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 true if an error box should be displayed , false otherwise + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidation#setShowErrorBox(boolean) */ public void setShowErrorBox(boolean show) { _showErrorBox = show; } - /** - * @return true if an error box should be displayed , false 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 @@ this.setShowPromptBox(true); } - /** - * @return Prompt box's title or null + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidation#getPromptBoxTitle() */ public String getPromptBoxTitle() { return _prompt_title; } - /** - * @return Prompt box's text or null + /* (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 @@ this.setShowErrorBox(true); } - /** - * @return Error box's title or null + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxTitle() */ public String getErrorBoxTitle() { return _error_title; } - /** - * @return Error box's text or null + /* (non-Javadoc) + * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxText() */ public String getErrorBoxText() { return _error_text; @@ -227,7 +199,7 @@ 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(), --- src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java (revision 0) +++ src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java (revision 0) @@ -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 Radhakrishnan J + * + */ +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); + } +} --- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 942001) +++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy) @@ -51,15 +51,17 @@ 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; @@ -88,7 +90,7 @@ /** * reference to the low level {@link InternalSheet} object */ - private final InternalSheet _sheet; + protected final InternalSheet _sheet; /** stores rows by zero-based row number */ private final TreeMap _rows; protected final InternalWorkbook _book; @@ -373,13 +375,14 @@ * 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 @@ } return result; } + + public DataValidationHelper getDataValidationHelper() { + return new HSSFDataValidationHelper(this); + } + + } --- src/java/org/apache/poi/ss/usermodel/DataValidation.java (revision 0) +++ src/java/org/apache/poi/ss/usermodel/DataValidation.java (revision 0) @@ -0,0 +1,136 @@ +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 true if this object should treats empty as valid value , false + * 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 true if a list should display the values into a drop down list , + * false otherwise . + */ + public abstract boolean getSuppressDropDownArrow(); + + /** + * Sets the behaviour when a cell which belongs to this object is selected + * + * @param show true if an prompt box should be displayed , false otherwise + */ + public abstract void setShowPromptBox(boolean show); + + /** + * @return true if an prompt box should be displayed , false otherwise + */ + public abstract boolean getShowPromptBox(); + + /** + * Sets the behaviour when an invalid value is entered + * + * @param show true if an error box should be displayed , false otherwise + */ + public abstract void setShowErrorBox(boolean show); + + /** + * @return true if an error box should be displayed , false 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 null + */ + public abstract String getPromptBoxTitle(); + + /** + * @return Prompt box's text or null + */ + 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 null + */ + public abstract String getErrorBoxTitle(); + + /** + * @return Error box's text or null + */ + public abstract String getErrorBoxText(); + + public abstract CellRangeAddressList getRegions(); + +} --- src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java (revision 0) +++ src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java (revision 0) @@ -0,0 +1,102 @@ +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 null + */ + public abstract String getFormula1(); + + /** + * Sets a formula for expression 1. + */ + public abstract void setFormula1(String formula1); + + /** + * @return the formula for expression 2. May be null + */ + 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 + } + } + } +} --- src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java (revision 0) +++ src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java (revision 0) @@ -0,0 +1,33 @@ +/** + * + */ +package org.apache.poi.ss.usermodel; + +import org.apache.poi.ss.util.CellRangeAddressList; + +/** + * @author Radhakrishnan J + * + */ +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); +} --- src/java/org/apache/poi/ss/usermodel/Sheet.java (revision 942001) +++ src/java/org/apache/poi/ss/usermodel/Sheet.java (working copy) @@ -19,6 +19,9 @@ import java.util.Iterator; +import org.apache.poi.hssf.record.DVRecord; +import org.apache.poi.hssf.record.aggregates.DataValidityTable; +import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.util.CellRangeAddress; @@ -798,4 +801,12 @@ * @return the {@link CellRange} of cells affected by this change */ CellRange removeArrayFormula(Cell cell); + + public DataValidationHelper getDataValidationHelper(); + + /** + * Creates a data validation object + * @param dataValidation The Data validation object settings + */ + public void addValidationData(DataValidation dataValidation); } --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java (revision 0) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java (revision 0) @@ -0,0 +1,243 @@ +/** + * + */ +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 Radhakrishnan J + * + */ +public class XSSFDataValidation implements DataValidation { + private CTDataValidation ctDdataValidation; + private XSSFDataValidationConstraint validationConstraint; + private CellRangeAddressList regions; + public static Map operatorTypeMappings = new HashMap(); + public static Map operatorTypeReverseMappings = new HashMap(); + public static Map validationTypeMappings = new HashMap(); + public static Map validationTypeReverseMappings = new HashMap(); + public static Map errorStyleMappings = new HashMap(); + 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 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 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; + } +} --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java (revision 0) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java (revision 0) @@ -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 Radhakrishnan J + * + */ +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(); + } +} --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java (revision 0) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java (revision 0) @@ -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 Radhakrishnan J + * + */ +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 sqref = new ArrayList(); + 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); + } +} --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (revision 942001) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (working copy) @@ -42,11 +42,14 @@ 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.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 @@ private CommentsTable sheetComments; private Map sharedFormulas; private List arrayFormulas; + private XSSFDataValidationHelper dataValidationHelper; /** * Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch. @@ -90,6 +128,7 @@ */ protected XSSFSheet() { super(); + dataValidationHelper = new XSSFDataValidationHelper(this); onDocumentCreate(); } @@ -102,6 +141,7 @@ */ protected XSSFSheet(PackagePart part, PackageRelationship rel) { super(part, rel); + dataValidationHelper = new XSSFDataValidationHelper(this); } /** @@ -2794,4 +2834,48 @@ 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 getDataValidations() { + List xssfValidations = new ArrayList(); + CTDataValidations dataValidations = this.worksheet.getDataValidations(); + if( dataValidations!=null && dataValidations.getCount() > 0 ) { + List dataValidationList = dataValidations.getDataValidationList(); + for (CTDataValidation ctDataValidation : dataValidationList) { + CellRangeAddressList addressList = new CellRangeAddressList(); + + @SuppressWarnings("unchecked") + List 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.getDataValidationList().size(); + dataValidations.getDataValidationList().add(xssfDataValidation.getCtDdataValidation()); + dataValidations.setCount(currentCount + 1); + + } } --- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java (revision 0) +++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java (revision 0) @@ -0,0 +1,242 @@ +package org.apache.poi.xssf.usermodel; + +import java.io.File; +import java.io.FileInputStream; +import java.io.FileOutputStream; +import java.math.BigDecimal; +import java.util.List; + +import junit.framework.TestCase; + +import org.apache.poi.ss.usermodel.Cell; +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.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +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; + +public class TestXSSFDataValidation extends TestCase { + public void testAddValidations() throws Exception { + File inputFile = new File("test-data/spreadsheet/DataValidations-49244.xlsx"); + File outputFile = new File("test-data/spreadsheet/DataValidations-49244-TestXSSFDataValidation_testAddValidations.xlsx"); + FileInputStream fis = new FileInputStream(inputFile); + Workbook workbook = new XSSFWorkbook(fis); + Sheet sheet = workbook.getSheetAt(0); + List 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()); +// for (XSSFDataValidation dataValidation : dataValidations) { +// System.out.println(dataValidation.prettyPrint()); +// } + + 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(); + System.out.println(listFormula); + 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()); + } + } + FileOutputStream fileOutputStream = new FileOutputStream(outputFile); + workbook.write(fileOutputStream); + fileOutputStream.flush(); + fileOutputStream.close(); + + fis = new FileInputStream(outputFile); + workbook = new XSSFWorkbook(fis); + 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); + } +} --- src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java (revision 942001) +++ src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java (working copy) @@ -35,6 +35,10 @@ import org.apache.poi.hssf.record.RecordFormatException; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; +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.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; @@ -48,9 +52,9 @@ /** Convenient access to ERROR_STYLE constants */ /*package*/ static final HSSFDataValidation.ErrorStyle ES = null; /** Convenient access to OPERATOR constants */ - /*package*/ static final DVConstraint.ValidationType VT = null; + /*package*/ static final DataValidationConstraint.ValidationType VT = null; /** Convenient access to OPERATOR constants */ - /*package*/ static final DVConstraint.OperatorType OP = null; + /*package*/ static final DataValidationConstraint.OperatorType OP = null; private static void log(String msg) { if (false) { // successful tests should be silent @@ -92,9 +96,10 @@ String[] explicitListValues) { int rowNum = _currentRowIndex++; - DVConstraint dc = createConstraint(operatorType, firstFormula, secondFormula, explicitListValues); + DataValidationHelper dataValidationHelper = _sheet.getDataValidationHelper(); + DataValidationConstraint dc = createConstraint(dataValidationHelper,operatorType, firstFormula, secondFormula, explicitListValues); - HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc); + DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(rowNum, rowNum, 0, 0)); dv.setEmptyCellAllowed(allowEmpty); dv.setErrorStyle(errorStyle); @@ -116,24 +121,34 @@ } writeOtherSettings(_sheet, _style_1, promptDescr); } - private DVConstraint createConstraint(int operatorType, String firstFormula, + private DataValidationConstraint createConstraint(DataValidationHelper dataValidationHelper,int operatorType, String firstFormula, String secondFormula, String[] explicitListValues) { if (_validationType == VT.LIST) { if (explicitListValues != null) { - return DVConstraint.createExplicitListConstraint(explicitListValues); + return dataValidationHelper.createExplicitListConstraint(explicitListValues); } - return DVConstraint.createFormulaListConstraint(firstFormula); + return dataValidationHelper.createFormulaListConstraint(firstFormula); } if (_validationType == VT.TIME) { - return DVConstraint.createTimeConstraint(operatorType, firstFormula, secondFormula); + return dataValidationHelper.createTimeConstraint(operatorType, firstFormula, secondFormula); } if (_validationType == VT.DATE) { - return DVConstraint.createDateConstraint(operatorType, firstFormula, secondFormula, null); + return dataValidationHelper.createDateConstraint(operatorType, firstFormula, secondFormula, null); } if (_validationType == VT.FORMULA) { - return DVConstraint.createCustomFormulaConstraint(firstFormula); + return dataValidationHelper.createCustomConstraint(firstFormula); } - return DVConstraint.createNumericConstraint(_validationType, operatorType, firstFormula, secondFormula); + + if( _validationType == VT.INTEGER) { + return dataValidationHelper.createIntegerConstraint(operatorType, firstFormula, secondFormula); + } + if( _validationType == VT.DECIMAL) { + return dataValidationHelper.createDecimalConstraint(operatorType, firstFormula, secondFormula); + } + if( _validationType == VT.TEXT_LENGTH) { + return dataValidationHelper.createTextLengthConstraint(operatorType, firstFormula, secondFormula); + } + return null; } /** * writes plain text values into cells in a tabular format to form comments readable from within @@ -572,9 +587,10 @@ // and then deleting the row that contains the cell. HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls"); int dvRow = 0; - HSSFSheet sheet = wb.getSheetAt(0); - DVConstraint dc = DVConstraint.createNumericConstraint(VT.INTEGER, OP.EQUAL, "42", null); - HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc); + Sheet sheet = wb.getSheetAt(0); + DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); + DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OP.EQUAL, "42", null); + DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(dvRow, dvRow, 0, 0)); dv.setEmptyCellAllowed(false); dv.setErrorStyle(ES.STOP); --- src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (revision 942001) +++ src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (working copy) @@ -42,6 +42,9 @@ import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock; import org.apache.poi.hssf.usermodel.RecordInspector.RecordCollector; import org.apache.poi.ss.usermodel.BaseTestSheet; +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.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.TempFile; @@ -382,10 +385,10 @@ HSSFSheet sheet = workbook.createSheet("Sheet1"); sheet.protectSheet("secret"); - DVConstraint dvc = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, - DVConstraint.OperatorType.BETWEEN, "10", "100"); + DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); + DataValidationConstraint dvc = dataValidationHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, "10", "100"); CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1); - HSSFDataValidation dv = new HSSFDataValidation(numericCellAddressList, dvc); + DataValidation dv = dataValidationHelper.createValidation(dvc,numericCellAddressList); try { sheet.addValidationData(dv); } catch (IllegalStateException e) {