Free cookie consent management tool by TermsFeed Policy Generator

source: branches/2989_MovingPeaksBenchmark/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/DataValidation/ExcelDataValidationCollection.cs

Last change on this file was 12074, checked in by sraggl, 10 years ago

#2341: Added EPPlus-4.0.3 to ExtLibs

File size: 16.5 KB
Line 
1/*******************************************************************************
2 * You may amend and distribute as you like, but don't remove this header!
3 *
4 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
5 * See http://www.codeplex.com/EPPlus for details.
6 *
7 * Copyright (C) 2011  Jan Källman
8 *
9 * This library is free software; you can redistribute it and/or
10 * modify it under the terms of the GNU Lesser General Public
11 * License as published by the Free Software Foundation; either
12 * version 2.1 of the License, or (at your option) any later version.
13
14 * This library is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
17 * See the GNU Lesser General Public License for more details.
18 *
19 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
20 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
21 *
22 * All code and executables are provided "as is" with no warranty either express or implied.
23 * The author accepts no liability for any damage or loss of business that this product may cause.
24 *
25 * Code change notes:
26 *
27 * Author             Change            Date
28 * ******************************************************************************
29 * Mats Alm                       Added                   2011-01-01
30 * Mats Alm                         Applying patch submitted    2011-11-14
31 *                                  by Ted Heatherington
32 * Jan Källman                    License changed GPL-->LGPL  2011-12-27
33 * Raziq York                   Added support for Any type  2014-08-08
34*******************************************************************************/
35using System;
36using System.Collections.Generic;
37using System.Linq;
38using System.Text;
39using System.Collections;
40using OfficeOpenXml.Utils;
41using System.Xml;
42using OfficeOpenXml.DataValidation.Contracts;
43
44namespace OfficeOpenXml.DataValidation
45{
46    /// <summary>
47    /// <para>
48    /// Collection of <see cref="ExcelDataValidation"/>. This class is providing the API for EPPlus data validation.
49    /// </para>
50    /// <para>
51    /// The public methods of this class (Add[...]Validation) will create a datavalidation entry in the worksheet. When this
52    /// validation has been created changes to the properties will affect the workbook immediately.
53    /// </para>
54    /// <para>
55    /// Each type of validation has either a formula or a typed value/values, except for custom validation which has a formula only.
56    /// </para>
57    /// <code>
58    /// // Add a date time validation
59    /// var validation = worksheet.DataValidation.AddDateTimeValidation("A1");
60    /// // set validation properties
61    /// validation.ShowErrorMessage = true;
62    /// validation.ErrorTitle = "An invalid date was entered";
63    /// validation.Error = "The date must be between 2011-01-31 and 2011-12-31";
64    /// validation.Prompt = "Enter date here";
65    /// validation.Formula.Value = DateTime.Parse("2011-01-01");
66    /// validation.Formula2.Value = DateTime.Parse("2011-12-31");
67    /// validation.Operator = ExcelDataValidationOperator.between;
68    /// </code>
69    /// </summary>
70    public class ExcelDataValidationCollection : XmlHelper, IEnumerable<IExcelDataValidation>
71    {
72        private List<IExcelDataValidation> _validations = new List<IExcelDataValidation>();
73        private ExcelWorksheet _worksheet = null;
74
75        private const string DataValidationPath = "//d:dataValidations";
76        private readonly string DataValidationItemsPath = string.Format("{0}/d:dataValidation", DataValidationPath);
77
78        /// <summary>
79        /// Constructor
80        /// </summary>
81        /// <param name="worksheet"></param>
82        internal ExcelDataValidationCollection(ExcelWorksheet worksheet)
83            : base(worksheet.NameSpaceManager, worksheet.WorksheetXml.DocumentElement)
84        {
85            Require.Argument(worksheet).IsNotNull("worksheet");
86            _worksheet = worksheet;
87            SchemaNodeOrder = worksheet.SchemaNodeOrder;
88
89            // check existing nodes and load them
90            var dataValidationNodes = worksheet.WorksheetXml.SelectNodes(DataValidationItemsPath, worksheet.NameSpaceManager);
91            if (dataValidationNodes != null && dataValidationNodes.Count > 0)
92            {
93                foreach (XmlNode node in dataValidationNodes)
94                {
95                    if (node.Attributes["sqref"] == null) continue;
96
97                    var addr = node.Attributes["sqref"].Value;
98
99                    var typeSchema = node.Attributes["type"] != null ? node.Attributes["type"].Value : "";
100
101                    var type = ExcelDataValidationType.GetBySchemaName(typeSchema);
102                    _validations.Add(ExcelDataValidationFactory.Create(type, worksheet, addr, node));
103                }
104            }
105            if (_validations.Count > 0)
106            {
107                OnValidationCountChanged();
108            }
109        }
110
111        private void EnsureRootElementExists()
112        {
113            var node = _worksheet.WorksheetXml.SelectSingleNode(DataValidationPath, _worksheet.NameSpaceManager);
114            if (node == null)
115            {
116                CreateNode(DataValidationPath.TrimStart('/'));
117            }
118        }
119
120        private void OnValidationCountChanged()
121        {
122            //if (TopNode != null)
123            //{
124            //    SetXmlNodeString("@count", _validations.Count.ToString());
125            //}
126        }
127
128        private XmlNode GetRootNode()
129        {
130            EnsureRootElementExists();
131            TopNode = _worksheet.WorksheetXml.SelectSingleNode(DataValidationPath, _worksheet.NameSpaceManager);
132            return TopNode;
133        }
134
135        /// <summary>
136        /// Validates address - not empty, collisions
137        /// </summary>
138        /// <param name="address"></param>
139        /// <param name="validatingValidation"></param>
140        private void ValidateAddress(string address, IExcelDataValidation validatingValidation)
141        {
142            Require.Argument(address).IsNotNullOrEmpty("address");
143
144            // ensure that the new address does not collide with an existing validation.
145            var newAddress = new ExcelAddress(address);
146            if (_validations.Count > 0)
147            {
148                foreach (var validation in _validations)
149                {
150                    if (validatingValidation != null && validatingValidation == validation)
151                    {
152                        continue;
153                    }
154                    var result = validation.Address.Collide(newAddress);
155                    if (result != ExcelAddressBase.eAddressCollition.No)
156                    {
157                        throw new InvalidOperationException(string.Format("The address ({0}) collides with an existing validation ({1})", address, validation.Address.Address));
158                    }
159                }
160            }
161        }
162
163        private void ValidateAddress(string address)
164        {
165            ValidateAddress(address, null);
166        }
167
168        /// <summary>
169        /// Validates all data validations.
170        /// </summary>
171        internal void ValidateAll()
172        {
173            foreach (var validation in _validations)
174            {
175                validation.Validate();
176
177                ValidateAddress(validation.Address.Address, validation);
178            }
179        }
180
181        /// <summary>
182        /// Adds a <see cref="ExcelDataValidationAny"/> to the worksheet.
183        /// </summary>
184        /// <param name="address">The range/address to validate</param>
185        /// <returns></returns>
186        public IExcelDataValidationAny AddAnyValidation(string address)
187        {
188            ValidateAddress(address);
189            EnsureRootElementExists();
190            var item = new ExcelDataValidationAny(_worksheet, address, ExcelDataValidationType.Any);
191            _validations.Add(item);
192            OnValidationCountChanged();
193            return item;
194        }
195
196        /// <summary>
197        /// Adds an <see cref="IExcelDataValidationInt"/> to the worksheet. Whole means that the only accepted values
198        /// are integer values.
199        /// </summary>
200        /// <param name="address">the range/address to validate</param>
201        public IExcelDataValidationInt AddIntegerValidation(string address)
202        {
203            ValidateAddress(address);
204            EnsureRootElementExists();
205            var item = new ExcelDataValidationInt(_worksheet, address, ExcelDataValidationType.Whole);
206            _validations.Add(item);
207            OnValidationCountChanged();
208            return item;
209        }
210
211        /// <summary>
212        /// Addes an <see cref="IExcelDataValidationDecimal"/> to the worksheet. The only accepted values are
213        /// decimal values.
214        /// </summary>
215        /// <param name="address">The range/address to validate</param>
216        /// <returns></returns>
217        public IExcelDataValidationDecimal AddDecimalValidation(string address)
218        {
219            ValidateAddress(address);
220            EnsureRootElementExists();
221            var item = new ExcelDataValidationDecimal(_worksheet, address, ExcelDataValidationType.Decimal);
222            _validations.Add(item);
223            OnValidationCountChanged();
224            return item;
225        }
226
227        /// <summary>
228        /// Adds an <see cref="IExcelDataValidationList"/> to the worksheet. The accepted values are defined
229        /// in a list.
230        /// </summary>
231        /// <param name="address">The range/address to validate</param>
232        /// <returns></returns>
233        public IExcelDataValidationList AddListValidation(string address)
234        {
235            ValidateAddress(address);
236            EnsureRootElementExists();
237            var item = new ExcelDataValidationList(_worksheet, address, ExcelDataValidationType.List);
238            _validations.Add(item);
239            OnValidationCountChanged();
240            return item;
241        }
242
243        /// <summary>
244        /// Adds an <see cref="IExcelDataValidationInt"/> regarding text length to the worksheet.
245        /// </summary>
246        /// <param name="address">The range/address to validate</param>
247        /// <returns></returns>
248        public IExcelDataValidationInt AddTextLengthValidation(string address)
249        {
250            ValidateAddress(address);
251            EnsureRootElementExists();
252            var item = new ExcelDataValidationInt(_worksheet, address, ExcelDataValidationType.TextLength);
253            _validations.Add(item);
254            OnValidationCountChanged();
255            return item;
256        }
257
258        /// <summary>
259        /// Adds an <see cref="IExcelDataValidationDateTime"/> to the worksheet.
260        /// </summary>
261        /// <param name="address">The range/address to validate</param>
262        /// <returns></returns>
263        public IExcelDataValidationDateTime AddDateTimeValidation(string address)
264        {
265            ValidateAddress(address);
266            EnsureRootElementExists();
267            var item = new ExcelDataValidationDateTime(_worksheet, address, ExcelDataValidationType.DateTime);
268            _validations.Add(item);
269            OnValidationCountChanged();
270            return item;
271        }
272
273
274        public IExcelDataValidationTime AddTimeValidation(string address)
275        {
276            ValidateAddress(address);
277            EnsureRootElementExists();
278            var item = new ExcelDataValidationTime(_worksheet, address, ExcelDataValidationType.Time);
279            _validations.Add(item);
280            OnValidationCountChanged();
281            return item;
282        }
283        /// <summary>
284        /// Adds a <see cref="ExcelDataValidationCustom"/> to the worksheet.
285        /// </summary>
286        /// <param name="address">The range/address to validate</param>
287        /// <returns></returns>
288        public IExcelDataValidationCustom AddCustomValidation(string address)
289        {
290            ValidateAddress(address);
291            EnsureRootElementExists();
292            var item = new ExcelDataValidationCustom(_worksheet, address, ExcelDataValidationType.Custom);
293            _validations.Add(item);
294            OnValidationCountChanged();
295            return item;
296        }
297
298        /// <summary>
299        /// Removes an <see cref="ExcelDataValidation"/> from the collection.
300        /// </summary>
301        /// <param name="item">The item to remove</param>
302        /// <returns>True if remove succeeds, otherwise false</returns>
303        /// <exception cref="ArgumentNullException">if <paramref name="item"/> is null</exception>
304        public bool Remove(IExcelDataValidation item)
305        {
306            if (!(item is ExcelDataValidation))
307            {
308                throw new InvalidCastException("The supplied item must inherit OfficeOpenXml.DataValidation.ExcelDataValidation");
309            }
310            Require.Argument(item).IsNotNull("item");
311            TopNode.RemoveChild(((ExcelDataValidation)item).TopNode);
312            var retVal = _validations.Remove(item);
313            if (retVal) OnValidationCountChanged();
314            return retVal;
315        }
316
317        /// <summary>
318        /// Number of validations
319        /// </summary>
320        public int Count
321        {
322            get { return _validations.Count; }
323        }
324
325        /// <summary>
326        /// Index operator, returns by 0-based index
327        /// </summary>
328        /// <param name="index"></param>
329        /// <returns></returns>
330        public IExcelDataValidation this[int index]
331        {
332            get { return _validations[index]; }
333            set { _validations[index] = value; }
334        }
335
336        /// <summary>
337        /// Index operator, returns a data validation which address partly or exactly matches the searched address.
338        /// </summary>
339        /// <param name="address">A cell address or range</param>
340        /// <returns>A <see cref="ExcelDataValidation"/> or null if no match</returns>
341        public IExcelDataValidation this[string address]
342        {
343            get
344            {
345                var searchedAddress = new ExcelAddress(address);
346                return _validations.Find(x => x.Address.Collide(searchedAddress) != ExcelAddressBase.eAddressCollition.No);
347            }
348        }
349
350        /// <summary>
351        /// Returns all validations that matches the supplied predicate <paramref name="match"/>.
352        /// </summary>
353        /// <param name="match">predicate to filter out matching validations</param>
354        /// <returns></returns>
355        public IEnumerable<IExcelDataValidation> FindAll(Predicate<IExcelDataValidation> match)
356        {
357            return _validations.FindAll(match);
358        }
359
360        /// <summary>
361        /// Returns the first matching validation.
362        /// </summary>
363        /// <param name="match"></param>
364        /// <returns></returns>
365        public IExcelDataValidation Find(Predicate<IExcelDataValidation> match)
366        {
367            return _validations.Find(match);
368        }
369
370        /// <summary>
371        /// Removes all validations from the collection.
372        /// </summary>
373        public void Clear()
374        {
375            DeleteAllNode(DataValidationItemsPath.TrimStart('/'));
376            _validations.Clear();
377        }
378
379        /// <summary>
380        /// Removes the validations that matches the predicate
381        /// </summary>
382        /// <param name="match"></param>
383        public void RemoveAll(Predicate<IExcelDataValidation> match)
384        {
385            var matches = _validations.FindAll(match);
386            foreach (var m in matches)
387            {
388                if (!(m is ExcelDataValidation))
389                {
390                    throw new InvalidCastException("The supplied item must inherit OfficeOpenXml.DataValidation.ExcelDataValidation");
391                }
392                TopNode.SelectSingleNode(DataValidationPath.TrimStart('/'), NameSpaceManager).RemoveChild(((ExcelDataValidation)m).TopNode);
393            }
394            _validations.RemoveAll(match);
395            OnValidationCountChanged();
396        }
397
398        IEnumerator<IExcelDataValidation> IEnumerable<IExcelDataValidation>.GetEnumerator()
399        {
400            return _validations.GetEnumerator();
401        }
402
403        IEnumerator System.Collections.IEnumerable.GetEnumerator()
404        {
405            return _validations.GetEnumerator();
406        }
407    }
408}
Note: See TracBrowser for help on using the repository browser.