/// <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(); } }