|
|
function SUM(...val){
|
let num = null;
|
if(val&&val.length>0){
|
val.forEach(item=>{
|
num+=item;
|
})
|
}
|
return num;
|
}
|
function MAX(...val){
|
let max = null;
|
if(val&&val.length>0){
|
val = val.filter(item=>item!=null&&item!=='')
|
if(val.length>0){
|
max = Math.max(...val)
|
}else{
|
max = null;
|
}
|
}
|
return max;
|
}
|
function MIN(...val){
|
let min = null;
|
if(val&&val.length>0){
|
val = val.filter(item=>item!=null&&item!=='')
|
console.log(val)
|
if(val.length>0){
|
min = Math.min(...val)
|
}
|
}
|
return min;
|
}
|
|
function AVERAGE(...val){
|
let num = null;
|
let arr = [];
|
if(val&&val.length>0){
|
arr = val.filter(item=>item!=null&&item!=='')
|
arr.forEach(item=>{
|
num+=item;
|
})
|
if(arr.length>0){
|
return num/arr.length;
|
}else{
|
return null;
|
}
|
}else{
|
return null;
|
}
|
}
|
|
function APS(val){
|
return Math.abs(val);
|
}
|
|
|
|
/**
|
* 根据坐标获取列名
|
* @param {Object} cellId
|
*/
|
function getColumnNameFromId(cellId){
|
try{
|
if (! Array.isArray(cellId)) {
|
cellId = cellId.split('-');
|
}
|
var i = cellId[0];
|
var letter = '';
|
if (i > 701) {
|
letter += String.fromCharCode(64 + parseInt(i / 676));
|
letter += String.fromCharCode(64 + parseInt((i % 676) / 26));
|
} else if (i > 25) {
|
letter += String.fromCharCode(64 + parseInt(i / 26));
|
}
|
letter += String.fromCharCode(65 + (i % 26));
|
return letter + (parseInt(cellId[1]) + 1);
|
}catch(e){
|
console.log('error',cellId)
|
}
|
}
|
/**
|
* 根据列名获取坐标
|
* @param {Object} id
|
* @param {Object} arr
|
*/
|
function getIdFromColumnName(id, arr) {
|
try{
|
// Get the letters
|
var t = /^[a-zA-Z]+/.exec(id);
|
if (t) {
|
// Base 26 calculation
|
var code = 0;
|
for (var i = 0; i < t[0].length; i++) {
|
code += parseInt(t[0].charCodeAt(i) - 64) * Math.pow(26, (t[0].length - 1 - i));
|
}
|
code--;
|
// Make sure jexcel starts on zero
|
if (code < 0) {
|
code = 0;
|
}
|
|
// Number
|
var number = parseInt(/[0-9]+$/.exec(id));
|
if (number > 0) {
|
number--;
|
}
|
|
if (arr == true) {
|
id = [ code, number ];
|
} else {
|
// id = code + '-' + number;
|
id = {
|
c:code,
|
r:number
|
}
|
}
|
}
|
return id;
|
}catch(e){
|
console.log('error',id)
|
}
|
}
|
|
// 获取参数列表
|
function changeParameter(f){
|
let arr = getABCList(f)
|
let arr2 = []
|
arr.forEach(item=>{
|
if(item.includes(':')){
|
let r0 = getIdFromColumnName(item.split(':')[0]).r;
|
let c0 = getIdFromColumnName(item.split(':')[0]).c;
|
let r1 = getIdFromColumnName(item.split(':')[1]).r;
|
let c1 = getIdFromColumnName(item.split(':')[1]).c;
|
for (let i = Number(r0); i <= Number(r1); i++) {
|
for (let u = Number(c0); u <= Number(c1); u++) {
|
arr2.push({
|
r: i,
|
c: u
|
})
|
}
|
}
|
}else{
|
arr2.push(getIdFromColumnName(item))
|
}
|
})
|
return arr2;
|
}
|
|
function getABCList(f){
|
let regex = /[=\+\-\*\%\(\)\/\^\s]/g;
|
let fouList = [
|
"SUM",
|
'MAX',
|
'MIN',
|
'AVERAGE',
|
'APS',
|
]
|
f = f.replace(regex, ',')
|
fouList.forEach(item=>{
|
f = f.replaceAll(item,',')
|
})
|
let arr = f.split(',').filter(item=>{
|
return item&& /[a-zA-Z]/.test(item)&&item!='undefined'&&item!='null'
|
});
|
return arr;
|
}
|
|
function getAllCell(f){
|
let arr = changeParameter(f)
|
let arr0 = {}
|
arr.forEach(item=>{
|
arr0[getColumnNameFromId(`${item.c}`+'-'+`${item.r}`)] = null
|
})
|
return arr0;
|
}
|
|
// 计算公式
|
function compute(f,comValue){
|
let str = f
|
// 获取单元格对应值
|
let arr = getAllCell(f)
|
for (var a in comValue) {
|
if(comValue[a]!=='undefine'&&comValue[a]!=='null'&&comValue[a]!==undefined){
|
arr[a] = comValue[a]
|
}
|
}
|
// 解析公式参数，特别是带：的
|
let arr0 = getABCList(f)
|
let obj = {}
|
arr0.forEach(item=>{
|
if(item.includes(':')){
|
let arr1 = []
|
let r0 = getIdFromColumnName(item.split(':')[0]).r;
|
let c0 = getIdFromColumnName(item.split(':')[0]).c;
|
let r1 = getIdFromColumnName(item.split(':')[1]).r;
|
let c1 = getIdFromColumnName(item.split(':')[1]).c;
|
for (let i = Number(r0); i <= Number(r1); i++) {
|
for (let u = Number(c0); u <= Number(c1); u++) {
|
arr1.push({
|
r: i,
|
c: u
|
})
|
}
|
}
|
let arr2 = []
|
arr1.forEach(m=>{
|
arr2.push(getColumnNameFromId(`${m.c}`+'-'+`${m.r}`))
|
})
|
obj[item.split(':').join('-')] = arr2.join(',')
|
}else{
|
obj[item] = item
|
}
|
})
|
str = str.replaceAll(':', '-')
|
// 替换参数
|
for (var a in obj) {
|
str = str.replaceAll(a, obj[a])
|
}
|
// 计算
|
try{
|
for (var a in arr) {
|
str = str.replaceAll(a, arr[a])
|
}
|
return eval(str)
|
}catch(e){
|
console.log('e',e)
|
return 0
|
}
|
}
|
|
export default {
|
compute,
|
changeParameter
|
}
|
