递归遍历Excel函数树时嵌套参数丢失问题求助
2026-7-3
处理Excel嵌套函数树结构时参数丢失的问题
我有一棵表示Excel函数的树结构，每个节点包含以下属性：
name：函数名称（比如SUM）type：节点类型（function/number/unary-expression）arguments：函数参数（参数本身也可以是函数节点）
目前的问题是，处理简单函数没问题，但嵌套层次较深时参数会丢失。以下是我的现有代码：
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})`; 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} } }
测试用例
示例Excel公式
=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)))
对应的树结构
{ "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 }
预期输出
[ { "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" } ]
解决方案
问题分析
原代码的核心问题：
- 判断参数是否为函数时，仅通过
arg.arguments判断，忽略了unary-expression类型节点 - 构建参数字符串时重复调用递归函数，导致嵌套参数解析不完整
- 缺少函数结果（
res字段）的计算逻辑
修复后的代码
// 定义Excel函数计算逻辑 const excelFunctions = { SUM(args) { return args.reduce((acc, val) => acc + Number(val), 0); }, MAX(args) { return Math.max(...args.map(val => Number(val))); }, AVERAGE(args) { const sum = args.reduce((acc, val) => acc + Number(val), 0); return sum / args.length; }, ABS(args) { return Math.abs(Number(args[0])); } }; function processNode(node) { // 处理数值节点 if (node.type === "number") { return { str: String(node.value), value: node.value }; } // 处理一元表达式节点（如-7） if (node.type === "unary-expression") { const operand = processNode(node.operand); const value = node.operator === "-" ? -operand.value : operand.value; return { str: `${node.operator}${operand.str}`, value: value }; } // 处理函数节点 if (node.type === "function") { // 递归处理所有参数，获取参数的字符串和计算值 const processedArgs = node.arguments.map(arg => processNode(arg)); const argStrings = processedArgs.map(item => item.str); const argValues = processedArgs.map(item => item.value); // 构建当前函数的字符串表示 const funcStr = `${node.name}(${argStrings.join(",")})`; // 计算当前函数的结果 const funcValue = excelFunctions[node.name]?.(argValues) ?? ""; // 收集当前函数信息 const result = [{ name: funcStr, depth: node.depth, res: String(funcValue) }]; // 递归收集所有子函数的信息 node.arguments.forEach(arg => { if (arg.type === "function") { result.push(...processNode(arg).subResults); } }); return { str: funcStr, value: funcValue, subResults: result }; } return { str: "", value: "" }; } function getSubFormulas(rootNode) { const processed = processNode(rootNode); return processed.subResults; }
修复说明
- 统一节点处理：新增
processNode函数，递归处理所有类型节点，返回每个节点的字符串表示和计算值 - 函数计算封装：单独定义
excelFunctions对象，封装常用Excel函数的计算逻辑，便于扩展 - 完整嵌套收集：处理函数节点时，先解析所有参数，再递归收集子函数信息，避免参数丢失
- 支持一元表达式：专门处理
unary-expression类型节点，正确生成带符号的数值字符串并计算值
调用getSubFormulas(rootNode)即可得到符合预期的输出结果。
内容的提问来源于stack exchange，提问作者doxzi
相关产品推荐
相关产品推荐
