前些天公司要求做一个表格 表格要支持 拖拽 ,打印 ,导出 ,可编辑,嵌套公式计算等功能。其中难点是公式的计算有些不好处理。
公式的要求是 d行 = a行的数据 + b行的数据 - c行的数据 类似进行这样的四则运算处理。但是有个问题是
c行的值可能也是公式计算得来的。所以要判断好对应的执行顺序。需要求出c行的值 然后再算d行的值。
如下图所示:
这里我贴一个例子,写写我实现的思路 ,完整代码如下:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>公式的demo</title>
</head>
<body>
<h1></h1>
<script>
//测试数据
var arr = [ {expression:'2+11+14+16+15' ,value:'',rowid:1},
{ rowid:2, expression:'' ,value:41661.16},
{ rowid:3, expression:'' ,value:43805.81},
{ rowid:4, expression:'' ,value:1110.54},
{ rowid:5, expression:'' ,value:1351.59},
{ rowid:6, expression:'' ,value:41343.69},
{ rowid:7, expression:'' ,value:36312.15},
{ rowid:8, expression:'' ,value:5031.54},
{ rowid:9, expression:'' ,value:93.69},
{ rowid:10, expression:'' ,value:223.78},
{ rowid:11, expression:'12-13' ,value:''},
{ rowid:12, expression:'' ,value:14901.16},
{ rowid:13, expression:'' ,value:3466.02},
{ rowid:14, expression:'' ,value:10000},
{ rowid:15, expression:'' ,value:43.7},
{ rowid:16, expression:'' ,value:34.18},
{ rowid:17, expression:'18+19+20+21' ,value:''},
{ rowid:18, expression:'' ,value:455.66},
{ rowid:19, expression:'' ,value:9780.35},
{ rowid:20, expression:'1*0.005' ,value:''},
{ rowid:21, expression:'' ,value:78.45},
{ rowid:22, expression:'1-17' ,value:''},
{ rowid:23, expression:'' ,value:7.36},
{ rowid:24, expression:'' ,value:27.86},
{ rowid:25, expression:'' ,value:26769.52},
{ rowid:26, expression:'' ,value:17.06},
{ rowid:27, expression:'22+23-24+25-26' ,value:''},
{ rowid:28, expression:'' ,value:35343.48},
{ rowid:29, expression:'27-28' ,value:''},
{ rowid:30, expression:'' ,value:0},
]
//执行顺序的排序
const sortList = (arr) =>{
let symboinfo = ['+','-','*','/','(',')']
arr.forEach(ele => { //找出公式
ele.sortindex =0
if (ele.expression ) {
let str = ''
ele.pressionArr = []
ele.sortindex = 1
for (const i of ele.expression) {
if (symboinfo.includes(i)) {
if (str) { ele.pressionArr.push(str) }
ele.pressionArr.push(i)
str = ''
}else{
str += i
}
}
if (str) { ele.pressionArr.push(str) }
}
});
// arr = setIndex(info,arr)
arr.forEach(ele =>{
if (ele.sortindex) {
ele.sortindex = setIndex(ele,arr ,ele.sortindex)
}
})
arr =arr.sort((a,b)=>{
return a.sortindex- b.sortindex
})
return arr
}
//设置最大排序值
const setIndex = (info,arr,sortindex) =>{
let symboinfo = ['+','-','*','/','(',')']
for (let i = 0; i < info.pressionArr.length; i++) {
let pers = info.pressionArr[i]
// 我们公司公式的>100后是直接取值 这里的<100是指不超过100行
// 如果不需要请删掉 (pers<100 || pers > 1 ),你也可以加些自己的判断
if (!symboinfo.includes(pers) && (pers<100 || pers > 1 ) ){
if (arr[pers-1] && arr[pers-1].expression ) {
sortindex++
sortindex = setIndex( arr[pers-1],arr ,sortindex )
}
}
}
return sortindex
}
//数据处理
const getnewList = (arr)=>{
const symboinfo = ['+','-','*','/','(',')']
arr.forEach( ele=>{
if (ele.expression) {
let evalStr = ''
for (const i of ele.pressionArr ) {
// 我们公司公式的>100后是直接取值 这里的<100是指不超过100行
// 如果不需要请删掉 ( i< 1 || i>100 ),你也可以加些自己的判断
if (symboinfo.includes(i) || i< 1 || i>100 ) {
evalStr+=i
}else{
let indexInfo = arr.find( res => res.rowid == i )
evalStr += `(${ indexInfo.value})`
}
}
ele.value = evalPro(evalStr)
}
})
return arr.sort((a,b)=> a.rowid-b.rowid)
}
const evalPro = str =>{
let Fn = Function
return new Fn('return ' + str )()
}
sortList(arr)
getnewList(arr)
console.log(arr);
</script>
</body>
</html>
**总结:我实现的步骤是 **
- 先处理公式 并设置排序值
- 对公式的排序值进行处理
- 计算对应的行的公式结果值
- 重新排序成以前的顺序