Beispiel #1
0
        private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
        {
            var ws = name.Worksheet;
            var id = ExcelCellBase.GetCellID(ws == null?0:ws.SheetID, name.Index, 0);

            if (!depChain.index.ContainsKey(id))
            {
                var f = new FormulaCell()
                {
                    SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula = name.NameFormula
                };
                if (!string.IsNullOrEmpty(f.Formula))
                {
                    f.Tokens = lexer.Tokenize(f.Formula, (ws == null ? null : ws.Name)).ToList();
                    if (ws == null)
                    {
                        name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
                    }
                    else
                    {
                        ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
                    }
                    depChain.Add(f);
                    FollowChain(depChain, lexer, name._workbook, ws, f, options);
                }
            }
        }
Beispiel #2
0
        internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options)
        {
            var depChain = new DependencyChain();

            GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options);

            return(depChain);
        }
Beispiel #3
0
        internal static DependencyChain Create(ExcelWorksheet ws, ExcelCalculationOption options)
        {
            ws.CheckSheetType();
            var depChain = new DependencyChain();

            GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws.Cells, options);
            return(depChain);
        }
        internal static DependencyChain Create(ExcelWorksheet ws, string Formula, ExcelCalculationOption options)
        {
            ws.CheckSheetType();
            var depChain = new DependencyChain();

            GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws, Formula, options);
            
            return depChain;
        }
Beispiel #5
0
 private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
 {
     foreach (var name in ws.Names)
     {
         if (!string.IsNullOrEmpty(name.NameFormula))
         {
             GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
         }
     }
 }
 private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
 {
     foreach (var name in ws.Names)
     {
         if (!string.IsNullOrEmpty(name.NameFormula))
         {
             GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
         }
     }
 }
Beispiel #7
0
        internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options)
        {
            var depChain = new DependencyChain();

            foreach (var ws in wb.Worksheets)
            {
                if (!(ws is ExcelChartsheet))
                {
                    GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options);
                }
            }
            return(depChain);
        }
Beispiel #8
0
        private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelWorksheet ws, string formula, ExcelCalculationOption options)
        {
            var f = new FormulaCell()
            {
                SheetID = ws.SheetID, Row = -1, Column = -1
            };

            f.Formula = formula;
            if (!string.IsNullOrEmpty(f.Formula))
            {
                f.Tokens = lexer.Tokenize(f.Formula, ws.Name).ToList();
                depChain.Add(f);
                FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
            }
        }
 internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options)
 {
     var depChain = new DependencyChain();
     foreach (var ws in wb.Worksheets)
     {
         if (!(ws is ExcelChartsheet))
         {
             GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options);
             GetWorksheetNames(ws, depChain, options);
         }
     }
     foreach (var name in wb.Names)
     {
         if (name.NameValue==null)
         {
             GetChain(depChain, wb.FormulaParser.Lexer, name, options);
         }
     }
     return depChain;
 }
Beispiel #10
0
        private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
        {
            var ws = Range.Worksheet;
            var fs = new CellsStoreEnumerator <object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);

            while (fs.Next())
            {
                if (fs.Value == null || fs.Value.ToString().Trim() == "")
                {
                    continue;
                }
                var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
                if (!depChain.index.ContainsKey(id))
                {
                    var f = new FormulaCell()
                    {
                        SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column
                    };
                    if (fs.Value is int)
                    {
                        f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
                    }
                    else
                    {
                        f.Formula = fs.Value.ToString();
                    }
                    if (!string.IsNullOrEmpty(f.Formula))
                    {
                        f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
                        ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
                        depChain.Add(f);
                        FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
                    }
                }
            }
        }
Beispiel #11
0
        /// <summary>
        /// This method follows the calculation chain to get the order of the calculation
        /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
        /// </summary>
        /// <param name="depChain">The dependency chain object</param>
        /// <param name="lexer">The formula tokenizer</param>
        /// <param name="wb">The workbook where the formula comes from</param>
        /// <param name="ws">The worksheet where the formula comes from</param>
        /// <param name="f">The cell function object</param>
        /// <param name="options">Calcultaiton options</param>
        private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
        {
            Stack<FormulaCell> stack = new Stack<FormulaCell>();
        iterateToken:
            while (f.tokenIx < f.Tokens.Count)
            {
                var t = f.Tokens[f.tokenIx];
                if (t.TokenType == TokenType.ExcelAddress)
                {
                    var adr = new ExcelFormulaAddress(t.Value);
                    if (adr.Table != null)
                    {
                        adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
                    }

                    if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column))!=ExcelAddressBase.eAddressCollition.No)
                    {
                        throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
                    }

                    if (adr._fromRow > 0 && adr._fromCol > 0)
                    {                        
                        if (string.IsNullOrEmpty(adr.WorkSheet))
                        {
                            if (f.ws == null)
                            {
                                f.ws = ws;
                            }
                            else if (f.ws.SheetID != f.SheetID)
                            {
                                f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
                            }
                        }
                        else
                        {
                            f.ws = wb.Worksheets[adr.WorkSheet];
                        }

                        if (f.ws != null)
                        {
                            f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
                            goto iterateCells;
                        }
                    }
                }
                else if (t.TokenType == TokenType.NameValue)
                {
                    string adrWb, adrWs, adrName;
                    ExcelNamedRange name;
                    ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws==null ? "" : f.ws.Name);
                    if (!string.IsNullOrEmpty(adrWs))
                    {
                        if (f.ws == null)
                        {
                            f.ws = wb.Worksheets[adrWs];
                        }
                        if(f.ws.Names.ContainsKey(t.Value))
                        {
                            name = f.ws.Names[adrName];
                        }
                        else if (wb.Names.ContainsKey(adrName))
                        {
                            name = wb.Names[adrName];
                        }
                        else
                        {
                            name = null;
                        }
                        if(name != null) f.ws = name.Worksheet;                        
                    }
                    else if (wb.Names.ContainsKey(adrName))
                    {
                        name = wb.Names[t.Value];
                        if (string.IsNullOrEmpty(adrWs))
                        {
                            f.ws = name.Worksheet;
                        }
                    }
                    else
                    {
                        name = null;
                    }

                    if (name != null)
                    {
        
                        if (string.IsNullOrEmpty(name.NameFormula))
                        {
                            if (name.NameValue == null)
                            {
                                f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, name.Start.Row,
                                    name.Start.Column, name.End.Row, name.End.Column);
                                goto iterateCells;
                            }
                        }
                        else
                        {
                            var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);

                            if (!depChain.index.ContainsKey(id))
                            {
                                var rf = new FormulaCell() { SheetID = name.LocalSheetId, Row = name.Index, Column = 0 };
                                rf.Formula = name.NameFormula;
                                rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();
                                
                                depChain.Add(rf);
                                stack.Push(f);
                                f = rf;
                                goto iterateToken;
                            }
                            else
                            {
                                if (stack.Count > 0)
                                {
                                    //Check for circular references
                                    foreach (var par in stack)
                                    {
                                        if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id)
                                        {
                                            throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                f.tokenIx++;
            }
            depChain.CalcOrder.Add(f.Index);
            if (stack.Count > 0)
            {
                f = stack.Pop();
                goto iterateCells;
            }
            return;
        iterateCells:

            while (f.iterator != null && f.iterator.Next())
            {
                var v = f.iterator.Value;
                if (v == null || v.ToString().Trim() == "") continue;
                var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
                if (!depChain.index.ContainsKey(id))
                {
                    var rf = new FormulaCell() { SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column };
                    if (f.iterator.Value is int)
                    {
                        rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
                    }
                    else
                    {
                        rf.Formula = v.ToString();
                    }
                    rf.ws = f.ws;
                    rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
                    ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
                    depChain.Add(rf);
                    stack.Push(f);
                    f = rf;
                    goto iterateToken;
                }
                else
                {
                    if (stack.Count > 0)
                    {
                        //Check for circular references
                        foreach (var par in stack)
                        {
                            if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
                            {
                                if (options.AllowCirculareReferences == false)
                                {
                                    throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
                                }
                                else
                                {
                                    f = stack.Pop();
                                    goto iterateCells;
                                }
                            }
                        }
                    }
                }
            }
            f.tokenIx++;
            goto iterateToken;
        }
Beispiel #12
0
 private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
 {
     var ws = Range.Worksheet;
     var fs = new CellsStoreEnumerator<object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);
     while (fs.Next())
     {
         if (fs.Value == null || fs.Value.ToString().Trim() == "") continue;
         var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
         if (!depChain.index.ContainsKey(id))
         {
             var f = new FormulaCell() { SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column };
             if (fs.Value is int)
             {
                 f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
             }
             else
             {
                 f.Formula = fs.Value.ToString();
             }
             if (!string.IsNullOrEmpty(f.Formula))
             {
                 f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
                 ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
                 depChain.Add(f);
                 FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
             }
         }
     }
 }
Beispiel #13
0
 private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelWorksheet ws, string formula, ExcelCalculationOption options)
 {
     var f = new FormulaCell() { SheetID = ws.SheetID, Row = -1, Column = -1 };
     f.Formula = formula;
     if (!string.IsNullOrEmpty(f.Formula))
     {
         f.Tokens = lexer.Tokenize(f.Formula, ws.Name).ToList();
         depChain.Add(f);
         FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
     }
 }
Beispiel #14
0
 private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
 {
     var ws = name.Worksheet;
     var id = ExcelCellBase.GetCellID(ws==null?0:ws.SheetID, name.Index, 0);
     if (!depChain.index.ContainsKey(id))
     {
         var f = new FormulaCell() { SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula=name.NameFormula };
         if (!string.IsNullOrEmpty(f.Formula))
         {
             f.Tokens = lexer.Tokenize(f.Formula, (ws==null ? null : ws.Name)).ToList();
             if (ws == null)
             {
                 name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
             }
             else
             {
                 ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
             }
             depChain.Add(f);
             FollowChain(depChain, lexer,name._workbook, ws, f, options);
         }
     }
 }
Beispiel #15
0
        /// <summary>
        /// This method follows the calculation chain to get the order of the calculation
        /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
        /// </summary>
        /// <param name="depChain">The dependency chain object</param>
        /// <param name="lexer">The formula tokenizer</param>
        /// <param name="wb">The workbook where the formula comes from</param>
        /// <param name="ws">The worksheet where the formula comes from</param>
        /// <param name="f">The cell function object</param>
        /// <param name="options">Calcultaiton options</param>
        private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
        {
            Stack <FormulaCell> stack = new Stack <FormulaCell>();

iterateToken:
            while (f.tokenIx < f.Tokens.Count)
            {
                var t = f.Tokens[f.tokenIx];
                if (t.TokenType == TokenType.ExcelAddress)
                {
                    var adr = new ExcelFormulaAddress(t.Value);
                    if (adr.Table != null)
                    {
                        adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
                    }

                    if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)) != ExcelAddressBase.eAddressCollition.No && !options.AllowCirculareReferences)
                    {
                        throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
                    }

                    if (adr._fromRow > 0 && adr._fromCol > 0)
                    {
                        if (string.IsNullOrEmpty(adr.WorkSheet))
                        {
                            if (f.ws == null)
                            {
                                f.ws = ws;
                            }
                            else if (f.ws.SheetID != f.SheetID)
                            {
                                f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
                            }
                        }
                        else
                        {
                            f.ws = wb.Worksheets[adr.WorkSheet];
                        }

                        if (f.ws != null)
                        {
                            f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
                            goto iterateCells;
                        }
                    }
                }
                else if (t.TokenType == TokenType.NameValue)
                {
                    string          adrWb, adrWs, adrName;
                    ExcelNamedRange name;
                    ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws == null ? "" : f.ws.Name);
                    if (!string.IsNullOrEmpty(adrWs))
                    {
                        if (f.ws == null)
                        {
                            f.ws = wb.Worksheets[adrWs];
                        }
                        if (f.ws.Names.ContainsKey(t.Value))
                        {
                            name = f.ws.Names[adrName];
                        }
                        else if (wb.Names.ContainsKey(adrName))
                        {
                            name = wb.Names[adrName];
                        }
                        else
                        {
                            name = null;
                        }
                        if (name != null)
                        {
                            f.ws = name.Worksheet;
                        }
                    }
                    else if (wb.Names.ContainsKey(adrName))
                    {
                        name = wb.Names[t.Value];
                        f.ws = name.Worksheet;
                    }
                    else
                    {
                        name = null;
                    }

                    if (name != null)
                    {
                        if (string.IsNullOrEmpty(name.NameFormula))
                        {
                            if (name.NameValue == null)
                            {
                                f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, name.Start.Row,
                                                                               name.Start.Column, name.End.Row, name.End.Column);
                                goto iterateCells;
                            }
                        }
                        else
                        {
                            var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);

                            if (!depChain.index.ContainsKey(id))
                            {
                                var rf = new FormulaCell()
                                {
                                    SheetID = name.LocalSheetId, Row = name.Index, Column = 0
                                };
                                rf.Formula = name.NameFormula;
                                rf.Tokens  = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();

                                depChain.Add(rf);
                                stack.Push(f);
                                f = rf;
                                goto iterateToken;
                            }
                            else
                            {
                                if (stack.Count > 0)
                                {
                                    //Check for circular references
                                    foreach (var par in stack)
                                    {
                                        if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id && !options.AllowCirculareReferences)
                                        {
                                            throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                f.tokenIx++;
            }
            depChain.CalcOrder.Add(f.Index);
            if (stack.Count > 0)
            {
                f = stack.Pop();
                goto iterateCells;
            }
            return;

iterateCells:

            while (f.iterator != null && f.iterator.Next())
            {
                var v = f.iterator.Value;
                if (v == null || v.ToString().Trim() == "")
                {
                    continue;
                }
                var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
                if (!depChain.index.ContainsKey(id))
                {
                    var rf = new FormulaCell()
                    {
                        SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
                    };
                    if (f.iterator.Value is int)
                    {
                        rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
                    }
                    else
                    {
                        rf.Formula = v.ToString();
                    }
                    rf.ws     = f.ws;
                    rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
                    ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
                    depChain.Add(rf);
                    stack.Push(f);
                    f = rf;
                    goto iterateToken;
                }
                else
                {
                    if (stack.Count > 0)
                    {
                        //Check for circular references
                        foreach (var par in stack)
                        {
                            if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
                            {
                                if (options.AllowCirculareReferences == false)
                                {
                                    throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
                                }
                                else
                                {
                                    f = stack.Pop();
                                    goto iterateCells;
                                }
                            }
                        }
                    }
                }
            }
            f.tokenIx++;
            goto iterateToken;
        }
 private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc)
 {
     foreach (var ix in dc.CalcOrder)
     {
         var item = dc.list[ix];
         try
         {
             var ws = wb.Worksheets.GetBySheetID(item.SheetID);
             var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
             SetValue(wb, item, v);
         }
         catch (FormatException fe)
         {
             throw (fe);
         }
         catch// (Exception e)
         {
             var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
             SetValue(wb, item, error);
         }
     }
 }
Beispiel #17
0
        internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options)
        {
            var depChain = new DependencyChain();

            GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options);

            return depChain;
        }
Beispiel #18
0
        /// <summary>
        /// This method follows the calculation chain to get the order of the calculation
        /// Goto (!) is used internally to prevent stackoverflow on extremly large dependency trees (that is, many recursive formulas).
        /// </summary>
        /// <param name="depChain">The dependency chain object</param>
        /// <param name="lexer">The formula tokenizer</param>
        /// <param name="wb">The workbook where the formula comes from</param>
        /// <param name="ws">The worksheet where the formula comes from</param>
        /// <param name="f">The cell function object</param>
        /// <param name="options">Calcultaiton options</param>
        private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
        {
            Stack <FormulaCell> stack = new Stack <FormulaCell>();

iterateToken:
            while (f.tokenIx < f.Tokens.Count)
            {
                var t = f.Tokens[f.tokenIx];
                if (t.TokenType == TokenType.ExcelAddress)
                {
                    var adr = new ExcelFormulaAddress(t.Value);
                    if (adr.IsTableAddress)
                    {
                        adr.SetRCFromTable(ws.Package, new ExcelAddress(f.Row, f.Column, f.Row, f.Column));
                    }

                    if (adr.WorkSheet == null && adr.Collide(new ExcelAddress(f.Row, f.Column, f.Row, f.Column)) != ExcelAddress.eAddressCollition.No && !options.AllowCircularReferences)
                    {
                        throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddress.GetAddress(f.Row, f.Column))));
                    }

                    if (adr._fromRow > 0 && adr._fromCol > 0)
                    {
                        if (string.IsNullOrEmpty(adr.WorkSheet))
                        {
                            if (f.ws == null)
                            {
                                f.ws = ws;
                            }
                            else if (f.ws.SheetID != f.SheetID)
                            {
                                f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
                            }
                        }
                        else
                        {
                            f.ws = wb.Worksheets[adr.WorkSheet];
                        }

                        if (f.ws != null)
                        {
                            f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
                            goto iterateCells;
                        }
                    }
                }
                else if (t.TokenType == TokenType.NameValue)
                {
                    ExcelNamedRange name      = null;
                    var             worksheet = f.ws ?? ws;
                    // Worksheet-scoped named ranges take precedence over workbook-scoped named ranges.
                    if (worksheet?.Names?.ContainsKey(t.Value) == true)
                    {
                        name = worksheet.Names[t.Value];
                    }
                    else if (wb.Names.ContainsKey(t.Value))
                    {
                        name = wb.Names[t.Value];
                    }
                    if (name != null)
                    {
                        var nameFormulaTokens = name.GetRelativeNameFormula(f.Row, f.Column)?.ToList();
                        if (nameFormulaTokens.Count == 0 && !string.IsNullOrEmpty(name.NameFormula))
                        {
                            nameFormulaTokens = name.Workbook.FormulaParser.Lexer.Tokenize(name.NameFormula)?.ToList();
                        }
                        // Remove the current named range token and replace it with the named range's formula.
                        f.Tokens.RemoveAt(f.tokenIx);
                        f.Tokens.InsertRange(f.tokenIx, nameFormulaTokens);
                        goto iterateToken;
                    }
                }
                else if (t.TokenType == TokenType.Function && t.Value.IsEquivalentTo(Offset.Name))
                {
                    var stringBuilder    = new StringBuilder($"{OffsetAddress.Name}(");
                    int offsetStartIndex = f.tokenIx;
                    int parenCount       = 1;
                    for (f.tokenIx += 2; parenCount > 0 && f.tokenIx < f.Tokens.Count; f.tokenIx++)
                    {
                        var token = f.Tokens[f.tokenIx];
                        stringBuilder.Append(token.Value);
                        if (token.TokenType == TokenType.OpeningParenthesis)
                        {
                            parenCount++;
                        }
                        else if (token.TokenType == TokenType.ClosingParenthesis)
                        {
                            parenCount--;
                        }
                    }
                    ExcelRange cell                 = ws.Cells[f.Row, f.Column];
                    string     originalFormula      = cell.Formula;
                    string     addressOffsetFormula = stringBuilder.ToString();
                    stringBuilder.Clear();
                    for (int i = 0; i < f.Tokens.Count; i++)
                    {
                        if (i == offsetStartIndex)
                        {
                            stringBuilder.Append(0);
                        }
                        else if (i < offsetStartIndex || i >= f.tokenIx)
                        {
                            stringBuilder.Append(f.Tokens[i].Value);
                        }
                    }
                    cell.Formula = stringBuilder.ToString();
                    var offsetResult = ws.Calculate(addressOffsetFormula, f.Row, f.Column);
                    cell.Formula = originalFormula;
                    if (offsetResult is string resultString)
                    {
                        ExcelAddress adr       = new ExcelAddress(resultString);
                        var          worksheet = string.IsNullOrEmpty(adr.WorkSheet) ? ws : wb.Worksheets[adr.WorkSheet];
                        // Only complete the OFFSET's dependency chain if a valid existing address was successfully parsed.
                        if (worksheet != null)
                        {
                            f.Tokens.RemoveRange(offsetStartIndex, f.tokenIx - offsetStartIndex);
                            var offsetResultTokens = wb.FormulaParser.Lexer.Tokenize(adr.FullAddress);
                            f.Tokens.InsertRange(offsetStartIndex, offsetResultTokens);
                            f.ws       = worksheet;
                            f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
                            goto iterateCells;
                        }
                    }
                }
                f.tokenIx++;
            }
            depChain.CalcOrder.Add(f.Index);
            if (stack.Count > 0)
            {
                f = stack.Pop();
                goto iterateCells;
            }
            return;

iterateCells:

            while (f.iterator != null && f.iterator.MoveNext())
            {
                var v = f.iterator.Value;
                if (v == null || v.ToString().Trim() == "")
                {
                    continue;
                }
                var id = ExcelAddress.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
                if (!depChain.Index.ContainsKey(id))
                {
                    var rf = new FormulaCell()
                    {
                        SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
                    };
                    if (f.iterator.Value is int)
                    {
                        rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
                    }
                    else
                    {
                        rf.Formula = v.ToString();
                    }
                    rf.ws     = f.ws;
                    rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
                    ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
                    depChain.Add(rf);
                    stack.Push(f);
                    f = rf;
                    goto iterateToken;
                }
                else
                {
                    if (stack.Count > 0)
                    {
                        //Check for circular references
                        foreach (var par in stack)
                        {
                            if (ExcelAddress.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id ||
                                ExcelAddress.GetCellID(par.ws.SheetID, par.Row, par.Column) == id)
                            {
                                if (options.AllowCircularReferences == false)
                                {
                                    throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
                                }
                                else
                                {
                                    f = stack.Pop();
                                    goto iterateCells;
                                }
                            }
                        }
                    }
                }
            }
            f.tokenIx++;
            goto iterateToken;
        }