DefinedName.cs

/*
 * NanoXLSX is a small .NET library to generate and read XLSX (Microsoft Excel 2007 or newer) files in an easy and native way
 * Copyright Raphael Stoeckli © 2026
 * This library is licensed under the MIT License.
 * You find a copy of the license in project folder or on: http://opensource.org/licenses/MIT
 */
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using NanoXLSX.Enums;
using NanoXLSX.Exceptions;
using NanoXLSX.Internal;
using NanoXLSX.Utils;
using static NanoXLSX.Enums.Errors;
using FormatException = NanoXLSX.Exceptions.FormatException;
 
namespace NanoXLSX
{
    public sealed class DefinedName : IEquatable<DefinedName>, IComparable<DefinedName>
    {
        #region enums
        public enum NameType
        {
            Cell,
            Range,
            Formula,
            Constant
 
        }
 
        #endregion
 
        #region constants
 
        private static readonly Regex EXT_WORKSHEET_REFERENCE_REGEX = new Regex(
        @"^\‍[[0-9]+\‍].+", RegexOptions.Compiled | RegexOptions.CultureInvariant);
 
        private const int MAX_NAME_LENGTH = 255;

        private static readonly HashSet<string> DISALLOWED_NAMES = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
        "C",
        "R"
            };

        private static readonly char[] ALLOWED_NAME_START_CHARS = { '\\', '_' };
        private static readonly char[] ALLOWED_NAME_CHARS = { '_', '.', '\\' };
 
        #endregion
 
        #region properties

        public NameType Type { get; }

        public string Name { get; }

        public Worksheet TargetWorksheet { get; }

        public string TextValue { get; private set; }

        public object Value { get; private set; }

        public Worksheet LocalSheet { get; }

        public string Comment { get; }

        public FormulaError Error { get; private set; }

        public bool HasExternalReferences { get { return Features.ContainsExternalLinks; } }
 
        internal FeatureSet Features { get; private set; } = FeatureSet.CreateDefinedName();
 
        #endregion
 
        #region constructors

        internal DefinedName(Workbook workbook, NameType type, string name, object reference, Worksheet worksheet, Worksheet localSheet = null, string comment = null, bool? containsExternalLinks = null)
        {
            if (workbook == null)
            {
                throw new FormatException("To set a defined name, a workbook must be provided.");
            }
            ValidateName(workbook, name, localSheet);
            if (reference == null || string.IsNullOrEmpty(reference.ToString()))
            {
                throw new FormatException("The reference of a defined name must not be null or empty.");
            }
            this.Type = type;
            this.Name = name;
            this.Value = reference;
            this.TargetWorksheet = worksheet;
            this.LocalSheet = localSheet;
            this.Comment = comment;
            CastValue(workbook);
            this.Features.Add(workbook.Features); // Add feature reference already here
            bool hasExternalLinks = containsExternalLinks
                ?? (this.Type == NameType.Formula && ParserUtils.ContainsExternalReference(this.TextValue));
            bool isFormula = this.Type == NameType.Formula || hasExternalLinks;
            this.Features.SetDefinedNameFeatures(isFormula, hasExternalLinks);
        }
        #endregion
 
        #region methods

        internal static DefinedName ResolveDefinedName(string name, string reference, Workbook workbook, Worksheet localSheet, string comment)
        {
            string worksheetName;
            NameType type;
            FormulaError formulaError;
            object value = GetParsedObject(reference, out type, out worksheetName, out formulaError);
            bool containsExternalLink = ContainsExternalLink(worksheetName, type, value);
            //workbook.Features.SetFormulaFeatures(false, containsExternalLink);
            Worksheet worksheet = null;
            if (worksheetName != null && !containsExternalLink)
            {
                foreach (Worksheet ws in workbook.Worksheets)
                {
                    if (string.Equals(worksheetName, ws.SheetName, StringComparison.OrdinalIgnoreCase))
                    {
                        worksheet = ws;
                        break;
                    }
                }
            }
            DefinedName definedName = new DefinedName(workbook, type, name, value, worksheet, localSheet, comment, containsExternalLink);
            definedName.Error = formulaError;
            //definedName.HasExternalReferences = containsExternalLink;
            //definedName.Features.SetExternalLinkFeature(containsExternalLink);
            return definedName;
        }

        internal void ReplaceExpression(string expression) // Do not remove. This method may be used by NanoXLSX.Compatibility
        {
            if (Type == NameType.Formula)
            {
                Value = expression;
                TextValue = expression;
            }
        }

        private static void ValidateName(Workbook workbook, string name, Worksheet localSheet)
        {
            if (string.IsNullOrWhiteSpace(name))
            {
                throw new FormatException("The name of a defined name must not be null or empty.");
            }
            if (name.Length > MAX_NAME_LENGTH)
            {
                throw new FormatException($"A defined name must not exceed {MAX_NAME_LENGTH} characters.");
            }
 
            char firstChar = name[0];
 
            if (!char.IsLetter(firstChar)
                && !ALLOWED_NAME_START_CHARS.Contains(firstChar))
            {
                throw new FormatException($"The name of a defined name must start with a letter, underscore, or backslash. Provided: '{name}'");
            }
            if (DISALLOWED_NAMES.Contains(name))
            {
                throw new FormatException($"'{name}' cannot be used as a defined name.");
            }
            for (int i = 1; i < name.Length; i++)
            {
                char character = name[i];
 
                if (!char.IsLetterOrDigit(character) && !ALLOWED_NAME_CHARS.Contains(character))
                {
                    throw new FormatException($"The character '{character}' at position {i} is not valid in the defined name '{name}'.");
                }
            }
            if (workbook.FindDefinedNameIndex(name, localSheet) >= 0)
            {
                string scope = localSheet == null ? "workbook" : "worksheet '" + localSheet.SheetName + "'";
                throw new WorksheetException("A defined name with the name '" + name + "' already exists in the " + scope + " scope.");
            }
            try
            {
                Validators.ValidateCellAddressExpression(name, Cell.AddressScope.SingleAddress);
            }
            catch
            {
                // Not a valid cell address; therefore it may be used as a defined name.
                return;
            }
            throw new FormatException($"The defined name '{name}' must not be a valid cell address.");
        }

        private void CastValue(Workbook workbook)
        {
            switch (this.Type)
            {
                // The object type is assumed to be validated prior
                case NameType.Cell:
                    string address = this.Value is Address addressValue ? addressValue.ToString() : this.Value as string;
                    Validators.ValidateCellAddressExpression(address, Cell.AddressScope.SingleAddress); // throw if not an address
                    Address fixedAddress = new Address(address, Cell.AddressType.FixedRowAndColumn);
                    this.TextValue = fixedAddress.ToString();
                    this.Value = fixedAddress; // Reformat passed object
                    break;
                case NameType.Range:
                    string range = this.Value is Range rangeValue ? rangeValue.ToString() : this.Value as string;
                    Validators.ValidateCellAddressExpression(range, Cell.AddressScope.Range); // throw if not valid range
                    Range tempRange = new Range(range);
                    Range fixedRange = new Range(new Address(tempRange.StartAddress.Column, tempRange.StartAddress.Row, Cell.AddressType.FixedRowAndColumn), new Address(tempRange.EndAddress.Column, tempRange.EndAddress.Row, Cell.AddressType.FixedRowAndColumn));
                    this.TextValue = fixedRange.ToString();
                    this.Value = fixedRange;
                    break;
                case NameType.Formula:
                    this.TextValue = this.Value.ToString(); // No formula validation yet
                    break;
                default: // constant
                    this.TextValue = ParserUtils.ToCachedValueString(this.Value, false);
                    break;
            }
        }

        private static bool ContainsExternalLink(string worksheet, NameType type, object value)
        {
            switch (type)
            {
                case NameType.Formula:
                    string formula = value as string;
                    return ParserUtils.ContainsExternalReference(formula);
                case NameType.Range:
                case NameType.Cell:
                    return worksheet != null && EXT_WORKSHEET_REFERENCE_REGEX.IsMatch(worksheet);
                default: // constant
                    break; // NoOp
            }
            return false;
        }

        private static object GetParsedObject(string reference, out NameType type, out string worksheet, out FormulaError error)
        {
            reference = reference?.Trim();
            error = FormulaError.NoError;
            worksheet = null;
            if (ParserUtils.TryParseFormulaStringConstant(reference, out string stringValue))
            {
                type = NameType.Constant; // Formula string is interpreted as constant in this case
                return stringValue;
            }
            if (ParserUtils.TryParseBool(reference, out bool boolValue))
            {
                type = NameType.Constant;
                return boolValue;
            }
            if (ParserUtils.TryParseInt(reference, out int intValue))
            {
                type = NameType.Constant;
                return intValue;
            }
            if (ParserUtils.TryParseDouble(reference, out double doubleValue))
            {
                type = NameType.Constant;
                return doubleValue;
            }
            string worksheetName;
            string addressExpression;
            if (ParserUtils.TryParseWorksheetQualifiedReference(reference, out worksheetName, out addressExpression))
            {
                worksheet = worksheetName;
                try
                {
                    Address addressValue = new Address(addressExpression);
                    type = NameType.Cell;
                    return addressValue;
                }
                catch
                {
                    // NoOp
                }
                try
                {
                    Range range = new Range(addressExpression);
                    type = NameType.Range;
                    return range;
                }
                catch
                {
                    // NoOp
                }
            }
            FormulaError referenceError;
            if (Errors.TryParseFormulaError(reference, out referenceError))
            {
                error = referenceError;
            }
            type = NameType.Formula;
            return reference;
        }

        public bool Equals(DefinedName other)
        {
            if (other is null)
            {
                return false;
            }
            if (ReferenceEquals(this, other))
            {
                return true;
            }
            return string.Equals(Name, other.Name, StringComparison.OrdinalIgnoreCase)
                && Enum.Equals(Type, other.Type)
                && string.Equals(TextValue, other.TextValue, StringComparison.Ordinal) // object implicit compared by string
                && string.Equals(Comment, other.Comment, StringComparison.Ordinal)
                && ReferenceEquals(TargetWorksheet, other.TargetWorksheet)
                && ReferenceEquals(LocalSheet, other.LocalSheet);
        }

        public override bool Equals(object obj)
        {
            return Equals(obj as DefinedName);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hash = 17;
                hash = (hash * 31) + (Name != null ? StringComparer.OrdinalIgnoreCase.GetHashCode(Name) : 0);
                hash = (hash * 31) + Type.GetHashCode();
                hash = (hash * 31) + (TextValue != null ? TextValue.GetHashCode() : 0); // Object implicit covered by string
                hash = (hash * 31) + (Comment != null ? Comment.GetHashCode() : 0);
                hash = (hash * 31) + (TargetWorksheet != null ? System.Runtime.CompilerServices.RuntimeHelpers.GetHashCode(TargetWorksheet) : 0);
                hash = (hash * 31) + (LocalSheet != null ? System.Runtime.CompilerServices.RuntimeHelpers.GetHashCode(LocalSheet) : 0);
                return hash;
            }
        }

        public int CompareTo(DefinedName other)
        {
            if (other is null)
            {
                return 1;
            }
            int cmp = StringComparer.OrdinalIgnoreCase.Compare(Name, other.Name);
            if (cmp != 0)
            {
                return cmp;
            }
            cmp = Type.CompareTo(other.Type);
            if (cmp != 0)
            {
                return cmp;
            }
            cmp = CompareScope(LocalSheet, other.LocalSheet);
            if (cmp != 0)
            {
                return cmp;
            }
            cmp = CompareScope(TargetWorksheet, other.TargetWorksheet);
            if (cmp != 0)
            {
                return cmp;
            }
            cmp = string.CompareOrdinal(TextValue, other.TextValue);
            if (cmp != 0)
            {
                return cmp;
            }
            return string.CompareOrdinal(Comment, other.Comment);
        }

        public override string ToString()
        {
            string scope = LocalSheet == null ? "workbook" : "sheet:" + LocalSheet.SheetName;
            return "DefinedName{name=" + Name + ", scope=" + scope + ", ref=" + TextValue + "}";
        }

        private static int CompareScope(Worksheet left, Worksheet right)
        {
            if (ReferenceEquals(left, right))
            {
                return 0;
            }
            if (left == null)
            {
                return -1;
            }
            if (right == null)
            {
                return 1;
            }
            return left.SheetID.CompareTo(right.SheetID);
        }
        #endregion
    }
}