From 771699a2a85aecde95a4393ef6016b28f73a551e Mon Sep 17 00:00:00 2001
From: licp <lichunping@guanfang.com.cn>
Date: 星期五, 28 六月 2024 14:11:46 +0800
Subject: [PATCH] 检验任务bug修复
---
src/util/excelFountion.js | 312 ++++++++++++++++++++++++++++++++++-----------------
1 files changed, 206 insertions(+), 106 deletions(-)
diff --git a/src/util/excelFountion.js b/src/util/excelFountion.js
index b8c51a1..de10531 100644
--- a/src/util/excelFountion.js
+++ b/src/util/excelFountion.js
@@ -1,24 +1,134 @@
-let f = 'SUM(MAX(1,2,3)*2-MIN(1,2,3),1,2)'
+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 regex = /[=\+\-\*\%\(\)\/\^\s]/g;
- let fouList = [
- "SUM",
- 'MAX',
- 'MIN'
- ]
- f = f.replace(regex, ',')
- fouList.forEach(item=>{
- f = f.replaceAll(item,',')
- })
- let arr = f.split(',').filter(item=>{
- return item&& /[a-zA-Z]/.test(item)
- });
+ 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;
@@ -37,100 +147,90 @@
})
return arr2;
}
-function SUM(...val){
- let num = 0;
- if(val&&val.length>0){
- val.forEach(item=>{
- num+=item;
- })
+
+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]
+ }
}
- console.log('SUM',num)
- return num;
-}
-function MAX(...val){
- let max = 0;
- if(val&&val.length>0){
- max = Math.max(...val)
- }
- console.log('MAX',max)
- return max;
-}
-function MIN(...val){
- let min = 0;
- if(val&&val.length>0){
- min = Math.min(...val)
- }
- console.log('MIN',min)
- return min;
-}
-function handel(f){
- console.log(changeParameter(f))
- // console.log(eval(f))
-}
-
-
-/**
- * 鏍规嵁鍧愭爣鑾峰彇鍒楀悕
- * @param {Object} cellId
- */
-function getColumnNameFromId(cellId){
- 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);
-}
-console.log("getColumnNameFromId",getColumnNameFromId([1,1]))//B2
-/**
- * 鏍规嵁鍒楀悕鑾峰彇鍧愭爣
- * @param {Object} id
- * @param {Object} arr
- */
-function getIdFromColumnName(id, arr) {
- // 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
+ // 瑙ｆ瀽鍏紡鍙傛暟锛岀壒鍒槸甯︼細鐨�
+ 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
+ })
+ }
}
- }
- }
- return id;
+ 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
+ }
}
-
-console.log("getIdFromColumnName",getIdFromColumnName("B2",true))//[1,1]
-
export default {
- handel
+ compute,
+ changeParameter
}
--
Gitblit v1.9.3