EpplusFormulaParser/ExcelUtilities/ExcelAddressBase.cs

using System.Collections.Frozen;
using System.Collections.Generic;
using System.Globalization;

/// <summary>
/// A range address
/// </summary>
/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
internal static class ExcelAddressUtilities {
  /// <summary>
  /// Maximum number of columns in a worksheet (16384).
  /// </summary>
  private const int _maxColumns = 16384;

  /// <summary>
  /// Maximum number of rows in a worksheet (1048576).
  /// </summary>
  private const int _maxRows = 1048576;

  private static readonly FrozenSet<char> _formulaChars = new HashSet<char> {
    '(',
    ')',
    '+',
    '-',
    '*',
    '/',
    '.',
    '=',
    '^',
    '&',
    '%',
    '\"',
  }.ToFrozenSet();

  public enum AddressType {
    Invalid,
    InternalAddress,
    ExternalAddress,
    InternalName,
    ExternalName,
    Formula,
  }

  public static AddressType IsValid(string address) {
    if (address == "#REF!") {
      return AddressType.Invalid;
    }
    if (double.TryParse(
        address,
        NumberStyles.Any,
        CultureInfo.InvariantCulture,
        out _)) //A double, no valid address
    {
      return AddressType.Invalid;
    }
    if (IsFormula(address)) {
      return AddressType.Formula;
    }
    if (SplitAddress(address, out var wb, out var intAddress)) {
      if (intAddress.Contains(
          '[')) //Table reference
      {
        return string.IsNullOrEmpty(wb) ? AddressType.InternalAddress : AddressType.ExternalAddress;
      }
      if (intAddress.Contains(',')) {
        intAddress = intAddress[..intAddress.IndexOf(',')];
      }
      if (IsAddress(intAddress)) {
        return string.IsNullOrEmpty(wb) ? AddressType.InternalAddress : AddressType.ExternalAddress;
      }
      return string.IsNullOrEmpty(wb) ? AddressType.InternalName : AddressType.ExternalName;
    }
    return AddressType.Invalid;
  }

  private static bool GetRowCol(string address, out int row, out int col, bool throwException) {
    var colPart = true;
    var colStartIx = 0;
    var colLength = 0;
    col = 0;
    row = 0;

    if (address.EndsWith("#REF!")) {
      row = 0;
      col = 0;
      return true;
    }

    var sheetMarkerIndex = address.IndexOf('!');
    if (sheetMarkerIndex >= 0) {
      colStartIx = sheetMarkerIndex + 1;
    }
    address = address.ToUpper(CultureInfo.InvariantCulture);
    for (var i = colStartIx; i < address.Length; i++) {
      var c = address[i];
      if (colPart && c >= 'A' && c <= 'Z' && colLength <= 3) {
        col *= 26;
        col += c - 64;
        colLength++;
      } else if (c >= '0' && c <= '9') {
        row *= 10;
        row += c - 48;
        colPart = false;
      } else if (c == '$') {
        if (i == colStartIx) {
          colStartIx++;
        } else {
          colPart = false;
        }
      } else {
        row = 0;
        col = 0;
        if (throwException) {
          throw new($"Invalid Address format {address}");
        }
        return false;
      }
    }
    return row != 0 || col != 0;
  }

  private static bool IsAddress(string intAddress) {
    if (string.IsNullOrEmpty(intAddress)) {
      return false;
    }
    var cells = intAddress.Split(':');
    int toRow,
        toCol;

    if (!GetRowCol(cells[0], out var fromRow, out var fromCol, false)) {
      return false;
    }
    if (cells.Length > 1) {
      if (!GetRowCol(cells[1], out toRow, out toCol, false)) {
        return false;
      }
    } else {
      toRow = fromRow;
      toCol = fromCol;
    }
    return fromRow <= toRow
        && fromCol <= toCol
        && fromCol > -1
        && toCol <= _maxColumns
        && fromRow > -1
        && toRow <= _maxRows;
  }

  private static bool SplitAddress(string address, out string wb, out string intAddress) {
    wb = "";
    intAddress = "";
    var text = "";
    var isText = false;
    var brackPos = -1;
    for (var i = 0; i < address.Length; i++) {
      if (address[i] == '\'') {
        isText = !isText;
        if (i > 0 && address[i - 1] == '\'') {
          text += "'";
        }
      } else {
        if (address[i] == '!' && !isText) {
          if (text.Length > 0 && text[0] == '[') {
            wb = text.Substring(1, text.IndexOf(']') - 1);
          }
          intAddress = address[(i + 1)..];
          return true;
        }
        if (address[i] == '[' && !isText) {
          if (i
              > 0) //Table reference return full address;
          {
            intAddress = address;
            return true;
          }
          brackPos = i;
        } else if (address[i] == ']' && !isText) {
          if (brackPos > -1) {
            wb = text;
            text = "";
          } else {
            return false;
          }
        } else {
          text += address[i];
        }
      }
    }
    intAddress = text;
    return true;
  }

  private static bool IsFormula(string address) {
    var isText = false;
    foreach (var c in address) {
      if (c == '\'') {
        isText = !isText;
      } else {
        if (isText == false && _formulaChars.Contains(c)) {
          return true;
        }
      }
    }
    return false;
  }
}