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));
}
