From e7ac90d4b4cae0ef71908bd6d0a22cf2b39d55c8 Mon Sep 17 00:00:00 2001
From: licp <lichunping@guanfang.com.cn>
Date: 星期五, 28 六月 2024 15:59:27 +0800
Subject: [PATCH] 添加公式
---
src/util/excelFountion.js | 210 ++++++++++++++++++++++++++++++++++++++--------------
1 files changed, 152 insertions(+), 58 deletions(-)
diff --git a/src/util/excelFountion.js b/src/util/excelFountion.js
index d879f8f..626ef6f 100644
--- a/src/util/excelFountion.js
+++ b/src/util/excelFountion.js
@@ -1,7 +1,12 @@
-
+/**
+ * 璁＄畻澶氫釜鏁板�肩殑鍜�
+ *
+ * @param val 鏁板�煎瀷鍙傛暟锛屽彲鍙橀暱鍙傛暟鍒楄〃
+ * @returns 杩斿洖鎵�鏈夊弬鏁扮殑鍜岋紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栧弬鏁扮被鍨嬮潪鏁板�煎瀷锛屽垯杩斿洖null
+ */
function SUM(...val){
- let num = 0;
+ let num = null;
if(val&&val.length>0){
val.forEach(item=>{
num+=item;
@@ -9,39 +14,99 @@
}
return num;
}
+/**
+ * 璁＄畻浼犲叆鍙傛暟涓殑鏈�澶у��
+ *
+ * @param ...val 鍙彉鍙傛暟鍒楄〃锛岀敤浜庤绠楁渶澶у�肩殑鏁板�兼垨鍙浆鎹负鏁板�肩殑绫诲瀷
+ * @returns 杩斿洖鏈�澶у�硷紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栦负null/undefined/''锛屽垯杩斿洖null
+ */
function MAX(...val){
- let max = 0;
+ let max = null;
if(val&&val.length>0){
val = val.filter(item=>item!=null&&item!=='')
- max = Math.max(...val)
+ if(val.length>0){
+ max = Math.max(...val)
+ }else{
+ max = null;
+ }
}
return max;
}
+/**
+ * 璁＄畻浼犲叆鍙傛暟涓殑鏈�灏忓��
+ *
+ * @param val 鍙彉鍙傛暟锛岀敤浜庤绠楁渶灏忓�肩殑鏁板�兼暟缁�
+ * @returns 杩斿洖浼犲叆鍙傛暟涓殑鏈�灏忓�硷紝濡傛灉浼犲叆鍙傛暟涓虹┖鎴栨墍鏈夊�煎潎涓簄ull鎴栫┖瀛楃涓诧紝鍒欒繑鍥瀗ull
+ */
function MIN(...val){
- let min = 0;
+ let min = null;
if(val&&val.length>0){
val = val.filter(item=>item!=null&&item!=='')
- min = Math.min(...val)
+ console.log(val)
+ if(val.length>0){
+ min = Math.min(...val)
+ }
}
return min;
}
-
+/**
+ * 璁＄畻缁欏畾鏁板�肩殑骞冲潎鍊�
+ *
+ * @param val 鏁板�煎垪琛紝鍙寘鍚换鎰忎釜鍙傛暟
+ * @returns 杩斿洖骞冲潎鍊硷紝濡傛灉鏁板�煎垪琛ㄤ负绌烘垨鍖呭惈闈炴暟鍊奸」锛屽垯杩斿洖null
+ */
function AVERAGE(...val){
- let num = 0;
+ let num = null;
let arr = [];
if(val&&val.length>0){
arr = val.filter(item=>item!=null&&item!=='')
arr.forEach(item=>{
num+=item;
})
- return num/arr.length;
+ if(arr.length>0){
+ return num/arr.length;
+ }else{
+ return null;
+ }
}else{
- return 0;
+ return null;
}
}
-
+/**
+ * 璁＄畻涓�涓暟鐨勭粷瀵瑰��
+ *
+ * @param val 浠绘剰鏁板��
+ * @returns 杩斿洖璇ユ暟鍊肩殑缁濆鍊�
+ */
function APS(val){
return Math.abs(val);
+}
+/**
+ * 璁＄畻涓�缁勬暟瀛楃殑涓綅鏁�
+ *
+ * @param val 浠绘剰涓弬鏁帮紝闇�瑕佽绠椾腑浣嶆暟鐨勬暟瀛�
+ * @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;
+ }
+ }else{
+ return null;
+ }
+ }else{
+ return null;
+ }
}
@@ -51,19 +116,23 @@
* @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);
+ 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)
+ }
}
/**
* 鏍规嵁鍒楀悕鑾峰彇鍧愭爣
@@ -71,40 +140,49 @@
* @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
+ 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));
}
- }
- }
- return id;
+ 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)
+ }
}
-// 鑾峰彇鍙傛暟鍒楄〃
+/**
+ * 鏇存敼鍙傛暟
+ *
+ * @param f 鍙傛暟鍒楄〃
+ * @returns 杩斿洖涓�涓寘鍚潗鏍囦俊鎭殑鏁扮粍
+ */
function changeParameter(f){
let arr = getABCList(f)
let arr2 = []
@@ -128,7 +206,12 @@
})
return arr2;
}
-
+/**
+ * 鑾峰彇鍖呭惈 ABC 瀛楃鐨勫垪琛�
+ *
+ * @param f 瀛楃涓诧紝鍖呭惈闇�瑕佽В鏋愮殑鍏紡鎴栬〃杈惧紡
+ * @returns 鍖呭惈 ABC 瀛楃鐨勬暟缁勫垪琛�
+ */
function getABCList(f){
let regex = /[=\+\-\*\%\(\)\/\^\s]/g;
let fouList = [
@@ -147,7 +230,12 @@
});
return arr;
}
-
+/**
+ * 鑾峰彇鎵�鏈夊崟鍏冩牸
+ *
+ * @param f 琛ㄦ牸鏁版嵁鎴栫浉鍏冲弬鏁�
+ * @returns 杩斿洖涓�涓璞★紝鍏堕敭涓哄崟鍏冩牸鐨勫敮涓�鏍囪瘑绗︼紙鐢卞垪鍜岃ID鎷兼帴鑰屾垚锛夛紝鍊间负null
+ */
function getAllCell(f){
let arr = changeParameter(f)
let arr0 = {}
@@ -157,7 +245,13 @@
return arr0;
}
-// 璁＄畻鍏紡
+/**
+ * 璁＄畻鍑芥暟
+ *
+ * @param f 瀛楃涓茬被鍨嬶紝琛ㄧず寰呰绠楃殑鍏紡
+ * @param comValue 瀵硅薄绫诲瀷锛岃〃绀鸿鏇挎崲鐨勫崟鍏冩牸鍊硷紝閿负鍗曞厓鏍煎悕绉帮紝鍊间负鏇挎崲鍚庣殑鍊�
+ * @returns 杩斿洖璁＄畻鍚庣殑缁撴灉锛屽鏋滆绠楀け璐ュ垯杩斿洖0
+ */
function compute(f,comValue){
let str = f
// 鑾峰彇鍗曞厓鏍煎搴斿��
--
Gitblit v1.9.3