/// <summary>
/// Adds a <see cref="ExcelDataValidationCustom"/> to the worksheet.
/// </summary>
/// <param name="address">The range/address to validate</param>
/// <returns></returns>
public IExcelDataValidationCustom AddCustomValidation(string address)
{
ValidateAddress(address);
EnsureRootElementExists();
var item = new ExcelDataValidationCustom(_worksheet, ExcelDataValidation.NewId(), address, ExcelDataValidationType.Custom);
_validations.Add(item);
OnValidationCountChanged();
return(item);
}
/// <summary>
/// Adds an <see cref="IExcelDataValidationList"/> to the worksheet. The accepted values are defined
/// in a list.
/// </summary>
/// <param name="address">The range/address to validate</param>
/// <returns></returns>
public IExcelDataValidationList AddListValidation(string address)
{
ValidateAddress(address);
EnsureRootElementExists();
var item = new ExcelDataValidationList(_worksheet, ExcelDataValidation.NewId(), address, ExcelDataValidationType.List);
((ExcelDataValidationFormula)item.Formula).RegisterFormulaListener(_formulaListener);
_validations.Add(item);
OnValidationCountChanged();
return(item);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="worksheet"></param>
internal ExcelDataValidationCollection(ExcelWorksheet worksheet)
: base(worksheet.NameSpaceManager, worksheet.WorksheetXml.DocumentElement)
{
Require.Argument(worksheet).IsNotNull("worksheet");
_worksheet = worksheet;
_formulaListener = new DataValidationFormulaListener(this, _worksheet);
SchemaNodeOrder = worksheet.SchemaNodeOrder;
// check existing nodes and load them
var dataValidationNodes = worksheet.WorksheetXml.SelectNodes(DataValidationItemsPath, worksheet.NameSpaceManager);
if (dataValidationNodes != null && dataValidationNodes.Count > 0)
{
foreach (XmlNode node in dataValidationNodes)
{
if (node.Attributes["sqref"] == null)
{
continue;
}
var addr = node.Attributes["sqref"].Value;
var uid = node.Attributes["xr:uid"] != null && !string.IsNullOrEmpty(node.Attributes["xr:uid"].Value) ? node.Attributes["xr:uid"].Value : ExcelDataValidation.NewId();
var typeSchema = node.Attributes["type"] != null ? node.Attributes["type"].Value : "";
var type = ExcelDataValidationType.GetBySchemaName(typeSchema);
var validation = ExcelDataValidationFactory.Create(type, worksheet, addr, node, InternalValidationType.DataValidation, uid);
validation.Uid = uid;
_validations.Add(validation);
}
}
if (_validations.Count > 0)
{
OnValidationCountChanged();
}
_extLstValidations = new ExcelExLstDataValidationCollection(worksheet, _formulaListener);
_extListUsed = !_extLstValidations.IsEmpty;
InternalValidationEnabled = true;
if (worksheet.WorksheetXml.DocumentElement != null)
{
var xr = worksheet.WorksheetXml.DocumentElement.GetAttribute("xmlns:xr");
if (string.IsNullOrEmpty(xr))
{
worksheet.WorksheetXml.DocumentElement.SetAttribute("xmlns:xr", ExcelPackage.schemaXr);
var mc = worksheet.WorksheetXml.DocumentElement.GetAttribute("xmlns:mc");
if (mc != ExcelPackage.schemaMarkupCompatibility)
{
worksheet.WorksheetXml.DocumentElement.SetAttribute("xmlns:mc", ExcelPackage.schemaMarkupCompatibility);
}
var ignore = worksheet.WorksheetXml.DocumentElement.GetAttribute("mc:Ignorable");
var nsIgnore = ignore.Split(' ');
if (!nsIgnore.Contains("xr"))
{
worksheet.WorksheetXml.DocumentElement.SetAttribute("Ignorable", ExcelPackage.schemaMarkupCompatibility, string.IsNullOrEmpty(ignore) ? "xr" : ignore + " xr");
}
}
}
}
internal ExcelExLstDataValidationCollection(ExcelWorksheet worksheet, DataValidationFormulaListener formulaListener)
: base(worksheet.NameSpaceManager, worksheet.WorksheetXml.DocumentElement)
{
Require.Argument(worksheet).IsNotNull("worksheet");
_worksheet = worksheet;
_formulaListener = formulaListener;
//SchemaNodeOrder = worksheet.SchemaNodeOrder;
SchemaNodeOrder = new string[]
{
"xmlns:x14",
"uri"
};
// check validations in the extLst
var extLstValidationNodes = worksheet.WorksheetXml.SelectNodes(ExternalDataValidationItemsPath, worksheet.NameSpaceManager);
if (extLstValidationNodes != null && extLstValidationNodes.Count > 0)
{
foreach (XmlNode node in extLstValidationNodes)
{
var address = base.GetXmlNodeString(node, "xm:sqref");
var uid = node.Attributes["xr:uid"] != null && !string.IsNullOrEmpty(node.Attributes["xr:uid"].Value) ? node.Attributes["xr:uid"].Value : ExcelDataValidation.NewId();
var typeSchema = node.Attributes["type"] != null ? node.Attributes["type"].Value : "";
var type = ExcelDataValidationType.GetBySchemaName(typeSchema);
var val = ExcelDataValidationFactory.Create(type, worksheet, address, node, InternalValidationType.ExtLst, uid);
val.Uid = uid;
_validations.Add(val);
}
}
if (_validations.Count > 0)
{
OnValidationCountChanged();
}
}