From 41c2f79abd464e6c063166d680b51f746d6f0456 Mon Sep 17 00:00:00 2001
From: licp <lichunping@guanfang.com.cn>
Date: 星期六, 10 八月 2024 16:25:13 +0800
Subject: [PATCH] 修改特殊值填值

---
 src/util/excelFountion.js |  328 ++++++++++++++++++++++++++++++++----------------------
 1 files changed, 196 insertions(+), 132 deletions(-)

diff --git a/src/util/excelFountion.js b/src/util/excelFountion.js
index 626ef6f..05a3e7c 100644
--- a/src/util/excelFountion.js
+++ b/src/util/excelFountion.js
@@ -6,13 +6,17 @@
  * @returns 杩斿洖鎵�鏈夊弬鏁扮殑鍜岋紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栧弬鏁扮被鍨嬮潪鏁板�煎瀷锛屽垯杩斿洖null
  */
 function SUM(...val){
-  let num = null;
-  if(val&&val.length>0){
-    val.forEach(item=>{
-      num+=item;
-    })
+  try {
+    let num = null;
+    if(val&&val.length>0){
+      val.forEach(item=>{
+        num+=item;
+      })
+    }
+    return num;
+  } catch (error) {
+
   }
-  return num;
 }
 /**
  * 璁＄畻浼犲叆鍙傛暟涓殑鏈�澶у��
@@ -21,16 +25,20 @@
  * @returns 杩斿洖鏈�澶у�硷紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栦负null/undefined/''锛屽垯杩斿洖null
  */
 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;
+  try {
+    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;
+  } catch (error) {
+
   }
-  return max;
 }
 /**
  * 璁＄畻浼犲叆鍙傛暟涓殑鏈�灏忓��
@@ -39,15 +47,19 @@
  * @returns 杩斿洖浼犲叆鍙傛暟涓殑鏈�灏忓�硷紝濡傛灉浼犲叆鍙傛暟涓虹┖鎴栨墍鏈夊�煎潎涓簄ull鎴栫┖瀛楃涓诧紝鍒欒繑鍥瀗ull
  */
 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)
+  try {
+    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;
+  } catch (error) {
+
   }
-  return min;
 }
 /**
  * 璁＄畻缁欏畾鏁板�肩殑骞冲潎鍊�
@@ -56,20 +68,24 @@
  * @returns 杩斿洖骞冲潎鍊硷紝濡傛灉鏁板�煎垪琛ㄤ负绌烘垨鍖呭惈闈炴暟鍊奸」锛屽垯杩斿洖null
  */
 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;
+  try {
+    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;
     }
-  }else{
-    return null;
+  } catch (error) {
+
   }
 }
 /**
@@ -78,8 +94,12 @@
  * @param val 浠绘剰鏁板��
  * @returns 杩斿洖璇ユ暟鍊肩殑缁濆鍊�
  */
-function APS(val){
-  return Math.abs(val);
+function ABS(val){
+  try {
+    return Math.abs(val);
+  } catch (error) {
+
+  }
 }
 /**
  * 璁＄畻涓�缁勬暟瀛楃殑涓綅鏁�
@@ -88,24 +108,49 @@
  * @returns 濡傛灉鍙傛暟涓湁鏈夋晥鐨勬暟瀛楋紝鍒欒繑鍥炶绠楀嚭鐨勪腑浣嶆暟锛涘惁鍒欒繑鍥瀗ull
  */
 function MEDIAN(...val){
-  let arr = [];
-  if(val&&val.length>0){
-    arr = val.filter(item=>item!=null&&item!=='')
-    const sortedArr = arr.sort((a, b) => a - b);
-    // 璁＄畻涓綅鏁�
-    const half = Math.floor(sortedArr.length / 2);
-    if(arr.length>0){
-      // 濡傛灉鏁扮粍闀垮害鏄鏁帮紝鐩存帴鍙栦腑闂寸殑鍏冪礌
-      if (sortedArr.length % 2 === 1) {
-        return sortedArr[half];
-      } else { // 濡傛灉鏁扮粍闀垮害鏄伓鏁帮紝鍙栦腑闂翠袱涓厓绱犵殑骞冲潎鍊�
-        return (sortedArr[half - 1] + sortedArr[half]) / 2;
+  try {
+    let arr = [];
+    if(val&&val.length>0){
+      arr = val.filter(item=>item!=null&&item!=='')
+      const sortedArr = arr.sort((a, b) => a - b);
+      // 璁＄畻涓綅鏁�
+      const half = Math.floor(sortedArr.length / 2);
+      if(arr.length>0){
+        // 濡傛灉鏁扮粍闀垮害鏄鏁帮紝鐩存帴鍙栦腑闂寸殑鍏冪礌
+        if (sortedArr.length % 2 === 1) {
+          return sortedArr[half];
+        } else { // 濡傛灉鏁扮粍闀垮害鏄伓鏁帮紝鍙栦腑闂翠袱涓厓绱犵殑骞冲潎鍊�
+          return (sortedArr[half - 1] + sortedArr[half]) / 2;
+        }
+      }else{
+        return null;
       }
     }else{
       return null;
     }
-  }else{
-    return null;
+  } catch (error) {
+
+  }
+}
+
+/**
+ * 璁＄畻骞�
+ *
+ * @param str 瀛楃涓插舰寮忕殑骞傝〃杈惧紡锛屼緥濡�"2^3"
+ * @returns 杩斿洖璁＄畻鍚庣殑骞傚�硷紝濡傛灉琛ㄨ揪寮忔棤鏁堝垯杩斿洖null
+ */
+function CalculatePower(str){
+  try {
+    if(str&&str.includes('^')){
+      let arr = str.split('^');
+      if(arr&&arr.length>1){
+        return Math.pow(arr[0],arr[1]);
+      }else{
+        return null;
+      }
+    }
+  } catch (error) {
+
   }
 }
 
@@ -184,27 +229,31 @@
  * @returns 杩斿洖涓�涓寘鍚潗鏍囦俊鎭殑鏁扮粍
  */
 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
-          })
+  try {
+    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))
       }
-    }else{
-      arr2.push(getIdFromColumnName(item))
-    }
-  })
-  return arr2;
+    })
+    return arr2;
+  } catch (error) {
+
+  }
 }
 /**
  * 鑾峰彇鍖呭惈 ABC 瀛楃鐨勫垪琛�
@@ -213,22 +262,27 @@
  * @returns 鍖呭惈 ABC 瀛楃鐨勬暟缁勫垪琛�
  */
 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;
+  try {
+    let regex = /[=\+\-\*\%\(\)\/\^\s]/g;
+    let fouList = [
+      "SUM",
+      'MAX',
+      'MIN',
+      'AVERAGE',
+      'ABS',
+    ]
+    f = f.replace(regex, ',').replace(new RegExp('"&', 'g'),'').replace(new RegExp('&"', 'g'),'')
+    fouList.forEach(item=>{
+      f = f.replace(new RegExp(item, 'g'),'')
+    })
+    // console.log('f',f)
+    let arr = f.split(',').filter(item=>{
+      return item&& /[a-zA-Z]/.test(item)&&item!='undefined'&&item!='null'
+    });
+    return arr;
+  } catch (error) {
+
+  }
 }
 /**
  * 鑾峰彇鎵�鏈夊崟鍏冩牸
@@ -237,12 +291,16 @@
  * @returns 杩斿洖涓�涓璞★紝鍏堕敭涓哄崟鍏冩牸鐨勫敮涓�鏍囪瘑绗︼紙鐢卞垪鍜岃ID鎷兼帴鑰屾垚锛夛紝鍊间负null
  */
 function getAllCell(f){
-  let arr = changeParameter(f)
-  let arr0 = {}
-  arr.forEach(item=>{
-    arr0[getColumnNameFromId(`${item.c}`+'-'+`${item.r}`)] = null
-  })
-  return arr0;
+  try {
+    let arr = changeParameter(f)
+    let arr0 = {}
+    arr.forEach(item=>{
+      arr0[getColumnNameFromId(`${item.c}`+'-'+`${item.r}`)] = null
+    })
+    return arr0;
+  } catch (error) {
+
+  }
 }
 
 /**
@@ -253,55 +311,61 @@
  * @returns 杩斿洖璁＄畻鍚庣殑缁撴灉锛屽鏋滆绠楀け璐ュ垯杩斿洖0
  */
 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
-          })
+  try {
+    let str = f
+    // 鑾峰彇鍗曞厓鏍煎搴斿��
+    let arr = getAllCell(f)
+    for (var a in comValue) {
+      if(comValue[a]!=='undefined'&&comValue[a]!=='null'&&comValue[a]!==undefined){
+        if(typeof(comValue[a])=='string'&&comValue[a].includes('^')){
+          // 璁＄畻骞傛
+          arr[a] = CalculatePower(comValue[a])
+        } else if (typeof(comValue[a])=='string'&&comValue[a].includes('/')) {
+          arr[a] = comValue[a]
+        } else{
+          arr[a] = comValue[a]
         }
       }
-      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{
+    // 瑙ｆ瀽鍏紡鍙傛暟锛岀壒鍒槸甯︼細鐨�
+    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.replace(new RegExp(':', 'g'),'')
+    // 鏇挎崲鍙傛暟
+    for (var a in obj) {
+      str = str.replace(new RegExp(a, 'g'),obj[a])
+    }
+    // 璁＄畻
     for (var a in arr) {
-      str = str.replaceAll(a, arr[a])
+      str = str.replace(new RegExp(a, 'g'),arr[a])
     }
     return eval(str)
-  }catch(e){
-    console.log('e',e)
-    return 0
+  } catch (error) {
+
   }
 }
 

--
Gitblit v1.9.3