| 1 | /*******************************************************************************
|
|---|
| 2 | * You may amend and distribute as you like, but don't remove this header!
|
|---|
| 3 | *
|
|---|
| 4 | * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
|
|---|
| 5 | * See http://www.codeplex.com/EPPlus for details.
|
|---|
| 6 | *
|
|---|
| 7 | * Copyright (C) 2011 Jan Källman
|
|---|
| 8 | *
|
|---|
| 9 | * This library is free software; you can redistribute it and/or
|
|---|
| 10 | * modify it under the terms of the GNU Lesser General Public
|
|---|
| 11 | * License as published by the Free Software Foundation; either
|
|---|
| 12 | * version 2.1 of the License, or (at your option) any later version.
|
|---|
| 13 |
|
|---|
| 14 | * This library is distributed in the hope that it will be useful,
|
|---|
| 15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|---|
| 16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
|
|---|
| 17 | * See the GNU Lesser General Public License for more details.
|
|---|
| 18 | *
|
|---|
| 19 | * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
|
|---|
| 20 | * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
|
|---|
| 21 | *
|
|---|
| 22 | * All code and executables are provided "as is" with no warranty either express or implied.
|
|---|
| 23 | * The author accepts no liability for any damage or loss of business that this product may cause.
|
|---|
| 24 | *
|
|---|
| 25 | * Code change notes:
|
|---|
| 26 | *
|
|---|
| 27 | * Author Change Date
|
|---|
| 28 | * ******************************************************************************
|
|---|
| 29 | * Jan Källman Added 2012-03-04
|
|---|
| 30 | *******************************************************************************/
|
|---|
| 31 | using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
|
|---|
| 32 | using System;
|
|---|
| 33 | using System.Collections.Generic;
|
|---|
| 34 | using System.Linq;
|
|---|
| 35 | using System.Text;
|
|---|
| 36 | using OfficeOpenXml.FormulaParsing;
|
|---|
| 37 | using OfficeOpenXml.FormulaParsing.Exceptions;
|
|---|
| 38 |
|
|---|
| 39 | namespace OfficeOpenXml.FormulaParsing
|
|---|
| 40 | {
|
|---|
| 41 | internal static class DependencyChainFactory
|
|---|
| 42 | {
|
|---|
| 43 | internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options)
|
|---|
| 44 | {
|
|---|
| 45 | var depChain = new DependencyChain();
|
|---|
| 46 | foreach (var ws in wb.Worksheets)
|
|---|
| 47 | {
|
|---|
| 48 | if (!(ws is ExcelChartsheet))
|
|---|
| 49 | {
|
|---|
| 50 | GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options);
|
|---|
| 51 | GetWorksheetNames(ws, depChain, options);
|
|---|
| 52 | }
|
|---|
| 53 | }
|
|---|
| 54 | foreach (var name in wb.Names)
|
|---|
| 55 | {
|
|---|
| 56 | if (name.NameValue==null)
|
|---|
| 57 | {
|
|---|
| 58 | GetChain(depChain, wb.FormulaParser.Lexer, name, options);
|
|---|
| 59 | }
|
|---|
| 60 | }
|
|---|
| 61 | return depChain;
|
|---|
| 62 | }
|
|---|
| 63 |
|
|---|
| 64 | internal static DependencyChain Create(ExcelWorksheet ws, ExcelCalculationOption options)
|
|---|
| 65 | {
|
|---|
| 66 | ws.CheckSheetType();
|
|---|
| 67 | var depChain = new DependencyChain();
|
|---|
| 68 |
|
|---|
| 69 | GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws.Cells, options);
|
|---|
| 70 |
|
|---|
| 71 | GetWorksheetNames(ws, depChain, options);
|
|---|
| 72 |
|
|---|
| 73 | return depChain;
|
|---|
| 74 | }
|
|---|
| 75 | internal static DependencyChain Create(ExcelWorksheet ws, string Formula, ExcelCalculationOption options)
|
|---|
| 76 | {
|
|---|
| 77 | ws.CheckSheetType();
|
|---|
| 78 | var depChain = new DependencyChain();
|
|---|
| 79 |
|
|---|
| 80 | GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws, Formula, options);
|
|---|
| 81 |
|
|---|
| 82 | return depChain;
|
|---|
| 83 | }
|
|---|
| 84 |
|
|---|
| 85 | private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
|
|---|
| 86 | {
|
|---|
| 87 | foreach (var name in ws.Names)
|
|---|
| 88 | {
|
|---|
| 89 | if (!string.IsNullOrEmpty(name.NameFormula))
|
|---|
| 90 | {
|
|---|
| 91 | GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
|
|---|
| 92 | }
|
|---|
| 93 | }
|
|---|
| 94 | }
|
|---|
| 95 | internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options)
|
|---|
| 96 | {
|
|---|
| 97 | var depChain = new DependencyChain();
|
|---|
| 98 |
|
|---|
| 99 | GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options);
|
|---|
| 100 |
|
|---|
| 101 | return depChain;
|
|---|
| 102 | }
|
|---|
| 103 | private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
|
|---|
| 104 | {
|
|---|
| 105 | var ws = name.Worksheet;
|
|---|
| 106 | var id = ExcelCellBase.GetCellID(ws==null?0:ws.SheetID, name.Index, 0);
|
|---|
| 107 | if (!depChain.index.ContainsKey(id))
|
|---|
| 108 | {
|
|---|
| 109 | var f = new FormulaCell() { SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula=name.NameFormula };
|
|---|
| 110 | if (!string.IsNullOrEmpty(f.Formula))
|
|---|
| 111 | {
|
|---|
| 112 | f.Tokens = lexer.Tokenize(f.Formula, (ws==null ? null : ws.Name)).ToList();
|
|---|
| 113 | if (ws == null)
|
|---|
| 114 | {
|
|---|
| 115 | name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
|
|---|
| 116 | }
|
|---|
| 117 | else
|
|---|
| 118 | {
|
|---|
| 119 | ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
|
|---|
| 120 | }
|
|---|
| 121 | depChain.Add(f);
|
|---|
| 122 | FollowChain(depChain, lexer,name._workbook, ws, f, options);
|
|---|
| 123 | }
|
|---|
| 124 | }
|
|---|
| 125 | }
|
|---|
| 126 | private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelWorksheet ws, string formula, ExcelCalculationOption options)
|
|---|
| 127 | {
|
|---|
| 128 | var f = new FormulaCell() { SheetID = ws.SheetID, Row = -1, Column = -1 };
|
|---|
| 129 | f.Formula = formula;
|
|---|
| 130 | if (!string.IsNullOrEmpty(f.Formula))
|
|---|
| 131 | {
|
|---|
| 132 | f.Tokens = lexer.Tokenize(f.Formula, ws.Name).ToList();
|
|---|
| 133 | depChain.Add(f);
|
|---|
| 134 | FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
|
|---|
| 135 | }
|
|---|
| 136 | }
|
|---|
| 137 |
|
|---|
| 138 | private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options)
|
|---|
| 139 | {
|
|---|
| 140 | var ws = Range.Worksheet;
|
|---|
| 141 | var fs = new CellsStoreEnumerator<object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column);
|
|---|
| 142 | while (fs.Next())
|
|---|
| 143 | {
|
|---|
| 144 | if (fs.Value == null || fs.Value.ToString().Trim() == "") continue;
|
|---|
| 145 | var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column);
|
|---|
| 146 | if (!depChain.index.ContainsKey(id))
|
|---|
| 147 | {
|
|---|
| 148 | var f = new FormulaCell() { SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column };
|
|---|
| 149 | if (fs.Value is int)
|
|---|
| 150 | {
|
|---|
| 151 | f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name);
|
|---|
| 152 | }
|
|---|
| 153 | else
|
|---|
| 154 | {
|
|---|
| 155 | f.Formula = fs.Value.ToString();
|
|---|
| 156 | }
|
|---|
| 157 | if (!string.IsNullOrEmpty(f.Formula))
|
|---|
| 158 | {
|
|---|
| 159 | f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList();
|
|---|
| 160 | ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens);
|
|---|
| 161 | depChain.Add(f);
|
|---|
| 162 | FollowChain(depChain, lexer, ws.Workbook, ws, f, options);
|
|---|
| 163 | }
|
|---|
| 164 | }
|
|---|
| 165 | }
|
|---|
| 166 | }
|
|---|
| 167 | /// <summary>
|
|---|
| 168 | /// This method follows the calculation chain to get the order of the calculation
|
|---|
| 169 | /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
|
|---|
| 170 | /// </summary>
|
|---|
| 171 | /// <param name="depChain">The dependency chain object</param>
|
|---|
| 172 | /// <param name="lexer">The formula tokenizer</param>
|
|---|
| 173 | /// <param name="wb">The workbook where the formula comes from</param>
|
|---|
| 174 | /// <param name="ws">The worksheet where the formula comes from</param>
|
|---|
| 175 | /// <param name="f">The cell function object</param>
|
|---|
| 176 | /// <param name="options">Calcultaiton options</param>
|
|---|
| 177 | private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
|
|---|
| 178 | {
|
|---|
| 179 | Stack<FormulaCell> stack = new Stack<FormulaCell>();
|
|---|
| 180 | iterateToken:
|
|---|
| 181 | while (f.tokenIx < f.Tokens.Count)
|
|---|
| 182 | {
|
|---|
| 183 | var t = f.Tokens[f.tokenIx];
|
|---|
| 184 | if (t.TokenType == TokenType.ExcelAddress)
|
|---|
| 185 | {
|
|---|
| 186 | var adr = new ExcelFormulaAddress(t.Value);
|
|---|
| 187 | if (adr.Table != null)
|
|---|
| 188 | {
|
|---|
| 189 | adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
|
|---|
| 190 | }
|
|---|
| 191 |
|
|---|
| 192 | if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column))!=ExcelAddressBase.eAddressCollition.No)
|
|---|
| 193 | {
|
|---|
| 194 | throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
|
|---|
| 195 | }
|
|---|
| 196 |
|
|---|
| 197 | if (adr._fromRow > 0 && adr._fromCol > 0)
|
|---|
| 198 | {
|
|---|
| 199 | if (string.IsNullOrEmpty(adr.WorkSheet))
|
|---|
| 200 | {
|
|---|
| 201 | if (f.ws == null)
|
|---|
| 202 | {
|
|---|
| 203 | f.ws = ws;
|
|---|
| 204 | }
|
|---|
| 205 | else if (f.ws.SheetID != f.SheetID)
|
|---|
| 206 | {
|
|---|
| 207 | f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
|
|---|
| 208 | }
|
|---|
| 209 | }
|
|---|
| 210 | else
|
|---|
| 211 | {
|
|---|
| 212 | f.ws = wb.Worksheets[adr.WorkSheet];
|
|---|
| 213 | }
|
|---|
| 214 |
|
|---|
| 215 | if (f.ws != null)
|
|---|
| 216 | {
|
|---|
| 217 | f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
|
|---|
| 218 | goto iterateCells;
|
|---|
| 219 | }
|
|---|
| 220 | }
|
|---|
| 221 | }
|
|---|
| 222 | else if (t.TokenType == TokenType.NameValue)
|
|---|
| 223 | {
|
|---|
| 224 | string adrWb, adrWs, adrName;
|
|---|
| 225 | ExcelNamedRange name;
|
|---|
| 226 | ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws==null ? "" : f.ws.Name);
|
|---|
| 227 | if (!string.IsNullOrEmpty(adrWs))
|
|---|
| 228 | {
|
|---|
| 229 | if (f.ws == null)
|
|---|
| 230 | {
|
|---|
| 231 | f.ws = wb.Worksheets[adrWs];
|
|---|
| 232 | }
|
|---|
| 233 | if(f.ws.Names.ContainsKey(t.Value))
|
|---|
| 234 | {
|
|---|
| 235 | name = f.ws.Names[adrName];
|
|---|
| 236 | }
|
|---|
| 237 | else if (wb.Names.ContainsKey(adrName))
|
|---|
| 238 | {
|
|---|
| 239 | name = wb.Names[adrName];
|
|---|
| 240 | }
|
|---|
| 241 | else
|
|---|
| 242 | {
|
|---|
| 243 | name = null;
|
|---|
| 244 | }
|
|---|
| 245 | if(name != null) f.ws = name.Worksheet;
|
|---|
| 246 | }
|
|---|
| 247 | else if (wb.Names.ContainsKey(adrName))
|
|---|
| 248 | {
|
|---|
| 249 | name = wb.Names[t.Value];
|
|---|
| 250 | if (string.IsNullOrEmpty(adrWs))
|
|---|
| 251 | {
|
|---|
| 252 | f.ws = name.Worksheet;
|
|---|
| 253 | }
|
|---|
| 254 | }
|
|---|
| 255 | else
|
|---|
| 256 | {
|
|---|
| 257 | name = null;
|
|---|
| 258 | }
|
|---|
| 259 |
|
|---|
| 260 | if (name != null)
|
|---|
| 261 | {
|
|---|
| 262 |
|
|---|
| 263 | if (string.IsNullOrEmpty(name.NameFormula))
|
|---|
| 264 | {
|
|---|
| 265 | if (name.NameValue == null)
|
|---|
| 266 | {
|
|---|
| 267 | f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, name.Start.Row,
|
|---|
| 268 | name.Start.Column, name.End.Row, name.End.Column);
|
|---|
| 269 | goto iterateCells;
|
|---|
| 270 | }
|
|---|
| 271 | }
|
|---|
| 272 | else
|
|---|
| 273 | {
|
|---|
| 274 | var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);
|
|---|
| 275 |
|
|---|
| 276 | if (!depChain.index.ContainsKey(id))
|
|---|
| 277 | {
|
|---|
| 278 | var rf = new FormulaCell() { SheetID = name.LocalSheetId, Row = name.Index, Column = 0 };
|
|---|
| 279 | rf.Formula = name.NameFormula;
|
|---|
| 280 | rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();
|
|---|
| 281 |
|
|---|
| 282 | depChain.Add(rf);
|
|---|
| 283 | stack.Push(f);
|
|---|
| 284 | f = rf;
|
|---|
| 285 | goto iterateToken;
|
|---|
| 286 | }
|
|---|
| 287 | else
|
|---|
| 288 | {
|
|---|
| 289 | if (stack.Count > 0)
|
|---|
| 290 | {
|
|---|
| 291 | //Check for circular references
|
|---|
| 292 | foreach (var par in stack)
|
|---|
| 293 | {
|
|---|
| 294 | if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id)
|
|---|
| 295 | {
|
|---|
| 296 | throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
|
|---|
| 297 | }
|
|---|
| 298 | }
|
|---|
| 299 | }
|
|---|
| 300 | }
|
|---|
| 301 | }
|
|---|
| 302 | }
|
|---|
| 303 | }
|
|---|
| 304 | f.tokenIx++;
|
|---|
| 305 | }
|
|---|
| 306 | depChain.CalcOrder.Add(f.Index);
|
|---|
| 307 | if (stack.Count > 0)
|
|---|
| 308 | {
|
|---|
| 309 | f = stack.Pop();
|
|---|
| 310 | goto iterateCells;
|
|---|
| 311 | }
|
|---|
| 312 | return;
|
|---|
| 313 | iterateCells:
|
|---|
| 314 |
|
|---|
| 315 | while (f.iterator != null && f.iterator.Next())
|
|---|
| 316 | {
|
|---|
| 317 | var v = f.iterator.Value;
|
|---|
| 318 | if (v == null || v.ToString().Trim() == "") continue;
|
|---|
| 319 | var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
|
|---|
| 320 | if (!depChain.index.ContainsKey(id))
|
|---|
| 321 | {
|
|---|
| 322 | var rf = new FormulaCell() { SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column };
|
|---|
| 323 | if (f.iterator.Value is int)
|
|---|
| 324 | {
|
|---|
| 325 | rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
|
|---|
| 326 | }
|
|---|
| 327 | else
|
|---|
| 328 | {
|
|---|
| 329 | rf.Formula = v.ToString();
|
|---|
| 330 | }
|
|---|
| 331 | rf.ws = f.ws;
|
|---|
| 332 | rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
|
|---|
| 333 | ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
|
|---|
| 334 | depChain.Add(rf);
|
|---|
| 335 | stack.Push(f);
|
|---|
| 336 | f = rf;
|
|---|
| 337 | goto iterateToken;
|
|---|
| 338 | }
|
|---|
| 339 | else
|
|---|
| 340 | {
|
|---|
| 341 | if (stack.Count > 0)
|
|---|
| 342 | {
|
|---|
| 343 | //Check for circular references
|
|---|
| 344 | foreach (var par in stack)
|
|---|
| 345 | {
|
|---|
| 346 | if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
|
|---|
| 347 | {
|
|---|
| 348 | if (options.AllowCirculareReferences == false)
|
|---|
| 349 | {
|
|---|
| 350 | throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
|
|---|
| 351 | }
|
|---|
| 352 | else
|
|---|
| 353 | {
|
|---|
| 354 | f = stack.Pop();
|
|---|
| 355 | goto iterateCells;
|
|---|
| 356 | }
|
|---|
| 357 | }
|
|---|
| 358 | }
|
|---|
| 359 | }
|
|---|
| 360 | }
|
|---|
| 361 | }
|
|---|
| 362 | f.tokenIx++;
|
|---|
| 363 | goto iterateToken;
|
|---|
| 364 | }
|
|---|
| 365 | }
|
|---|
| 366 | }
|
|---|
