Context Navigation

source: trunk/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/FormulaParsing/DependencyChain/DependenyChainFactory.cs @ 15682

Visit:

Last change on this file since 15682 was 12074, checked in by sraggl, 10 years ago
#2341: Added EPPlus-4.0.3 to ExtLibs
File size: 16.3 KB

Line
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	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats:

Update cookies preferences