Last active
December 30, 2022 12:31
-
-
Save gamerxl/cf5184a437b10d0f86197b102cf1255a to your computer and use it in GitHub Desktop.
PEG.js based parser for excel formula. It's only a simple prototype for evaluation. Test online at https://pegjs.org/online
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// PEG.js based parser for excel formula. | |
// Executable on https://pegjs.org/online | |
{ | |
// Implementation of builtin core functionality and some demo stuff. | |
// TODO: Remove demo stuff. | |
const builtinFunctions = { | |
/** | |
* Returns a number (Demo function). | |
* TODO: Remove demo function. | |
* @returns {number} | |
*/ | |
"rnd": () => 5, | |
/** | |
* Returns boolean true. | |
* @returns {boolean} | |
*/ | |
"true": () => true, | |
/** | |
* Returns boolean false. | |
* @returns {boolean} | |
*/ | |
"false": () => false, | |
/** | |
* Returns today's date. | |
* @returns {Date} | |
*/ | |
"today": () => new Date(), | |
/** | |
* Returns year of a date. | |
* @param {Date} date | |
* @returns {number} | |
*/ | |
"year": (date) => date.getFullYear(), | |
/** | |
* Returns month day of a date. | |
* @param {Date} date | |
* @returns {number} | |
*/ | |
"day": (date) => date.getDate(), | |
/** | |
* Returns date based on passed year month and day. | |
* @param {number} year | |
* @param {number} month | |
* @param {number} day | |
* @returns {Date} | |
*/ | |
"date": (year, month, day) => new Date(year, month - 1, day), | |
}; | |
// Implementation of comparison operations. | |
const comparisonFunctions = { | |
/** | |
* Compares if left and right value are equal. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
"=": (left, right) => left === right, | |
/** | |
* Compares if left and right value are not equal. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
"<>": (left, right) => left !== right, | |
/** | |
* Compares if left value is greater or equal with right value. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
"<=": (left, right) => left <= right, | |
/** | |
* Compares if left value is lower or equal with right value. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
">=": (left, right) => left >= right, | |
/** | |
* Compares if left value is lower than right value. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
"<": (left, right) => left < right, | |
/** | |
* Compares if left value is greater than right value. | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
">": (left, right) => left > right, | |
}; | |
/** | |
* Resolves and calls builtin and by options provided functions. | |
* @param {string} functionName | |
* @param {Array} argumentList | |
* @returns {*} | |
*/ | |
const callFunction = (functionName, argumentList) => { | |
let result = null; | |
if (builtinFunctions[functionName]) { | |
result = builtinFunctions[functionName].apply({}, argumentList); | |
} else if (options.functions && options.functions[functionName]) { | |
result = options.functions[functionName].apply({}, argumentList); | |
} | |
return result; | |
}; | |
/** | |
* Resolves and calls comparison function based on comparison operator. | |
* @param {string} comparisonOperator | |
* @param {*} left | |
* @param {*} right | |
* @returns {boolean} | |
*/ | |
const callComparisonFunction = (comparisonOperator, left, right) => { | |
let result = false; | |
if (comparisonFunctions[comparisonOperator]) { | |
result = comparisonFunctions[comparisonOperator].call({}, left, right); | |
} | |
return result; | |
}; | |
/** | |
* Resolves and returns variable value. | |
* TODO: Return value is just for demo purposes and has to be replaced later. | |
* @param {string} variableName | |
* @returns {*} | |
*/ | |
const callVariable = (variableName) => { | |
console.log(variableName); | |
return 5; | |
}; | |
/** | |
* Calls console log function. | |
* TODO: Console log call has to be deactivated or replaced in production mode later. | |
*/ | |
const debug = () => { | |
//console.log.apply({}, arguments); | |
}; | |
/** | |
* Converts any value to string as represented by excel. | |
* @param {*} value | |
* @returns {string} | |
*/ | |
const toExcelString = (value) => { | |
if (value === true) { | |
value = "1"; | |
} else if (value === false) { | |
value = "0"; | |
} | |
return value; | |
}; | |
/** | |
* Returns a normalized identifier. E.g. an identifier could be a function name or a variable. | |
* @param {string|array} identifier | |
* @returns {string} | |
*/ | |
const normalizeIdentifier = (identifier) => { | |
return identifier instanceof Array | |
? identifier.join("") | |
: identifier.toLowerCase(); | |
}; | |
} | |
ResultExpression | |
= "="? _ expression:Expression _ { | |
return expression; | |
} | |
Expression | |
= ComparativeExpressions | |
Literals | |
= Boolean | |
/ Float | |
/ Integer | |
/ String | |
GroupExpression | |
= "(" _ expression:Expression _ ")" { | |
return expression; | |
} | |
PrimaryExpressions | |
= ConditionalExpression | |
/ Literals | |
/ CallFunction | |
/ CallVariable | |
/ GroupExpression | |
PowerOperators | |
= "^" | |
MultiplicativeOperators | |
= "*" | |
/ "/" | |
AdditiveOperators | |
= "+" | |
/ "-" | |
ConcatenativeOperators | |
= "&" | |
ComparativeOperators | |
= "=" | |
/ "<>" | |
/ "<=" | |
/ ">=" | |
/ "<" | |
/ ">" | |
PowerExpression | |
= head:PrimaryExpressions tail:(_ PowerOperators _ PrimaryExpressions)* { | |
const result = tail.reduce((current, element) => { | |
if (element[1] === "^") { return parseFloat((current ** element[3]).toFixed(15)); } | |
}, head); | |
debug("PowerExpressions", head, tail, result); | |
return result; | |
} | |
MultiplicativeExpressions | |
= head:PowerExpression tail:(_ MultiplicativeOperators _ PowerExpression)* { | |
const result = tail.reduce((current, element) => { | |
if (element[1] === "*") { return parseFloat((current * element[3]).toFixed(15)); } | |
if (element[1] === "/") { return parseFloat((current / element[3]).toFixed(15)); } | |
}, head); | |
debug("MultiplicativeExpressions", head, tail, result); | |
return result; | |
} | |
AdditiveExpressions | |
= head:MultiplicativeExpressions tail:(_ AdditiveOperators _ MultiplicativeExpressions)* { | |
const result = tail.reduce((current, element) => { | |
if (element[1] === "+") { return parseFloat((current + element[3]).toFixed(15)); } | |
if (element[1] === "-") { return parseFloat((current - element[3]).toFixed(15)); } | |
}, head); | |
debug("AdditiveExpressions", head, tail, result); | |
return result; | |
} | |
ConcatenativeExpressions | |
= head:AdditiveExpressions tail:(_ ConcatenativeOperators _ AdditiveExpressions)* { | |
const result = tail.reduce((current, element) => { | |
return toExcelString(current) + "" + toExcelString(element[3]); | |
}, head); | |
debug("ConcatenateExpressions", head, tail, result); | |
return result; | |
} | |
ComparativeExpressions | |
= head:ConcatenativeExpressions tail:(_ ComparativeOperators _ ConcatenativeExpressions)* { | |
const result = tail.reduce((current, element) => { | |
return callComparisonFunction(element[1], current, element[3]); | |
}, head); | |
debug("ComparisonExpression", head, tail, result); | |
return result; | |
} | |
_ "whitespace" | |
= [ \t\n\r]* | |
Boolean | |
= boolean:("true"i / "false"i) _ "()"? { | |
return !!boolean; | |
} | |
Float | |
= [+-]? [0-9]+ "." [0-9]+ ("e"i [+-] [0-9]+)? { | |
return parseFloat(text(), 10); | |
} | |
Integer | |
= [+-]? [0-9]+ ("e"i [+-] [0-9]+)? { | |
return parseInt(text(), 10); | |
} | |
String | |
= _ '\"' str:([^'"\n]+) '\"' { | |
return str.join(""); | |
} | |
Arguments | |
= "(" argumentList:ArgumentList? ")" { | |
return argumentList; | |
} | |
ArgumentList | |
= head:Expression argumentList:(_ "," _ Expression)* { | |
const result = [head]; | |
for (const argument of argumentList) { | |
result.push(argument[3]); | |
} | |
return result; | |
} | |
ConditionalExpression | |
= _ "if"i _ argumentList:Arguments { | |
let result = null; | |
if (!!argumentList[0]) { | |
result = argumentList[1]; | |
} else { | |
result = argumentList[2]; | |
} | |
debug("ConditionalExpression", result); | |
return result; | |
} | |
CallFunction | |
= _ functionName:[a-zA-Z0-9]+ _ argumentList:Arguments { | |
functionName = normalizeIdentifier(functionName); | |
const result = callFunction(functionName, argumentList); | |
debug("FunctionExpression", functionName, result); | |
return result; | |
} | |
CallVariable | |
= _ variableName:[a-zA-Z][a-zA-Z0-9!$]+ { | |
variableName = normalizeIdentifier(variableName); | |
const result = callVariable(variableName); | |
debug("CallVariable", variableName, result); | |
return result; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment