/*******************************************************************************
 * 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		Initial Release		        2009-10-01
 * Jan Källman		License changed GPL-->LGPL 2011-12-27
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Globalization;
using OfficeOpenXml.FormulaParsing;
using OfficeOpenXml.FormulaParsing.Excel.Functions;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;

namespace OfficeOpenXml;

/// <summary>
/// Base class containing cell address manipulating methods.
/// </summary>
public abstract class ExcelCellBase {
  /// <summary>
  /// Get the sheet, row and column from the CellID
  /// </summary>
  /// <param name="cellId"></param>
  /// <param name="sheet"></param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  static internal void SplitCellId(ulong cellId, out int sheet, out int row, out int col) {
    sheet = (int)(cellId % 0x8000);
    col = ((int)(cellId >> 15) & 0x3FF);
    row = ((int)(cellId >> 29));
  }

  /// <summary>
  /// Get the cellID for the cell.
  /// </summary>
  /// <param name="sheetId"></param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  /// <returns></returns>
  internal static ulong GetCellId(int sheetId, int row, int col) {
    return ((ulong)sheetId) + (((ulong)col) << 15) + (((ulong)row) << 29);
  }

  private delegate string AddressTranslator(
    string part,
    int row,
    int col,
    int rowIncr,
    int colIncr);

  /// <summary>
  /// Translates a R1C1 to an absolut address/Formula
  /// </summary>
  /// <param name="value">Address</param>
  /// <param name="row">Current row</param>
  /// <param name="col">Current column</param>
  /// <returns>The RC address</returns>
  public static string TranslateFromR1C1(string value, int row, int col) {
    return Translate(value, ToAbs, row, col, -1, -1);
  }

  /// <summary>
  /// Translates a R1C1 to an absolut address/Formula: Version 1
  /// </summary>
  /// <param name="value">Address</param>
  /// <param name="row">Current row</param>
  /// <param name="col">Current column</param>
  /// <returns>The RC address</returns>
  public static string TranslateFromR1C1_V1(string value, int row, int col) {
    return Translate_V1(value, ToAbs_V1, row, col, -1, -1);
  }

  /// <summary>
  /// Translates a absolut address to R1C1 Format
  /// </summary>
  /// <param name="value">R1C1 Address</param>
  /// <param name="row">Current row</param>
  /// <param name="col">Current column</param>
  /// <returns>The absolut address/Formula</returns>
  public static string TranslateToR1C1(string value, int row, int col) {
    return Translate(value, ToR1C1, row, col, -1, -1);
  }

  /// <summary>
  /// Translates a absolute address to R1C1 Format : Version 1
  /// </summary>
  /// <param name="value">R1C1 Address</param>
  /// <param name="row">Current row</param>
  /// <param name="col">Current column</param>
  /// <returns>The absolut address/Formula</returns>
  public static string TranslateToR1C1_V1(string value, int row, int col) {
    return Translate_V1(value, ToR1C1_V1, row, col, -1, -1);
  }

  /// <summary>
  /// Translates betweein R1C1 or absolute addresses : Version 1
  /// </summary>
  /// <param name="value">The addresss/function</param>
  /// <param name="addressTranslator">The translating function</param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  /// <param name="rowIncr"></param>
  /// <param name="colIncr"></param>
  /// <returns></returns>
  private static string Translate(
      string value,
      AddressTranslator addressTranslator,
      int row,
      int col,
      int rowIncr,
      int colIncr) {
    if (value == "") {
      return "";
    }
    bool isText = false;
    string ret = "";
    string part = "";
    char prevTq = (char)0;
    for (int pos = 0; pos < value.Length; pos++) {
      char c = value[pos];
      if (c == '"' || c == '\'') {
        if (isText && prevTq != c) {
          ret += c;
          continue;
        }

        if (isText == false && part != "" && prevTq == c) {
          ret += addressTranslator(part, row, col, rowIncr, colIncr);
          part = "";
          prevTq = (char)0;
        }
        prevTq = c;
        isText = !isText;
        ret += c;
      } else if (isText) {
        ret += c;
      } else {
        if ((c == '-'
                    || c == '+'
                    || c == '*'
                    || c == '/'
                    || c == '='
                    || c == '^'
                    || c == ','
                    || c == ':'
                    || c == '<'
                    || c == '>'
                    || c == '('
                    || c == ')'
                    || c == '!'
                    || c == ' '
                    || c == '&'
                    || c == '%')
            && (pos == 0
                    || value[pos - 1]
                        != '[')) //Last part to allow for R1C1 style [-x]
        {
          ret += addressTranslator(part, row, col, rowIncr, colIncr) + c;
          part = "";
        } else {
          part += c;
        }
      }
    }
    if (part != "") {
      ret += addressTranslator(part, row, col, rowIncr, colIncr);
    }
    return ret;
  }

  private static string Translate_V1(
      string value,
      AddressTranslator addressTranslator,
      int row,
      int col,
      int rowIncr,
      int colIncr) {
    if (value == "") {
      return "";
    }
    bool isText = false;
    string ret = "";
    string part = "";
    char prevTq = (char)0;
    value = value.Replace("\n", ""); // Eliminate new line characters in the formula
    for (int pos = 0; pos < value.Length; pos++) {
      char c = value[pos];
      if (c == '"' || c == '\'') {
        if (isText && prevTq != c) {
          ret += c;
          continue;
        }

        if (isText == false && part != "" && prevTq == c) {
          ret += addressTranslator(part, row, col, rowIncr, colIncr);
          part = "";
          prevTq = (char)0;
        }
        prevTq = c;
        isText = !isText;
        ret += c;
      } else if (isText) {
        ret += c;
      } else if (c
          == ':') // Keep Range expressions together
      {
        part += c;
      } else {
        if ((c == '-'
                    || c == '+'
                    || c == '*'
                    || c == '/'
                    || c == '='
                    || c == '^'
                    || c == ','
                    || c == '<'
                    || c == '>'
                    || c == '('
                    || c == ')'
                    || c == '!'
                    || c == ' '
                    || c == '&'
                    || c == '%')
            && (pos == 0
                    || value[pos - 1]
                        != '[')) //Last part to allow for R1C1 style [-x]
        {
          ret += addressTranslator(part, row, col, rowIncr, colIncr) + c;
          part = "";
        } else {
          part += c;
        }
      }
    }
    if (part != "") {
      ret += addressTranslator(part, row, col, rowIncr, colIncr);
    }
    return ret;
  }

  /// <summary>
  /// Translate to R1C1
  /// </summary>
  /// <param name="part">the value to be translated</param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  /// <param name="rowIncr"></param>
  /// <param name="colIncr"></param>
  /// <returns></returns>
  private static string ToR1C1(string part, int row, int col, int rowIncr, int colIncr) {
    int addrRow,
        addrCol;
    string ret = "R";
    if (GetRowCol(part, out addrRow, out addrCol, false)) {
      if (addrRow == 0 || addrCol == 0) {
        return part;
      }
      if (part.IndexOf('$', 1) > 0) {
        ret += addrRow.ToString();
      } else if (addrRow - row != 0) {
        ret += string.Format("[{0}]", addrRow - row);
      }

      if (part.StartsWith("$")) {
        return ret + "C" + addrCol;
      }
      if (addrCol - col != 0) {
        return ret + "C" + string.Format("[{0}]", addrCol - col);
      }
      return ret + "C";
    }
    return part;
  }

  private static string ToR1C1_V1(string part, int row, int col, int rowIncr, int colIncr) {
    int addrRow,
        addrCol;

    // Handle range expressions
    if ((part.Length > 1) && (part.IndexOf(':', 1) > 0)) {
      return RangeToR1C1_V1(part, row, col, rowIncr, colIncr);
    }

    string ret = "R";
    if (GetRowCol(part, out addrRow, out addrCol, false)) {
      if (addrRow == 0 || addrCol == 0) {
        return part;
      }
      if (part.IndexOf('$', 1) > 0) {
        ret += addrRow.ToString();
      } else if (addrRow - row != 0) {
        ret += string.Format("[{0}]", addrRow - row);
      }

      if (part.StartsWith("$")) {
        return ret + "C" + addrCol;
      }
      if (addrCol - col != 0) {
        return ret + "C" + string.Format("[{0}]", addrCol - col);
      }
      return ret + "C";
    }
    return part;
  }

  private static string RangeToR1C1_V1(string part, int row, int col, int rowIncr, int colIncr) {
    // Split range expression
    string[] cellValues = part.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries);

    // Convert range expressions
    string result = "";
    result += RangeCellToR1C1_V1(cellValues[0], row, col, rowIncr, colIncr);
    result += ":";
    result +=
        (cellValues.Length > 1)
            ? RangeCellToR1C1_V1(cellValues[1], row, col, rowIncr, colIncr)
            : "";

    // Return converted range expression
    return result;
  }

  private static string RangeCellToR1C1_V1(
      string part,
      int row,
      int col,
      int rowIncr,
      int colIncr) {
    int addrRow,
        addrCol;
    bool fixedRow,
        fixedCol;

    string result = "";
    if (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) {
      if (addrRow > 0) {
        result += "R";
        if (fixedRow) {
          // Absolute row
          result += addrRow.ToString();
        } else if (addrRow - row != 0) {
          // Relative row
          result += string.Format("[{0}]", addrRow - row);
        }
      }

      if (addrCol > 0) {
        result += "C";
        if (fixedCol) {
          // Absolute column
          result += addrCol;
        } else if (addrCol - col != 0) {
          // Relative column
          result += string.Format("[{0}]", addrCol - col);
        }
      }
      return result;
    }
    return part;
  }

  /// <summary>
  /// Translates to absolute address
  /// </summary>
  /// <param name="part"></param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  /// <param name="rowIncr"></param>
  /// <param name="colIncr"></param>
  /// <returns></returns>
  ///
  private static string ToAbs(string part, int row, int col, int rowIncr, int colIncr) {
    string check = part.ToUpper(CultureInfo.InvariantCulture);

    int rStart = check.IndexOf("R");
    if (rStart != 0) {
      return part;
    }
    if (part.Length
        == 1) //R
    {
      return GetAddress(row, col);
    }

    int cStart = check.IndexOf("C");
    bool absoluteRow,
        absoluteCol;
    if (cStart == -1) {
      int rNum = GetRc(part, row, out absoluteRow);
      if (rNum > int.MinValue) {
        return GetAddress(rNum, absoluteRow, col, false);
      }
      return part;
    } else {
      int rNum = GetRc(part.Substring(1, cStart - 1), row, out absoluteRow);
      int cNum = GetRc(part.Substring(cStart + 1, part.Length - cStart - 1), col, out absoluteCol);
      if (rNum > int.MinValue && cNum > int.MinValue) {
        return GetAddress(rNum, absoluteRow, cNum, absoluteCol);
      }
      return part;
    }
  }

  private static string ToAbs_V1(string part, int row, int col, int rowIncr, int colIncr) {
    bool absoluteCol = false;
    bool absoluteRow = false;
    int colNum = -1;
    int rowNum = -1;
    int num;
    int numLength;
    int pos = 0;

    // Handle range expressions
    if ((part.Length > 1) && (part.IndexOf(':', 1) > 0)) {
      return RangeToA1_V1(part, row, col, rowIncr, colIncr);
    }

    // Ensure part is present
    if (string.IsNullOrWhiteSpace(part)) {
      return "";
    }

    // Convert to upper case
    string check = part.ToUpper(CultureInfo.InvariantCulture);

    // Parse "R", if any
    if (pos < part.Length && check[pos] == 'R') {
      pos += 1;

      if (pos >= part.Length) {
        // Only "R" present
        absoluteRow = false;
        rowNum = row;
      } else if (pos < part.Length && check[pos] == 'C') {
        // "R" followed by "C"
        absoluteRow = false;
        rowNum = row;
      } else if (pos < part.Length && check[pos] == '[') {
        // "R" followed by relative row number
        pos += 1;
        num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength);
        if (num == Int32.MinValue) {
          return part;
        }
        pos += numLength;

        if (pos < part.Length && check[pos] == ']') {
          pos += 1;
        } else {
          return part;
        }

        absoluteRow = false;
        rowNum = row + num;
      } else if (pos < part.Length) {
        // "R" followed by absolute row number
        num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength);
        if (rowNum == Int32.MinValue) {
          return part;
        }
        pos += numLength;

        absoluteRow = true;
        rowNum = num;
      }
    }

    // Parse "C", if any
    if (pos < part.Length && check[pos] == 'C') {
      pos += 1;

      if (pos >= part.Length) {
        // Only "C" present
        absoluteCol = false;
        colNum = col;
      } else if (pos < part.Length && check[pos] == '[') {
        // "C" followed by relative column number
        pos += 1;
        num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength);
        if (num == Int32.MinValue) {
          return part;
        }
        pos += numLength;

        if (pos < part.Length && check[pos] == ']') {
          pos += 1;
        } else {
          return part;
        }

        absoluteCol = false;
        colNum = col + num;
      } else if (pos < part.Length) {
        // "C" followed by absolute column number
        num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength);
        if (num == Int32.MinValue) {
          return part;
        }
        pos += numLength;

        absoluteCol = true;
        colNum = num;
      }
    }

    // Ensure nothing remains unparsed
    if (pos < part.Length) {
      return part;
    }

    // Exit if neither row nor column is present
    if ((rowNum == Int32.MinValue) && (colNum == Int32.MinValue)) {
      return part;
    }

    // Append column
    string result = "";
    if (colNum >= 0) {
      if (absoluteCol) {
        result += "$";
      }
      result += GetColumnLetter(colNum);
    }

    // Append row
    if (rowNum >= 0) {
      if (absoluteRow) {
        result += "$";
      }
      result += rowNum.ToString();
    }

    // Return result
    return result;
  }

  private static int GetNumber_V1(string value, out int length) {
    // Get number length
    length = 0;

    // Ensure value is present
    if (string.IsNullOrWhiteSpace(value)) {
      return Int32.MinValue;
    }

    // Check for sign
    if ((length < value.Length) && ((value[length] == '-') || (value[length] == '+'))) {
      length += 1;
    }

    // Get number length
    while (length < value.Length && value[length] >= '0' && value[length] <= '9') {
      length += 1;
    }

    // No number found
    if (length == 0) {
      return Int32.MinValue;
    }

    // Return number value
    int result;
    return (int.TryParse(value.Substring(0, length), out result)) ? result : Int32.MinValue;
  }

  private static string RangeToA1_V1(string part, int row, int col, int rowIncr, int colIncr) {
    // Split range expression
    string[] cellValues = part.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries);

    // Convert range expressions
    string result = "";
    result += ToAbs_V1(cellValues[0], row, col, rowIncr, colIncr);
    result += ":";
    result += ToAbs_V1(cellValues[1], row, col, rowIncr, colIncr);

    // Return converted range expression
    return result;
  }

  private static string RangeCellToA1_V1(string part, int row, int col, int rowIncr, int colIncr) {
    int addrRow,
        addrCol;
    bool fixedRow,
        fixedCol;

    string result = "";
    if (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) {
      if (addrRow > 0) {
        result += "R";
        if (fixedRow) {
          // Absolute row
          result += addrRow.ToString();
        } else if (addrRow - row != 0) {
          // Relative row
          result += string.Format("[{0}]", addrRow - row);
        }
      }

      if (addrCol > 0) {
        result += "C";
        if (fixedCol) {
          // Absolute column
          result += addrCol;
        } else if (addrCol - col != 0) {
          // Relative column
          result += string.Format("[{0}]", addrCol - col);
        }
      }
      return result;
    }
    return part;
  }

  /// <summary>
  /// Adds or subtracts a row or column to an address
  /// </summary>
  /// <param name="address"></param>
  /// <param name="row"></param>
  /// <param name="col"></param>
  /// <param name="rowIncr"></param>
  /// <param name="colIncr"></param>
  /// <returns></returns>
  private static string AddToRowColumnTranslator(
      string address,
      int row,
      int col,
      int rowIncr,
      int colIncr) {
    int fromRow,
        fromCol;
    if (address == "#REF!") {
      return address;
    }
    if (GetRowCol(address, out fromRow, out fromCol, false)) {
      if (fromRow == 0 || fromCol == 0) {
        return address;
      }
      if (rowIncr != 0 && row != 0 && fromRow >= row && address.IndexOf('$', 1) == -1) {
        if (fromRow < row - rowIncr) {
          return "#REF!";
        }

        fromRow = fromRow + rowIncr;
      }

      if (colIncr != 0 && col != 0 && fromCol >= col && address.StartsWith("$") == false) {
        if (fromCol < col - colIncr) {
          return "#REF!";
        }

        fromCol = fromCol + colIncr;
      }

      address = GetAddress(fromRow, address.IndexOf('$', 1) > -1, fromCol, address.StartsWith("$"));
    }
    return address;
  }

  /// <summary>
  /// Returns with brackets if the value is negative
  /// </summary>
  /// <param name="v">The value</param>
  /// <returns></returns>
  private static string GetRcFmt(int v) {
    return (v < 0
            ? string.Format("[{0}]", v)
            : v > 0
                ? v.ToString()
                : "");
  }

  /// <summary>
  /// Get the offset value for RC format
  /// </summary>
  /// <param name="value"></param>
  /// <param name="offsetValue"></param>
  /// <param name="fixedAddr"></param>
  /// <returns></returns>
  ///
  private static int GetRc(string value, int offsetValue, out bool fixedAddr) {
    if (value == "") {
      fixedAddr = false;
      return offsetValue;
    }
    int num;
    if (value[0] == '['
        && value[value.Length - 1]
            == ']') //Offset?
    {
      fixedAddr = false;
      if (int.TryParse(value.Substring(1, value.Length - 2), out num)) {
        return (offsetValue + num);
      }
      return int.MinValue;
    }
    // Absolute address
    fixedAddr = true;
    if (int.TryParse(value, out num)) {
      return num;
    }
    return int.MinValue;
  }

  private static int GetRC_V1(string value, int offsetValue, out bool fixedAddr) {
    if ((value == "") || (value == "R") || (value == "C")) {
      // Relative address with no offset
      fixedAddr = false;
      return offsetValue;
    }
    int num;
    if (value[1] == '['
        && value[value.Length - 1]
            == ']') //Offset?
    {
      // Relative address
      fixedAddr = false;
      if (int.TryParse(value.Substring(2, value.Length - 3), out num)) {
        return (offsetValue + num);
      }
      return int.MinValue;
    }
    // Absolute address
    fixedAddr = true;
    if (int.TryParse(value.Substring(1, value.Length - 1), out num)) {
      return num;
    }
    return int.MinValue;
  }

  /// <summary>
  /// Returns the character representation of the numbered column
  /// </summary>
  /// <param name="iColumnNumber">The number of the column</param>
  /// <returns>The letter representing the column</returns>
  protected internal static string GetColumnLetter(int iColumnNumber) {
    return GetColumnLetter(iColumnNumber, false);
  }

  protected internal static string GetColumnLetter(int iColumnNumber, bool fixedCol) {
    if (iColumnNumber < 1) {
      //throw new Exception("Column number is out of range");
      return "#REF!";
    }

    string sCol = "";
    do {
      sCol = ((char)('A' + ((iColumnNumber - 1) % 26))) + sCol;
      iColumnNumber = (iColumnNumber - ((iColumnNumber - 1) % 26)) / 26;
    } while (iColumnNumber > 0);
    return fixedCol ? "$" + sCol : sCol;
  }

  internal static bool GetRowColFromAddress(
      string cellAddress,
      out int fromRow,
      out int fromColumn,
      out int toRow,
      out int toColumn) {
    bool fixedFromRow,
        fixedFromColumn,
        fixedToRow,
        fixedToColumn;
    return GetRowColFromAddress(
        cellAddress,
        out fromRow,
        out fromColumn,
        out toRow,
        out toColumn,
        out fixedFromRow,
        out fixedFromColumn,
        out fixedToRow,
        out fixedToColumn);
  }

  /// <summary>
  /// Get the row/columns for a Cell-address
  /// </summary>
  /// <param name="cellAddress">The address</param>
  /// <param name="fromRow">Returns the to column</param>
  /// <param name="fromColumn">Returns the from column</param>
  /// <param name="toRow">Returns the to row</param>
  /// <param name="toColumn">Returns the from row</param>
  /// <param name="fixedFromRow">Is the from row fixed?</param>
  /// <param name="fixedFromColumn">Is the from column fixed?</param>
  /// <param name="fixedToRow">Is the to row fixed?</param>
  /// <param name="fixedToColumn">Is the to column fixed?</param>
  /// <returns></returns>
  internal static bool GetRowColFromAddress(
      string cellAddress,
      out int fromRow,
      out int fromColumn,
      out int toRow,
      out int toColumn,
      out bool fixedFromRow,
      out bool fixedFromColumn,
      out bool fixedToRow,
      out bool fixedToColumn) {
    bool ret;
    if (cellAddress.IndexOf('[')
        > 0) //External reference or reference to Table or Pivottable.
    {
      fromRow = -1;
      fromColumn = -1;
      toRow = -1;
      toColumn = -1;
      fixedFromRow = false;
      fixedFromColumn = false;
      fixedToRow = false;
      fixedToColumn = false;
      return false;
    }

    cellAddress = cellAddress.ToUpper(CultureInfo.InvariantCulture);
    //This one can be removed when the worksheet Select format is fixed
    if (cellAddress.IndexOf(' ') > 0) {
      cellAddress = cellAddress.Substring(0, cellAddress.IndexOf(' '));
    }

    if (cellAddress.IndexOf(':') < 0) {
      ret = GetRowColFromAddress(
          cellAddress,
          out fromRow,
          out fromColumn,
          out fixedFromRow,
          out fixedFromColumn);
      toColumn = fromColumn;
      toRow = fromRow;
      fixedToRow = fixedFromRow;
      fixedToColumn = fixedFromColumn;
    } else {
      string[] cells = cellAddress.Split(':');
      ret = GetRowColFromAddress(
          cells[0],
          out fromRow,
          out fromColumn,
          out fixedFromRow,
          out fixedFromColumn);
      if (ret) {
        ret = GetRowColFromAddress(
            cells[1],
            out toRow,
            out toColumn,
            out fixedToRow,
            out fixedToColumn);
      } else {
        GetRowColFromAddress(cells[1], out toRow, out toColumn, out fixedToRow, out fixedToColumn);
      }

      if (fromColumn <= 0) {
        fromColumn = 1;
      }
      if (fromRow <= 0) {
        fromRow = 1;
      }
      if (toColumn <= 0) {
        toColumn = ExcelPackage.MaxColumns;
      }
      if (toRow <= 0) {
        toRow = ExcelPackage.MaxRows;
      }
    }
    return ret;
  }

  /// <summary>
  /// Get the row/column for n Cell-address
  /// </summary>
  /// <param name="cellAddress">The address</param>
  /// <param name="row">Returns Tthe row</param>
  /// <param name="column">Returns the column</param>
  /// <returns>true if valid</returns>
  internal static bool GetRowColFromAddress(string cellAddress, out int row, out int column) {
    return GetRowCol(cellAddress, out row, out column, true);
  }

  internal static bool GetRowColFromAddress(
      string cellAddress,
      out int row,
      out int col,
      out bool fixedRow,
      out bool fixedCol) {
    return GetRowCol(cellAddress, out row, out col, true, out fixedRow, out fixedCol);
  }

  /// <summary>
  /// Get the row/column for a Cell-address
  /// </summary>
  /// <param name="address">the address</param>
  /// <param name="row">returns the row</param>
  /// <param name="col">returns the column</param>
  /// <param name="throwException">throw exception if invalid, otherwise returns false</param>
  /// <returns></returns>
  internal static bool GetRowCol(string address, out int row, out int col, bool throwException) {
    bool fixedRow,
        fixedCol;
    return GetRowCol(address, out row, out col, throwException, out fixedRow, out fixedCol);
  }

  internal static bool GetRowCol(
      string address,
      out int row,
      out int col,
      bool throwException,
      out bool fixedRow,
      out bool fixedCol) {
    bool colPart = true;
    int colStartIx = 0;
    int colLength = 0;
    col = 0;
    row = 0;
    fixedRow = false;
    fixedCol = false;

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

    int sheetMarkerIndex = address.IndexOf('!');
    if (sheetMarkerIndex >= 0) {
      colStartIx = sheetMarkerIndex + 1;
    }
    address = address.ToUpper(CultureInfo.InvariantCulture);
    for (int i = colStartIx; i < address.Length; i++) {
      char 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++;
          fixedCol = true;
        } else {
          colPart = false;
          fixedRow = true;
        }
      } else {
        row = 0;
        col = 0;
        if (throwException) {
          throw (new(string.Format("Invalid Address format {0}", address)));
        }
        return false;
      }
    }
    return row != 0 || col != 0;
  }

  internal static bool GetRowCol_V1(
      string address,
      out int row,
      out int col,
      bool throwException,
      out bool fixedRow,
      out bool fixedCol) {
    bool colPart = true;
    bool isFixed = false;
    int colStartIx = 0;
    int colLength = 0;
    col = 0;
    row = 0;
    fixedRow = false;
    fixedCol = false;

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

    int sheetMarkerIndex = address.IndexOf('!');
    if (sheetMarkerIndex >= 0) {
      colStartIx = sheetMarkerIndex + 1;
    }
    address = address.ToUpper(CultureInfo.InvariantCulture);
    for (int i = colStartIx; i < address.Length; i++) {
      char c = address[i];
      if (c == '$') {
        // Absolute address
        isFixed = true;
      } else if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3) {
        // Column portion of address
        if (isFixed) {
          fixedCol = true;
          isFixed = false;
        }

        col *= 26;
        col += c - 64;
        colLength++;
      } else if (c >= '0' && c <= '9') {
        // Row portion of address
        if (isFixed) {
          fixedRow = true;
          isFixed = false;
        }

        row *= 10;
        row += c - 48;
        colPart = false;
      } else {
        row = 0;
        col = 0;
        if (throwException) {
          throw (new(string.Format("Invalid Address format {0}", address)));
        }
        return false;
      }
    }
    return row != 0 || col != 0;
  }

  private static int GetColumn(string sCol) {
    int col = 0;
    int len = sCol.Length - 1;
    for (int i = len; i >= 0; i--) {
      col += (sCol[i] - 64) * (int)(Math.Pow(26, len - i));
    }
    return col;
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="row">The number of the row</param>
  /// <param name="column">The number of the column in the worksheet</param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(int row, int column) {
    return GetAddress(row, column, false);
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="row">The number of the row</param>
  /// <param name="column">The number of the column in the worksheet</param>
  /// <param name="absoluteRow">Absolute row</param>
  /// <param name="absoluteCol">Absolute column</param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(int row, bool absoluteRow, int column, bool absoluteCol) {
    return (absoluteCol ? "$" : "") + GetColumnLetter(column) + (absoluteRow ? "$" : "") + row;
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="row">The number of the row</param>
  /// <param name="column">The number of the column in the worksheet</param>
  /// <param name="absolute">Get an absolute address ($A$1)</param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(int row, int column, bool absolute) {
    if (row == 0 || column == 0) {
      return "#REF!";
    }
    if (absolute) {
      return ("$" + GetColumnLetter(column) + "$" + row);
    }
    return (GetColumnLetter(column) + row);
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="fromRow">From row number</param>
  /// <param name="fromColumn">From column number</param>
  /// <param name="toRow">To row number</param>
  /// <param name="toColumn">From column number</param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(int fromRow, int fromColumn, int toRow, int toColumn) {
    return GetAddress(fromRow, fromColumn, toRow, toColumn, false);
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="fromRow">From row number</param>
  /// <param name="fromColumn">From column number</param>
  /// <param name="toRow">To row number</param>
  /// <param name="toColumn">From column number</param>
  /// <param name="absolute">if true address is absolute (like $A$1)</param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(
      int fromRow,
      int fromColumn,
      int toRow,
      int toColumn,
      bool absolute) {
    if (fromRow == toRow && fromColumn == toColumn) {
      return GetAddress(fromRow, fromColumn, absolute);
    }
    if (fromRow == 1 && toRow == ExcelPackage.MaxRows) {
      var absChar = absolute ? "$" : "";
      return absChar + GetColumnLetter(fromColumn) + ":" + absChar + GetColumnLetter(toColumn);
    }
    if (fromColumn == 1 && toColumn == ExcelPackage.MaxColumns) {
      var absChar = absolute ? "$" : "";
      return absChar + fromRow + ":" + absChar + toRow;
    }
    return GetAddress(fromRow, fromColumn, absolute) + ":" + GetAddress(toRow, toColumn, absolute);
  }

  /// <summary>
  /// Returns the AlphaNumeric representation that Excel expects for a Cell Address
  /// </summary>
  /// <param name="fromRow">From row number</param>
  /// <param name="fromColumn">From column number</param>
  /// <param name="toRow">To row number</param>
  /// <param name="toColumn">From column number</param>
  /// <param name="fixedFromColumn"></param>
  /// <param name="fixedFromRow"></param>
  /// <param name="fixedToColumn"></param>
  /// <param name="fixedToRow"></param>
  /// <returns>The cell address in the format A1</returns>
  public static string GetAddress(
      int fromRow,
      int fromColumn,
      int toRow,
      int toColumn,
      bool fixedFromRow,
      bool fixedFromColumn,
      bool fixedToRow,
      bool fixedToColumn) {
    if (fromRow == toRow && fromColumn == toColumn) {
      return GetAddress(fromRow, fixedFromRow, fromColumn, fixedFromColumn);
    }
    if (fromRow == 1 && toRow == ExcelPackage.MaxRows) {
      return GetColumnLetter(fromColumn, fixedFromColumn)
          + ":"
          + GetColumnLetter(toColumn, fixedToColumn);
    }
    if (fromColumn == 1 && toColumn == ExcelPackage.MaxColumns) {
      return (fixedFromRow ? "$" : "") + fromRow + ":" + (fixedToRow ? "$" : "") + toRow;
    }
    return GetAddress(fromRow, fixedFromRow, fromColumn, fixedFromColumn)
        + ":"
        + GetAddress(toRow, fixedToRow, toColumn, fixedToColumn);
  }

  /// <summary>
  /// Get the full address including the worksheet name
  /// </summary>
  /// <param name="worksheetName">The name of the worksheet</param>
  /// <param name="address">The address</param>
  /// <returns>The full address</returns>
  public static string GetFullAddress(string worksheetName, string address) {
    return GetFullAddress(worksheetName, address, true);
  }

  internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol) {
    if (address.IndexOf("!") == -1 || address == "#REF!") {
      if (fullRowCol) {
        string[] cells = address.Split(':');
        if (cells.Length > 0) {
          address = string.Format("'{0}'!{1}", worksheetName, cells[0]);
          if (cells.Length > 1) {
            address += string.Format(":{0}", cells[1]);
          }
        }
      } else {
        var a = new ExcelAddressBase(address);
        if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows)
            || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns)) {
          address = string.Format(
              "'{0}'!{1}{2}:{3}{4}",
              worksheetName,
              GetColumnLetter(a._fromCol),
              a._fromRow,
              GetColumnLetter(a._toCol),
              a._toRow);
        } else {
          address = GetFullAddress(worksheetName, address, true);
        }
      }
    }
    return address;
  }

  public static bool IsValidAddress(string address) {
    address = address.ToUpper(CultureInfo.InvariantCulture);
    string r1 = "",
        c1 = "",
        r2 = "",
        c2 = "";
    bool isSecond = false;
    for (int i = 0; i < address.Length; i++) {
      if (address[i] >= 'A' && address[i] <= 'Z') {
        if (isSecond == false) {
          if (r1 != "") {
            return false;
          }
          c1 += address[i];
          if (c1.Length > 3) {
            return false;
          }
        } else {
          if (r2 != "") {
            return false;
          }
          c2 += address[i];
          if (c2.Length > 3) {
            return false;
          }
        }
      } else if (address[i] >= '0' && address[i] <= '9') {
        if (isSecond == false) {
          r1 += address[i];
          if (r1.Length > 7) {
            return false;
          }
        } else {
          r2 += address[i];
          if (r2.Length > 7) {
            return false;
          }
        }
      } else if (address[i] == ':') {
        isSecond = true;
      } else if (address[i] == '$') {
        if (i == address.Length - 1 || address[i + 1] == ':') {
          return false;
        }
      } else {
        return false;
      }
    }

    if (r1 != ""
        && c1 != ""
        && r2 == ""
        && c2
            == "") //Single Cell
    {
      return (GetColumn(c1) <= ExcelPackage.MaxColumns && int.Parse(r1) <= ExcelPackage.MaxRows);
    }
    if (r1 != ""
        && r2 != ""
        && c1 != ""
        && c2
            != "") //Range
    {
      var iR2 = int.Parse(r2);
      var iC2 = GetColumn(c2);

      return GetColumn(c1) <= iC2
          && int.Parse(r1) <= iR2
          && iC2 <= ExcelPackage.MaxColumns
          && iR2 <= ExcelPackage.MaxRows;
    }
    if (r1 == ""
        && r2 == ""
        && c1 != ""
        && c2
            != "") //Full Column
    {
      var c2N = GetColumn(c2);
      return (GetColumn(c1) <= c2N && c2N <= ExcelPackage.MaxColumns);
    }
    if (r1 != "" && r2 != "" && c1 == "" && c2 == "") {
      var iR2 = int.Parse(r2);

      return int.Parse(r1) <= iR2 && iR2 <= ExcelPackage.MaxRows;
    }
    return false;
  }

  /// <summary>
  /// Checks that a cell address (e.g. A5) is valid.
  /// </summary>
  /// <param name="cellAddress">The alphanumeric cell address</param>
  /// <returns>True if the cell address is valid</returns>
  public static bool IsValidCellAddress(string cellAddress) {
    bool result = false;
    try {
      int row,
          col;
      if (GetRowColFromAddress(cellAddress, out row, out col)) {
        if (row > 0 && col > 0 && row <= ExcelPackage.MaxRows && col <= ExcelPackage.MaxColumns) {
          result = true;
        } else {
          result = false;
        }
      }
    } catch {}
    return result;
  }

  /// <summary>
  /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
  /// if they fall after the afterRow and afterColumn.
  /// Supports inserting rows and columns into existing templates.
  /// </summary>
  /// <param name="formula">The Excel formula</param>
  /// <param name="rowIncrement">The amount to increment the cell reference by</param>
  /// <param name="colIncrement">The amount to increment the cell reference by</param>
  /// <param name="afterRow">Only change rows after this row</param>
  /// <param name="afterColumn">Only change columns after this column</param>
  /// <returns></returns>
  internal static string UpdateFormulaReferences(
      string formula,
      int rowIncrement,
      int colIncrement,
      int afterRow,
      int afterColumn,
      bool setFixed = false) {
    //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
    var d = new Dictionary<string, object>();
    try {
      var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
      var tokens = sct.Tokenize(formula);
      String f = "";
      foreach (var t in tokens) {
        if (t.TokenType == TokenType.ExcelAddress) {
          var a = new ExcelAddressBase(t.Value);

          if (!String.IsNullOrEmpty(
              a._ws)) // Bug 15339
          {
            // This is from a different worksheet, thus no update is required
            f += a.Address;
            continue;
          }

          if (rowIncrement > 0) {
            a = a.AddRow(afterRow, rowIncrement, setFixed);
          } else if (rowIncrement < 0) {
            a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
          }
          if (colIncrement > 0) {
            a = a.AddColumn(afterColumn, colIncrement, setFixed);
          } else if (colIncrement < 0) {
            a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
          }
          if (a == null || !a.IsValidRowCol()) {
            f += "#REF!";
          } else {
            f += a.Address;
          }
        } else {
          f += t.Value;
        }
      }
      return f;
    } catch //Invalid formula, skip updateing addresses
    {
      return formula;
    }
  }
}