Free cookie consent management tool by TermsFeed Policy Generator

source: branches/Async/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/3.1.3/EPPlus-3.1.3/DataValidation/ExcelDataValidationCollection.cs @ 11642

Last change on this file since 11642 was 9580, checked in by sforsten, 11 years ago

#1730:

  • added SymbolicDataAnalysisExpressionExcelFormatter
  • changed modifiers in SymbolicExpressionTreeChart of methods SaveImageAsBitmap and SaveImageAsEmf to public
  • added menu item ExportSymbolicSolutionToExcelMenuItem to export a symbolic solution to an excel file
  • added EPPlus-3.1.3 to ExtLibs
File size: 15.8 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 *******************************************************************************/
34using System;
35using System.Collections.Generic;
36using System.Linq;
37using System.Text;
38using System.Collections;
39using OfficeOpenXml.Utils;
40using System.Xml;
41using OfficeOpenXml.DataValidation.Contracts;
42
43namespace OfficeOpenXml.DataValidation
44{
45    /// <summary>
46    /// <para>
47    /// Collection of <see cref="ExcelDataValidation"/>. This class is providing the API for EPPlus data validation.
48    /// </para>
49    /// <para>
50    /// The public methods of this class (Add[...]Validation) will create a datavalidation entry in the worksheet. When this
51    /// validation has been created changes to the properties will affect the workbook immediately.
52    /// </para>
53    /// <para>
54    /// Each type of validation has either a formula or a typed value/values, except for custom validation which has a formula only.
55    /// </para>
56    /// <code>
57    /// // Add a date time validation
58    /// var validation = worksheet.DataValidation.AddDateTimeValidation("A1");
59    /// // set validation properties
60    /// validation.ShowErrorMessage = true;
61    /// validation.ErrorTitle = "An invalid date was entered";
62    /// validation.Error = "The date must be between 2011-01-31 and 2011-12-31";
63    /// validation.Prompt = "Enter date here";
64    /// validation.Formula.Value = DateTime.Parse("2011-01-01");
65    /// validation.Formula2.Value = DateTime.Parse("2011-12-31");
66    /// validation.Operator = ExcelDataValidationOperator.between;
67    /// </code>
68    /// </summary>
69    public class ExcelDataValidationCollection : XmlHelper, IEnumerable<IExcelDataValidation>
70    {
71        private List<IExcelDataValidation> _validations = new List<IExcelDataValidation>();
72        private ExcelWorksheet _worksheet = null;
73
74        private const string DataValidationPath = "//d:dataValidations";
75        private readonly string DataValidationItemsPath = string.Format("{0}/d:dataValidation", DataValidationPath);
76
77        /// <summary>
78        /// Constructor
79        /// </summary>
80        /// <param name="worksheet"></param>
81        internal ExcelDataValidationCollection(ExcelWorksheet worksheet)
82            : base(worksheet.NameSpaceManager, worksheet.WorksheetXml.DocumentElement)
83        {
84            Require.Argument(worksheet).IsNotNull("worksheet");
85            _worksheet = worksheet;
86            SchemaNodeOrder = worksheet.SchemaNodeOrder;
87
88            // check existing nodes and load them
89            var dataValidationNodes = worksheet.WorksheetXml.SelectNodes(DataValidationItemsPath, worksheet.NameSpaceManager);
90            if (dataValidationNodes != null && dataValidationNodes.Count > 0)
91            {
92                foreach (XmlNode node in dataValidationNodes)
93                {
94                    if (node.Attributes["sqref"] == null || node.Attributes["type"] == null) continue;
95
96                    var addr = node.Attributes["sqref"].Value;
97
98                    var type = ExcelDataValidationType.GetBySchemaName(node.Attributes["type"].Value);
99                    _validations.Add(ExcelDataValidationFactory.Create(type, worksheet, addr, node));
100                }
101            }
102            if (_validations.Count > 0)
103            {
104                OnValidationCountChanged();
105            }
106        }
107
108        private void EnsureRootElementExists()
109        {
110            var node = _worksheet.WorksheetXml.SelectSingleNode(DataValidationPath, _worksheet.NameSpaceManager);
111            if (node == null)
112            {
113                CreateNode(DataValidationPath.TrimStart('/'));
114            }
115        }
116
117        private void OnValidationCountChanged()
118        {
119            if (TopNode != null)
120            {
121                SetXmlNodeString("@count", _validations.Count.ToString());
122            }
123        }
124
125        private XmlNode GetRootNode()
126        {
127            EnsureRootElementExists();
128            TopNode = _worksheet.WorksheetXml.SelectSingleNode(DataValidationPath, _worksheet.NameSpaceManager);
129            return TopNode;
130        }
131
132        /// <summary>
133        /// Validates address - not empty, collisions
134        /// </summary>
135        /// <param name="address"></param>
136        /// <param name="validatingValidation"></param>
137        private void ValidateAddress(string address, IExcelDataValidation validatingValidation)
138        {
139            Require.Argument(address).IsNotNullOrEmpty("address");
140           
141            // ensure that the new address does not collide with an existing validation.
142            var newAddress = new ExcelAddress(address);
143            if (_validations.Count > 0)
144            {
145                foreach (var validation in _validations)
146                {
147                    if (validatingValidation != null && validatingValidation == validation)
148                    {
149                        continue;
150                    }
151                    var result = validation.Address.Collide(newAddress);
152                    if (result != ExcelAddressBase.eAddressCollition.No)
153                    {
154                        throw new InvalidOperationException(string.Format("The address ({0}) collides with an existing validation ({1})", address, validation.Address.Address));
155                    }
156                }
157            }
158        }
159
160        private void ValidateAddress(string address)
161        {
162            ValidateAddress(address, null);
163        }
164
165        /// <summary>
166        /// Validates all data validations.
167        /// </summary>
168        internal void ValidateAll()
169        {
170            foreach (var validation in _validations)
171            {
172                validation.Validate();
173
174                ValidateAddress(validation.Address.Address, validation);
175            }
176        }
177
178        /// <summary>
179        /// Adds an <see cref="IExcelDataValidationInt"/> to the worksheet. Whole means that the only accepted values
180        /// are integer values.
181        /// </summary>
182        /// <param name="address">the range/address to validate</param>
183        public IExcelDataValidationInt AddIntegerValidation(string address)
184        {
185            ValidateAddress(address);
186            EnsureRootElementExists();
187            var item = new ExcelDataValidationInt(_worksheet, address, ExcelDataValidationType.Whole);
188            _validations.Add(item);
189            OnValidationCountChanged();
190            return item;
191        }
192
193        /// <summary>
194        /// Addes an <see cref="IExcelDataValidationDecimal"/> to the worksheet. The only accepted values are
195        /// decimal values.
196        /// </summary>
197        /// <param name="address">The range/address to validate</param>
198        /// <returns></returns>
199        public IExcelDataValidationDecimal AddDecimalValidation(string address)
200        {
201            ValidateAddress(address);
202            EnsureRootElementExists();
203            var item = new ExcelDataValidationDecimal(_worksheet, address, ExcelDataValidationType.Decimal);
204            _validations.Add(item);
205            OnValidationCountChanged();
206            return item;
207        }
208
209        /// <summary>
210        /// Adds an <see cref="IExcelDataValidationList"/> to the worksheet. The accepted values are defined
211        /// in a list.
212        /// </summary>
213        /// <param name="address">The range/address to validate</param>
214        /// <returns></returns>
215        public IExcelDataValidationList AddListValidation(string address)
216        {
217            ValidateAddress(address);
218            EnsureRootElementExists();
219            var item = new ExcelDataValidationList(_worksheet, address, ExcelDataValidationType.List);
220            _validations.Add(item);
221            OnValidationCountChanged();
222            return item;
223        }
224
225        /// <summary>
226        /// Adds an <see cref="IExcelDataValidationInt"/> regarding text length to the worksheet.
227        /// </summary>
228        /// <param name="address">The range/address to validate</param>
229        /// <returns></returns>
230        public IExcelDataValidationInt AddTextLengthValidation(string address)
231        {
232            ValidateAddress(address);
233            EnsureRootElementExists();
234            var item = new ExcelDataValidationInt(_worksheet, address, ExcelDataValidationType.TextLength);
235            _validations.Add(item);
236            OnValidationCountChanged();
237            return item;
238        }
239
240        /// <summary>
241        /// Adds an <see cref="IExcelDataValidationDateTime"/> to the worksheet.
242        /// </summary>
243        /// <param name="address">The range/address to validate</param>
244        /// <returns></returns>
245        public IExcelDataValidationDateTime AddDateTimeValidation(string address)
246        {
247            ValidateAddress(address);
248            EnsureRootElementExists();
249            var item = new ExcelDataValidationDateTime(_worksheet, address, ExcelDataValidationType.DateTime);
250            _validations.Add(item);
251            OnValidationCountChanged();
252            return item;
253        }
254
255       
256        public IExcelDataValidationTime AddTimeValidation(string address)
257        {
258            ValidateAddress(address);
259            EnsureRootElementExists();
260            var item = new ExcelDataValidationTime(_worksheet, address, ExcelDataValidationType.Time);
261            _validations.Add(item);
262            OnValidationCountChanged();
263            return item;
264        }
265        /// <summary>
266        /// Adds a <see cref="ExcelDataValidationCustom"/> to the worksheet.
267        /// </summary>
268        /// <param name="address">The range/address to validate</param>
269        /// <returns></returns>
270        public IExcelDataValidationCustom AddCustomValidation(string address)
271        {
272            ValidateAddress(address);
273            EnsureRootElementExists();
274            var item = new ExcelDataValidationCustom(_worksheet, address, ExcelDataValidationType.Custom);
275            _validations.Add(item);
276            OnValidationCountChanged();
277            return item;
278        }
279
280        /// <summary>
281        /// Removes an <see cref="ExcelDataValidation"/> from the collection.
282        /// </summary>
283        /// <param name="item">The item to remove</param>
284        /// <returns>True if remove succeeds, otherwise false</returns>
285        /// <exception cref="ArgumentNullException">if <paramref name="item"/> is null</exception>
286        public bool Remove(IExcelDataValidation item)
287        {
288            if (!(item is ExcelDataValidation))
289            {
290                throw new InvalidCastException("The supplied item must inherit OfficeOpenXml.DataValidation.ExcelDataValidation");
291            }
292            Require.Argument(item).IsNotNull("item");
293            TopNode.RemoveChild(((ExcelDataValidation)item).TopNode);
294            var retVal = _validations.Remove(item);
295            if (retVal) OnValidationCountChanged();
296            return retVal;
297        }
298
299        /// <summary>
300        /// Number of validations
301        /// </summary>
302        public int Count
303        {
304            get { return _validations.Count; }
305        }
306
307        /// <summary>
308        /// Index operator, returns by 0-based index
309        /// </summary>
310        /// <param name="index"></param>
311        /// <returns></returns>
312        public IExcelDataValidation this[int index]
313        {
314            get { return _validations[index]; }
315            set { _validations[index] = value; }
316        }
317
318        /// <summary>
319        /// Index operator, returns a data validation which address partly or exactly matches the searched address.
320        /// </summary>
321        /// <param name="address">A cell address or range</param>
322        /// <returns>A <see cref="ExcelDataValidation"/> or null if no match</returns>
323        public IExcelDataValidation this[string address]
324        {
325            get
326            {
327                var searchedAddress = new ExcelAddress(address);
328                return _validations.Find(x => x.Address.Collide(searchedAddress) != ExcelAddressBase.eAddressCollition.No);
329            }
330        }
331
332        /// <summary>
333        /// Returns all validations that matches the supplied predicate <paramref name="match"/>.
334        /// </summary>
335        /// <param name="match">predicate to filter out matching validations</param>
336        /// <returns></returns>
337        public IEnumerable<IExcelDataValidation> FindAll(Predicate<IExcelDataValidation> match)
338        {
339            return _validations.FindAll(match);
340        }
341
342        /// <summary>
343        /// Returns the first matching validation.
344        /// </summary>
345        /// <param name="match"></param>
346        /// <returns></returns>
347        public IExcelDataValidation Find(Predicate<IExcelDataValidation> match)
348        {
349            return _validations.Find(match);
350        }
351
352        /// <summary>
353        /// Removes all validations from the collection.
354        /// </summary>
355        public void Clear()
356        {
357            DeleteAllNode(DataValidationItemsPath.TrimStart('/'));
358            _validations.Clear();
359        }
360
361        /// <summary>
362        /// Removes the validations that matches the predicate
363        /// </summary>
364        /// <param name="match"></param>
365        public void RemoveAll(Predicate<IExcelDataValidation> match)
366        {
367            var matches = _validations.FindAll(match);
368            foreach (var m in matches)
369            {
370                if (!(m is ExcelDataValidation))
371                {
372                    throw new InvalidCastException("The supplied item must inherit OfficeOpenXml.DataValidation.ExcelDataValidation");
373                }
374                TopNode.SelectSingleNode(DataValidationPath.TrimStart('/'), NameSpaceManager).RemoveChild(((ExcelDataValidation)m).TopNode);
375            }
376            _validations.RemoveAll(match);
377            OnValidationCountChanged();
378        }
379
380        IEnumerator<IExcelDataValidation> IEnumerable<IExcelDataValidation>.GetEnumerator()
381        {
382            return _validations.GetEnumerator();
383        }
384
385        IEnumerator System.Collections.IEnumerable.GetEnumerator()
386        {
387            return _validations.GetEnumerator();
388        }
389    }
390}
Note: See TracBrowser for help on using the repository browser.