public ExcelValue ROUNDDOWN(List <ExcelValue> args, ExpressionScope scope) { if (args.NotDecimal(0, null, out double number)) { return(ExcelValue.VALUE); } if (args.NotInteger(1, null, out int decimals)) { return(ExcelValue.VALUE); } double round; if (decimals < 0) { var factor = Math.Pow(10, -decimals); round = Math.Truncate(number / factor) * factor; } else { var factor = Math.Pow(10, decimals); round = Math.Truncate(number * factor) / factor; } return(new ExcelValue.DecimalValue(round, scope.OutLanguage)); }
public ExcelValue VALUE(List <ExcelValue> args, ExpressionScope scope) { if (args.ContainErrorValues()) { return(ExcelValue.NA); } var textArg = args[0]; if (textArg is ExcelValue.BooleanValue) { return(ExcelValue.VALUE); } if (textArg is ExcelValue.DecimalValue) { return(args[0]); } if (textArg is ExcelValue.TextValue) { try { var value = scope.OutLanguage.ToDecimal(textArg.Text); return(new ExcelValue.DecimalValue(value, scope.OutLanguage)); } catch { return(ExcelValue.VALUE); } } throw new NotImplementedException(); }
public ExcelValue TYPE(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1) { if (args[0] is ExcelValue.DecimalValue) { return(new ExcelValue.DecimalValue(1, scope.OutLanguage)); } if (args[0] is ExcelValue.TextValue) { return(new ExcelValue.DecimalValue(2, scope.OutLanguage)); } if (args[0] is ExcelValue.BooleanValue) { return(new ExcelValue.DecimalValue(4, scope.OutLanguage)); } if (args[0] is ExcelValue.ErrorValue) { return(new ExcelValue.DecimalValue(16, scope.OutLanguage)); } if (args[0] is ExcelValue.ArrayValue) { return(new ExcelValue.DecimalValue(64, scope.OutLanguage)); } } return(ExcelValue.VALUE); }
public ExcelValue FLOOR_PRECISE(List <ExcelValue> args, ExpressionScope scope) { if (args.NotDecimal(0, null, out double number)) { return(ExcelValue.VALUE); } if (args.NotDecimal(1, 1, out double significance)) { return(ExcelValue.VALUE); } if (significance == 0) { return(ExcelValue.ZERO); } var sign = Math.Sign(number); number = Math.Abs(number); significance = Math.Abs(significance); double ceiling; if (sign < 0) { ceiling = Math.Ceiling(number / significance) * significance; } else { ceiling = Math.Floor(number / significance) * significance; } return(new ExcelValue.DecimalValue(sign * ceiling, scope.OutLanguage)); }
public ExcelValue SORTBY(List <ExcelValue> args, ExpressionScope scope) { // SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) if (args.NotArray(0, null, out ExcelValue.ArrayValue arrayToSort)) { return(ExcelValue.VALUE); } var argsCount = args.Count; if (argsCount < 2) { return(ExcelValue.NA); } var matrix = arrayToSort.Values.Select(v => new List <ExcelValue> { v }).ToList(); var sorters = argsCount / 2; var index = 1; var asc = new List <int> { 0 }; while (index < argsCount) { if (args.NotArray(1, null, out ExcelValue.ArrayValue sortBy)) { return(ExcelValue.VALUE); } ++index; if (args.NotInteger(2, 1, out int sortOrder)) { return(ExcelValue.VALUE); } if (sortOrder != 1 && sortOrder != -1) { return(ExcelValue.VALUE); } asc.Add(sortOrder); ++index; var arraySortBy = sortBy.Values.ToArray(); for (var i = 0; i < matrix.Count; i++) { matrix[i].Add(arraySortBy[i]); } } matrix.Sort((a1, a2) => { var result = -1; var column = 1; do { result = asc[column] * a1[column].CompareTo(a2[column]); ++column; } while (result != 0 && column <= sorters); return(result); }); return(new ExcelValue.ArrayValue(matrix.Select(r => r[0]), scope.OutLanguage)); }
public ExcelValue FIXED(List <ExcelValue> args, ExpressionScope scope) { if (args.ContainErrorValues()) { return(ExcelValue.NA); } if (args.NotDecimal(0, null, out double num)) { return(ExcelValue.VALUE); } if (args.NotInteger(1, 2, out int decimals)) { return(ExcelValue.VALUE); } if (args.NotBoolean(2, false, out bool noComma)) { return(ExcelValue.VALUE); } if (decimals < 0) { var factor = Math.Pow(10, -decimals); num = Math.Round(num / factor, 0) * factor; decimals = 0; } return(new ExcelValue.DecimalValue(num, scope.OutLanguage, noComma ? ExpressionFormat.CreateFixedPoint(decimals) : ExpressionFormat.CreateNumeric(decimals))); }
public ExcelValue VLOOKUP(List <ExcelValue> args, ExpressionScope scope) { // VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) var argsCount = args.Count(); if (argsCount < 3) { return(ExcelValue.NA); } if (args.NotArray(1, null, out ExcelValue.ArrayValue tableArray)) { return(ExcelValue.REF); } if (args.NotPosInteger(2, null, out int colNum)) { return(ExcelValue.VALUE); } args.NotBoolean(3, true, out bool approxMatch); if (!(tableArray.GetColumn(1) is ExcelValue.ArrayValue lookupVector)) { return(ExcelValue.REF); } if (!(tableArray.GetColumn(colNum) is ExcelValue.ArrayValue resultVector)) { return(ExcelValue.REF); } string pattern = null; var matchMode = approxMatch ? -1 : (ExcelCriteria.IsRegex(args[0].Text, out pattern) ? 2 : 0); return(XLOOKUP(args[0], lookupVector, resultVector, ExcelValue.NA, matchMode, 2, pattern)); }
public ExcelValue LEN(List <ExcelValue> args, ExpressionScope scope) { if (args.ContainErrorValues()) { return(ExcelValue.NA); } return(new ExcelValue.DecimalValue(args[0].Text.Length, scope.OutLanguage)); }
public ExcelValue AND(List <ExcelValue> args, ExpressionScope scope) { if (args.Any(a => !a.AsBoolean().HasValue)) { return(ExcelValue.NA); } return(ExcelValue.BooleanValue.Create(args.All(o => o.AsBoolean().Value))); }
public ExcelValue XOR(List <ExcelValue> args, ExpressionScope scope) { if (args.Any(a => !a.AsBoolean().HasValue)) { return(ExcelValue.NA); } return(ExcelValue.BooleanValue.Create(args.Select(o => o.AsBoolean().Value).Aggregate((a, b) => a ^ b))); }
public ExcelValue TRIM(List <ExcelValue> args, ExpressionScope scope) { if (args.NotText(0, null, scope.OutLanguage, out string text)) { return(ExcelValue.NA); } return(new ExcelValue.TextValue(text.Trim(), scope.OutLanguage)); }
public ExcelValue ISTEXT(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] is ExcelValue.TextValue) { return(ExcelValue.TRUE); } return(ExcelValue.FALSE); }
public ExcelValue UPPER(List <ExcelValue> args, ExpressionScope scope) { if (args.ContainErrorValues()) { return(ExcelValue.NA); } return(new ExcelValue.TextValue(scope.OutLanguage.ToUpper(args[0].Text), scope.OutLanguage)); }
public ExcelValue ROWS(List <ExcelValue> args, ExpressionScope scope) { if (args.NotArray(0, null, out ExcelValue.ArrayValue array)) { return(args.ElementAtOrDefault(0) is ExcelValue.DecimalValue ? ExcelValue.ONE : ExcelValue.VALUE); } return(new ExcelValue.DecimalValue(array.Values.Count(), scope.OutLanguage)); }
public ExcelValue IF(List <ExcelValue> args, ExpressionScope scope) { if (!args[0].AsBoolean().HasValue) { return(ExcelValue.NA); } return(args[0].AsBoolean().Value ? args[1] : args[2]); }
public ExcelValue ISNA(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] == ExcelValue.NA) { return(ExcelValue.TRUE); } return(ExcelValue.FALSE); }
public ExcelValue ISNUMBER(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] is ExcelValue.DecimalValue) { return(ExcelValue.TRUE); } return(ExcelValue.FALSE); }
public ExcelValue ISLOGICAL(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] is ExcelValue.BooleanValue) { return(ExcelValue.TRUE); } return(ExcelValue.FALSE); }
public ExcelValue ISBLANK(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] is ExcelValue.RangeValue) { return(ExcelValue.BooleanValue.Create(scope.Contains(args[0].Text))); } return(ExcelValue.FALSE); }
public ExcelValue ISERROR(List <ExcelValue> args, ExpressionScope scope) { if (args.Count == 1 && args[0] is ExcelValue.ErrorValue) { return(ExcelValue.TRUE); } return(ExcelValue.FALSE); }
public ExcelValue ENCODEURL(List <ExcelValue> args, ExpressionScope scope) { if (args.NotText(0, null, scope.OutLanguage, out string url)) { return(ExcelValue.NA); } return(new ExcelValue.TextValue(Uri.EscapeDataString(url), scope.OutLanguage)); }
public ExcelValue TIMEVALUE(List <ExcelValue> args, ExpressionScope scope) { if (args.NotDecimal(0, null, out double serial)) { return(ExcelValue.VALUE); } return(new ExcelValue.DecimalValue(serial - Math.Truncate(serial), scope.OutLanguage, ExpressionFormat.General)); }
public ExcelValue XLOOKUP(List <ExcelValue> args, ExpressionScope scope) { // XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) var argsCount = args.Count(); if (argsCount < 3) { return(ExcelValue.NA); } var lookupValue = args[0]; if (!lookupValue.SingleValue) { return(ExcelValue.VALUE); } if (args.NotArray(1, null, out ExcelValue.ArrayValue lookupArray)) { return(ExcelValue.REF); } if (args.NotArray(2, null, out ExcelValue.ArrayValue returnArray)) { return(ExcelValue.REF); } var ifNotFound = ExcelValue.NA; if (argsCount > 3 && args[3] != null) { ifNotFound = args[3]; } if (args.NotInteger(4, 0, out int matchMode)) { return(ExcelValue.VALUE); } if (args.NotInteger(5, 1, out int searchMode)) { return(ExcelValue.VALUE); } if (searchMode < -2 || searchMode > 2 || searchMode == 0) { return(ExcelValue.VALUE); } string pattern = null; if (matchMode == 2) { if (!ExcelCriteria.IsRegex(lookupValue.Text, out pattern)) { return(ExcelValue.VALUE); } } return(XLOOKUP(lookupValue, lookupArray, returnArray, ifNotFound, matchMode, searchMode, pattern)); }
public ExcelValue CONCATENATE(List <ExcelValue> args, ExpressionScope scope) { if (args.ContainErrorValues()) { return(ExcelValue.NA); } var parts = args.Select(a => a.ToString(scope.OutLanguage, null)); var result = string.Join(string.Empty, parts); return(new ExcelValue.TextValue(result, scope.OutLanguage)); }
public ExcelValue SUM(List <ExcelValue> args, ExpressionScope scope) { var numbers = args.FlattenNumbers(false); if (numbers == null) { return(ExcelValue.VALUE); } return(new ExcelValue.DecimalValue(numbers.Sum(), scope.OutLanguage)); }
public ExcelValue NOT(List <ExcelValue> args, ExpressionScope scope) { if (args[0] is ExcelValue.TextValue) { return(ExcelValue.VALUE); } if (!args[0].AsBoolean().HasValue) { return(ExcelValue.NA); } return(ExcelValue.BooleanValue.Create(!args[0].AsBoolean().Value)); }
public ExcelValue T(List <ExcelValue> args, ExpressionScope scope) { if (args[0] is ExcelValue.ErrorValue) { return(ExcelValue.NA); } if (args[0] is ExcelValue.TextValue /*|| args[0] is ExcelValue.JsonTextValue*/) { return(args[0]); } return(scope.OutLanguage.EmptyText); }
private void ResolveRangeValues(ExpressionScope scope) { for (var index = 0; index < this.Count; index++) { var partValue = this[index]; if (partValue.HasRangeValue) { var rangeAddress = partValue.Value.Text; this[index] = new ExcelExpressionPart(scope.Get(rangeAddress)); } } }
public ExcelExpressionPart(ExcelFormulaToken token, ExpressionScope scope) { this.originalToken = token; this.TokenType = token.Type; this.scope = scope; if (token.Type == ExcelFormulaTokenType.OperatorInfix || token.Type == ExcelFormulaTokenType.OperatorPrefix || token.Type == ExcelFormulaTokenType.OperatorPostfix ) { this.Operator = token.Value; } }
private ExcelValue Math1(List <ExcelValue> args, ExpressionScope scope, Func <double, double> oper, Func <double, bool> guard = null) { if (args.NotDecimal(0, null, out double number)) { return(ExcelValue.VALUE); } if (guard != null && guard(number)) { return(ExcelValue.VALUE); } return(new ExcelValue.DecimalValue(oper(number), scope.OutLanguage)); }