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 | * Mats Alm Added 2013-03-01 (Prior file history on https://github.com/swmal/ExcelFormulaParser)
|
---|
30 | *******************************************************************************/
|
---|
31 | using System;
|
---|
32 | using System.Collections.Generic;
|
---|
33 | using System.Linq;
|
---|
34 | using System.Text;
|
---|
35 | using OfficeOpenXml.FormulaParsing.ExpressionGraph;
|
---|
36 | using OfficeOpenXml.FormulaParsing.Exceptions;
|
---|
37 | using OfficeOpenXml.Utils;
|
---|
38 |
|
---|
39 | namespace OfficeOpenXml.FormulaParsing.Excel.Operators
|
---|
40 | {
|
---|
41 | public class Operator : IOperator
|
---|
42 | {
|
---|
43 | private const int PrecedencePercent = 2;
|
---|
44 | private const int PrecedenceExp = 4;
|
---|
45 | private const int PrecedenceMultiplyDevide = 6;
|
---|
46 | private const int PrecedenceIntegerDivision = 8;
|
---|
47 | private const int PrecedenceModulus = 10;
|
---|
48 | private const int PrecedenceAddSubtract = 12;
|
---|
49 | private const int PrecedenceConcat = 15;
|
---|
50 | private const int PrecedenceComparison = 25;
|
---|
51 |
|
---|
52 | private Operator() { }
|
---|
53 |
|
---|
54 | private Operator(Operators @operator, int precedence, Func<CompileResult, CompileResult, CompileResult> implementation)
|
---|
55 | {
|
---|
56 | _implementation = implementation;
|
---|
57 | _precedence = precedence;
|
---|
58 | _operator = @operator;
|
---|
59 | }
|
---|
60 |
|
---|
61 | private readonly Func<CompileResult, CompileResult, CompileResult> _implementation;
|
---|
62 | private readonly int _precedence;
|
---|
63 | private readonly Operators _operator;
|
---|
64 |
|
---|
65 | int IOperator.Precedence
|
---|
66 | {
|
---|
67 | get { return _precedence; }
|
---|
68 | }
|
---|
69 |
|
---|
70 | Operators IOperator.Operator
|
---|
71 | {
|
---|
72 | get { return _operator; }
|
---|
73 | }
|
---|
74 |
|
---|
75 | public CompileResult Apply(CompileResult left, CompileResult right)
|
---|
76 | {
|
---|
77 | if (left.Result is ExcelErrorValue)
|
---|
78 | {
|
---|
79 | return new CompileResult(left.Result, DataType.ExcelError);
|
---|
80 | //throw(new ExcelErrorValueException((ExcelErrorValue)left.Result));
|
---|
81 | }
|
---|
82 | else if (right.Result is ExcelErrorValue)
|
---|
83 | {
|
---|
84 | return new CompileResult(right.Result, DataType.ExcelError);
|
---|
85 | //throw(new ExcelErrorValueException((ExcelErrorValue)right.Result));
|
---|
86 | }
|
---|
87 | return _implementation(left, right);
|
---|
88 | }
|
---|
89 |
|
---|
90 | public override string ToString()
|
---|
91 | {
|
---|
92 | return "Operator: " + _operator;
|
---|
93 | }
|
---|
94 |
|
---|
95 | private static IOperator _plus;
|
---|
96 | public static IOperator Plus
|
---|
97 | {
|
---|
98 | get
|
---|
99 | {
|
---|
100 | return _plus ?? (_plus = new Operator(Operators.Plus, PrecedenceAddSubtract, (l, r) =>
|
---|
101 | {
|
---|
102 | l = l == null || l.Result == null ? new CompileResult(0, DataType.Integer) : l;
|
---|
103 | r = r == null || r.Result == null ? new CompileResult(0, DataType.Integer) : r;
|
---|
104 | ExcelErrorValue errorVal;
|
---|
105 | if (EitherIsError(l, r, out errorVal))
|
---|
106 | {
|
---|
107 | return new CompileResult(errorVal);
|
---|
108 | }
|
---|
109 | if (l.DataType == DataType.Integer && r.DataType == DataType.Integer)
|
---|
110 | {
|
---|
111 | return new CompileResult(l.ResultNumeric + r.ResultNumeric, DataType.Integer);
|
---|
112 | }
|
---|
113 | else if ((l.IsNumeric || l.IsNumericString || l.Result is ExcelDataProvider.IRangeInfo) &&
|
---|
114 | (r.IsNumeric || r.IsNumericString || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
115 | {
|
---|
116 | return new CompileResult(l.ResultNumeric + r.ResultNumeric, DataType.Decimal);
|
---|
117 | }
|
---|
118 | return new CompileResult(eErrorType.Value);
|
---|
119 | }));
|
---|
120 | }
|
---|
121 | }
|
---|
122 |
|
---|
123 | private static IOperator _minus;
|
---|
124 | public static IOperator Minus
|
---|
125 | {
|
---|
126 | get
|
---|
127 | {
|
---|
128 | return _minus ?? (_minus = new Operator(Operators.Minus, PrecedenceAddSubtract, (l, r) =>
|
---|
129 | {
|
---|
130 | l = l == null || l.Result == null ? new CompileResult(0, DataType.Integer) : l;
|
---|
131 | r = r == null || r.Result == null ? new CompileResult(0, DataType.Integer) : r;
|
---|
132 | if (l.DataType == DataType.Integer && r.DataType == DataType.Integer)
|
---|
133 | {
|
---|
134 | return new CompileResult(l.ResultNumeric - r.ResultNumeric, DataType.Integer);
|
---|
135 | }
|
---|
136 | else if ((l.IsNumeric || l.IsNumericString || l.Result is ExcelDataProvider.IRangeInfo) &&
|
---|
137 | (r.IsNumeric || r.IsNumericString || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
138 | {
|
---|
139 | return new CompileResult(l.ResultNumeric - r.ResultNumeric, DataType.Decimal);
|
---|
140 | }
|
---|
141 |
|
---|
142 | return new CompileResult(eErrorType.Value);
|
---|
143 | }));
|
---|
144 | }
|
---|
145 | }
|
---|
146 |
|
---|
147 | private static IOperator _multiply;
|
---|
148 | public static IOperator Multiply
|
---|
149 | {
|
---|
150 | get
|
---|
151 | {
|
---|
152 | return _multiply ?? (_multiply = new Operator(Operators.Multiply, PrecedenceMultiplyDevide, (l, r) =>
|
---|
153 | {
|
---|
154 | l = l ?? new CompileResult(0, DataType.Integer);
|
---|
155 | r = r ?? new CompileResult(0, DataType.Integer);
|
---|
156 | if (l.DataType == DataType.Integer && r.DataType == DataType.Integer)
|
---|
157 | {
|
---|
158 | return new CompileResult(l.ResultNumeric*r.ResultNumeric, DataType.Integer);
|
---|
159 | }
|
---|
160 | else if ((l.IsNumeric || l.IsNumericString || l.Result is ExcelDataProvider.IRangeInfo) &&
|
---|
161 | (r.IsNumeric || r.IsNumericString || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
162 | {
|
---|
163 | return new CompileResult(l.ResultNumeric*r.ResultNumeric, DataType.Decimal);
|
---|
164 | }
|
---|
165 | return new CompileResult(eErrorType.Value);
|
---|
166 | }));
|
---|
167 | }
|
---|
168 | }
|
---|
169 |
|
---|
170 | private static IOperator _divide;
|
---|
171 | public static IOperator Divide
|
---|
172 | {
|
---|
173 | get
|
---|
174 | {
|
---|
175 | return _divide ?? (_divide = new Operator(Operators.Divide, PrecedenceMultiplyDevide, (l, r) =>
|
---|
176 | {
|
---|
177 | if (!(l.IsNumeric || l.IsNumericString || l.Result is ExcelDataProvider.IRangeInfo) ||
|
---|
178 | !(r.IsNumeric || r.IsNumericString || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
179 | {
|
---|
180 | return new CompileResult(eErrorType.Value);
|
---|
181 | }
|
---|
182 | var left = l.ResultNumeric;
|
---|
183 | var right = r.ResultNumeric;
|
---|
184 | if (Math.Abs(right - 0d) < double.Epsilon)
|
---|
185 | {
|
---|
186 | return new CompileResult(eErrorType.Div0);
|
---|
187 | }
|
---|
188 | else if ((l.IsNumeric || l.IsNumericString || l.Result is ExcelDataProvider.IRangeInfo) &&
|
---|
189 | (r.IsNumeric || r.IsNumericString || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
190 | {
|
---|
191 | return new CompileResult(left/right, DataType.Decimal);
|
---|
192 | }
|
---|
193 | return new CompileResult(eErrorType.Value);
|
---|
194 | }));
|
---|
195 | }
|
---|
196 | }
|
---|
197 |
|
---|
198 | public static IOperator Exp
|
---|
199 | {
|
---|
200 | get
|
---|
201 | {
|
---|
202 | return new Operator(Operators.Exponentiation, PrecedenceExp, (l, r) =>
|
---|
203 | {
|
---|
204 | if (l == null && r == null)
|
---|
205 | {
|
---|
206 | return new CompileResult(eErrorType.Value);
|
---|
207 | }
|
---|
208 | l = l ?? new CompileResult(0, DataType.Integer);
|
---|
209 | r = r ?? new CompileResult(0, DataType.Integer);
|
---|
210 | if ((l.IsNumeric || l.Result is ExcelDataProvider.IRangeInfo) && (r.IsNumeric || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
211 | {
|
---|
212 | return new CompileResult(Math.Pow(l.ResultNumeric, r.ResultNumeric), DataType.Decimal);
|
---|
213 | }
|
---|
214 | return new CompileResult(0d, DataType.Decimal);
|
---|
215 | });
|
---|
216 | }
|
---|
217 | }
|
---|
218 |
|
---|
219 | public static IOperator Concat
|
---|
220 | {
|
---|
221 | get
|
---|
222 | {
|
---|
223 | return new Operator(Operators.Concat, PrecedenceConcat, (l, r) =>
|
---|
224 | {
|
---|
225 | l = l ?? new CompileResult(string.Empty, DataType.String);
|
---|
226 | r = r ?? new CompileResult(string.Empty, DataType.String);
|
---|
227 | var lStr = l.Result != null ? l.ResultValue.ToString() : string.Empty;
|
---|
228 | var rStr = r.Result != null ? r.ResultValue.ToString() : string.Empty;
|
---|
229 | return new CompileResult(string.Concat(lStr, rStr), DataType.String);
|
---|
230 | });
|
---|
231 | }
|
---|
232 | }
|
---|
233 |
|
---|
234 | private static IOperator _greaterThan;
|
---|
235 | public static IOperator GreaterThan
|
---|
236 | {
|
---|
237 | get
|
---|
238 | {
|
---|
239 | //return new Operator(Operators.GreaterThan, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) > 0, DataType.Boolean));
|
---|
240 | return _greaterThan ??
|
---|
241 | (_greaterThan =
|
---|
242 | new Operator(Operators.LessThanOrEqual, PrecedenceComparison,
|
---|
243 | (l, r) => Compare(l, r, (compRes) => compRes > 0)));
|
---|
244 | }
|
---|
245 | }
|
---|
246 |
|
---|
247 | private static IOperator _eq;
|
---|
248 | public static IOperator Eq
|
---|
249 | {
|
---|
250 | get
|
---|
251 | {
|
---|
252 | //return new Operator(Operators.Equals, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) == 0, DataType.Boolean));
|
---|
253 | return _eq ??
|
---|
254 | (_eq =
|
---|
255 | new Operator(Operators.LessThanOrEqual, PrecedenceComparison,
|
---|
256 | (l, r) => Compare(l, r, (compRes) => compRes == 0)));
|
---|
257 | }
|
---|
258 | }
|
---|
259 |
|
---|
260 | private static IOperator _notEqualsTo;
|
---|
261 | public static IOperator NotEqualsTo
|
---|
262 | {
|
---|
263 | get
|
---|
264 | {
|
---|
265 | //return new Operator(Operators.NotEqualTo, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) != 0, DataType.Boolean));
|
---|
266 | return _notEqualsTo ??
|
---|
267 | (_notEqualsTo =
|
---|
268 | new Operator(Operators.LessThanOrEqual, PrecedenceComparison,
|
---|
269 | (l, r) => Compare(l, r, (compRes) => compRes != 0)));
|
---|
270 | }
|
---|
271 | }
|
---|
272 |
|
---|
273 | private static IOperator _greaterThanOrEqual;
|
---|
274 | public static IOperator GreaterThanOrEqual
|
---|
275 | {
|
---|
276 | get
|
---|
277 | {
|
---|
278 | //return new Operator(Operators.GreaterThanOrEqual, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) >= 0, DataType.Boolean));
|
---|
279 | return _greaterThanOrEqual ??
|
---|
280 | (_greaterThanOrEqual =
|
---|
281 | new Operator(Operators.LessThanOrEqual, PrecedenceComparison,
|
---|
282 | (l, r) => Compare(l, r, (compRes) => compRes >= 0)));
|
---|
283 | }
|
---|
284 | }
|
---|
285 |
|
---|
286 | private static IOperator _lessThan;
|
---|
287 | public static IOperator LessThan
|
---|
288 | {
|
---|
289 | get
|
---|
290 | {
|
---|
291 | //return new Operator(Operators.LessThan, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) < 0, DataType.Boolean));
|
---|
292 | return _lessThan ??
|
---|
293 | (_lessThan =
|
---|
294 | new Operator(Operators.LessThanOrEqual, PrecedenceComparison,
|
---|
295 | (l, r) => Compare(l, r, (compRes) => compRes < 0)));
|
---|
296 | }
|
---|
297 | }
|
---|
298 |
|
---|
299 | public static IOperator LessThanOrEqual
|
---|
300 | {
|
---|
301 | get
|
---|
302 | {
|
---|
303 | //return new Operator(Operators.LessThanOrEqual, PrecedenceComparison, (l, r) => new CompileResult(Compare(l, r) <= 0, DataType.Boolean));
|
---|
304 | return new Operator(Operators.LessThanOrEqual, PrecedenceComparison, (l, r) => Compare(l, r, (compRes) => compRes <= 0));
|
---|
305 | }
|
---|
306 | }
|
---|
307 |
|
---|
308 | private static IOperator _percent;
|
---|
309 | public static IOperator Percent
|
---|
310 | {
|
---|
311 | get
|
---|
312 | {
|
---|
313 | if (_percent == null)
|
---|
314 | {
|
---|
315 | _percent = new Operator(Operators.Percent, PrecedencePercent, (l, r) =>
|
---|
316 | {
|
---|
317 | l = l ?? new CompileResult(0, DataType.Integer);
|
---|
318 | r = r ?? new CompileResult(0, DataType.Integer);
|
---|
319 | if (l.DataType == DataType.Integer && r.DataType == DataType.Integer)
|
---|
320 | {
|
---|
321 | return new CompileResult(l.ResultNumeric * r.ResultNumeric, DataType.Integer);
|
---|
322 | }
|
---|
323 | else if ((l.IsNumeric || l.Result is ExcelDataProvider.IRangeInfo) && (r.IsNumeric || r.Result is ExcelDataProvider.IRangeInfo))
|
---|
324 | {
|
---|
325 | return new CompileResult(l.ResultNumeric * r.ResultNumeric, DataType.Decimal);
|
---|
326 | }
|
---|
327 | return new CompileResult(eErrorType.Value);
|
---|
328 | });
|
---|
329 | }
|
---|
330 | return _percent;
|
---|
331 | }
|
---|
332 | }
|
---|
333 |
|
---|
334 | private static object GetObjFromOther(CompileResult obj, CompileResult other)
|
---|
335 | {
|
---|
336 | if (obj.Result == null)
|
---|
337 | {
|
---|
338 | if (other.DataType == DataType.String) return string.Empty;
|
---|
339 | else return 0d;
|
---|
340 | }
|
---|
341 | return obj.ResultValue;
|
---|
342 | }
|
---|
343 |
|
---|
344 | private static CompileResult Compare(CompileResult l, CompileResult r, Func<int, bool> comparison )
|
---|
345 | {
|
---|
346 | ExcelErrorValue errorVal;
|
---|
347 | if (EitherIsError(l, r, out errorVal))
|
---|
348 | {
|
---|
349 | return new CompileResult(errorVal);
|
---|
350 | }
|
---|
351 | object left, right;
|
---|
352 | left = GetObjFromOther(l, r);
|
---|
353 | right = GetObjFromOther(r, l);
|
---|
354 | if (ConvertUtil.IsNumeric(left) && ConvertUtil.IsNumeric(right))
|
---|
355 | {
|
---|
356 | var lnum = ConvertUtil.GetValueDouble(left);
|
---|
357 | var rnum = ConvertUtil.GetValueDouble(right);
|
---|
358 | if (Math.Abs(lnum - rnum) < double.Epsilon)
|
---|
359 | {
|
---|
360 | return new CompileResult(comparison(0), DataType.Boolean);
|
---|
361 | }
|
---|
362 | var comparisonResult = lnum.CompareTo(rnum);
|
---|
363 | return new CompileResult(comparison(comparisonResult), DataType.Boolean);
|
---|
364 | }
|
---|
365 | else
|
---|
366 | {
|
---|
367 | var comparisonResult = CompareString(left, right);
|
---|
368 | return new CompileResult(comparison(comparisonResult), DataType.Boolean);
|
---|
369 | }
|
---|
370 | }
|
---|
371 |
|
---|
372 | private static int CompareString(object l, object r)
|
---|
373 | {
|
---|
374 | var sl = (l ?? "").ToString();
|
---|
375 | var sr = (r ?? "").ToString();
|
---|
376 | return System.String.Compare(sl, sr, System.StringComparison.Ordinal);
|
---|
377 | }
|
---|
378 |
|
---|
379 | private static bool EitherIsError(CompileResult l, CompileResult r, out ExcelErrorValue errorVal)
|
---|
380 | {
|
---|
381 | if (l.DataType == DataType.ExcelError)
|
---|
382 | {
|
---|
383 | errorVal = (ExcelErrorValue) l.Result;
|
---|
384 | return true;
|
---|
385 | }
|
---|
386 | if (r.DataType == DataType.ExcelError)
|
---|
387 | {
|
---|
388 | errorVal = (ExcelErrorValue) r.Result;
|
---|
389 | return true;
|
---|
390 | }
|
---|
391 | errorVal = null;
|
---|
392 | return false;
|
---|
393 | }
|
---|
394 | }
|
---|
395 | }
|
---|