JsonExcelExpressions 0.3.0

JsonExcelExpressions

Install the JsonExcelExpressions package

Install the JsonExcelExpressions library with NuGet:

dotnet add package JsonExcelExpressions

Examples

Single expression

// Create an evaluator
var evaluator = new ExpressionEvaluator();
// Evaluate an expression
var result = evaluator.Evaluate("1 + 1");
// Print the result
Console.WriteLine(result.Value); // 2

Expression on JSON object

// Get a JSON object
var json = JObject.Parse("{x: 2, y: 3}");
// Create an evaluator
var evaluator = new ExpressionEvaluator();
// Evaluate an expression
var result = evaluator.Evaluate("x * y", json);
// Print the result
Console.WriteLine(result.Value); // 6

Expression with Excel functions

// Get a JSON object
var json = JObject.Parse("{x: [1, 2, 3], y: 'abc'}");
// Create an evaluator
var evaluator = new ExpressionEvaluator();
// Evaluate an expression
var result = evaluator.Evaluate("UPPER(y) & SUM(x)", json);
// Print the result
Console.WriteLine(result.Value); // ABC6

Multiple expressions

// Get a JSON object
var json = JObject.Parse("{x: 2, y: 3}");
// Create an evaluator
var evaluator = new ExpressionEvaluator();
// Create a set of expressions
var expressions = new string[] { "x + y", "x - y" };
// Evaluate an expression
var results = evaluator.Evaluate(expressions, json);
// Print the result
foreach (var result in results)
    Console.WriteLine(result.Value); // 5, -1

More examples for all supported functions can be found in the unit tests of the package.

Supported Excel Functions

Date and time

• DATE(year, month, day)
• DATEDIF(start_date, end_date, unit)
• DATEVALUE(date_text)
• DAY(serial_number)
• DAYS(end_date, start_date)
• HOUR(serial_number)
• MINUTE(serial_number)
• MONTH(serial_number)
• NOW()
• SECOND(serial_number)
• TIME(hour, minute, second)
• TIMEVALUE(time_text)
• TODAY()
• YEAR(serial_number)

Text

• CONCATENATE(text1, text2, ...)
• EXACT(text1, text2)
• FIND(find_text, within_text, start_num)
• FIXED(number, decimal, no_commas)
• LEFT(text, num_chars)
• LEN(text)
• LOWER(text)
• MID(text, start_num, num_chars)
• PROPER(text)
• REPLACE(old_text, start_num, num_chars, new_text)
• RIGHT(text, num_chars)
• SEARCH(find_text, within_text, start_num)
• SUBSTITUTE(text, old_text, new_text, instance_num)
• T(value)
• TEXT(value, format_text)
• TRIM(text)
• UPPER(text)
• VALUE(text)

Logical

• AND(logical1, logical2, ...)
• IF(logical_test, value_if_true, value_if_false)
• IFERROR(value, value_if_error)
• IFNA(value, value_if_na)
• NOT(logical)
• OR(logical1, logical2, ...)
• XOR(logical1, logical2, ...)

Math

• Operators +, -, *, /, %, ^
• ABS(number)
• ACOS(number)
• ACOSH(number)
• ACOT(number)
• ACOTH(number)
• ASIN(number)
• ASINH(number)
• ATAN(number)
• ATAN2(x_num, y_num)
• ATANH(number)
• CEILING(number)
• CEILING.MATH(number, significance, mode)
• CEILING.PRECISE(number, significance)
• COS(number)
• COSH(number)
• COT(number)
• COTH(number)
• CSC(number)
• CSCH(number)
• FLOOR(number)
• FLOOR.MATH(number, significance, mode)
• FLOOR.PRECISE(number, significance)
• PI()
• ROUND(number, digits)
• ROUNDUP(number, digits)
• ROUNDDOWN(number, digits)
• TRUNC(number, digits)
• EXP(number)
• INT(number)
• LN(number),
• LOG(number, base)
• LOG10(number)
• MOD(number, divisor)
• POWER(x, y)
• RAND()
• RANDBETWEEN(min, max)
• SIGN(number)
• SEC(number)
• SECH(number)
• SIN(number)
• SINH(number)
• SUM(number1, number2, ...)
• TAN(number)
• TANH(number)

Web

• ENCODEURL(url)
• FILTERXML(xml, xpath)
• WEBSERVICE(url)

Lookup and Reference

• CHOOSE(index_num, value1, [value2], ...)
• COLUMNS(array)
• INDEX(array, row_num, [column_num])
• INDIRECT(refText)
• HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• HYPERLINK(linkUrl, [linkText])
• LOOKUP(lookup_value, lookup_vector, [result_vector])
• MATCH(lookup_value, lookup_array, [match_type])
• ROWS(array)
• SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
• UNIQUE(array,[by_col],[exactly_once])
• VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
• XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Information

• ISEVEN(number)
• ISODD(number)
• ISBLANK(value)
• ISERR(value)
• ISERROR(value)
• ISLOGICAL(value)
• ISNA(value)
• ISNONTEXT(value)
• ISTEXT(value)
• ISTEXT(value)
• N(value)
• NA()
• TYPE(value)