﻿/*******************************************************************************
 * 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 System.Text;
using OfficeOpenXml.Style;
using System.Text.RegularExpressions;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
using System.Linq;
using OfficeOpenXml.FormulaParsing.Excel.Functions;
using OfficeOpenXml.FormulaParsing;
namespace OfficeOpenXml
{
    /// <summary>
    /// Base class containing cell address manipulating methods.
    /// </summary>
    public abstract class ExcelCellBase
    {
        #region "public functions"
        /// <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);
        }
        #endregion
        #region "Formula Functions"
        private delegate string dlgTransl(string part, int row, int col, int rowIncr, int colIncr);
        #region R1C1 Functions"
        /// <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, dlgTransl 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 == true && 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, dlgTransl 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 == true && 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;
                }
                else if (addrCol - col != 0)
                {
                    return Ret + "C" + string.Format("[{0}]", addrCol - col);
                }
                else
                {
                    return Ret + "C";
                }
            }
            else
            {
                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;
                }
                else if (addrCol - col != 0)
                {
                    return Ret + "C" + string.Format("[{0}]", addrCol - col);
                }
                else
                {
                    return Ret + "C";
                }
            }
            else
            {
                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 char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);

            // Convert range expressions
            string result = "";
            result += RangeCellToR1C1_V1(cellValues[0], row, col, rowIncr, colIncr);
            result += ":";
            result += 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;
            }
            else
            {
                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);
                }
                else
                {
                    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);
                }
                else
                {
                    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 char[] { ':' }, 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;
            }
            else
            {
                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);
                }
                else
                {
                    return int.MinValue;
                }
            }
            else
            {
                // Absolute address
                fixedAddr = true;
                if (int.TryParse(value, out num))
                {
                    return num;
                }
                else
                {
                    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);
                }
                else
                {
                    return int.MinValue;
                }
            }
            else
            {
                // Absolute address
                fixedAddr = true;
                if (int.TryParse(value.Substring(1, value.Length - 1), out num))
                {
                    return num;
                }
                else
                {
                    return int.MinValue;
                }
            }
        }
        #endregion
        #region "Address Functions"
        #region GetColumnLetter
        /// <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;
        }
        #endregion

        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 += ((int)c) - 64;
                colLength++;
            }
            else if (c >= '0' && c <= '9')
            {
                row *= 10;
                row += ((int)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 Exception(string.Format("Invalid Address format {0}", address)));
                }
                else
                {
                    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 += ((int)c) - 64;
                    colLength++;
                }
                else if (c >= '0' && c <= '9')
                {
                    // Row portion of address
                    if (isFixed)
                    {
                        fixedRow = true;
                        isFixed = false;
                    }

                    row *= 10;
                    row += ((int)c) - 48;
                    colPart = false;
                }
                else
                {
                    row = 0;
                    col = 0;
                    if (throwException)
                    {
                        throw (new Exception(string.Format("Invalid Address format {0}", address)));
                    }
                    else
                    {
                        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 += (((int)sCol[i]) - 64) * (int)(Math.Pow(26, len - i));
            }
            return col;
        }
        #region GetAddress
        /// <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.ToString();
        }
        /// <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.ToString());
            }
            else
            {
                return (GetColumnLetter(Column) + Row.ToString());
            }
        }
        /// <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);
            }
            else
            {
                if (FromRow == 1 && ToRow == ExcelPackage.MaxRows)
                {
                    var absChar = Absolute ? "$" : "";
                    return absChar + GetColumnLetter(FromColumn) + ":" + absChar + GetColumnLetter(ToColumn);
                }
                else if(FromColumn==1 && ToColumn==ExcelPackage.MaxColumns)
                {
                    var absChar = Absolute ? "$" : "";
                    return absChar + FromRow.ToString() + ":" + absChar + ToRow.ToString();
                }
                else
                {
                    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);
            }
            else
            {
                if (FromRow == 1 && ToRow == ExcelPackage.MaxRows)
                {
                    return GetColumnLetter(FromColumn, FixedFromColumn) + ":" + GetColumnLetter(ToColumn, FixedToColumn);
                }
                else if (FromColumn == 1 && ToColumn == ExcelPackage.MaxColumns)
                {                    
                    return (FixedFromRow ? "$":"") + FromRow.ToString() + ":" + (FixedToRow ? "$":"") + ToRow.ToString();
                }
                else
                {
                    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, ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow);
                       }
                       else
                       {
                           address=GetFullAddress(worksheetName, address, true);
                       }
                   }
               }
               return address;
        }
        #endregion
        #region IsValidCellAddress
        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);   
            }
            else 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;
                                                    
            }
            else if (r1 == "" && r2 == "" && c1 != "" && c2 != "") //Full Column
            {                
                var c2n=GetColumn(c2);
                return (GetColumn(c1) <= c2n && c2n <= ExcelPackage.MaxColumns);
            }
            else if (r1 != "" && r2 != "" && c1 == "" && c2 == "")
            {
                var iR2 = int.Parse(r2);

                return int.Parse(r1) <= iR2 && iR2 <= ExcelPackage.MaxRows;
            }
            else
            {
                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;
        }
        #endregion
        #region UpdateFormulaReferences
        /// <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;
            }
        }

        #endregion
        #endregion
        #endregion
    }
}