AppsheetEpplus/ExcelAddress.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 *
 * Author							Change						Date
 *******************************************************************************
 * Jan Källman		Added		18-MAR-2010
 * Jan Källman		License changed GPL-->LGPL 2011-12-16
 *******************************************************************************/

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

namespace AppsheetEpplus;

public class ExcelTableAddress {
  public string Name { get; set; }

  public string ColumnSpan { get; set; }

  public bool IsAll { get; set; }

  public bool IsHeader { get; set; }

  public bool IsData { get; set; }

  public bool IsTotals { get; set; }

  public bool IsThisRow { get; set; }
}

/// <summary>
/// A range address
/// </summary>
/// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
public class ExcelAddressBase : ExcelCellBase {
  protected internal int _fromRow = -1,
      _toRow,
      _fromCol,
      _toCol;
  protected internal bool _fromRowFixed,
      _fromColFixed,
      _toRowFixed,
      _toColFixed;
  protected internal string _wb;
  protected internal string _ws;
  protected internal string _address;

  protected internal event EventHandler AddressChange;

  internal enum eAddressCollition {
    No,
    Partly,
    Inside,
    Equal,
  }

  internal enum eShiftType {
    Right,
    Down,
    EntireRow,
    EntireColumn,
  }

  internal ExcelAddressBase() {}

  /// <summary>
  /// Creates an Address object
  /// </summary>
  /// <param name="fromRow">start row</param>
  /// <param name="fromCol">start column</param>
  /// <param name="toRow">End row</param>
  /// <param name="toColumn">End column</param>
  public ExcelAddressBase(int fromRow, int fromCol, int toRow, int toColumn) {
    _fromRow = fromRow;
    _toRow = toRow;
    _fromCol = fromCol;
    _toCol = toColumn;
    Validate();

    _address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
  }

  /// <summary>
  /// Creates an Address object
  /// </summary>
  /// <param name="fromRow">start row</param>
  /// <param name="fromCol">start column</param>
  /// <param name="toRow">End row</param>
  /// <param name="toColumn">End column</param>
  /// <param name="fromRowFixed">start row fixed</param>
  /// <param name="fromColFixed">start column fixed</param>
  /// <param name="toRowFixed">End row fixed</param>
  /// <param name="toColFixed">End column fixed</param>
  public ExcelAddressBase(
      int fromRow,
      int fromCol,
      int toRow,
      int toColumn,
      bool fromRowFixed,
      bool fromColFixed,
      bool toRowFixed,
      bool toColFixed) {
    _fromRow = fromRow;
    _toRow = toRow;
    _fromCol = fromCol;
    _toCol = toColumn;
    _fromRowFixed = fromRowFixed;
    _fromColFixed = fromColFixed;
    _toRowFixed = toRowFixed;
    _toColFixed = toColFixed;
    Validate();

    _address = GetAddress(
        _fromRow,
        _fromCol,
        _toRow,
        _toCol,
        _fromRowFixed,
        fromColFixed,
        _toRowFixed,
        _toColFixed);
  }

  /// <summary>
  /// Creates an Address object
  /// </summary>
  /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
  /// <param name="address">The Excel Address</param>
  public ExcelAddressBase(string address) {
    SetAddress(address);
  }

  /// <summary>
  /// Creates an Address object
  /// </summary>
  /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
  /// <param name="address">The Excel Address</param>
  /// <param name="pck">Reference to the package to find information about tables and names</param>
  /// <param name="referenceAddress">The address</param>
  protected ExcelAddressBase(
      string address,
      ExcelWorkbook workbook,
      ExcelAddressBase referenceAddress) {
    SetAddress(address);
    SetRcFromTable(workbook, referenceAddress);
  }

  internal void SetRcFromTable(ExcelWorkbook workbook, ExcelAddressBase referenceAddress) {
    if (string.IsNullOrEmpty(_wb) && Table != null) {
      foreach (var ws in workbook.Worksheets) {
        foreach (var t in ws.Tables) {
          if (t.Name.Equals(Table.Name, StringComparison.InvariantCultureIgnoreCase)) {
            _ws = ws.Name;
            if (Table.IsAll) {
              _fromRow = t.Address._fromRow;
              _toRow = t.Address._toRow;
            } else {
              if (Table.IsThisRow) {
                if (referenceAddress == null) {
                  _fromRow = -1;
                  _toRow = -1;
                } else {
                  _fromRow = referenceAddress._fromRow;
                  _toRow = _fromRow;
                }
              } else if (Table.IsHeader && Table.IsData) {
                _fromRow = t.Address._fromRow;
                _toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
              } else if (Table.IsData && Table.IsTotals) {
                _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                _toRow = t.Address._toRow;
              } else if (Table.IsHeader) {
                _fromRow = t.ShowHeader ? t.Address._fromRow : -1;
                _toRow = t.ShowHeader ? t.Address._fromRow : -1;
              } else if (Table.IsTotals) {
                _fromRow = t.ShowTotal ? t.Address._toRow : -1;
                _toRow = t.ShowTotal ? t.Address._toRow : -1;
              } else {
                _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                _toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
              }
            }

            if (string.IsNullOrEmpty(Table.ColumnSpan)) {
              _fromCol = t.Address._fromCol;
              _toCol = t.Address._toCol;
              return;
            }
            var col = t.Address._fromCol;
            var cols = Table.ColumnSpan.Split(':');
            foreach (var c in t.Columns) {
              if (_fromCol <= 0
                  && cols[0]
                      .Equals(
                          c.Name,
                          StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
              {
                _fromCol = col;
                if (cols.Length == 1) {
                  _toCol = _fromCol;
                  return;
                }
              } else if (cols.Length > 1
                  && _fromCol > 0
                  && cols[1]
                      .Equals(
                          c.Name,
                          StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
              {
                _toCol = col;
                return;
              }

              col++;
            }
          }
        }
      }
    }
  }

  /// <summary>
  /// Address is an defined name
  /// </summary>
  /// <param name="address">the name</param>
  /// <param name="isName">Should always be true</param>
  internal ExcelAddressBase(string address, bool isName) {
    if (isName) {
      _address = address;
      _fromRow = -1;
      _fromCol = -1;
      _toRow = -1;
      _toCol = -1;
      _start = null;
      _end = null;
    } else {
      SetAddress(address);
    }
  }

  protected internal void SetAddress(string address) {
    address = address.Trim();
    if (address.StartsWith("'")) {
      SetWbWs(address);
    } else if (address.StartsWith(
        "[")) //Remove any external reference
    {
      SetWbWs(address);
    } else {
      _address = address;
    }
    if (_address.IndexOfAny([',', '!', '[']) > -1) {
      //Advanced address. Including Sheet or multi or table.
      ExtractAddress(_address);
    } else {
      //Simple address
      GetRowColFromAddress(
          _address,
          out _fromRow,
          out _fromCol,
          out _toRow,
          out _toCol,
          out _fromRowFixed,
          out _fromColFixed,
          out _toRowFixed,
          out _toColFixed);
      _addresses = null;
      _start = null;
      _end = null;
    }
    _address = address;
    Validate();
  }

  internal void ChangeAddress() {
    if (AddressChange != null) {
      AddressChange(this, new());
    }
  }

  private void SetWbWs(string address) {
    int pos = 0;

    // Get Workbook, if any
    if (address[pos] == '[') {
      pos = address.IndexOf("]");
      _wb = address.Substring(1, pos - 1);
      pos++;
    } else {
      _wb = "";
    }

    // Get Worksheet
    if (address[pos] == '\'') {
      int startPos = pos;
      pos = address.IndexOf("'", pos + 1);
      while (pos < address.Length && address[pos + 1] == '\'') {
        pos = address.IndexOf("'", pos + 2);
      }
      _ws = address.Substring(startPos + 1, pos - startPos - 1).Replace("''", "'");
      pos++;
    } else {
      int startPos = pos;
      pos = address.IndexOf("!", pos);
      if (pos > -1) {
        _ws = address.Substring(startPos, pos - startPos);
      }
    }

    // Get Address
    pos = address.IndexOf("!", pos);
    if (pos > -1) {
      _address = address.Substring(pos + 1);
    } else {
      _address = "";
    }
  }

  internal void ChangeWorksheet(string wsName, string newWs) {
    if (_ws == wsName) {
      _ws = newWs;
    }
    var fullAddress = GetAddress();

    if (Addresses != null) {
      foreach (var a in Addresses) {
        if (a._ws == wsName) {
          a._ws = newWs;
          fullAddress += "," + a.GetAddress();
        } else {
          fullAddress += "," + a._address;
        }
      }
    }
    _address = fullAddress;
  }

  private string GetAddress() {
    var adr = "";
    if (string.IsNullOrEmpty(_wb)) {
      adr = "[" + _wb + "]";
    }

    if (string.IsNullOrEmpty(_ws)) {
      adr += string.Format("'{0}'!", _ws);
    }
    adr += GetAddress(_fromRow, _fromCol, _toRow, _toCol);
    return adr;
  }

  private ExcelCellAddress _start;

  /// <summary>
  /// Gets the row and column of the top left cell.
  /// </summary>
  /// <value>The start row column.</value>
  public ExcelCellAddress Start {
    get {
      if (_start == null) {
        _start = new(_fromRow, _fromCol);
      }
      return _start;
    }
  }

  private ExcelCellAddress _end;

  /// <summary>
  /// Gets the row and column of the bottom right cell.
  /// </summary>
  /// <value>The end row column.</value>
  public ExcelCellAddress End {
    get {
      if (_end == null) {
        _end = new(_toRow, _toCol);
      }
      return _end;
    }
  }

  private ExcelTableAddress _table;

  public ExcelTableAddress Table => _table;

  /// <summary>
  /// The address for the range
  /// </summary>
  public virtual string Address => _address;

  /// <summary>
  /// If the address is a defined name
  /// </summary>
  public bool IsName => _fromRow < 0;

  /// <summary>
  /// Returns the address text
  /// </summary>
  /// <returns></returns>
  public override string ToString() {
    return _address;
  }

  private string _firstAddress;

  /// <summary>
  /// returns the first address if the address is a multi address.
  /// A1:A2,B1:B2 returns A1:A2
  /// </summary>
  internal string FirstAddress {
    get {
      if (string.IsNullOrEmpty(_firstAddress)) {
        return _address;
      }
      return _firstAddress;
    }
  }

  internal string AddressSpaceSeparated => _address.Replace(',', ' '); //Conditional formatting and a few other places use space as separator for mulit addresses.

  /// <summary>
  /// Validate the address
  /// </summary>
  protected void Validate() {
    if (_fromRow > _toRow || _fromCol > _toCol) {
      throw new ArgumentOutOfRangeException(
          "Start cell Address must be less or equal to End cell address");
    }
  }

  internal string WorkSheet => _ws;

  protected internal List<ExcelAddress> _addresses;

  internal virtual List<ExcelAddress> Addresses => _addresses;

  private bool ExtractAddress(string fullAddress) {
    var brackPos = new Stack<int>();
    var bracketParts = new List<string>();
    string first = "",
        second = "";
    bool isText = false,
        hasSheet = false;
    try {
      if (fullAddress == "#REF!") {
        SetAddress(ref fullAddress, ref second, ref hasSheet);
        return true;
      }
      if (fullAddress.StartsWith("!")) {
        // invalid address!
        return false;
      }
      for (int i = 0; i < fullAddress.Length; i++) {
        var c = fullAddress[i];
        if (c == '\'') {
          if (isText && i + 1 < fullAddress.Length && fullAddress[i] == '\'') {
            if (hasSheet) {
              second += c;
            } else {
              first += c;
            }
          }
          isText = !isText;
        } else {
          if (brackPos.Count > 0) {
            if (c == '[' && !isText) {
              brackPos.Push(i);
            } else if (c == ']' && !isText) {
              if (brackPos.Count > 0) {
                var from = brackPos.Pop();
                bracketParts.Add(fullAddress.Substring(from + 1, i - from - 1));

                if (brackPos.Count == 0) {
                  HandleBrackets(first, second, bracketParts);
                }
              } else {
                //Invalid address!
                return false;
              }
            }
          } else if (c == '[' && !isText) {
            brackPos.Push(i);
          } else if (c == '!' && !isText && !first.EndsWith("#REF") && !second.EndsWith("#REF")) {
            hasSheet = true;
          } else if (c == ',' && !isText) {
            SetAddress(ref first, ref second, ref hasSheet);
          } else {
            if (hasSheet) {
              second += c;
            } else {
              first += c;
            }
          }
        }
      }
      if (Table == null) {
        SetAddress(ref first, ref second, ref hasSheet);
      }
      return true;
    } catch {
      return false;
    }
  }

  private void HandleBrackets(string first, string second, List<string> bracketParts) {
    if (!string.IsNullOrEmpty(first)) {
      _table = new();
      Table.Name = first;
      foreach (var s in bracketParts) {
        if (s.IndexOf("[") < 0) {
          switch (s.ToLower(CultureInfo.InvariantCulture)) {
            case "#all":
              _table.IsAll = true;
              break;
            case "#headers":
              _table.IsHeader = true;
              break;
            case "#data":
              _table.IsData = true;
              break;
            case "#totals":
              _table.IsTotals = true;
              break;
            case "#this row":
              _table.IsThisRow = true;
              break;
            default:
              if (string.IsNullOrEmpty(_table.ColumnSpan)) {
                _table.ColumnSpan = s;
              } else {
                _table.ColumnSpan += ":" + s;
              }
              break;
          }
        }
      }
    }
  }

  internal eAddressCollition Collide(ExcelAddressBase address) {
    if (address.WorkSheet != WorkSheet && address.WorkSheet != null) {
      return eAddressCollition.No;
    }

    if (address._fromRow > _toRow
        || address._fromCol > _toCol
        || _fromRow > address._toRow
        || _fromCol > address._toCol) {
      return eAddressCollition.No;
    }
    if (address._fromRow == _fromRow
        && address._fromCol == _fromCol
        && address._toRow == _toRow
        && address._toCol == _toCol) {
      return eAddressCollition.Equal;
    }
    if (address._fromRow >= _fromRow
        && address._toRow <= _toRow
        && address._fromCol >= _fromCol
        && address._toCol <= _toCol) {
      return eAddressCollition.Inside;
    }
    return eAddressCollition.Partly;
  }

  internal ExcelAddressBase AddRow(int row, int rows, bool setFixed = false) {
    if (row > _toRow) {
      return this;
    }
    if (row <= _fromRow) {
      return new(
          (setFixed && _fromRowFixed ? _fromRow : _fromRow + rows),
          _fromCol,
          (setFixed && _toRowFixed ? _toRow : _toRow + rows),
          _toCol,
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    return new(
        _fromRow,
        _fromCol,
        (setFixed && _toRowFixed ? _toRow : _toRow + rows),
        _toCol,
        _fromRowFixed,
        _fromColFixed,
        _toRowFixed,
        _toColFixed);
  }

  internal ExcelAddressBase DeleteRow(int row, int rows, bool setFixed = false) {
    if (row
        > _toRow) //After
    {
      return this;
    }
    if (row + rows
        <= _fromRow) //Before
    {
      return new(
          (setFixed && _fromRowFixed ? _fromRow : _fromRow - rows),
          _fromCol,
          (setFixed && _toRowFixed ? _toRow : _toRow - rows),
          _toCol,
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    if (row <= _fromRow
        && row + rows
            > _toRow) //Inside
    {
      return null;
    } //Partly
    if (row <= _fromRow) {
      return new(
          row,
          _fromCol,
          (setFixed && _toRowFixed ? _toRow : _toRow - rows),
          _toCol,
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    return new(
        _fromRow,
        _fromCol,
        (setFixed && _toRowFixed
                ? _toRow
                : _toRow - rows < row
                    ? row - 1
                    : _toRow - rows),
        _toCol,
        _fromRowFixed,
        _fromColFixed,
        _toRowFixed,
        _toColFixed);
  }

  internal ExcelAddressBase AddColumn(int col, int cols, bool setFixed = false) {
    if (col > _toCol) {
      return this;
    }
    if (col <= _fromCol) {
      return new(
          _fromRow,
          (setFixed && _fromColFixed ? _fromCol : _fromCol + cols),
          _toRow,
          (setFixed && _toColFixed ? _toCol : _toCol + cols),
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    return new(
        _fromRow,
        _fromCol,
        _toRow,
        (setFixed && _toColFixed ? _toCol : _toCol + cols),
        _fromRowFixed,
        _fromColFixed,
        _toRowFixed,
        _toColFixed);
  }

  internal ExcelAddressBase DeleteColumn(int col, int cols, bool setFixed = false) {
    if (col
        > _toCol) //After
    {
      return this;
    }
    if (col + cols
        <= _fromCol) //Before
    {
      return new(
          _fromRow,
          (setFixed && _fromColFixed ? _fromCol : _fromCol - cols),
          _toRow,
          (setFixed && _toColFixed ? _toCol : _toCol - cols),
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    if (col <= _fromCol
        && col + cols
            > _toCol) //Inside
    {
      return null;
    } //Partly
    if (col <= _fromCol) {
      return new(
          _fromRow,
          col,
          _toRow,
          (setFixed && _toColFixed ? _toCol : _toCol - cols),
          _fromRowFixed,
          _fromColFixed,
          _toRowFixed,
          _toColFixed);
    }
    return new(
        _fromRow,
        _fromCol,
        _toRow,
        (setFixed && _toColFixed
                ? _toCol
                : _toCol - cols < col
                    ? col - 1
                    : _toCol - cols),
        _fromRowFixed,
        _fromColFixed,
        _toRowFixed,
        _toColFixed);
  }

  internal ExcelAddressBase Insert(
      ExcelAddressBase address,
      eShiftType shift /*, out ExcelAddressBase topAddress, out ExcelAddressBase leftAddress, out ExcelAddressBase rightAddress, out ExcelAddressBase bottomAddress*/) {
    //Before or after, no change
    //if ((_toRow > address._fromRow && _toCol > address.column) ||
    //    (_fromRow > address._toRow && column > address._toCol))
    if (_toRow < address._fromRow
        || _toCol < address._fromCol
        || (_fromRow > address._toRow && _fromCol > address._toCol)) {
      //topAddress = null;
      //leftAddress = null;
      //rightAddress = null;
      //bottomAddress = null;
      return this;
    }

    int rows = address.Rows;
    int cols = address.Columns;
    string retAddress = "";
    if (shift == eShiftType.Right) {
      if (address._fromRow > _fromRow) {
        retAddress = GetAddress(
            _fromRow,
            _fromCol,
            address._fromRow,
            _toCol,
            _fromRowFixed,
            _fromColFixed,
            _toRowFixed,
            _toColFixed);
      }
      if (address._fromCol > _fromCol) {
        retAddress = GetAddress(
            _fromRow < address._fromRow ? _fromRow : address._fromRow,
            _fromCol,
            address._fromRow,
            _toCol,
            _fromRowFixed,
            _fromColFixed,
            _toRowFixed,
            _toColFixed);
      }
    }
    if (_toRow < address._fromRow) {
      if (_fromRow < address._fromRow) {}
    }
    return null;
  }

  private void SetAddress(ref string first, ref string second, ref bool hasSheet) {
    string ws,
        address;
    if (hasSheet) {
      ws = first;
      address = second;
      first = "";
      second = "";
    } else {
      address = first;
      ws = "";
      first = "";
    }
    hasSheet = false;
    if (string.IsNullOrEmpty(_firstAddress)) {
      if (string.IsNullOrEmpty(_ws) || !string.IsNullOrEmpty(ws)) {
        _ws = ws;
      }
      _firstAddress = address;
      GetRowColFromAddress(
          address,
          out _fromRow,
          out _fromCol,
          out _toRow,
          out _toCol,
          out _fromRowFixed,
          out _fromColFixed,
          out _toRowFixed,
          out _toColFixed);
    } else {
      if (_addresses == null) {
        _addresses = [];
      }
      _addresses.Add(new(_ws, address));
    }
  }

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

  internal 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 _, out var intAddress)) {
      if (intAddress.Contains(
          "[")) //Table reference
      {
        return string.IsNullOrEmpty(wb) ? AddressType.InternalAddress : AddressType.ExternalAddress;
      }
      if (intAddress.Contains(",")) {
        intAddress = intAddress.Substring(0, 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 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;
    }
    if (fromRow <= toRow
        && fromCol <= toCol
        && fromCol > -1
        && toCol <= ExcelPackage.MaxColumns
        && fromRow > -1
        && toRow <= ExcelPackage.MaxRows) {
      return true;
    }
    return false;
  }

  private static bool SplitAddress(
      string address,
      out string wb,
      out string ws,
      out string intAddress) {
    wb = "";
    ws = "";
    intAddress = "";
    var text = "";
    bool isText = false;
    var brackPos = -1;
    for (int 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);
            ws = text.Substring(text.IndexOf("]") + 1);
          } else {
            ws = text;
          }
          intAddress = address.Substring(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;
    for (int i = 0; i < address.Length; i++) {
      if (address[i] == '\'') {
        isText = !isText;
      } else {
        if (isText == false
            && address
                .Substring(i, 1)
                .IndexOfAny(['(', ')', '+', '-', '*', '/', '.', '=', '^', '&', '%', '\"']) > -1) {
          return true;
        }
      }
    }
    return false;
  }

  public int Rows => _toRow - _fromRow + 1;

  public int Columns => _toCol - _fromCol + 1;

  internal static String GetWorkbookPart(string address) {
    int ix;
    if (address[0] == '[') {
      ix = address.IndexOf(']') + 1;
      if (ix > 0) {
        return address.Substring(1, ix - 2);
      }
    }
    return "";
  }

  internal static string GetWorksheetPart(string address, string defaultWorkSheet, ref int endIx) {
    if (address == "") {
      return defaultWorkSheet;
    }
    var ix = 0;
    if (address[0] == '[') {
      ix = address.IndexOf(']') + 1;
    }
    if (ix > 0 && ix < address.Length) {
      if (address[ix] == '\'') {
        return GetString(address, ix, out endIx);
      }
      var ixEnd = address.IndexOf('!', ix);
      if (ixEnd > ix) {
        return address.Substring(ix, ixEnd - ix);
      }
      return defaultWorkSheet;
    }
    return defaultWorkSheet;
  }

  internal static void SplitAddress(
      string fullAddress,
      out string wb,
      out string ws,
      out string address,
      string defaultWorksheet = "") {
    wb = GetWorkbookPart(fullAddress);
    int ix = 0;
    ws = GetWorksheetPart(fullAddress, defaultWorksheet, ref ix);
    if (ix < fullAddress.Length) {
      if (fullAddress[ix] == '!') {
        address = fullAddress.Substring(ix + 1);
      } else {
        address = fullAddress.Substring(ix);
      }
    } else {
      address = "";
    }
  }

  private static string GetString(string address, int ix, out int endIx) {
    var strIx = address.IndexOf("''");
    while (strIx > -1) {
      strIx = address.IndexOf("''");
    }
    endIx = address.IndexOf("'");
    return address.Substring(ix, endIx - ix).Replace("''", "'");
  }

  internal bool IsValidRowCol() {
    return !(_fromRow > _toRow
            || _fromCol > _toCol
            || _fromRow < 1
            || _fromCol < 1
            || _toRow > ExcelPackage.MaxRows
            || _toCol > ExcelPackage.MaxColumns);
  }
}

/// <summary>
/// Range address with the address property readonly
/// </summary>
public class ExcelAddress : ExcelAddressBase {
  internal ExcelAddress() {}

  public ExcelAddress(int fromRow, int fromCol, int toRow, int toColumn)
      : base(fromRow, fromCol, toRow, toColumn) {
    _ws = "";
  }

  public ExcelAddress(string address)
      : base(address) {}

  internal ExcelAddress(string ws, string address)
      : base(address) {
    if (string.IsNullOrEmpty(_ws)) {
      _ws = ws;
    }
  }

  internal ExcelAddress(string ws, string address, bool isName)
      : base(address, isName) {
    if (string.IsNullOrEmpty(_ws)) {
      _ws = ws;
    }
  }

  public ExcelAddress(string address, ExcelWorkbook workbook, ExcelAddressBase referenceAddress)
      : base(address, workbook, referenceAddress) {}

  /// <summary>
  /// The address for the range
  /// </summary>
  /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
  public new string Address {
    get {
      if (string.IsNullOrEmpty(_address) && _fromRow > 0) {
        _address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
      }
      return _address;
    }
    set {
      SetAddress(value);
      ChangeAddress();
    }
  }
}

public class ExcelFormulaAddress : ExcelAddressBase {
  private bool _fromRowFixed,
      _toRowFixed,
      _fromColFixed,
      _toColFixed;

  public ExcelFormulaAddress(int fromRow, int fromCol, int toRow, int toColumn)
      : base(fromRow, fromCol, toRow, toColumn) {
    _ws = "";
  }

  public ExcelFormulaAddress(string address)
      : base(address) {
    SetFixed();
  }

  private void SetFixed() {
    if (Address.IndexOf("[") >= 0) {
      return;
    }
    var address = FirstAddress;
    if (_fromRow == _toRow && _fromCol == _toCol) {
      GetFixed(address, out _fromRowFixed, out _fromColFixed);
    } else {
      var cells = address.Split(':');
      GetFixed(cells[0], out _fromRowFixed, out _fromColFixed);
      GetFixed(cells[1], out _toRowFixed, out _toColFixed);
    }
  }

  private void GetFixed(string address, out bool rowFixed, out bool colFixed) {
    rowFixed = colFixed = false;
    var ix = address.IndexOf('$');
    while (ix > -1) {
      ix++;
      if (ix < address.Length) {
        if (address[ix] >= '0' && address[ix] <= '9') {
          rowFixed = true;
          break;
        }
        colFixed = true;
      }
      ix = address.IndexOf('$', ix);
    }
  }

  /// <summary>
  /// The address for the range
  /// </summary>
  /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
  public new string Address {
    get {
      if (string.IsNullOrEmpty(_address) && _fromRow > 0) {
        _address = GetAddress(
            _fromRow,
            _fromCol,
            _toRow,
            _toCol,
            _fromRowFixed,
            _toRowFixed,
            _fromColFixed,
            _toColFixed);
      }
      return _address;
    }
    set {
      SetAddress(value);
      ChangeAddress();
      SetFixed();
    }
  }

  internal new List<ExcelFormulaAddress> _addresses;

  public new List<ExcelFormulaAddress> Addresses {
    get {
      if (_addresses == null) {
        _addresses = [];
      }
      return _addresses;
    }
  }

  internal string GetOffset(int row, int column) {
    int fromRow = _fromRow,
        fromCol = _fromCol,
        toRow = _toRow,
        tocol = _toCol;
    var isMulti = (fromRow != toRow || fromCol != tocol);
    if (!_fromRowFixed) {
      fromRow += row;
    }
    if (!_fromColFixed) {
      fromCol += column;
    }
    if (isMulti) {
      if (!_toRowFixed) {
        toRow += row;
      }
      if (!_toColFixed) {
        tocol += column;
      }
    } else {
      toRow = fromRow;
      tocol = fromCol;
    }
    string a = GetAddress(
        fromRow,
        fromCol,
        toRow,
        tocol,
        _fromRowFixed,
        _fromColFixed,
        _toRowFixed,
        _toColFixed);
    if (Addresses != null) {
      foreach (var sa in Addresses) {
        a += "," + sa.GetOffset(row, column);
      }
    }
    return a;
  }
}