I have a tree that represents an excel function, where node contains name : function_name (for example SUM);type : function_type (function or number); arguments : function_ arguments. The whole problem for me is that arguments can be another function. I already have an algorithm that works on simple functions, but with a lot of nesting, arguments are lost. I would like to know how to solve this. I will attach my code below.
function getSubFormulas(node) {
if (node.type === "function") {
var args = node.arguments.map(arg => {
console.log(arg)
if (arg.arguments) {
// Если аргумент - это массив, извлекаем свойство name из каждого элемента
console.log(arg.arguments.map(subArg => getSubFormulas(subArg).name).join(","))
return arg.name +"(" +arg.arguments.map(subArg => getSubFormulas(subArg).name).join(",") + ")";
} else {
// Если аргумент не является массивом, извлекаем свойство name
return getSubFormulas(arg).name;
}
}).join(",");
console.log(args + " args ")
var name = `${node.name}(${args})`;
// console.log(name);
const formula = {
name: name,
depth: node.depth
};
return [formula, ...node.arguments.filter((elem) => elem.type == "function").map(getSubFormulas).flat()];
} else {
if (node.operand == null) {
let temp_name = node.value;
return {name: temp_name}
}
let temp_name = 0 - node.operand.value;
return {name : temp_name}
}
}
Formula of the form
=SUM(MAX(3,5),AVERAGE (1,2,3,4,SUM(MAX(1,3),AVERAGE (SUM(2,3,4,4),MAX(1,2,1,2,1,2)))),ABS(SUM(-7,4,1)))
looks like
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 3,
"depth": 2
},
{
"type": "number",
"value": 5,
"depth": 2
}
],
"depth": 1
},
{
"type": "function",
"name": "AVERAGE",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 2
},
{
"type": "number",
"value": 2,
"depth": 2
},
{
"type": "number",
"value": 3,
"depth": 2
},
{
"type": "number",
"value": 4,
"depth": 2
},
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 4
},
{
"type": "number",
"value": 3,
"depth": 4
}
],
"depth": 3
},
{
"type": "function",
"name": "AVERAGE",
"arguments": [
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 3,
"depth": 5
},
{
"type": "number",
"value": 4,
"depth": 5
},
{
"type": "number",
"value": 4,
"depth": 5
}
],
"depth": 4
},
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
}
],
"depth": 4
}
],
"depth": 3
}
],
"depth": 2
}
],
"depth": 1
},
{
"type": "function",
"name": "ABS",
"arguments": [
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "unary-expression",
"operator": "-",
"operand": {
"type": "number",
"value": 7
},
"depth": 3
},
{
"type": "number",
"value": 4,
"depth": 3
},
{
"type": "number",
"value": 1,
"depth": 3
}
],
"depth": 2
}
],
"depth": 1
}
],
"depth": 0
}
And expected output
[
{
"name": "SUM(MAX(3,5),AVERAGE(1,2,3,4,SUM(MAX(1,3),AVERAGE(SUM(2,3,4,4),MAX(1,2,1,2,1,2)))),ABS(SUM(-7,4,1)))",
"depth": 0,
"res": "11,1"
},
{
"name": "MAX(3,5)",
"depth": 1,
"res": "5"
},
{
"name": "AVERAGE(1,2,3,4,SUM(,))",
"depth": 1,
"res": "2"
},
{
"name": "SUM(MAX(1,3),AVERAGE(,))",
"depth": 2,
"res": "3"
},
{
"name": "MAX(1,3)",
"depth": 3,
"res": "3"
},
{
"name": "AVERAGE(SUM(2,3,4,4),MAX(1,2,1,2,1,2))",
"depth": 3,
"res": "7,5"
},
{
"name": "SUM(2,3,4,4)",
"depth": 4,
"res": "13"
},
{
"name": "MAX(1,2,1,2,1,2)",
"depth": 4,
"res": "2"
},
{
"name": "ABS(SUM(-7,4,1))",
"depth": 1,
"res": "2"
},
{
"name": "SUM(-7,4,1)",
"depth": 2,
"res": "-2"
}
]
Here I used two functions, getFormula recursively gets the formula for a node, and walkTree recursively builds the output objects into a flat array.
const getFormula = (node) => {
if (node.type === "function") {
if (node.arguments) {
return node.name + "(" + node.arguments.map(getFormula).join(",") + ")";
}
return node.name + "()";
} else if (node.type === "unary-expression") {
if (node.operator === "-") {
return -node.operand.value;
}
}
return node.value;
};
const walkTree = (node, output=[], depth=0) => {
if (node.type === "function") {
output.push({
name: getFormula(node),
depth
});
if (node.arguments) {
node.arguments.forEach(arg => walkTree(arg, output, depth + 1));
}
}
return output;
};
let testNode = {
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 3,
"depth": 2
},
{
"type": "number",
"value": 5,
"depth": 2
}
],
"depth": 1
},
{
"type": "function",
"name": "AVERAGE",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 2
},
{
"type": "number",
"value": 2,
"depth": 2
},
{
"type": "number",
"value": 3,
"depth": 2
},
{
"type": "number",
"value": 4,
"depth": 2
},
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 4
},
{
"type": "number",
"value": 3,
"depth": 4
}
],
"depth": 3
},
{
"type": "function",
"name": "AVERAGE",
"arguments": [
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 3,
"depth": 5
},
{
"type": "number",
"value": 4,
"depth": 5
},
{
"type": "number",
"value": 4,
"depth": 5
}
],
"depth": 4
},
{
"type": "function",
"name": "MAX",
"arguments": [
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
},
{
"type": "number",
"value": 1,
"depth": 5
},
{
"type": "number",
"value": 2,
"depth": 5
}
],
"depth": 4
}
],
"depth": 3
}
],
"depth": 2
}
],
"depth": 1
},
{
"type": "function",
"name": "ABS",
"arguments": [
{
"type": "function",
"name": "SUM",
"arguments": [
{
"type": "unary-expression",
"operator": "-",
"operand": {
"type": "number",
"value": 7
},
"depth": 3
},
{
"type": "number",
"value": 4,
"depth": 3
},
{
"type": "number",
"value": 1,
"depth": 3
}
],
"depth": 2
}
],
"depth": 1
}
],
"depth": 0
};
let result = walkTree(testNode);
console.log(result);