static DVConstraint createDVConstraint(DVRecord dvRecord, FormulaRenderingWorkbook book) { switch (dvRecord.getDataType()) { case ValidationType.ANY: return new DVConstraint(ValidationType.ANY, dvRecord.getConditionOperator(), null, null, null, null, null); case ValidationType.INTEGER: case ValidationType.DECIMAL: case ValidationType.TIME: case ValidationType.TEXT_LENGTH: FormulaValuePair pair1 = toFormulaString(dvRecord.getFormula1(), book); FormulaValuePair pair2 = toFormulaString(dvRecord.getFormula2(), book); return new DVConstraint(dvRecord.getDataType(), dvRecord.getConditionOperator(), pair1.formula(), pair2.formula(), pair1.value(), pair2.value(), null); case ValidationType.LIST: if (dvRecord.getListExplicitFormula()) { String values = toFormulaString(dvRecord.getFormula1(), book).string(); if (values.startsWith("\"")) { values = values.substring(1); return createExplicitListConstraint(explicitListValues); } else { String listFormula = toFormulaString(dvRecord.getFormula1(), book).string(); return createFormulaListConstraint(listFormula); return createCustomFormulaConstraint(toFormulaString(dvRecord.getFormula1(), book).string()); default: throw new UnsupportedOperationException("validationType="+dvRecord.getDataType());
public DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2, String dateFormat) { return DVConstraint.createDateConstraint(operatorType, formula1, formula2, dateFormat); }
public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) { return DVConstraint.createNumericConstraint(ValidationType.INTEGER, operatorType, formula1, formula2); }
/** * @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); }
/** * Creates a time based data validation constraint. The text values entered for expr1 and expr2 * can be either standard Excel formulas or formatted time values. If the expression starts * with '=' it is parsed as a formula, otherwise it is parsed as a formatted time. To parse * formatted times, two formats are supported: "HH:MM" or "HH:MM:SS". This is contrary to * Excel which uses the default time format from the OS. * * @param comparisonOperator constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum * @param expr1 date formula (when first char is '=') or formatted time value * @param expr2 date formula (when first char is '=') or formatted time value */ public static DVConstraint createTimeConstraint(int comparisonOperator, String expr1, String expr2) { if (expr1 == null) { throw new IllegalArgumentException("expr1 must be supplied"); } OperatorType.validateSecondArg(comparisonOperator, expr1); // formula1 and value1 are mutually exclusive String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertTime(expr1) : null; // formula2 and value2 are mutually exclusive String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertTime(expr2) : null; return new DVConstraint(ValidationType.TIME, comparisonOperator, formula1, formula2, value1, value2, null); }
String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertNumber(expr1) : null; String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertNumber(expr2) : null; return new DVConstraint(validationType, comparisonOperator, formula1, formula2, value1, value2, null);
public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) { return DVConstraint.createExplicitListConstraint(listOfValues); }
String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertDate(expr1, df) : null; String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertDate(expr2, df) : null; return new DVConstraint(ValidationType.DATE, comparisonOperator, formula1, formula2, value1, value2, null);
public static DVConstraint createFormulaListConstraint(String listFormula) { return new DVConstraint(listFormula, null); } public static DVConstraint createExplicitListConstraint(String[] explicitListValues) {
public DataValidationConstraint createFormulaListConstraint(String listFormula) { return DVConstraint.createFormulaListConstraint(listFormula); }
public DataValidationConstraint createCustomConstraint(String formula) { return DVConstraint.createCustomFormulaConstraint(formula); }
public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) { return DVConstraint.createExplicitListConstraint(listOfValues); }
/** * @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); }
/** * Creates a time based data validation constraint. The text values entered for expr1 and expr2 * can be either standard Excel formulas or formatted time values. If the expression starts * with '=' it is parsed as a formula, otherwise it is parsed as a formatted time. To parse * formatted times, two formats are supported: "HH:MM" or "HH:MM:SS". This is contrary to * Excel which uses the default time format from the OS. * * @param comparisonOperator constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum * @param expr1 date formula (when first char is '=') or formatted time value * @param expr2 date formula (when first char is '=') or formatted time value */ public static DVConstraint createTimeConstraint(int comparisonOperator, String expr1, String expr2) { if (expr1 == null) { throw new IllegalArgumentException("expr1 must be supplied"); } OperatorType.validateSecondArg(comparisonOperator, expr1); // formula1 and value1 are mutually exclusive String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertTime(expr1) : null; // formula2 and value2 are mutually exclusive String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertTime(expr2) : null; return new DVConstraint(ValidationType.TIME, comparisonOperator, formula1, formula2, value1, value2, null); }
/** * Creates a date based data validation constraint. The text values entered for expr1 and expr2 * can be either standard Excel formulas or formatted date values. If the expression starts * with '=' it is parsed as a formula, otherwise it is parsed as a formatted date (Excel uses * the same convention). To parse formatted dates, a date format needs to be specified. This * is contrary to Excel which uses the default short date format from the OS. * * @param comparisonOperator constant from {@link org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType} enum * @param expr1 date formula (when first char is '=') or formatted date value * @param expr2 date formula (when first char is '=') or formatted date value * @param dateFormat ignored if both expr1 and expr2 are formulas. Default value is "YYYY/MM/DD" * otherwise any other valid argument for <tt>SimpleDateFormat</tt> can be used * @see <a href='http://java.sun.com/j2se/1.5.0/docs/api/java/text/DateFormat.html'>SimpleDateFormat</a> */ public static DVConstraint createDateConstraint(int comparisonOperator, String expr1, String expr2, String dateFormat) { if (expr1 == null) { throw new IllegalArgumentException("expr1 must be supplied"); } OperatorType.validateSecondArg(comparisonOperator, expr2); SimpleDateFormat df = dateFormat == null ? null : new SimpleDateFormat(dateFormat); // formula1 and value1 are mutually exclusive String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertDate(expr1, df) : null; // formula2 and value2 are mutually exclusive String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertDate(expr2, df) : null; return new DVConstraint(VT.DATE, comparisonOperator, formula1, formula2, value1, value2, null); }
String formula1 = getFormulaFromTextExpression(expr1); Double value1 = formula1 == null ? convertNumber(expr1) : null; String formula2 = getFormulaFromTextExpression(expr2); Double value2 = formula2 == null ? convertNumber(expr2) : null; return new DVConstraint(validationType, comparisonOperator, formula1, formula2, value1, value2, null);
public static DVConstraint createExplicitListConstraint(String[] explicitListValues) { return new DVConstraint(null, explicitListValues); }
public DataValidationConstraint createFormulaListConstraint(String listFormula) { return DVConstraint.createFormulaListConstraint(listFormula); }
public DataValidationConstraint createCustomConstraint(String formula) { return DVConstraint.createCustomFormulaConstraint(formula); }
static DVConstraint createDVConstraint(DVRecord dvRecord, FormulaRenderingWorkbook book) { switch (dvRecord.getDataType()) { case ValidationType.ANY: return new DVConstraint(ValidationType.ANY, dvRecord.getConditionOperator(), null, null, null, null, null); case ValidationType.INTEGER: case ValidationType.DECIMAL: case ValidationType.TIME: case ValidationType.TEXT_LENGTH: FormulaValuePair pair1 = toFormulaString(dvRecord.getFormula1(), book); FormulaValuePair pair2 = toFormulaString(dvRecord.getFormula2(), book); return new DVConstraint(dvRecord.getDataType(), dvRecord.getConditionOperator(), pair1.formula(), pair2.formula(), pair1.value(), pair2.value(), null); case ValidationType.LIST: if (dvRecord.getListExplicitFormula()) { String values = toFormulaString(dvRecord.getFormula1(), book).string(); if (values.startsWith("\"")) { values = values.substring(1); return createExplicitListConstraint(explicitListValues); } else { String listFormula = toFormulaString(dvRecord.getFormula1(), book).string(); return createFormulaListConstraint(listFormula); return createCustomFormulaConstraint(toFormulaString(dvRecord.getFormula1(), book).string()); default: throw new UnsupportedOperationException("validationType="+dvRecord.getDataType());