View | Details | Raw Unified | Return to bug 49244
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/usermodel/DVConstraint.java (-131 / +79 lines)
Lines 26-94 Link Here
26
import org.apache.poi.hssf.record.formula.Ptg;
26
import org.apache.poi.hssf.record.formula.Ptg;
27
import org.apache.poi.hssf.record.formula.StringPtg;
27
import org.apache.poi.hssf.record.formula.StringPtg;
28
import org.apache.poi.ss.formula.FormulaType;
28
import org.apache.poi.ss.formula.FormulaType;
29
import org.apache.poi.ss.usermodel.DataValidationConstraint;
29
30
30
/**
31
/**
31
 * 
32
 * 
32
 * @author Josh Micich
33
 * @author Josh Micich
33
 */
34
 */
34
public class DVConstraint {
35
public class DVConstraint implements DataValidationConstraint {
35
	/**
36
	/* package */ public static final class FormulaPair {
36
	 * ValidationType enum
37
	 */
38
	public static final class ValidationType {
39
		private ValidationType() {
40
			// no instances of this class
41
		}
42
		/** 'Any value' type - value not restricted */
43
		public static final int ANY         = 0x00;
44
		/** Integer ('Whole number') type */
45
		public static final int INTEGER     = 0x01;
46
		/** Decimal type */
47
		public static final int DECIMAL     = 0x02;
48
		/** List type ( combo box type ) */
49
		public static final int LIST        = 0x03;
50
		/** Date type */
51
		public static final int DATE        = 0x04;
52
		/** Time type */
53
		public static final int TIME        = 0x05;
54
		/** String length type */
55
		public static final int TEXT_LENGTH = 0x06;
56
		/** Formula ( 'Custom' ) type */
57
		public static final int FORMULA     = 0x07;
58
	}
59
	/**
60
	 * Condition operator enum
61
	 */
62
	public static final class OperatorType {
63
		private OperatorType() {
64
			// no instances of this class
65
		}
66
37
67
		public static final int BETWEEN = 0x00;
68
		public static final int NOT_BETWEEN = 0x01;
69
		public static final int EQUAL = 0x02;
70
		public static final int NOT_EQUAL = 0x03;
71
		public static final int GREATER_THAN = 0x04;
72
		public static final int LESS_THAN = 0x05;
73
		public static final int GREATER_OR_EQUAL = 0x06;
74
		public static final int LESS_OR_EQUAL = 0x07;
75
		/** default value to supply when the operator type is not used */
76
		public static final int IGNORED = BETWEEN;
77
		
78
		/* package */ static void validateSecondArg(int comparisonOperator, String paramValue) {
79
			switch (comparisonOperator) {
80
				case BETWEEN:
81
				case NOT_BETWEEN:
82
					if (paramValue == null) {
83
						throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
84
					}
85
				// all other operators don't need second arg
86
			}
87
		}
88
	}
89
	
90
	/* package */ static final class FormulaPair {
91
92
		private final Ptg[] _formula1;
38
		private final Ptg[] _formula1;
93
		private final Ptg[] _formula2;
39
		private final Ptg[] _formula2;
94
40
Lines 211-218 Link Here
211
		String formula2 = getFormulaFromTextExpression(expr2);
157
		String formula2 = getFormulaFromTextExpression(expr2);
212
		Double value2 = formula2 == null ? convertTime(expr2) : null;
158
		Double value2 = formula2 == null ? convertTime(expr2) : null;
213
		return new DVConstraint(VT.TIME, comparisonOperator, formula1, formula2, value1, value2, null);
159
		return new DVConstraint(VT.TIME, comparisonOperator, formula1, formula2, value1, value2, null);
214
		
215
	}
160
	}
161
	
216
	/**
162
	/**
217
	 * Creates a date based data validation constraint. The text values entered for expr1 and expr2
163
	 * Creates a date based data validation constraint. The text values entered for expr1 and expr2
218
	 * can be either standard Excel formulas or formatted date values. If the expression starts 
164
	 * can be either standard Excel formulas or formatted date values. If the expression starts 
Lines 321-386 Link Here
321
		return new DVConstraint(VT.FORMULA, OperatorType.IGNORED, formula, null, null, null, null);
267
		return new DVConstraint(VT.FORMULA, OperatorType.IGNORED, formula, null, null, null, null);
322
	}
268
	}
323
	
269
	
324
	/**
270
	/* (non-Javadoc)
325
	 * @return both parsed formulas (for expression 1 and 2). 
271
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getValidationType()
326
	 */
272
	 */
327
	/* package */ FormulaPair createFormulas(HSSFSheet sheet) {
328
		Ptg[] formula1;
329
		Ptg[] formula2;
330
		if (isListValidationType()) {
331
			formula1 = createListFormula(sheet);
332
			formula2 = Ptg.EMPTY_PTG_ARRAY;
333
		} else {
334
			formula1 = convertDoubleFormula(_formula1, _value1, sheet);
335
			formula2 = convertDoubleFormula(_formula2, _value2, sheet);
336
		}
337
		return new FormulaPair(formula1, formula2);
338
	}
339
340
	private Ptg[] createListFormula(HSSFSheet sheet) {
341
342
		if (_explicitListValues == null) {
343
            HSSFWorkbook wb = sheet.getWorkbook();
344
            // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
345
			return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet));
346
			// To do: Excel places restrictions on the available operations within a list formula.
347
			// Some things like union and intersection are not allowed.
348
		}
349
		// explicit list was provided
350
		StringBuffer sb = new StringBuffer(_explicitListValues.length * 16);
351
		for (int i = 0; i < _explicitListValues.length; i++) {
352
			if (i > 0) {
353
				sb.append('\0'); // list delimiter is the nul char
354
			}
355
			sb.append(_explicitListValues[i]);
356
		
357
		}
358
		return new Ptg[] { new StringPtg(sb.toString()), };
359
	}
360
361
	/**
362
	 * @return The parsed token array representing the formula or value specified. 
363
	 * Empty array if both formula and value are <code>null</code>
364
	 */
365
	private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) {
366
		if (formula == null) {
367
			if (value == null) {
368
				return Ptg.EMPTY_PTG_ARRAY;
369
			}
370
			return new Ptg[] { new NumberPtg(value.doubleValue()), };
371
		}
372
		if (value != null) {
373
			throw new IllegalStateException("Both formula and value cannot be present");
374
		}
375
        HSSFWorkbook wb = sheet.getWorkbook();
376
		return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet));
377
	}
378
	
379
	
380
	/**
381
	 * @return data validation type of this constraint
382
	 * @see ValidationType
383
	 */
384
	public int getValidationType() {
273
	public int getValidationType() {
385
		return _validationType;
274
		return _validationType;
386
	}
275
	}
Lines 398-421 Link Here
398
	public boolean isExplicitList() {
287
	public boolean isExplicitList() {
399
		return _validationType == VT.LIST && _explicitListValues != null;
288
		return _validationType == VT.LIST && _explicitListValues != null;
400
	}
289
	}
401
	/**
290
	/* (non-Javadoc)
402
	 * @return the operator used for this constraint
291
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getOperator()
403
	 * @see OperatorType
404
	 */
292
	 */
405
	public int getOperator() {
293
	public int getOperator() {
406
		return _operator;
294
		return _operator;
407
	}
295
	}
408
	/**
296
	/* (non-Javadoc)
409
	 * Sets the comparison operator for this constraint
297
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setOperator(int)
410
	 * @see OperatorType
411
	 */
298
	 */
412
	public void setOperator(int operator) {
299
	public void setOperator(int operator) {
413
		_operator = operator;
300
		_operator = operator;
414
	}
301
	}
415
	
302
	
303
	/* (non-Javadoc)
304
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getExplicitListValues()
305
	 */
416
	public String[] getExplicitListValues() {
306
	public String[] getExplicitListValues() {
417
		return _explicitListValues;
307
		return _explicitListValues;
418
	}
308
	}
309
	/* (non-Javadoc)
310
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setExplicitListValues(java.lang.String[])
311
	 */
419
	public void setExplicitListValues(String[] explicitListValues) {
312
	public void setExplicitListValues(String[] explicitListValues) {
420
		if (_validationType != VT.LIST) {
313
		if (_validationType != VT.LIST) {
421
			throw new RuntimeException("Cannot setExplicitListValues on non-list constraint");
314
			throw new RuntimeException("Cannot setExplicitListValues on non-list constraint");
Lines 424-437 Link Here
424
		_explicitListValues = explicitListValues;
317
		_explicitListValues = explicitListValues;
425
	}
318
	}
426
319
427
	/**
320
	/* (non-Javadoc)
428
	 * @return the formula for expression 1. May be <code>null</code>
321
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula1()
429
	 */
322
	 */
430
	public String getFormula1() {
323
	public String getFormula1() {
431
		return _formula1;
324
		return _formula1;
432
	}
325
	}
433
	/**
326
	/* (non-Javadoc)
434
	 * Sets a formula for expression 1.
327
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula1(java.lang.String)
435
	 */
328
	 */
436
	public void setFormula1(String formula1) {
329
	public void setFormula1(String formula1) {
437
		_value1 = null;
330
		_value1 = null;
Lines 439-452 Link Here
439
		_formula1 = formula1;
332
		_formula1 = formula1;
440
	}
333
	}
441
334
442
	/**
335
	/* (non-Javadoc)
443
	 * @return the formula for expression 2. May be <code>null</code>
336
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#getFormula2()
444
	 */
337
	 */
445
	public String getFormula2() {
338
	public String getFormula2() {
446
		return _formula2;
339
		return _formula2;
447
	}
340
	}
448
	/**
341
	/* (non-Javadoc)
449
	 * Sets a formula for expression 2.
342
	 * @see org.apache.poi.hssf.usermodel.DataValidationConstraint#setFormula2(java.lang.String)
450
	 */
343
	 */
451
	public void setFormula2(String formula2) {
344
	public void setFormula2(String formula2) {
452
		_value2 = null;
345
		_value2 = null;
Lines 480-483 Link Here
480
		_formula2 = null;
373
		_formula2 = null;
481
		_value2 = new Double(value2);
374
		_value2 = new Double(value2);
482
	}
375
	}
376
	
377
	/**
378
	 * @return both parsed formulas (for expression 1 and 2). 
379
	 */
380
	/* package */ FormulaPair createFormulas(HSSFSheet sheet) {
381
		Ptg[] formula1;
382
		Ptg[] formula2;
383
		if (isListValidationType()) {
384
			formula1 = createListFormula(sheet);
385
			formula2 = Ptg.EMPTY_PTG_ARRAY;
386
		} else {
387
			formula1 = convertDoubleFormula(_formula1, _value1, sheet);
388
			formula2 = convertDoubleFormula(_formula2, _value2, sheet);
389
		}
390
		return new FormulaPair(formula1, formula2);
391
	}
392
393
	private Ptg[] createListFormula(HSSFSheet sheet) {
394
395
		if (_explicitListValues == null) {
396
            HSSFWorkbook wb = sheet.getWorkbook();
397
            // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
398
			return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet));
399
			// To do: Excel places restrictions on the available operations within a list formula.
400
			// Some things like union and intersection are not allowed.
401
		}
402
		// explicit list was provided
403
		StringBuffer sb = new StringBuffer(_explicitListValues.length * 16);
404
		for (int i = 0; i < _explicitListValues.length; i++) {
405
			if (i > 0) {
406
				sb.append('\0'); // list delimiter is the nul char
407
			}
408
			sb.append(_explicitListValues[i]);
409
		
410
		}
411
		return new Ptg[] { new StringPtg(sb.toString()), };
412
	}
413
414
	/**
415
	 * @return The parsed token array representing the formula or value specified. 
416
	 * Empty array if both formula and value are <code>null</code>
417
	 */
418
	private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) {
419
		if (formula == null) {
420
			if (value == null) {
421
				return Ptg.EMPTY_PTG_ARRAY;
422
			}
423
			return new Ptg[] { new NumberPtg(value.doubleValue()), };
424
		}
425
		if (value != null) {
426
			throw new IllegalStateException("Both formula and value cannot be present");
427
		}
428
        HSSFWorkbook wb = sheet.getWorkbook();
429
		return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet));
430
	}	
483
}
431
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFDataValidation.java (-83 / +55 lines)
Lines 19-24 Link Here
19
19
20
import org.apache.poi.hssf.record.DVRecord;
20
import org.apache.poi.hssf.record.DVRecord;
21
import org.apache.poi.hssf.usermodel.DVConstraint.FormulaPair;
21
import org.apache.poi.hssf.usermodel.DVConstraint.FormulaPair;
22
import org.apache.poi.ss.usermodel.DataValidation;
23
import org.apache.poi.ss.usermodel.DataValidationConstraint;
24
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
22
import org.apache.poi.ss.util.CellRangeAddressList;
25
import org.apache.poi.ss.util.CellRangeAddressList;
23
26
24
/**
27
/**
Lines 26-44 Link Here
26
 * 
29
 * 
27
 * @author Dragos Buleandra (dragos.buleandra@trade2b.ro)
30
 * @author Dragos Buleandra (dragos.buleandra@trade2b.ro)
28
 */
31
 */
29
public final class HSSFDataValidation {
32
public final class HSSFDataValidation implements DataValidation {
30
	/**
31
	 * Error style constants for error box
32
	 */
33
	public static final class ErrorStyle {
34
    	/** STOP style */
35
    	public static final int STOP    = 0x00;
36
    	/** WARNING style */
37
    	public static final int WARNING = 0x01;
38
    	/** INFO style */
39
    	public static final int INFO    = 0x02;
40
	}    
41
42
	private String _prompt_title;
33
	private String _prompt_title;
43
	private String _prompt_text;
34
	private String _prompt_text;
44
	private String _error_title;
35
	private String _error_title;
Lines 49-55 Link Here
49
	private boolean _suppress_dropdown_arrow = false;
40
	private boolean _suppress_dropdown_arrow = false;
50
	private boolean _showPromptBox = true;
41
	private boolean _showPromptBox = true;
51
	private boolean _showErrorBox = true;
42
	private boolean _showErrorBox = true;
52
	private final CellRangeAddressList _regions;
43
	public final CellRangeAddressList _regions;
53
	private DVConstraint _constraint;
44
	private DVConstraint _constraint;
54
45
55
	/**
46
	/**
Lines 57-175 Link Here
57
	 * applied
48
	 * applied
58
	 * @param constraint 
49
	 * @param constraint 
59
	 */
50
	 */
60
	public HSSFDataValidation(CellRangeAddressList regions, DVConstraint constraint) {
51
	public HSSFDataValidation(CellRangeAddressList regions, DataValidationConstraint constraint) {
61
		_regions = regions;
52
		_regions = regions;
62
		_constraint = constraint;
53
		
54
		//FIXME: This cast can be avoided.
55
		_constraint = (DVConstraint)constraint;
63
	}
56
	}
64
57
65
58
59
	/* (non-Javadoc)
60
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getConstraint()
61
	 */
62
	public DataValidationConstraint getValidationConstraint() {
63
		return _constraint;
64
	}
65
66
	public DVConstraint getConstraint() {
66
	public DVConstraint getConstraint() {
67
		return _constraint;
67
		return _constraint;
68
	}
68
	}
69
	
70
	public CellRangeAddressList getRegions() {
71
		return _regions;
72
	}
69
73
70
	/**
74
71
	 * Sets the error style for error box
75
	/* (non-Javadoc)
72
	 * @see ErrorStyle
76
	 * @see org.apache.poi.hssf.usermodel.DataValidation#setErrorStyle(int)
73
	 */
77
	 */
74
	public void setErrorStyle(int error_style) {
78
	public void setErrorStyle(int error_style) {
75
		_errorStyle = error_style;
79
		_errorStyle = error_style;
76
	}
80
	}
77
81
78
	/**
82
	/* (non-Javadoc)
79
	 * @return the error style of error box
83
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorStyle()
80
	 * @see ErrorStyle
81
	 */
84
	 */
82
	public int getErrorStyle() {
85
	public int getErrorStyle() {
83
		return _errorStyle;
86
		return _errorStyle;
84
	}
87
	}
85
88
86
	/**
89
	/* (non-Javadoc)
87
	 * Sets if this object allows empty as a valid value
90
	 * @see org.apache.poi.hssf.usermodel.DataValidation#setEmptyCellAllowed(boolean)
88
	 * 
89
	 * @param allowed <code>true</code> if this object should treats empty as valid value , <code>false</code>
90
	 *            otherwise
91
	 */
91
	 */
92
	public void setEmptyCellAllowed(boolean allowed) {
92
	public void setEmptyCellAllowed(boolean allowed) {
93
		_emptyCellAllowed = allowed;
93
		_emptyCellAllowed = allowed;
94
	}
94
	}
95
95
96
	/**
96
	/* (non-Javadoc)
97
	 * Retrieve the settings for empty cells allowed
97
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getEmptyCellAllowed()
98
	 * 
99
	 * @return True if this object should treats empty as valid value , false
100
	 *         otherwise
101
	 */
98
	 */
102
	public boolean getEmptyCellAllowed() {
99
	public boolean getEmptyCellAllowed() {
103
		return _emptyCellAllowed;
100
		return _emptyCellAllowed;
104
	}
101
	}
105
102
106
	/**
103
	/* (non-Javadoc)
107
	 * Useful for list validation objects .
104
	 * @see org.apache.poi.hssf.usermodel.DataValidation#setSuppressDropDownArrow(boolean)
108
	 * 
109
	 * @param suppress
110
	 *            True if a list should display the values into a drop down list ,
111
	 *            false otherwise . In other words , if a list should display
112
	 *            the arrow sign on its right side
113
	 */
105
	 */
114
	public void setSuppressDropDownArrow(boolean suppress) {
106
	public void setSuppressDropDownArrow(boolean suppress) {
115
		_suppress_dropdown_arrow = suppress;
107
		_suppress_dropdown_arrow = suppress;
116
	}
108
	}
117
109
118
	/**
110
	/* (non-Javadoc)
119
	 * Useful only list validation objects . This method always returns false if
111
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getSuppressDropDownArrow()
120
	 * the object isn't a list validation object
121
	 * 
122
	 * @return <code>true</code> if a list should display the values into a drop down list ,
123
	 *         <code>false</code> otherwise .
124
	 */
112
	 */
125
	public boolean getSuppressDropDownArrow() {
113
	public boolean getSuppressDropDownArrow() {
126
		if (_constraint.isListValidationType()) {
114
		if (_constraint.getValidationType()==ValidationType.LIST) {
127
			return _suppress_dropdown_arrow;
115
			return _suppress_dropdown_arrow;
128
		}
116
		}
129
		return false;
117
		return false;
130
	}
118
	}
131
119
132
	/**
120
	/* (non-Javadoc)
133
	 * Sets the behaviour when a cell which belongs to this object is selected
121
	 * @see org.apache.poi.hssf.usermodel.DataValidation#setShowPromptBox(boolean)
134
	 * 
135
	 * @param show <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
136
	 */
122
	 */
137
	public void setShowPromptBox(boolean show) {
123
	public void setShowPromptBox(boolean show) {
138
		_showPromptBox = show;
124
		_showPromptBox = show;
139
	}
125
	}
140
126
141
	/**
127
	/* (non-Javadoc)
142
	 * @return <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
128
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getShowPromptBox()
143
	 */
129
	 */
144
	public boolean getShowPromptBox() {
130
	public boolean getShowPromptBox() {
145
		return _showPromptBox;
131
		return _showPromptBox;
146
	}
132
	}
147
133
148
	/**
134
	/* (non-Javadoc)
149
	 * Sets the behaviour when an invalid value is entered
135
	 * @see org.apache.poi.hssf.usermodel.DataValidation#setShowErrorBox(boolean)
150
	 * 
151
	 * @param show <code>true</code> if an error box should be displayed , <code>false</code> otherwise
152
	 */
136
	 */
153
	public void setShowErrorBox(boolean show) {
137
	public void setShowErrorBox(boolean show) {
154
		_showErrorBox = show;
138
		_showErrorBox = show;
155
	}
139
	}
156
140
157
	/**
141
	/* (non-Javadoc)
158
	 * @return <code>true</code> if an error box should be displayed , <code>false</code> otherwise
142
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getShowErrorBox()
159
	 */
143
	 */
160
	public boolean getShowErrorBox() {
144
	public boolean getShowErrorBox() {
161
		return _showErrorBox;
145
		return _showErrorBox;
162
	}
146
	}
163
147
164
148
165
	/**
149
	/* (non-Javadoc)
166
	 * Sets the title and text for the prompt box . Prompt box is displayed when
150
	 * @see org.apache.poi.hssf.usermodel.DataValidation#createPromptBox(java.lang.String, java.lang.String)
167
	 * the user selects a cell which belongs to this validation object . In
168
	 * order for a prompt box to be displayed you should also use method
169
	 * setShowPromptBox( boolean show )
170
	 * 
171
	 * @param title The prompt box's title
172
	 * @param text The prompt box's text
173
	 */
151
	 */
174
	public void createPromptBox(String title, String text) {
152
	public void createPromptBox(String title, String text) {
175
		_prompt_title = title;
153
		_prompt_title = title;
Lines 177-204 Link Here
177
		this.setShowPromptBox(true);
155
		this.setShowPromptBox(true);
178
	}
156
	}
179
157
180
	/**
158
	/* (non-Javadoc)
181
	 * @return Prompt box's title or <code>null</code>
159
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getPromptBoxTitle()
182
	 */
160
	 */
183
	public String getPromptBoxTitle() {
161
	public String getPromptBoxTitle() {
184
		return _prompt_title;
162
		return _prompt_title;
185
	}
163
	}
186
164
187
	/**
165
	/* (non-Javadoc)
188
	 * @return Prompt box's text or <code>null</code>
166
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getPromptBoxText()
189
	 */
167
	 */
190
	public String getPromptBoxText() {
168
	public String getPromptBoxText() {
191
		return _prompt_text;
169
		return _prompt_text;
192
	}
170
	}
193
171
194
	/**
172
	/* (non-Javadoc)
195
	 * Sets the title and text for the error box . Error box is displayed when
173
	 * @see org.apache.poi.hssf.usermodel.DataValidation#createErrorBox(java.lang.String, java.lang.String)
196
	 * the user enters an invalid value int o a cell which belongs to this
197
	 * validation object . In order for an error box to be displayed you should
198
	 * also use method setShowErrorBox( boolean show )
199
	 * 
200
	 * @param title The error box's title
201
	 * @param text The error box's text
202
	 */
174
	 */
203
	public void createErrorBox(String title, String text) {
175
	public void createErrorBox(String title, String text) {
204
		_error_title = title;
176
		_error_title = title;
Lines 206-220 Link Here
206
		this.setShowErrorBox(true);
178
		this.setShowErrorBox(true);
207
	}
179
	}
208
180
209
	/**
181
	/* (non-Javadoc)
210
	 * @return Error box's title or <code>null</code>
182
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxTitle()
211
	 */
183
	 */
212
	public String getErrorBoxTitle() {
184
	public String getErrorBoxTitle() {
213
		return _error_title;
185
		return _error_title;
214
	}
186
	}
215
187
216
	/**
188
	/* (non-Javadoc)
217
	 * @return Error box's text or <code>null</code>
189
	 * @see org.apache.poi.hssf.usermodel.DataValidation#getErrorBoxText()
218
	 */
190
	 */
219
	public String getErrorBoxText() {
191
	public String getErrorBoxText() {
220
		return _error_text;
192
		return _error_text;
Lines 227-233 Link Here
227
		return new DVRecord(_constraint.getValidationType(),
199
		return new DVRecord(_constraint.getValidationType(),
228
				_constraint.getOperator(),
200
				_constraint.getOperator(),
229
				_errorStyle, _emptyCellAllowed, getSuppressDropDownArrow(),
201
				_errorStyle, _emptyCellAllowed, getSuppressDropDownArrow(),
230
				_constraint.isExplicitList(),
202
				_constraint.getValidationType()==ValidationType.LIST && _constraint.getExplicitListValues()!=null,
231
				_showPromptBox, _prompt_title, _prompt_text,
203
				_showPromptBox, _prompt_title, _prompt_text,
232
				_showErrorBox, _error_title, _error_text,
204
				_showErrorBox, _error_title, _error_text,
233
				fp.getFormula1(), fp.getFormula2(),
205
				fp.getFormula1(), fp.getFormula2(),
(-)src/java/org/apache/poi/hssf/usermodel/HSSFDataValidationHelper.java (+118 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.hssf.usermodel;
5
6
import org.apache.poi.ss.usermodel.DataValidation;
7
import org.apache.poi.ss.usermodel.DataValidationConstraint;
8
import org.apache.poi.ss.usermodel.DataValidationHelper;
9
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
10
import org.apache.poi.ss.util.CellRangeAddressList;
11
12
/**
13
 * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
14
 * 
15
 */
16
public class HSSFDataValidationHelper implements DataValidationHelper {
17
	@SuppressWarnings("unused")
18
	private HSSFSheet sheet;
19
	
20
	public HSSFDataValidationHelper(HSSFSheet sheet) {
21
		super();
22
		this.sheet = sheet;
23
	}
24
25
	/*
26
	 * (non-Javadoc)
27
	 * 
28
	 * @see
29
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createDateConstraint
30
	 * (int, java.lang.String, java.lang.String, java.lang.String)
31
	 */
32
	public DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2, String dateFormat) {
33
		return DVConstraint.createDateConstraint(operatorType, formula1, formula2, dateFormat);
34
	}
35
36
	/*
37
	 * (non-Javadoc)
38
	 * 
39
	 * @see
40
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createExplicitListConstraint
41
	 * (java.lang.String[])
42
	 */
43
	public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) {
44
		return DVConstraint.createExplicitListConstraint(listOfValues);
45
	}
46
47
	/*
48
	 * (non-Javadoc)
49
	 * 
50
	 * @see
51
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createFormulaListConstraint
52
	 * (java.lang.String)
53
	 */
54
	public DataValidationConstraint createFormulaListConstraint(String listFormula) {
55
		return DVConstraint.createFormulaListConstraint(listFormula);
56
	}
57
58
	
59
	
60
	public DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2) {
61
		return DVConstraint.createNumericConstraint(validationType, operatorType, formula1, formula2);
62
	}
63
64
	public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) {
65
		return DVConstraint.createNumericConstraint(ValidationType.INTEGER, operatorType, formula1, formula2);
66
	}
67
	
68
	/*
69
	 * (non-Javadoc)
70
	 * 
71
	 * @see
72
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createNumericConstraint
73
	 * (int, java.lang.String, java.lang.String)
74
	 */
75
	public DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2) {
76
		return DVConstraint.createNumericConstraint(ValidationType.DECIMAL, operatorType, formula1, formula2);
77
	}
78
79
	/*
80
	 * (non-Javadoc)
81
	 * 
82
	 * @see
83
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createTextLengthConstraint
84
	 * (int, java.lang.String, java.lang.String)
85
	 */
86
	public DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2) {
87
		return DVConstraint.createNumericConstraint(ValidationType.TEXT_LENGTH, operatorType, formula1, formula2);
88
	}
89
90
	/*
91
	 * (non-Javadoc)
92
	 * 
93
	 * @see
94
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createTimeConstraint
95
	 * (int, java.lang.String, java.lang.String, java.lang.String)
96
	 */
97
	public DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2) {
98
		return DVConstraint.createTimeConstraint(operatorType, formula1, formula2);
99
	}
100
101
	
102
	
103
	public DataValidationConstraint createCustomConstraint(String formula) {
104
		return DVConstraint.createCustomFormulaConstraint(formula);
105
	}
106
107
	/*
108
	 * (non-Javadoc)
109
	 * 
110
	 * @see
111
	 * org.apache.poi.ss.usermodel.DataValidationHelper#createValidation(org
112
	 * .apache.poi.ss.usermodel.DataValidationConstraint,
113
	 * org.apache.poi.ss.util.CellRangeAddressList)
114
	 */
115
	public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
116
		return new HSSFDataValidation(cellRangeAddressList, constraint); 
117
	}
118
}
(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-4 / +13 lines)
Lines 51-65 Link Here
51
import org.apache.poi.hssf.record.formula.Ptg;
51
import org.apache.poi.hssf.record.formula.Ptg;
52
import org.apache.poi.hssf.util.PaneInformation;
52
import org.apache.poi.hssf.util.PaneInformation;
53
import org.apache.poi.hssf.util.Region;
53
import org.apache.poi.hssf.util.Region;
54
import org.apache.poi.ss.SpreadsheetVersion;
54
import org.apache.poi.ss.formula.FormulaType;
55
import org.apache.poi.ss.formula.FormulaType;
55
import org.apache.poi.ss.usermodel.Cell;
56
import org.apache.poi.ss.usermodel.Cell;
56
import org.apache.poi.ss.usermodel.CellRange;
57
import org.apache.poi.ss.usermodel.CellRange;
57
import org.apache.poi.ss.usermodel.CellStyle;
58
import org.apache.poi.ss.usermodel.CellStyle;
59
import org.apache.poi.ss.usermodel.DataValidation;
60
import org.apache.poi.ss.usermodel.DataValidationHelper;
58
import org.apache.poi.ss.usermodel.Row;
61
import org.apache.poi.ss.usermodel.Row;
59
import org.apache.poi.ss.util.CellRangeAddress;
62
import org.apache.poi.ss.util.CellRangeAddress;
60
import org.apache.poi.ss.util.CellReference;
63
import org.apache.poi.ss.util.CellReference;
61
import org.apache.poi.ss.util.SSCellRange;
64
import org.apache.poi.ss.util.SSCellRange;
62
import org.apache.poi.ss.SpreadsheetVersion;
63
import org.apache.poi.util.POILogFactory;
65
import org.apache.poi.util.POILogFactory;
64
import org.apache.poi.util.POILogger;
66
import org.apache.poi.util.POILogger;
65
67
Lines 88-94 Link Here
88
    /**
90
    /**
89
     * reference to the low level {@link InternalSheet} object
91
     * reference to the low level {@link InternalSheet} object
90
     */
92
     */
91
    private final InternalSheet _sheet;
93
    protected final InternalSheet _sheet;
92
    /** stores rows by zero-based row number */
94
    /** stores rows by zero-based row number */
93
    private final TreeMap<Integer, HSSFRow> _rows;
95
    private final TreeMap<Integer, HSSFRow> _rows;
94
    protected final InternalWorkbook _book;
96
    protected final InternalWorkbook _book;
Lines 373-385 Link Here
373
     * Creates a data validation object
375
     * Creates a data validation object
374
     * @param dataValidation The Data validation object settings
376
     * @param dataValidation The Data validation object settings
375
     */
377
     */
376
    public void addValidationData(HSSFDataValidation dataValidation) {
378
    public void addValidationData(DataValidation dataValidation) {
377
       if (dataValidation == null) {
379
       if (dataValidation == null) {
378
           throw new IllegalArgumentException("objValidation must not be null");
380
           throw new IllegalArgumentException("objValidation must not be null");
379
       }
381
       }
382
       HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation;
380
       DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
383
       DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
381
384
382
       DVRecord dvRecord = dataValidation.createDVRecord(this);
385
       DVRecord dvRecord = hssfDataValidation.createDVRecord(this);
383
       dvt.addDataValidation(dvRecord);
386
       dvt.addDataValidation(dvRecord);
384
    }
387
    }
385
388
Lines 1997-2000 Link Here
1997
        }
2000
        }
1998
        return result;
2001
        return result;
1999
    }
2002
    }
2003
2004
	public DataValidationHelper getDataValidationHelper() {
2005
		return new HSSFDataValidationHelper(this);
2006
	}
2007
    
2008
    
2000
}
2009
}
(-)src/java/org/apache/poi/ss/usermodel/DataValidation.java (+136 lines)
Line 0 Link Here
1
package org.apache.poi.ss.usermodel;
2
3
import org.apache.poi.ss.util.CellRangeAddressList;
4
5
6
public interface DataValidation {
7
	/**
8
	 * Error style constants for error box
9
	 */
10
	public static final class ErrorStyle {
11
    	/** STOP style */
12
    	public static final int STOP    = 0x00;
13
    	/** WARNING style */
14
    	public static final int WARNING = 0x01;
15
    	/** INFO style */
16
    	public static final int INFO    = 0x02;
17
	}    
18
19
	public abstract DataValidationConstraint getValidationConstraint();
20
21
	/**
22
	 * Sets the error style for error box
23
	 * @see ErrorStyle
24
	 */
25
	public abstract void setErrorStyle(int error_style);
26
27
	/**o
28
	 * @return the error style of error box
29
	 * @see ErrorStyle
30
	 */
31
	public abstract int getErrorStyle();
32
33
	/**
34
	 * Sets if this object allows empty as a valid value
35
	 * 
36
	 * @param allowed <code>true</code> if this object should treats empty as valid value , <code>false</code>
37
	 *            otherwise
38
	 */
39
	public abstract void setEmptyCellAllowed(boolean allowed);
40
41
	/**
42
	 * Retrieve the settings for empty cells allowed
43
	 * 
44
	 * @return True if this object should treats empty as valid value , false
45
	 *         otherwise
46
	 */
47
	public abstract boolean getEmptyCellAllowed();
48
49
	/**
50
	 * Useful for list validation objects .
51
	 * 
52
	 * @param suppress
53
	 *            True if a list should display the values into a drop down list ,
54
	 *            false otherwise . In other words , if a list should display
55
	 *            the arrow sign on its right side
56
	 */
57
	public abstract void setSuppressDropDownArrow(boolean suppress);
58
59
	/**
60
	 * Useful only list validation objects . This method always returns false if
61
	 * the object isn't a list validation object
62
	 * 
63
	 * @return <code>true</code> if a list should display the values into a drop down list ,
64
	 *         <code>false</code> otherwise .
65
	 */
66
	public abstract boolean getSuppressDropDownArrow();
67
68
	/**
69
	 * Sets the behaviour when a cell which belongs to this object is selected
70
	 * 
71
	 * @param show <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
72
	 */
73
	public abstract void setShowPromptBox(boolean show);
74
75
	/**
76
	 * @return <code>true</code> if an prompt box should be displayed , <code>false</code> otherwise
77
	 */
78
	public abstract boolean getShowPromptBox();
79
80
	/**
81
	 * Sets the behaviour when an invalid value is entered
82
	 * 
83
	 * @param show <code>true</code> if an error box should be displayed , <code>false</code> otherwise
84
	 */
85
	public abstract void setShowErrorBox(boolean show);
86
87
	/**
88
	 * @return <code>true</code> if an error box should be displayed , <code>false</code> otherwise
89
	 */
90
	public abstract boolean getShowErrorBox();
91
92
	/**
93
	 * Sets the title and text for the prompt box . Prompt box is displayed when
94
	 * the user selects a cell which belongs to this validation object . In
95
	 * order for a prompt box to be displayed you should also use method
96
	 * setShowPromptBox( boolean show )
97
	 * 
98
	 * @param title The prompt box's title
99
	 * @param text The prompt box's text
100
	 */
101
	public abstract void createPromptBox(String title, String text);
102
103
	/**
104
	 * @return Prompt box's title or <code>null</code>
105
	 */
106
	public abstract String getPromptBoxTitle();
107
108
	/**
109
	 * @return Prompt box's text or <code>null</code>
110
	 */
111
	public abstract String getPromptBoxText();
112
113
	/**
114
	 * Sets the title and text for the error box . Error box is displayed when
115
	 * the user enters an invalid value int o a cell which belongs to this
116
	 * validation object . In order for an error box to be displayed you should
117
	 * also use method setShowErrorBox( boolean show )
118
	 * 
119
	 * @param title The error box's title
120
	 * @param text The error box's text
121
	 */
122
	public abstract void createErrorBox(String title, String text);
123
124
	/**
125
	 * @return Error box's title or <code>null</code>
126
	 */
127
	public abstract String getErrorBoxTitle();
128
129
	/**
130
	 * @return Error box's text or <code>null</code>
131
	 */
132
	public abstract String getErrorBoxText();
133
134
	public abstract CellRangeAddressList getRegions();
135
136
}
(-)src/java/org/apache/poi/ss/usermodel/DataValidationConstraint.java (+102 lines)
Line 0 Link Here
1
package org.apache.poi.ss.usermodel;
2
3
4
public interface DataValidationConstraint {
5
6
	/**
7
	 * @return data validation type of this constraint
8
	 * @see ValidationType
9
	 */
10
	public abstract int getValidationType();
11
12
	/**
13
	 * @return the operator used for this constraint
14
	 * @see OperatorType
15
	 */
16
	public abstract int getOperator();
17
18
	/**
19
	 * Sets the comparison operator for this constraint
20
	 * @see OperatorType
21
	 */
22
	public abstract void setOperator(int operator);
23
24
	public abstract String[] getExplicitListValues();
25
26
	public abstract void setExplicitListValues(String[] explicitListValues);
27
28
	/**
29
	 * @return the formula for expression 1. May be <code>null</code>
30
	 */
31
	public abstract String getFormula1();
32
33
	/**
34
	 * Sets a formula for expression 1.
35
	 */
36
	public abstract void setFormula1(String formula1);
37
38
	/**
39
	 * @return the formula for expression 2. May be <code>null</code>
40
	 */
41
	public abstract String getFormula2();
42
43
	/**
44
	 * Sets a formula for expression 2.
45
	 */
46
	public abstract void setFormula2(String formula2);
47
	
48
	/**
49
	 * ValidationType enum
50
	 */
51
	public static final class ValidationType {
52
		private ValidationType() {
53
			// no instances of this class
54
		}
55
		/** 'Any value' type - value not restricted */
56
		public static final int ANY         = 0x00;
57
		/** Integer ('Whole number') type */
58
		public static final int INTEGER     = 0x01;
59
		/** Decimal type */
60
		public static final int DECIMAL     = 0x02;
61
		/** List type ( combo box type ) */
62
		public static final int LIST        = 0x03;
63
		/** Date type */
64
		public static final int DATE        = 0x04;
65
		/** Time type */
66
		public static final int TIME        = 0x05;
67
		/** String length type */
68
		public static final int TEXT_LENGTH = 0x06;
69
		/** Formula ( 'Custom' ) type */
70
		public static final int FORMULA     = 0x07;
71
	}
72
	/**
73
	 * Condition operator enum
74
	 */
75
	public static final class OperatorType {
76
		private OperatorType() {
77
			// no instances of this class
78
		}
79
80
		public static final int BETWEEN = 0x00;
81
		public static final int NOT_BETWEEN = 0x01;
82
		public static final int EQUAL = 0x02;
83
		public static final int NOT_EQUAL = 0x03;
84
		public static final int GREATER_THAN = 0x04;
85
		public static final int LESS_THAN = 0x05;
86
		public static final int GREATER_OR_EQUAL = 0x06;
87
		public static final int LESS_OR_EQUAL = 0x07;
88
		/** default value to supply when the operator type is not used */
89
		public static final int IGNORED = BETWEEN;
90
		
91
		/* package */ public static void validateSecondArg(int comparisonOperator, String paramValue) {
92
			switch (comparisonOperator) {
93
				case BETWEEN:
94
				case NOT_BETWEEN:
95
					if (paramValue == null) {
96
						throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
97
					}
98
				// all other operators don't need second arg
99
			}
100
		}
101
	}
102
}
(-)src/java/org/apache/poi/ss/usermodel/DataValidationHelper.java (+33 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.ss.usermodel;
5
6
import org.apache.poi.ss.util.CellRangeAddressList;
7
8
/**
9
 * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
10
 * 
11
 */
12
public interface DataValidationHelper {
13
	
14
	DataValidationConstraint createFormulaListConstraint(String listFormula);
15
16
	DataValidationConstraint createExplicitListConstraint(String[] listOfValues);
17
18
	DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2);
19
	
20
	DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2);
21
	
22
	DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2);
23
	
24
	DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2);
25
	
26
	DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2,String dateFormat);
27
	
28
	DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2);
29
	
30
	DataValidationConstraint createCustomConstraint(String formula);
31
	
32
	DataValidation createValidation(DataValidationConstraint constraint,CellRangeAddressList cellRangeAddressList);
33
}
(-)src/java/org/apache/poi/ss/usermodel/Sheet.java (+11 lines)
Lines 19-24 Link Here
19
19
20
import java.util.Iterator;
20
import java.util.Iterator;
21
21
22
import org.apache.poi.hssf.record.DVRecord;
23
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
24
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
22
import org.apache.poi.hssf.util.PaneInformation;
25
import org.apache.poi.hssf.util.PaneInformation;
23
import org.apache.poi.ss.util.CellRangeAddress;
26
import org.apache.poi.ss.util.CellRangeAddress;
24
27
Lines 798-801 Link Here
798
     * @return the {@link CellRange} of cells affected by this change
801
     * @return the {@link CellRange} of cells affected by this change
799
     */
802
     */
800
    CellRange<? extends Cell> removeArrayFormula(Cell cell);
803
    CellRange<? extends Cell> removeArrayFormula(Cell cell);
804
    
805
    public DataValidationHelper getDataValidationHelper();
806
807
	/**
808
	 * Creates a data validation object
809
	 * @param dataValidation The Data validation object settings
810
	 */
811
	public void addValidationData(DataValidation dataValidation);
801
}
812
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidation.java (+243 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.xssf.usermodel;
5
6
import java.util.HashMap;
7
import java.util.Map;
8
9
import org.apache.poi.ss.usermodel.DataValidation;
10
import org.apache.poi.ss.usermodel.DataValidationConstraint;
11
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
12
import org.apache.poi.ss.util.CellRangeAddress;
13
import org.apache.poi.ss.util.CellRangeAddressList;
14
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
15
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationErrorStyle;
16
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator;
17
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
18
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator.Enum;
19
20
/**
21
 * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
22
 *
23
 */
24
public class XSSFDataValidation implements DataValidation {
25
	private CTDataValidation ctDdataValidation;
26
	private XSSFDataValidationConstraint validationConstraint;
27
	private CellRangeAddressList regions;
28
	public static Map<Integer,STDataValidationOperator.Enum> operatorTypeMappings = new HashMap<Integer,STDataValidationOperator.Enum>();
29
	public static Map<STDataValidationOperator.Enum,Integer> operatorTypeReverseMappings = new HashMap<STDataValidationOperator.Enum,Integer>();
30
	public static Map<Integer,STDataValidationType.Enum> validationTypeMappings = new HashMap<Integer,STDataValidationType.Enum>();
31
	public static Map<STDataValidationType.Enum,Integer> validationTypeReverseMappings = new HashMap<STDataValidationType.Enum,Integer>();
32
    public static Map<Integer,STDataValidationErrorStyle.Enum> errorStyleMappings = new HashMap<Integer,STDataValidationErrorStyle.Enum>();
33
    static {
34
		errorStyleMappings.put(DataValidation.ErrorStyle.INFO, STDataValidationErrorStyle.INFORMATION);
35
		errorStyleMappings.put(DataValidation.ErrorStyle.STOP, STDataValidationErrorStyle.STOP);
36
		errorStyleMappings.put(DataValidation.ErrorStyle.WARNING, STDataValidationErrorStyle.WARNING);
37
    }
38
	
39
    
40
	static {
41
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.BETWEEN,STDataValidationOperator.BETWEEN);
42
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_BETWEEN,STDataValidationOperator.NOT_BETWEEN);
43
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.EQUAL,STDataValidationOperator.EQUAL);
44
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_EQUAL,STDataValidationOperator.NOT_EQUAL);
45
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_THAN,STDataValidationOperator.GREATER_THAN);    	
46
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,STDataValidationOperator.GREATER_THAN_OR_EQUAL);
47
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_THAN,STDataValidationOperator.LESS_THAN);    	
48
		operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,STDataValidationOperator.LESS_THAN_OR_EQUAL);
49
		
50
		for( Map.Entry<Integer,STDataValidationOperator.Enum> entry : operatorTypeMappings.entrySet() ) {
51
			operatorTypeReverseMappings.put(entry.getValue(),entry.getKey());
52
		}
53
	}
54
55
	static {
56
		validationTypeMappings.put(DataValidationConstraint.ValidationType.FORMULA,STDataValidationType.CUSTOM);
57
		validationTypeMappings.put(DataValidationConstraint.ValidationType.DATE,STDataValidationType.DATE);
58
		validationTypeMappings.put(DataValidationConstraint.ValidationType.DECIMAL,STDataValidationType.DECIMAL);    	
59
		validationTypeMappings.put(DataValidationConstraint.ValidationType.LIST,STDataValidationType.LIST); 
60
		validationTypeMappings.put(DataValidationConstraint.ValidationType.ANY,STDataValidationType.NONE);
61
		validationTypeMappings.put(DataValidationConstraint.ValidationType.TEXT_LENGTH,STDataValidationType.TEXT_LENGTH);
62
		validationTypeMappings.put(DataValidationConstraint.ValidationType.TIME,STDataValidationType.TIME);  
63
		validationTypeMappings.put(DataValidationConstraint.ValidationType.INTEGER,STDataValidationType.WHOLE);
64
		
65
		for( Map.Entry<Integer,STDataValidationType.Enum> entry : validationTypeMappings.entrySet() ) {
66
			validationTypeReverseMappings.put(entry.getValue(),entry.getKey());
67
		}
68
	}
69
70
	
71
	XSSFDataValidation(CellRangeAddressList regions,CTDataValidation ctDataValidation) {
72
		super();
73
		this.validationConstraint = getConstraint(ctDataValidation);
74
		this.ctDdataValidation = ctDataValidation;
75
		this.regions = regions;
76
		this.ctDdataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
77
		this.ctDdataValidation.setAllowBlank(true);
78
	}	
79
80
	public XSSFDataValidation(XSSFDataValidationConstraint constraint,CellRangeAddressList regions,CTDataValidation ctDataValidation) {
81
		super();
82
		this.validationConstraint = constraint;
83
		this.ctDdataValidation = ctDataValidation;
84
		this.regions = regions;
85
		this.ctDdataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
86
		this.ctDdataValidation.setAllowBlank(true);
87
	}
88
 
89
	CTDataValidation getCtDdataValidation() {
90
		return ctDdataValidation;
91
	}
92
93
94
95
	/* (non-Javadoc)
96
	 * @see org.apache.poi.ss.usermodel.DataValidation#createErrorBox(java.lang.String, java.lang.String)
97
	 */
98
	public void createErrorBox(String title, String text) {
99
		ctDdataValidation.setErrorTitle(title);
100
		ctDdataValidation.setError(text);
101
	}
102
103
	/* (non-Javadoc)
104
	 * @see org.apache.poi.ss.usermodel.DataValidation#createPromptBox(java.lang.String, java.lang.String)
105
	 */
106
	public void createPromptBox(String title, String text) {
107
		ctDdataValidation.setPromptTitle(title);
108
		ctDdataValidation.setPrompt(text);
109
	}
110
111
	/* (non-Javadoc)
112
	 * @see org.apache.poi.ss.usermodel.DataValidation#getEmptyCellAllowed()
113
	 */
114
	public boolean getEmptyCellAllowed() {
115
		return ctDdataValidation.getAllowBlank();
116
	}
117
118
	/* (non-Javadoc)
119
	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorBoxText()
120
	 */
121
	public String getErrorBoxText() {
122
		return ctDdataValidation.getError();
123
	}
124
125
	/* (non-Javadoc)
126
	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorBoxTitle()
127
	 */
128
	public String getErrorBoxTitle() {
129
		return ctDdataValidation.getErrorTitle();
130
	}
131
132
	/* (non-Javadoc)
133
	 * @see org.apache.poi.ss.usermodel.DataValidation#getErrorStyle()
134
	 */
135
	public int getErrorStyle() {
136
		return ctDdataValidation.getErrorStyle().intValue();
137
	}
138
139
	/* (non-Javadoc)
140
	 * @see org.apache.poi.ss.usermodel.DataValidation#getPromptBoxText()
141
	 */
142
	public String getPromptBoxText() {
143
		return ctDdataValidation.getPrompt();
144
	}
145
146
	/* (non-Javadoc)
147
	 * @see org.apache.poi.ss.usermodel.DataValidation#getPromptBoxTitle()
148
	 */
149
	public String getPromptBoxTitle() {
150
		return ctDdataValidation.getPromptTitle();
151
	}
152
153
	/* (non-Javadoc)
154
	 * @see org.apache.poi.ss.usermodel.DataValidation#getShowErrorBox()
155
	 */
156
	public boolean getShowErrorBox() {
157
		return ctDdataValidation.getShowErrorMessage();
158
	}
159
160
	/* (non-Javadoc)
161
	 * @see org.apache.poi.ss.usermodel.DataValidation#getShowPromptBox()
162
	 */
163
	public boolean getShowPromptBox() {
164
		return ctDdataValidation.getShowInputMessage();
165
	}
166
167
	/* (non-Javadoc)
168
	 * @see org.apache.poi.ss.usermodel.DataValidation#getSuppressDropDownArrow()
169
	 */
170
	public boolean getSuppressDropDownArrow() {
171
		return !ctDdataValidation.getShowDropDown();
172
	}
173
174
	/* (non-Javadoc)
175
	 * @see org.apache.poi.ss.usermodel.DataValidation#getValidationConstraint()
176
	 */
177
	public DataValidationConstraint getValidationConstraint() {
178
		return validationConstraint;
179
	}
180
181
	/* (non-Javadoc)
182
	 * @see org.apache.poi.ss.usermodel.DataValidation#setEmptyCellAllowed(boolean)
183
	 */
184
	public void setEmptyCellAllowed(boolean allowed) {
185
		ctDdataValidation.setAllowBlank(allowed);
186
	}
187
188
	/* (non-Javadoc)
189
	 * @see org.apache.poi.ss.usermodel.DataValidation#setErrorStyle(int)
190
	 */
191
	public void setErrorStyle(int errorStyle) {
192
		ctDdataValidation.setErrorStyle(errorStyleMappings.get(errorStyle));
193
	}
194
195
	/* (non-Javadoc)
196
	 * @see org.apache.poi.ss.usermodel.DataValidation#setShowErrorBox(boolean)
197
	 */
198
	public void setShowErrorBox(boolean show) {
199
		ctDdataValidation.setShowErrorMessage(show);
200
	}
201
202
	/* (non-Javadoc)
203
	 * @see org.apache.poi.ss.usermodel.DataValidation#setShowPromptBox(boolean)
204
	 */
205
	public void setShowPromptBox(boolean show) {
206
		ctDdataValidation.setShowInputMessage(show);
207
	}
208
209
	/* (non-Javadoc)
210
	 * @see org.apache.poi.ss.usermodel.DataValidation#setSuppressDropDownArrow(boolean)
211
	 */
212
	public void setSuppressDropDownArrow(boolean suppress) {
213
		if (validationConstraint.getValidationType()==ValidationType.LIST) {
214
			ctDdataValidation.setShowDropDown(!suppress);
215
		}
216
	}
217
218
	public CellRangeAddressList getRegions() {
219
		return regions;
220
	}
221
	
222
	public String prettyPrint() {
223
		StringBuilder builder = new StringBuilder();
224
		for(CellRangeAddress address : regions.getCellRangeAddresses()) {
225
			builder.append(address.formatAsString());
226
		}
227
		builder.append(" => ");
228
		builder.append(this.validationConstraint.prettyPrint());	
229
		return builder.toString();
230
	}
231
	
232
    private XSSFDataValidationConstraint getConstraint(CTDataValidation ctDataValidation) {
233
    	XSSFDataValidationConstraint constraint = null;
234
    	String formula1 = ctDataValidation.getFormula1();
235
    	String formula2 = ctDataValidation.getFormula2();
236
    	Enum operator = ctDataValidation.getOperator();
237
    	org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType.Enum type = ctDataValidation.getType();
238
		Integer validationType = XSSFDataValidation.validationTypeReverseMappings.get(type);
239
		Integer operatorType = XSSFDataValidation.operatorTypeReverseMappings.get(operator);
240
		constraint = new XSSFDataValidationConstraint(validationType,operatorType, formula1,formula2);
241
    	return constraint;
242
    }
243
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java (+194 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.xssf.usermodel;
5
6
import java.util.Arrays;
7
8
import org.apache.poi.ss.usermodel.DataValidationConstraint;
9
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
10
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator.Enum;
11
12
/**
13
 * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
14
 *
15
 */
16
public class XSSFDataValidationConstraint implements DataValidationConstraint {
17
	private String formula1;
18
	private String formula2;
19
	private int validationType = -1;
20
	private int operator = -1;
21
	private String[] explicitListOfValues;
22
23
	public XSSFDataValidationConstraint(String[] explicitListOfValues) {
24
		if( explicitListOfValues==null || explicitListOfValues.length==0) {
25
			throw new IllegalArgumentException("List validation with explicit values must specify at least one value");
26
		}
27
		this.validationType = ValidationType.LIST;
28
		setExplicitListValues(explicitListOfValues);
29
		
30
		validate();
31
	}
32
	
33
	public XSSFDataValidationConstraint(int validationType,String formula1) {
34
		super();
35
		setFormula1(formula1);
36
		this.validationType = validationType;
37
		validate();
38
	}
39
40
41
42
	public XSSFDataValidationConstraint(int validationType, int operator,String formula1) {
43
		super();
44
		setFormula1(formula1);
45
		this.validationType = validationType;
46
		this.operator = operator;
47
		validate();
48
	}
49
50
	public XSSFDataValidationConstraint(int validationType, int operator,String formula1, String formula2) {
51
		super();
52
		setFormula1(formula1);
53
		setFormula2(formula2);
54
		this.validationType = validationType;
55
		this.operator = operator;
56
		
57
		validate();
58
		
59
		//FIXME: Need to confirm if this is not a formula.
60
		if( ValidationType.LIST==validationType) {
61
			explicitListOfValues = formula1.split(",");
62
		}
63
	}
64
65
	/* (non-Javadoc)
66
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getExplicitListValues()
67
	 */
68
	public String[] getExplicitListValues() {
69
		return explicitListOfValues;
70
	}
71
72
	/* (non-Javadoc)
73
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getFormula1()
74
	 */
75
	public String getFormula1() {
76
		return formula1;
77
	}
78
79
	/* (non-Javadoc)
80
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getFormula2()
81
	 */
82
	public String getFormula2() {
83
		return formula2;
84
	}
85
86
	/* (non-Javadoc)
87
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getOperator()
88
	 */
89
	public int getOperator() {
90
		return operator;
91
	}
92
93
	/* (non-Javadoc)
94
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#getValidationType()
95
	 */
96
	public int getValidationType() {
97
		return validationType;
98
	}
99
100
	/* (non-Javadoc)
101
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setExplicitListValues(java.lang.String[])
102
	 */
103
	public void setExplicitListValues(String[] explicitListValues) {
104
		this.explicitListOfValues = explicitListValues;
105
		if( explicitListOfValues!=null && explicitListOfValues.length > 0 ) {
106
			StringBuilder builder = new StringBuilder("\"");
107
			for (int i = 0; i < explicitListValues.length; i++) {
108
				String string = explicitListValues[i];
109
				if( builder.length() > 1) {
110
					builder.append(",");
111
				}
112
				builder.append(string);
113
			}
114
			builder.append("\"");
115
			setFormula1(builder.toString());			
116
		}
117
	}
118
119
	/* (non-Javadoc)
120
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setFormula1(java.lang.String)
121
	 */
122
	public void setFormula1(String formula1) {
123
		this.formula1 = removeLeadingEquals(formula1);
124
	}
125
126
	protected String removeLeadingEquals(String formula1) {
127
		return isFormulaEmpty(formula1) ? formula1 : formula1.charAt(0)=='=' ? formula1.substring(1) : formula1;
128
	}
129
130
	/* (non-Javadoc)
131
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setFormula2(java.lang.String)
132
	 */
133
	public void setFormula2(String formula2) {
134
		this.formula2 = removeLeadingEquals(formula2);
135
	}
136
137
	/* (non-Javadoc)
138
	 * @see org.apache.poi.ss.usermodel.DataValidationConstraint#setOperator(int)
139
	 */
140
	public void setOperator(int operator) {
141
		this.operator = operator;
142
	}
143
144
	public void validate() {
145
		if (validationType==ValidationType.ANY) {
146
			return;
147
		}
148
		
149
		if (validationType==ValidationType.LIST ) {
150
			if (isFormulaEmpty(formula1)) {
151
				throw new IllegalArgumentException("A valid formula or a list of values must be specified for list validation.");
152
			}
153
		} else  {
154
			if( isFormulaEmpty(formula1) ) {
155
				throw new IllegalArgumentException("Formula is not specified. Formula is required for all validation types except explicit list validation.");
156
			}
157
			
158
			if( validationType!= ValidationType.FORMULA ) {
159
				if (operator==-1) {
160
					throw new IllegalArgumentException("This validation type requires an operator to be specified.");
161
				} else if (( operator==OperatorType.BETWEEN || operator==OperatorType.NOT_BETWEEN) && isFormulaEmpty(formula2)) {
162
					throw new IllegalArgumentException("Between and not between comparisons require two formulae to be specified.");
163
				}
164
			}
165
		}
166
	}
167
168
	protected boolean isFormulaEmpty(String formula1) {
169
		return formula1 == null || formula1.trim().length()==0;
170
	}
171
	
172
	public String prettyPrint() {
173
		StringBuilder builder = new StringBuilder();
174
		STDataValidationType.Enum vt = XSSFDataValidation.validationTypeMappings.get(validationType);
175
		Enum ot = XSSFDataValidation.operatorTypeMappings.get(operator);
176
		builder.append(vt);
177
		builder.append(' ');
178
		if (validationType!=ValidationType.ANY) {
179
			if (validationType != ValidationType.LIST && validationType != ValidationType.ANY && validationType != ValidationType.FORMULA) {
180
				builder.append(",").append(ot).append(", ");
181
			}
182
			final String QUOTE = "";
183
			if (validationType == ValidationType.LIST && explicitListOfValues != null) {
184
				builder.append(QUOTE).append(Arrays.asList(explicitListOfValues)).append(QUOTE).append(' ');
185
			} else {
186
				builder.append(QUOTE).append(formula1).append(QUOTE).append(' ');
187
			}
188
			if (formula2 != null) {
189
				builder.append(QUOTE).append(formula2).append(QUOTE).append(' ');
190
			}
191
		}
192
		return builder.toString();
193
	}
194
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationHelper.java (+155 lines)
Line 0 Link Here
1
/**
2
 * 
3
 */
4
package org.apache.poi.xssf.usermodel;
5
6
import java.util.ArrayList;
7
import java.util.List;
8
9
import org.apache.poi.ss.usermodel.DataValidation;
10
import org.apache.poi.ss.usermodel.DataValidationConstraint;
11
import org.apache.poi.ss.usermodel.DataValidationHelper;
12
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
13
import org.apache.poi.ss.util.CellRangeAddress;
14
import org.apache.poi.ss.util.CellRangeAddressList;
15
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
16
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
17
18
/**
19
 * @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
20
 *
21
 */
22
public class XSSFDataValidationHelper implements DataValidationHelper {
23
	private XSSFSheet xssfSheet;
24
	
25
    
26
    public XSSFDataValidationHelper(XSSFSheet xssfSheet) {
27
		super();
28
		this.xssfSheet = xssfSheet;
29
	}
30
31
	/* (non-Javadoc)
32
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createDateConstraint(int, java.lang.String, java.lang.String, java.lang.String)
33
	 */
34
	public DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2, String dateFormat) {
35
		return new XSSFDataValidationConstraint(ValidationType.DATE, operatorType,formula1, formula2);
36
	}
37
38
	/* (non-Javadoc)
39
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createDecimalConstraint(int, java.lang.String, java.lang.String)
40
	 */
41
	public DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2) {
42
		return new XSSFDataValidationConstraint(ValidationType.DECIMAL, operatorType,formula1, formula2);
43
	}
44
45
	/* (non-Javadoc)
46
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createExplicitListConstraint(java.lang.String[])
47
	 */
48
	public DataValidationConstraint createExplicitListConstraint(String[] listOfValues) {
49
		return new XSSFDataValidationConstraint(listOfValues);
50
	}
51
52
	/* (non-Javadoc)
53
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createFormulaListConstraint(java.lang.String)
54
	 */
55
	public DataValidationConstraint createFormulaListConstraint(String listFormula) {
56
		return new XSSFDataValidationConstraint(ValidationType.LIST, listFormula);
57
	}
58
59
	
60
	
61
	public DataValidationConstraint createNumericConstraint(int validationType, int operatorType, String formula1, String formula2) {
62
		if( validationType==ValidationType.INTEGER) {
63
			return createIntegerConstraint(operatorType, formula1, formula2);
64
		} else if ( validationType==ValidationType.DECIMAL) {
65
			return createDecimalConstraint(operatorType, formula1, formula2);
66
		} else if ( validationType==ValidationType.TEXT_LENGTH) {
67
			return createTextLengthConstraint(operatorType, formula1, formula2);
68
		}
69
		return null;
70
	}
71
72
	/* (non-Javadoc)
73
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createIntegerConstraint(int, java.lang.String, java.lang.String)
74
	 */
75
	public DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2) {
76
		return new XSSFDataValidationConstraint(ValidationType.INTEGER, operatorType,formula1,formula2);
77
	}
78
79
	/* (non-Javadoc)
80
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createTextLengthConstraint(int, java.lang.String, java.lang.String)
81
	 */
82
	public DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2) {
83
		return new XSSFDataValidationConstraint(ValidationType.TEXT_LENGTH, operatorType,formula1,formula2);
84
	}
85
86
	/* (non-Javadoc)
87
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createTimeConstraint(int, java.lang.String, java.lang.String, java.lang.String)
88
	 */
89
	public DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2) {
90
		return new XSSFDataValidationConstraint(ValidationType.TIME, operatorType,formula1,formula2);
91
	}
92
93
	public DataValidationConstraint createCustomConstraint(String formula) {
94
		return new XSSFDataValidationConstraint(ValidationType.FORMULA, formula);
95
	}
96
97
	/* (non-Javadoc)
98
	 * @see org.apache.poi.ss.usermodel.DataValidationHelper#createValidation(org.apache.poi.ss.usermodel.DataValidationConstraint, org.apache.poi.ss.util.CellRangeAddressList)
99
	 */
100
	public DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) {
101
		XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
102
		CTDataValidation newDataValidation = CTDataValidation.Factory.newInstance();
103
104
		int validationType = constraint.getValidationType();
105
		switch(validationType) {
106
			case DataValidationConstraint.ValidationType.LIST:
107
		    	newDataValidation.setType(STDataValidationType.LIST);
108
				newDataValidation.setFormula1(constraint.getFormula1());				
109
		    	break;
110
			case DataValidationConstraint.ValidationType.ANY:				
111
				newDataValidation.setType(STDataValidationType.NONE);				
112
				break;
113
			case DataValidationConstraint.ValidationType.TEXT_LENGTH:
114
				newDataValidation.setType(STDataValidationType.TEXT_LENGTH);
115
				break;				
116
			case DataValidationConstraint.ValidationType.DATE:
117
				newDataValidation.setType(STDataValidationType.DATE);
118
				break;				
119
			case DataValidationConstraint.ValidationType.INTEGER:
120
				newDataValidation.setType(STDataValidationType.WHOLE);
121
				break;				
122
			case DataValidationConstraint.ValidationType.DECIMAL:
123
				newDataValidation.setType(STDataValidationType.DECIMAL);
124
				break;				
125
			case DataValidationConstraint.ValidationType.TIME:
126
				newDataValidation.setType(STDataValidationType.TIME);
127
				break;
128
			case DataValidationConstraint.ValidationType.FORMULA:
129
				newDataValidation.setType(STDataValidationType.CUSTOM);
130
				break;
131
			default:
132
				newDataValidation.setType(STDataValidationType.NONE);				
133
		}
134
		
135
		if (validationType!=ValidationType.ANY && validationType!=ValidationType.LIST) {
136
			newDataValidation.setOperator(XSSFDataValidation.operatorTypeMappings.get(constraint.getOperator()));			
137
			if (constraint.getFormula1() != null) {
138
				newDataValidation.setFormula1(constraint.getFormula1());
139
			}
140
			if (constraint.getFormula2() != null) {
141
				newDataValidation.setFormula2(constraint.getFormula2());
142
			}
143
		}
144
		
145
		CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
146
		List<String> sqref = new ArrayList<String>();
147
		for (int i = 0; i < cellRangeAddresses.length; i++) {
148
			CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
149
			sqref.add(cellRangeAddress.formatAsString());
150
		}
151
		newDataValidation.setSqref(sqref);
152
		
153
		return new XSSFDataValidation(dataValidationConstraint,cellRangeAddressList,newDataValidation);
154
	}
155
}
(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (-1 / +85 lines)
Lines 42-52 Link Here
42
import org.apache.poi.ss.usermodel.Cell;
42
import org.apache.poi.ss.usermodel.Cell;
43
import org.apache.poi.ss.usermodel.CellRange;
43
import org.apache.poi.ss.usermodel.CellRange;
44
import org.apache.poi.ss.usermodel.CellStyle;
44
import org.apache.poi.ss.usermodel.CellStyle;
45
import org.apache.poi.ss.usermodel.DataValidation;
46
import org.apache.poi.ss.usermodel.DataValidationHelper;
45
import org.apache.poi.ss.usermodel.Footer;
47
import org.apache.poi.ss.usermodel.Footer;
46
import org.apache.poi.ss.usermodel.Header;
48
import org.apache.poi.ss.usermodel.Header;
47
import org.apache.poi.ss.usermodel.Row;
49
import org.apache.poi.ss.usermodel.Row;
48
import org.apache.poi.ss.usermodel.Sheet;
50
import org.apache.poi.ss.usermodel.Sheet;
49
import org.apache.poi.ss.util.CellRangeAddress;
51
import org.apache.poi.ss.util.CellRangeAddress;
52
import org.apache.poi.ss.util.CellRangeAddressList;
50
import org.apache.poi.ss.util.CellReference;
53
import org.apache.poi.ss.util.CellReference;
51
import org.apache.poi.ss.util.SSCellRange;
54
import org.apache.poi.ss.util.SSCellRange;
52
import org.apache.poi.util.Internal;
55
import org.apache.poi.util.Internal;
Lines 58-64 Link Here
58
import org.apache.xmlbeans.XmlException;
61
import org.apache.xmlbeans.XmlException;
59
import org.apache.xmlbeans.XmlOptions;
62
import org.apache.xmlbeans.XmlOptions;
60
import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
63
import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
61
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
64
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBreak;
65
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
66
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
67
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;
68
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols;
69
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
70
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
71
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
72
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidations;
73
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDrawing;
74
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHeaderFooter;
75
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink;
76
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing;
77
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
78
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
79
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOutlinePr;
80
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageBreak;
81
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageMargins;
82
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetUpPr;
83
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPane;
84
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPrintOptions;
85
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
86
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSelection;
87
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
88
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetData;
89
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr;
90
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetPr;
91
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;
92
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView;
93
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews;
94
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
95
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
96
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane;
97
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPaneState;
98
import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument;
62
99
63
/**
100
/**
64
 * High level representation of a SpreadsheetML worksheet.
101
 * High level representation of a SpreadsheetML worksheet.
Lines 82-87 Link Here
82
    private CommentsTable sheetComments;
119
    private CommentsTable sheetComments;
83
    private Map<Integer, XSSFCell> sharedFormulas;
120
    private Map<Integer, XSSFCell> sharedFormulas;
84
    private List<CellRangeAddress> arrayFormulas;
121
    private List<CellRangeAddress> arrayFormulas;
122
    private XSSFDataValidationHelper dataValidationHelper;    
85
123
86
    /**
124
    /**
87
     * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
125
     * Creates new XSSFSheet   - called by XSSFWorkbook to create a sheet from scratch.
Lines 90-95 Link Here
90
     */
128
     */
91
    protected XSSFSheet() {
129
    protected XSSFSheet() {
92
        super();
130
        super();
131
        dataValidationHelper = new XSSFDataValidationHelper(this);
93
        onDocumentCreate();
132
        onDocumentCreate();
94
    }
133
    }
95
134
Lines 102-107 Link Here
102
     */
141
     */
103
    protected XSSFSheet(PackagePart part, PackageRelationship rel) {
142
    protected XSSFSheet(PackagePart part, PackageRelationship rel) {
104
        super(part, rel);
143
        super(part, rel);
144
        dataValidationHelper = new XSSFDataValidationHelper(this);
105
    }
145
    }
106
146
107
    /**
147
    /**
Lines 2794-2797 Link Here
2794
        String ref = ((XSSFCell)cell).getCTCell().getR();
2834
        String ref = ((XSSFCell)cell).getCTCell().getR();
2795
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
2835
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
2796
    }
2836
    }
2837
2838
2839
	public DataValidationHelper getDataValidationHelper() {
2840
		return dataValidationHelper;
2841
	}
2842
    
2843
    public List<XSSFDataValidation> getDataValidations() {
2844
    	List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
2845
    	CTDataValidations dataValidations = this.worksheet.getDataValidations();
2846
    	if( dataValidations!=null && dataValidations.getCount() > 0 ) {
2847
    		List<CTDataValidation> dataValidationList = dataValidations.getDataValidationList();
2848
    		for (CTDataValidation ctDataValidation : dataValidationList) {
2849
    			CellRangeAddressList addressList = new CellRangeAddressList();
2850
    			
2851
    			@SuppressWarnings("unchecked")
2852
    			List<String> sqref = ctDataValidation.getSqref();
2853
    			for (String stRef : sqref) {
2854
    				String[] regions = stRef.split(" ");
2855
    				for (int i = 0; i < regions.length; i++) {
2856
						String[] parts = regions[i].split(":");
2857
						CellReference begin = new CellReference(parts[0]);
2858
						CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
2859
						CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
2860
						addressList.addCellRangeAddress(cellRangeAddress);
2861
					}
2862
				}
2863
				XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
2864
				xssfValidations.add(xssfDataValidation);
2865
			}
2866
    	}
2867
    	return xssfValidations;
2868
    }
2869
2870
	public void addValidationData(DataValidation dataValidation) {
2871
		XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation;		
2872
		CTDataValidations dataValidations = worksheet.getDataValidations();
2873
		if( dataValidations==null ) {
2874
			dataValidations = worksheet.addNewDataValidations();
2875
		}
2876
		int currentCount = dataValidations.getDataValidationList().size();
2877
		dataValidations.getDataValidationList().add(xssfDataValidation.getCtDdataValidation());		
2878
		dataValidations.setCount(currentCount + 1);
2879
2880
	}
2797
}
2881
}
(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataValidation.java (+242 lines)
Line 0 Link Here
1
package org.apache.poi.xssf.usermodel;
2
3
import java.io.File;
4
import java.io.FileInputStream;
5
import java.io.FileOutputStream;
6
import java.math.BigDecimal;
7
import java.util.List;
8
9
import junit.framework.TestCase;
10
11
import org.apache.poi.ss.usermodel.Cell;
12
import org.apache.poi.ss.usermodel.DataValidation;
13
import org.apache.poi.ss.usermodel.DataValidationConstraint;
14
import org.apache.poi.ss.usermodel.DataValidationHelper;
15
import org.apache.poi.ss.usermodel.Row;
16
import org.apache.poi.ss.usermodel.Sheet;
17
import org.apache.poi.ss.usermodel.Workbook;
18
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
19
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
20
import org.apache.poi.ss.util.CellRangeAddress;
21
import org.apache.poi.ss.util.CellRangeAddressList;
22
import org.apache.poi.ss.util.CellReference;
23
24
public class TestXSSFDataValidation extends TestCase {
25
	public void testAddValidations() throws Exception {
26
		File inputFile = new File("test-data/spreadsheet/DataValidations-49244.xlsx");
27
		File outputFile = new File("test-data/spreadsheet/DataValidations-49244-TestXSSFDataValidation_testAddValidations.xlsx");
28
		FileInputStream fis = new FileInputStream(inputFile);
29
		Workbook workbook = new XSSFWorkbook(fis);
30
		Sheet sheet = workbook.getSheetAt(0);
31
		List<XSSFDataValidation> dataValidations = ((XSSFSheet)sheet).getDataValidations();
32
		
33
/**
34
 * 		For each validation type, there are two cells with the same validation. This tests
35
 * 		application of a single validation definition to multiple cells.
36
 * 		
37
 * 		For list ( 3 validations for explicit and 3 for formula )
38
 * 			- one validation that allows blank. 
39
 * 			- one that does not allow blank.
40
 * 			- one that does not show the drop down arrow.
41
 * 		= 2
42
 * 
43
 * 		For number validations ( integer/decimal and text length ) with 8 different types of operators.
44
 *		= 50  
45
 * 
46
 * 		= 52 ( Total )
47
 */
48
		assertEquals(52,dataValidations.size());
49
//		for (XSSFDataValidation dataValidation : dataValidations) {
50
//			System.out.println(dataValidation.prettyPrint());
51
//		}
52
		
53
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
54
		int[] validationTypes = new int[]{ValidationType.INTEGER,ValidationType.DECIMAL,ValidationType.TEXT_LENGTH};
55
		
56
		int[] singleOperandOperatorTypes = new int[]{
57
				OperatorType.LESS_THAN,OperatorType.LESS_OR_EQUAL,
58
				OperatorType.GREATER_THAN,OperatorType.GREATER_OR_EQUAL,
59
				OperatorType.EQUAL,OperatorType.NOT_EQUAL
60
				} ;
61
		int[] doubleOperandOperatorTypes = new int[]{
62
				OperatorType.BETWEEN,OperatorType.NOT_BETWEEN
63
		};
64
		
65
		BigDecimal value  = new BigDecimal("10"),value2 = new BigDecimal("20");
66
		BigDecimal dvalue = new BigDecimal("10.001"),dvalue2 = new BigDecimal("19.999");
67
		final int lastRow = sheet.getLastRowNum();
68
		int offset = lastRow + 3;
69
		
70
		int lastKnownNumValidations = dataValidations.size();
71
		
72
		Row row = sheet.createRow(offset++);
73
		Cell cell = row.createCell(0);
74
		DataValidationConstraint explicitListValidation = dataValidationHelper.createExplicitListConstraint(new String[]{"MA","MI","CA"});
75
		CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
76
		cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
77
		DataValidation dataValidation = dataValidationHelper.createValidation(explicitListValidation, cellRangeAddressList);
78
		setOtherValidationParameters(dataValidation);
79
		sheet.addValidationData(dataValidation);
80
		lastKnownNumValidations++;
81
		
82
		row = sheet.createRow(offset++);
83
		cell = row.createCell(0);
84
85
		cellRangeAddressList = new CellRangeAddressList();
86
		cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
87
		
88
		Cell firstCell =  row.createCell(1);firstCell.setCellValue("UT");
89
		Cell secondCell = row.createCell(2);secondCell.setCellValue("MN");
90
		Cell thirdCell  = row.createCell(3);thirdCell.setCellValue("IL");
91
		
92
		int rowNum = row.getRowNum() + 1;
93
		String listFormula = new StringBuilder("$B$").append(rowNum).append(":").append("$D$").append(rowNum).toString();
94
		System.out.println(listFormula);
95
		DataValidationConstraint formulaListValidation = dataValidationHelper.createFormulaListConstraint(listFormula);
96
		
97
		dataValidation = dataValidationHelper.createValidation(formulaListValidation, cellRangeAddressList);
98
		setOtherValidationParameters(dataValidation);
99
		sheet.addValidationData(dataValidation);
100
		lastKnownNumValidations++;
101
		
102
		offset++;
103
		offset++;
104
		
105
		for (int i = 0; i < validationTypes.length; i++) {
106
			int validationType = validationTypes[i];
107
			offset = offset + 2;
108
			final Row row0 = sheet.createRow(offset++);			
109
			Cell cell_10 = row0.createCell(0);
110
			cell_10.setCellValue(validationType==ValidationType.DECIMAL ? "Decimal " : validationType==ValidationType.INTEGER ? "Integer" : "Text Length");
111
			offset++;
112
			for (int j = 0; j < singleOperandOperatorTypes.length; j++) {
113
				int operatorType = singleOperandOperatorTypes[j];
114
				final Row row1 = sheet.createRow(offset++);
115
				
116
				//For Integer (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
117
				final Row row2 = i==0 && j < 2 ? sheet.createRow(offset++) : null;
118
				
119
				cell_10 = row1.createCell(0);
120
				cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());				
121
				Cell cell_11 = row1.createCell(1);
122
				Cell cell_21 = row1.createCell(2);
123
				Cell cell_22 = i==0 && j < 2 ? row2.createCell(2) : null;
124
				
125
				Cell cell_13 = row1.createCell(3);
126
				
127
				
128
				cell_13.setCellType(Cell.CELL_TYPE_NUMERIC);				
129
				cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
130
131
				
132
				//First create value based validation;
133
				DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, value.toString(), null);
134
				cellRangeAddressList = new CellRangeAddressList();
135
				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(),cell_11.getRowIndex(),cell_11.getColumnIndex(),cell_11.getColumnIndex()));
136
				DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
137
				setOtherValidationParameters(validation);
138
				sheet.addValidationData(validation);
139
				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
140
				
141
				//Now create real formula based validation.
142
				String formula1 = new CellReference(cell_13.getRowIndex(),cell_13.getColumnIndex()).formatAsString();
143
				constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, formula1, null);
144
				if (i==0 && j==0) {
145
					cellRangeAddressList = new CellRangeAddressList();
146
					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
147
					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
148
					setOtherValidationParameters(validation);
149
					sheet.addValidationData(validation);
150
					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
151
					
152
					cellRangeAddressList = new CellRangeAddressList();
153
					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
154
					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
155
					setOtherValidationParameters( validation);
156
					sheet.addValidationData(validation);
157
					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
158
				} else if(i==0 && j==1 ){
159
					cellRangeAddressList = new CellRangeAddressList();					
160
					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
161
					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
162
					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
163
					setOtherValidationParameters( validation);
164
					sheet.addValidationData(validation);
165
					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
166
				} else {
167
					cellRangeAddressList = new CellRangeAddressList();
168
					cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
169
					validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
170
					setOtherValidationParameters(validation);
171
					sheet.addValidationData(validation);
172
					assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
173
				}
174
			}
175
176
			for (int j = 0; j < doubleOperandOperatorTypes.length; j++) {
177
				int operatorType = doubleOperandOperatorTypes[j];
178
				final Row row1 = sheet.createRow(offset++);
179
				
180
				cell_10 = row1.createCell(0);
181
				cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());				
182
				
183
				Cell cell_11 = row1.createCell(1);
184
				Cell cell_21 = row1.createCell(2);
185
				
186
				Cell cell_13 = row1.createCell(3);
187
				Cell cell_14 = row1.createCell(4);
188
				
189
				
190
				String value1String = validationType==ValidationType.DECIMAL ? dvalue.toString() : value.toString();
191
				cell_13.setCellType(Cell.CELL_TYPE_NUMERIC);				
192
				cell_13.setCellValue(validationType==ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
193
194
				String value2String = validationType==ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
195
				cell_14.setCellType(Cell.CELL_TYPE_NUMERIC);
196
				cell_14.setCellValue(validationType==ValidationType.DECIMAL ? dvalue2.doubleValue() : value2.intValue());
197
				
198
				
199
				//First create value based validation;
200
				DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, value1String, value2String);
201
				cellRangeAddressList = new CellRangeAddressList();
202
				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(),cell_11.getRowIndex(),cell_11.getColumnIndex(),cell_11.getColumnIndex()));
203
				DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
204
				setOtherValidationParameters(validation);
205
				sheet.addValidationData(validation);
206
				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
207
				
208
				
209
				//Now create real formula based validation.
210
				String formula1 = new CellReference(cell_13.getRowIndex(),cell_13.getColumnIndex()).formatAsString();
211
				String formula2 = new CellReference(cell_14.getRowIndex(),cell_14.getColumnIndex()).formatAsString();
212
				constraint = dataValidationHelper.createNumericConstraint(validationType,operatorType, formula1, formula2);
213
				cellRangeAddressList = new CellRangeAddressList();
214
				cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(),cell_21.getRowIndex(),cell_21.getColumnIndex(),cell_21.getColumnIndex()));
215
				validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
216
				
217
				setOtherValidationParameters(validation);
218
				sheet.addValidationData(validation);	
219
				assertEquals(++lastKnownNumValidations,((XSSFSheet)sheet).getDataValidations().size());
220
			}
221
		}
222
		FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
223
		workbook.write(fileOutputStream);
224
		fileOutputStream.flush();
225
		fileOutputStream.close();
226
		
227
		fis = new FileInputStream(outputFile);
228
		workbook = new XSSFWorkbook(fis);
229
		Sheet sheetAt = workbook.getSheetAt(0);
230
		assertEquals(lastKnownNumValidations,((XSSFSheet)sheetAt).getDataValidations().size());
231
	}
232
233
	protected void setOtherValidationParameters(DataValidation validation) {
234
		boolean yesNo = true;
235
		validation.setEmptyCellAllowed(yesNo);
236
		validation.setShowErrorBox(yesNo);
237
		validation.setShowPromptBox(yesNo);
238
		validation.createErrorBox("Error Message Title", "Error Message");
239
		validation.createPromptBox("Prompt", "Enter some value");
240
		validation.setSuppressDropDownArrow(yesNo);
241
	}
242
}
(-)src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java (-14 / +30 lines)
Lines 35-40 Link Here
35
import org.apache.poi.hssf.record.RecordFormatException;
35
import org.apache.poi.hssf.record.RecordFormatException;
36
import org.apache.poi.hssf.util.HSSFColor;
36
import org.apache.poi.hssf.util.HSSFColor;
37
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
37
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
38
import org.apache.poi.ss.usermodel.DataValidation;
39
import org.apache.poi.ss.usermodel.DataValidationConstraint;
40
import org.apache.poi.ss.usermodel.DataValidationHelper;
41
import org.apache.poi.ss.usermodel.Sheet;
38
import org.apache.poi.ss.util.CellRangeAddress;
42
import org.apache.poi.ss.util.CellRangeAddress;
39
import org.apache.poi.ss.util.CellRangeAddressList;
43
import org.apache.poi.ss.util.CellRangeAddressList;
40
44
Lines 48-56 Link Here
48
	/** Convenient access to ERROR_STYLE constants */
52
	/** Convenient access to ERROR_STYLE constants */
49
	/*package*/ static final HSSFDataValidation.ErrorStyle ES = null;
53
	/*package*/ static final HSSFDataValidation.ErrorStyle ES = null;
50
	/** Convenient access to OPERATOR constants */
54
	/** Convenient access to OPERATOR constants */
51
	/*package*/ static final DVConstraint.ValidationType VT = null;
55
	/*package*/ static final DataValidationConstraint.ValidationType VT = null;
52
	/** Convenient access to OPERATOR constants */
56
	/** Convenient access to OPERATOR constants */
53
	/*package*/ static final DVConstraint.OperatorType OP = null;
57
	/*package*/ static final DataValidationConstraint.OperatorType OP = null;
54
58
55
	private static void log(String msg) {
59
	private static void log(String msg) {
56
		if (false) { // successful tests should be silent
60
		if (false) { // successful tests should be silent
Lines 92-100 Link Here
92
				String[] explicitListValues) {
96
				String[] explicitListValues) {
93
			int rowNum = _currentRowIndex++;
97
			int rowNum = _currentRowIndex++;
94
98
95
			DVConstraint dc = createConstraint(operatorType, firstFormula, secondFormula, explicitListValues);
99
			DataValidationHelper dataValidationHelper = _sheet.getDataValidationHelper();
100
			DataValidationConstraint dc = createConstraint(dataValidationHelper,operatorType, firstFormula, secondFormula, explicitListValues);
96
101
97
			HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc);
102
			DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(rowNum, rowNum, 0, 0));
98
			
103
			
99
			dv.setEmptyCellAllowed(allowEmpty);
104
			dv.setEmptyCellAllowed(allowEmpty);
100
			dv.setErrorStyle(errorStyle);
105
			dv.setErrorStyle(errorStyle);
Lines 116-139 Link Here
116
			}
121
			}
117
			writeOtherSettings(_sheet, _style_1, promptDescr);
122
			writeOtherSettings(_sheet, _style_1, promptDescr);
118
		}
123
		}
119
		private DVConstraint createConstraint(int operatorType, String firstFormula,
124
		private DataValidationConstraint createConstraint(DataValidationHelper dataValidationHelper,int operatorType, String firstFormula,
120
				String secondFormula, String[] explicitListValues) {
125
				String secondFormula, String[] explicitListValues) {
121
			if (_validationType == VT.LIST) {
126
			if (_validationType == VT.LIST) {
122
				if (explicitListValues != null) {
127
				if (explicitListValues != null) {
123
					return DVConstraint.createExplicitListConstraint(explicitListValues);
128
					return dataValidationHelper.createExplicitListConstraint(explicitListValues);
124
				}
129
				}
125
				return DVConstraint.createFormulaListConstraint(firstFormula);
130
				return dataValidationHelper.createFormulaListConstraint(firstFormula);
126
			}
131
			}
127
			if (_validationType == VT.TIME) {
132
			if (_validationType == VT.TIME) {
128
				return DVConstraint.createTimeConstraint(operatorType, firstFormula, secondFormula);
133
				return dataValidationHelper.createTimeConstraint(operatorType, firstFormula, secondFormula);
129
			}
134
			}
130
			if (_validationType == VT.DATE) {
135
			if (_validationType == VT.DATE) {
131
				return DVConstraint.createDateConstraint(operatorType, firstFormula, secondFormula, null);
136
				return dataValidationHelper.createDateConstraint(operatorType, firstFormula, secondFormula, null);
132
			}
137
			}
133
			if (_validationType == VT.FORMULA) {
138
			if (_validationType == VT.FORMULA) {
134
				return DVConstraint.createCustomFormulaConstraint(firstFormula);
139
				return dataValidationHelper.createCustomConstraint(firstFormula);
135
			}
140
			}
136
			return DVConstraint.createNumericConstraint(_validationType, operatorType, firstFormula, secondFormula);
141
			
142
			if( _validationType == VT.INTEGER) {
143
				return dataValidationHelper.createIntegerConstraint(operatorType, firstFormula, secondFormula);
144
			}
145
			if( _validationType == VT.DECIMAL) {
146
				return dataValidationHelper.createDecimalConstraint(operatorType, firstFormula, secondFormula);
147
			}			
148
			if( _validationType == VT.TEXT_LENGTH) {
149
				return dataValidationHelper.createTextLengthConstraint(operatorType, firstFormula, secondFormula);
150
			}
151
			return null;
137
		}
152
		}
138
		/**
153
		/**
139
		 * writes plain text values into cells in a tabular format to form comments readable from within 
154
		 * writes plain text values into cells in a tabular format to form comments readable from within 
Lines 572-580 Link Here
572
		// and then deleting the row that contains the cell.
587
		// and then deleting the row that contains the cell.
573
		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");  
588
		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");  
574
		int dvRow = 0;
589
		int dvRow = 0;
575
		HSSFSheet sheet = wb.getSheetAt(0);
590
		Sheet sheet = wb.getSheetAt(0);
576
		DVConstraint dc = DVConstraint.createNumericConstraint(VT.INTEGER, OP.EQUAL, "42", null);
591
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
577
		HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc);
592
		DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OP.EQUAL, "42", null);
593
		DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(dvRow, dvRow, 0, 0));
578
		
594
		
579
		dv.setEmptyCellAllowed(false);
595
		dv.setEmptyCellAllowed(false);
580
		dv.setErrorStyle(ES.STOP);
596
		dv.setErrorStyle(ES.STOP);
(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (-3 / +6 lines)
Lines 42-47 Link Here
42
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
42
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
43
import org.apache.poi.hssf.usermodel.RecordInspector.RecordCollector;
43
import org.apache.poi.hssf.usermodel.RecordInspector.RecordCollector;
44
import org.apache.poi.ss.usermodel.BaseTestSheet;
44
import org.apache.poi.ss.usermodel.BaseTestSheet;
45
import org.apache.poi.ss.usermodel.DataValidation;
46
import org.apache.poi.ss.usermodel.DataValidationConstraint;
47
import org.apache.poi.ss.usermodel.DataValidationHelper;
45
import org.apache.poi.ss.util.CellRangeAddress;
48
import org.apache.poi.ss.util.CellRangeAddress;
46
import org.apache.poi.ss.util.CellRangeAddressList;
49
import org.apache.poi.ss.util.CellRangeAddressList;
47
import org.apache.poi.util.TempFile;
50
import org.apache.poi.util.TempFile;
Lines 382-391 Link Here
382
        HSSFSheet sheet = workbook.createSheet("Sheet1");
385
        HSSFSheet sheet = workbook.createSheet("Sheet1");
383
        sheet.protectSheet("secret");
386
        sheet.protectSheet("secret");
384
387
385
        DVConstraint dvc = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,
388
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
386
                                                DVConstraint.OperatorType.BETWEEN, "10", "100");
389
        DataValidationConstraint dvc = dataValidationHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, "10", "100");
387
        CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
390
        CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
388
        HSSFDataValidation dv = new HSSFDataValidation(numericCellAddressList, dvc);
391
        DataValidation dv = dataValidationHelper.createValidation(dvc,numericCellAddressList);
389
        try {
392
        try {
390
            sheet.addValidationData(dv);
393
            sheet.addValidationData(dv);
391
        } catch (IllegalStateException e) {
394
        } catch (IllegalStateException e) {

Return to bug 49244