ParserUtils.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.Globalization;
using System.Linq;
using System.Text;
 
namespace NanoXLSX.Utils
{
    public static class ParserUtils
    {
        #region constants

        public const string NumericFormat = "G";

        public static readonly CultureInfo InvariantCulture = CultureInfo.InvariantCulture;
 
        #endregion
 
        #region primitiveParsing
        public static bool StartsWith(string input, string value)
        {
            if (input == null && value == null)
            {
                return true;
            }
            else if (input == null && value != null)
            {
                return false;
            }
            else if (value == null)
            {
                return false;
            }
            return input.StartsWith(value, StringComparison.InvariantCulture);
        }

        public static bool NotStartsWith(string input, string value)
        {
            return !StartsWith(input, value);
        }

        public static string ToUpper(string input)
        {
            return !string.IsNullOrEmpty(input) ? input.ToUpper(InvariantCulture) : input;
        }

        public static string ToLower(string input)
        {
            return !string.IsNullOrEmpty(input) ? input.ToLower(InvariantCulture) : input;
        }

        public static string ToString(int input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(float input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(byte input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(sbyte input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(double input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(decimal input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(uint input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(long input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(ulong input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(short input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static string ToString(ushort input)
        {
            return input.ToString(NumericFormat, InvariantCulture);
        }

        public static float ParseFloat(string rawValue)
        {
            return float.Parse(rawValue, InvariantCulture);
        }

        public static int ParseInt(string rawValue)
        {
            return int.Parse(rawValue, NumberStyles.Any, InvariantCulture);
        }

        public static double ParseDouble(string rawValue)
        {
            return double.Parse(rawValue, InvariantCulture);
        }

        public static int ParseBinaryBool(String rawValue)
        {
            if (string.IsNullOrEmpty(rawValue))
            {
                return 0;
            }
            int value;
            if (TryParseInt(rawValue, out value))
            {
                return value >= 1 ? 1 : 0;
            }
            bool regularBool;
            if (TryParseBool(rawValue, out regularBool))
            {
                return regularBool ? 1 : 0;
            }
            return 0;
        }

        public static bool TryParseBool(string rawValue, out bool parsedValue)
        {
            return bool.TryParse(rawValue, out parsedValue);
        }

        public static bool TryParseInt(string rawValue, out int parsedValue)
        {
            return int.TryParse(rawValue, NumberStyles.Integer, InvariantCulture, out parsedValue);
        }

        public static bool TryParseUint(string rawValue, out uint parsedValue)
        {
            return uint.TryParse(rawValue, NumberStyles.Integer, InvariantCulture, out parsedValue);
        }

        public static bool TryParseLong(string rawValue, out long parsedValue)
        {
            return long.TryParse(rawValue, NumberStyles.Integer, InvariantCulture, out parsedValue);
        }

        public static bool TryParseUlong(string rawValue, out ulong parsedValue)
        {
            return ulong.TryParse(rawValue, NumberStyles.Integer, InvariantCulture, out parsedValue);
        }

        public static bool TryParseFloat(string rawValue, out float parsedValue)
        {
            return float.TryParse(rawValue, NumberStyles.Any, CultureInfo.InvariantCulture, out parsedValue);
        }

        public static bool TryParseDecimal(string rawValue, out decimal parsedValue)
        {
            return decimal.TryParse(rawValue, NumberStyles.Float, InvariantCulture, out parsedValue);
        }

        public static bool TryParseDouble(string rawValue, out double parsedValue, NumberStyles numberStyles = NumberStyles.Any)
        {
            return double.TryParse(rawValue, numberStyles, InvariantCulture, out parsedValue);
        }

        public static string NormalizeNewLines(string value)
        {
            if (value == null || (!value.Contains('\n') && !value.Contains('\r')))
            {
                return value;
            }
            return value.Replace("\n\r", "\n").Replace("\r\n", "\n").Replace("\r", "\n").Replace("\n", "\r\n");
        }

        public static bool IsAsciiDigit(char character)
        {
            return character >= '0' && character <= '9';
        }
        #endregion
 
        #region formulaParsing
        public static string ToCachedValueString(object input, bool convertBoolToNumber = true)
        {
            if (input == null) { return "0"; }
            else if (input is string)
            {
                string stringValue = input as string;
                return string.IsNullOrEmpty(stringValue) ? "0" : stringValue;
            }
            else if (input is bool)
            {
                if (convertBoolToNumber)
                {
                    return (bool)input ? "1" : "0";
                }
                else
                {
                    return (bool)input ? "TRUE" : "FALSE";
                }
            }
            else if (input is byte) { return ToString((byte)input); }
            else if (input is sbyte) { return ToString((sbyte)input); }
            else if (input is decimal) { return ToString((decimal)input); }
            else if (input is double) { return ToString((double)input); }
            else if (input is float) { return ToString((float)input); }
            else if (input is int) { return ToString((int)input); }
            else if (input is uint) { return ToString((uint)input); }
            else if (input is long) { return ToString((long)input); }
            else if (input is ulong) { return ToString((ulong)input); }
            else if (input is short) { return ToString((short)input); }
            else if (input is ushort) { return ToString((ushort)input); }
            else if (input is DateTime)
            {
                return DataUtils.GetOADateTimeString((DateTime)input);
            }
            else if (input is TimeSpan)
            {
                return DataUtils.GetOATimeString((TimeSpan)input);
            }
            else
            {
                return input.ToString(); // Generic string
            }
        }

        public static bool TryParseFormulaStringConstant(string expression, out string value, bool enclosingQuotesRemoved = false)
        {
            value = null;
            if (expression == null)
            {
                return false;
            }
 
            int startIndex;
            int endIndex;
            if (enclosingQuotesRemoved)
            {
                // The complete expression represents the content inside the string literal.
                startIndex = 0;
                endIndex = expression.Length;
            }
            else
            {
                // A complete formula string constant requires enclosing double quotes.
                if (expression.Length < 2
                    || expression[0] != '"'
                    || expression[expression.Length - 1] != '"')
                {
                    return false;
                }
                startIndex = 1;
                endIndex = expression.Length - 1;
            }
 
            StringBuilder builder = new StringBuilder(endIndex - startIndex);
            for (int i = startIndex; i < endIndex; i++)
            {
                char current = expression[i];
                if (current != '"')
                {
                    builder.Append(current);
                    continue;
                }
                // Quotes inside an Excel string constant must always occur as a pair.
                if (i + 1 >= endIndex || expression[i + 1] != '"')
                {
                    return false;
                }
                builder.Append('"');
                i++;
            }
 
            value = builder.ToString();
            return true;
        }
        #endregion
 
        #region referenceParsing
        public static bool TryParseWorksheetQualifiedReference(string expression, out string worksheetName, out string reference)
        {
            worksheetName = null;
            reference = null;
 
            if (string.IsNullOrEmpty(expression))
            {
                return false;
            }
 
            if (expression[0] != '\'')
            {
                int separatorIndex = expression.IndexOf('!');
                if (separatorIndex <= 0 || separatorIndex == expression.Length - 1)
                {
                    return false;
                }
 
                worksheetName = expression.Substring(0, separatorIndex);
                reference = expression.Substring(separatorIndex + 1);
                return true;
            }
 
            StringBuilder builder = new StringBuilder();
            for (int i = 1; i < expression.Length; i++)
            {
                char current = expression[i];
                if (current != '\'')
                {
                    builder.Append(current);
                    continue;
                }
 
                // Two apostrophes inside a quoted worksheet name represent one literal apostrophe.
                if (i + 1 < expression.Length && expression[i + 1] == '\'')
                {
                    builder.Append('\'');
                    i++;
                    continue;
                }
 
                // A single apostrophe closes the quoted worksheet name. It must immediately be followed by the reference separator.
                if (i + 1 >= expression.Length || expression[i + 1] != '!')
                {
                    return false;
                }
 
                if (i + 2 >= expression.Length)
                {
                    return false;
                }
 
                worksheetName = builder.ToString();
                reference = expression.Substring(i + 2);
                return true;
            }
            // No closing apostrophe was found.
            return false;
        }
        #endregion
 
        #region externalReferenceParsing

        internal static bool ContainsExternalReference(string formulaExpression)
        {
            if (string.IsNullOrEmpty(formulaExpression) || formulaExpression.IndexOf('[') < 0)
            {
                return false;
            }
 
            bool inStringLiteral = false;
            for (int i = 0; i < formulaExpression.Length; i++)
            {
                char current = formulaExpression[i];
                if (current == '"')
                {
                    if (inStringLiteral && i + 1 < formulaExpression.Length && formulaExpression[i + 1] == '"')
                    {
                        i++;
                        continue;
                    }
                    inStringLiteral = !inStringLiteral;
                    continue;
                }
                if (inStringLiteral || current != '[')
                {
                    continue;
                }
 
                int closingBracket = formulaExpression.IndexOf(']', i + 1);
                if (closingBracket <= i + 1)
                {
                    continue;
                }
 
                bool hasWorksheetName = false;
                for (int j = closingBracket + 1; j < formulaExpression.Length; j++)
                {
                    char referenceCharacter = formulaExpression[j];
                    if (referenceCharacter == '!')
                    {
                        if (hasWorksheetName)
                        {
                            return true;
                        }
                        break;
                    }
                    if (referenceCharacter == '[' || referenceCharacter == ']' || referenceCharacter == '"'
                        || referenceCharacter == '+' || referenceCharacter == '-' || referenceCharacter == '*'
                        || referenceCharacter == '/' || referenceCharacter == '^' || referenceCharacter == '&'
                        || referenceCharacter == '=' || referenceCharacter == '<' || referenceCharacter == '>'
                        || referenceCharacter == ',' || referenceCharacter == ';' || referenceCharacter == '('
                        || referenceCharacter == ')' || referenceCharacter == '{' || referenceCharacter == '}')
                    {
                        break;
                    }
                    if (!char.IsWhiteSpace(referenceCharacter) && referenceCharacter != '\'')
                    {
                        hasWorksheetName = true;
                    }
                }
                i = closingBracket;
            }
            return false;
        }

        internal static bool IsValidExternalLinkId(string identifier)
        {
            if (string.IsNullOrEmpty(identifier) ||
                identifier.Length < 3 ||
                identifier[0] != '[' ||
                identifier[identifier.Length - 1] != ']')
            {
                return false;
            }
 
            for (int i = 1; i < identifier.Length - 1; i++)
            {
                if (!IsAsciiDigit(identifier[i]))
                {
                    return false;
                }
            }
            return true;
        }

        internal static bool TryReadExternalLinkId(string expression, int startIndex, out int identifierLength)
        {
            identifierLength = 0;
            if (string.IsNullOrEmpty(expression) ||
                startIndex < 0 || startIndex >= expression.Length || expression[startIndex] != '[')
            {
                return false;
            }
 
            int currentIndex = startIndex + 1;
            if (currentIndex >= expression.Length ||
                !IsAsciiDigit(expression[currentIndex]))
            {
                return false;
            }
 
            do
            {
                currentIndex++;
            }
            while (currentIndex < expression.Length && IsAsciiDigit(expression[currentIndex]));
            if (currentIndex >= expression.Length || expression[currentIndex] != ']')
            {
                return false;
            }
 
            int closingBracketIndex = currentIndex;
            if (!HasValidPrefixBoundary(expression, startIndex))
            {
                return false;
            }
            if (!HasValidSuffixBoundary(expression, closingBracketIndex))
            {
                return false;
            }
            identifierLength = closingBracketIndex - startIndex + 1;
            return true;
        }

        private static bool HasValidPrefixBoundary(string expression, int openingBracketIndex)
        {
            if (openingBracketIndex == 0)
            {
                return true;
            }
 
            char previous = expression[openingBracketIndex - 1];
            // Quoted external sheet reference: '[1]Sheet name'!A1
            if (previous == '\'')
            {
                return true;
            }
            // Table1[1], SomeName[2], etc.
            return !IsNameCharacter(previous);
        }

        private static bool HasValidSuffixBoundary(string expression, int closingBracketIndex)
        {
            int nextIndex = closingBracketIndex + 1;
            if (nextIndex >= expression.Length)
            {
                // A bare [1] can be a structured table-column reference.
                return false;
            }
            char next = expression[nextIndex];
            // External defined name / workbook prefix: [1]!ExternalName
            if (next == '!')
            {
                return true;
            }
            // Broken external sheet reference:  [1]#REF!A1
            if (next == '#')
            {
                return true;
            }
            // The sheet or external name must immediately follow the ID.
            if (char.IsWhiteSpace(next))
            {
                return false;
            }
            switch (next)
            {
                case '"':
                case '[':
                case ']':
                case '(':
                case ')':
                case ',':
                case ';':
                case '+':
                case '-':
                case '*':
                case '/':
                case '^':
                case '&':
                case '=':
                case '<':
                case '>':
                case '%':
                case ':':
                    return false;
                default:
                    return true;
            }
        }

        private static bool IsNameCharacter(char character)
        {
            return char.IsLetterOrDigit(character) ||
                   character == '_' ||
                   character == '\\' ||
                   character == '.';
        }
 
        #endregion
 
    }
}