| /******************************************************************************* |
| * You may amend and distribute as you like, but don't remove this header! |
| * |
| * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets. |
| * See http://www.codeplex.com/EPPlus for details. |
| * |
| * Copyright (C) 2011 Jan Källman |
| * |
| * This library is free software; you can redistribute it and/or |
| * modify it under the terms of the GNU Lesser General Public |
| * License as published by the Free Software Foundation; either |
| * version 2.1 of the License, or (at your option) any later version. |
| |
| * This library is distributed in the hope that it will be useful, |
| * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
| * See the GNU Lesser General Public License for more details. |
| * |
| * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php |
| * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html |
| * |
| * All code and executables are provided "as is" with no warranty either express or implied. |
| * The author accepts no liability for any damage or loss of business that this product may cause. |
| * |
| * Code change notes: |
| * |
| * Author Change Date |
| * ****************************************************************************** |
| * Jan Källman Added 2012-03-04 |
| *******************************************************************************/ |
| |
| using System.Collections.Generic; |
| using System.Linq; |
| |
| namespace AppsheetEpplus; |
| |
| internal static class DependencyChainFactory { |
| 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; |
| } |
| |
| internal static DependencyChain Create(ExcelWorksheet ws, ExcelCalculationOption options) { |
| ws.CheckSheetType(); |
| var depChain = new DependencyChain(); |
| |
| GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws.Cells, options); |
| |
| GetWorksheetNames(ws, depChain, 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; |
| } |
| |
| 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); |
| } |
| } |
| } |
| |
| internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options) { |
| var depChain = new DependencyChain(); |
| |
| GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options); |
| |
| return depChain; |
| } |
| |
| private static void GetChain( |
| DependencyChain depChain, |
| ILexer lexer, |
| ExcelNamedRange name, |
| ExcelCalculationOption options) { |
| var ws = name.Worksheet; |
| var id = ExcelCellBase.GetCellId(ws?.SheetID ?? 0, name.Index, 0); |
| if (!depChain.index.ContainsKey(id)) { |
| var f = new FormulaCell { |
| SheetID = ws?.SheetID ?? 0, |
| Row = name.Index, |
| Column = 0, |
| Formula = name.NameFormula, |
| }; |
| if (!string.IsNullOrEmpty(f.Formula)) { |
| f.Tokens = lexer.Tokenize(f.Formula, 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); |
| } |
| } |
| } |
| |
| 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); |
| } |
| } |
| |
| 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 value) { |
| f.Formula = ws._sharedFormulas[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); |
| } |
| } |
| } |
| } |
| |
| /// <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.Workbook, new(f.Row, f.Column, f.Row, f.Column)); |
| } |
| |
| if (adr.WorkSheet == null |
| && adr.Collide(new(f.Row, f.Column, f.Row, f.Column)) |
| != ExcelAddressBase.eAddressCollition.No) { |
| throw (new CircularReferenceException( |
| string.Format( |
| "Circular Reference in cell {0}", |
| ExcelCellBase.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( |
| 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; |
| ExcelNamedRange name; |
| ExcelAddressBase.SplitAddress( |
| t.Value, |
| out adrWb, |
| out var adrWs, |
| out var 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( |
| f.ws._formulas, |
| name.Start.Row, |
| name.Start.Column, |
| name.End.Row, |
| name.End.Column); |
| goto iterateCells; |
| } |
| } else { |
| var id = ExcelCellBase.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; |
| } |
| if (stack.Count > 0) { |
| //Check for circular references |
| foreach (var par in stack) { |
| if (ExcelCellBase.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 = ExcelCellBase.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; |
| } |
| if (stack.Count > 0) { |
| //Check for circular references |
| foreach (var par in stack) { |
| if (ExcelCellBase.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, |
| ExcelCellBase.GetAddress(f.Row, f.Column)))); |
| } |
| f = stack.Pop(); |
| goto iterateCells; |
| } |
| } |
| } |
| } |
| f.tokenIx++; |
| goto iterateToken; |
| } |
| } |