/*******************************************************************************
|
* 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.Linq;
|
using System.Text;
|
using HH.WMS.Utils.EPPlus.Utils;
|
using System.Xml;
|
using System.Text.RegularExpressions;
|
using HH.WMS.Utils.EPPlus.DataValidation.Formulas.Contracts;
|
using HH.WMS.Utils.EPPlus.DataValidation.Contracts;
|
|
namespace HH.WMS.Utils.EPPlus.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();
|
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);
|
}
|
}
|
|
/// <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);
|
}
|
}
|
}
|
