EPPlus/DataValidation/ExcelDataValidation.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 * 
 * Author							Change						Date
 * ******************************************************************************
 * Mats Alm   		                Added       		        2011-01-01
 * Jan Källman		                License changed GPL-->LGPL  2011-12-27
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using OfficeOpenXml.Utils;
using System.Xml;
using System.Text.RegularExpressions;
using OfficeOpenXml.DataValidation.Formulas.Contracts;
using OfficeOpenXml.DataValidation.Contracts;

namespace OfficeOpenXml.DataValidation
{
    /// <summary>
    /// Excel datavalidation
    /// </summary>
    public abstract class ExcelDataValidation : XmlHelper, IExcelDataValidation
    {
        private const string _itemElementNodeName = "d:dataValidation";


        private readonly string _errorStylePath = "@errorStyle";
        private readonly string _errorTitlePath = "@errorTitle";
        private readonly string _errorPath = "@error";
        private readonly string _promptTitlePath = "@promptTitle";
        private readonly string _promptPath = "@prompt";
        private readonly string _operatorPath = "@operator";
        private readonly string _showErrorMessagePath = "@showErrorMessage";
        private readonly string _showInputMessagePath = "@showInputMessage";
        private readonly string _typeMessagePath = "@type";
        private readonly string _sqrefPath = "@sqref";
        private readonly string _allowBlankPath = "@allowBlank";
        protected readonly string _formula1Path = "d:formula1";
        protected readonly string _formula2Path = "d:formula2";

        internal ExcelDataValidation(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType)
            : this(worksheet, address, validationType, null)
        { }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="worksheet">worksheet that owns the validation</param>
        /// <param name="itemElementNode">Xml top node (dataValidations)</param>
        /// <param name="validationType">Data validation type</param>
        /// <param name="address">address for data validation</param>
        internal ExcelDataValidation(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType, XmlNode itemElementNode)
            : this(worksheet, address, validationType, itemElementNode, null)
        {

        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="worksheet">worksheet that owns the validation</param>
        /// <param name="itemElementNode">Xml top node (dataValidations) when importing xml</param>
        /// <param name="validationType">Data validation type</param>
        /// <param name="address">address for data validation</param>
        /// <param name="namespaceManager">Xml Namespace manager</param>
        internal ExcelDataValidation(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType, XmlNode itemElementNode, XmlNamespaceManager namespaceManager)
            : base(namespaceManager != null ? namespaceManager : worksheet.NameSpaceManager)
        {
            Require.Argument(address).IsNotNullOrEmpty("address");
            address = CheckAndFixRangeAddress(address);
            if (itemElementNode == null)
            {
                //var xmlDoc = worksheet.WorksheetXml;
                TopNode = worksheet.WorksheetXml.SelectSingleNode("//d:dataValidations", worksheet.NameSpaceManager);
                // did not succeed using the XmlHelper methods here... so I'm creating the new node using XmlDocument...
                var nsUri = NameSpaceManager.LookupNamespace("d");
                //itemElementNode = TopNode.OwnerDocument.CreateElement(_itemElementNodeName, nsUri);
                itemElementNode = TopNode.OwnerDocument.CreateElement(_itemElementNodeName.Split(':')[1], nsUri);
                TopNode.AppendChild(itemElementNode);
            }
            TopNode = itemElementNode;
            ValidationType = validationType;
            Address = new ExcelAddress(address);
            Init();
        }

        private void Init()
        {
            // set schema node order
            SchemaNodeOrder = new string[]{ 
                "type", 
                "errorStyle", 
                "operator", 
                "allowBlank",
                "showInputMessage", 
                "showErrorMessage", 
                "errorTitle", 
                "error", 
                "promptTitle", 
                "prompt", 
                "sqref",
                "formula1",
                "formula2"
            };
        }

        private string CheckAndFixRangeAddress(string address)
        {
            if (address.Contains(','))
            {
                throw new FormatException("Multiple addresses may not be commaseparated, use space instead");
            }
            address = address.ToUpper(CultureInfo.InvariantCulture);
            if (Regex.IsMatch(address, @"[A-Z]+:[A-Z]+"))
            {
                address = AddressUtility.ParseEntireColumnSelections(address);
            }
            return address;
        }

        private void SetNullableBoolValue(string path, bool? val)
        {
            if (val.HasValue)
            {
                SetXmlNodeBool(path, val.Value);
            }
            else
            {
                DeleteNode(path);
            }
        }

        /// <summary>
        /// This method will validate the state of the validation
        /// </summary>
        /// <exception cref="InvalidOperationException">If the state breaks the rules of the validation</exception>
        public virtual void Validate()
        {
            var address = Address.Address;
            // validate Formula1
            if (string.IsNullOrEmpty(Formula1Internal))
            {
                throw new InvalidOperationException("Validation of " + address + " failed: Formula1 cannot be empty");
            }
        }

        #region Public properties

        /// <summary>
        /// True if the validation type allows operator to be set.
        /// </summary>
        public bool AllowsOperator
        {
            get
            {
                return ValidationType.AllowOperator;
            }
        }

        /// <summary>
        /// Address of data validation
        /// </summary>
        public ExcelAddress Address
        {
            get
            {
                return new ExcelAddress(GetXmlNodeString(_sqrefPath));
            }
            private set
            {
                var address = AddressUtility.ParseEntireColumnSelections(value.Address);
                SetXmlNodeString(_sqrefPath, address);
            }
        }
        /// <summary>
        /// Validation type
        /// </summary>
        public ExcelDataValidationType ValidationType
        {
            get
            {
                var typeString = GetXmlNodeString(_typeMessagePath);
                return ExcelDataValidationType.GetBySchemaName(typeString);
            }
            private set
            {
                SetXmlNodeString(_typeMessagePath, value.SchemaName, true);
            }
        }

        /// <summary>
        /// Operator for comparison between the entered value and Formula/Formulas.
        /// </summary>
        public ExcelDataValidationOperator Operator
        {
            get
            {
                var operatorString = GetXmlNodeString(_operatorPath);
                if (!string.IsNullOrEmpty(operatorString))
                {
                    return (ExcelDataValidationOperator)Enum.Parse(typeof(ExcelDataValidationOperator), operatorString);
                }
                return default(ExcelDataValidationOperator);
            }
            set
            {
                if (!ValidationType.AllowOperator)
                {
                    throw new InvalidOperationException("The current validation type does not allow operator to be set");
                }
                SetXmlNodeString(_operatorPath, value.ToString());
            }
        }

        /// <summary>
        /// Warning style
        /// </summary>
        public ExcelDataValidationWarningStyle ErrorStyle
        {
            get
            {
                var errorStyleString = GetXmlNodeString(_errorStylePath);
                if (!string.IsNullOrEmpty(errorStyleString))
                {
                    return (ExcelDataValidationWarningStyle)Enum.Parse(typeof(ExcelDataValidationWarningStyle), errorStyleString);
                }
                return ExcelDataValidationWarningStyle.undefined;
            }
            set
            {
                if (value == ExcelDataValidationWarningStyle.undefined)
                {
                    DeleteNode(_errorStylePath);
                }
                SetXmlNodeString(_errorStylePath, value.ToString());
            }
        }

        /// <summary>
        /// True if blanks should be allowed
        /// </summary>
        public bool? AllowBlank
        {
            get
            {
                return GetXmlNodeBoolNullable(_allowBlankPath);
            }
            set
            {
                SetNullableBoolValue(_allowBlankPath, value);
            }
        }

        /// <summary>
        /// True if input message should be shown
        /// </summary>
        public bool? ShowInputMessage
        {
            get
            {
                return GetXmlNodeBoolNullable(_showInputMessagePath);
            }
            set
            {
                SetNullableBoolValue(_showInputMessagePath, value);
            }
        }

        /// <summary>
        /// True if error message should be shown
        /// </summary>
        public bool? ShowErrorMessage
        {
            get
            {
                return GetXmlNodeBoolNullable(_showErrorMessagePath);
            }
            set
            {
                SetNullableBoolValue(_showErrorMessagePath, value);
            }
        }

        /// <summary>
        /// Title of error message box
        /// </summary>
        public string ErrorTitle
        {
            get
            {
                return GetXmlNodeString(_errorTitlePath);
            }
            set
            {
                SetXmlNodeString(_errorTitlePath, value);
            }
        }

        /// <summary>
        /// Error message box text
        /// </summary>
        public string Error
        {
            get
            {
                return GetXmlNodeString(_errorPath);
            }
            set
            {
                SetXmlNodeString(_errorPath, value);
            }
        }

        public string PromptTitle
        {
            get
            {
                return GetXmlNodeString(_promptTitlePath);
            }
            set
            {
                SetXmlNodeString(_promptTitlePath, value);
            }
        }

        public string Prompt
        {
            get
            {
                return GetXmlNodeString(_promptPath);
            }
            set
            {
                SetXmlNodeString(_promptPath, value);
            }
        }

        /// <summary>
        /// Formula 1
        /// </summary>
        protected string Formula1Internal
        {
            get
            {
                return GetXmlNodeString(_formula1Path);
            }
        }

        /// <summary>
        /// Formula 2
        /// </summary>
        protected string Formula2Internal
        {
            get
            {
                return GetXmlNodeString(_formula2Path);
            }
        }

        #endregion

        protected void SetValue<T>(Nullable<T> val, string path)
            where T : struct
        {
            if (!val.HasValue)
            {
                DeleteNode(path);
            }
            var stringValue = val.Value.ToString().Replace(',', '.');
            SetXmlNodeString(path, stringValue);
        }
    }
}