From 996e3e343760bcfa23b55138f85abd62d6ebfb22 Mon Sep 17 00:00:00 2001
From: licp <lichunping@guanfang.com.cn>
Date: 星期三, 25 十二月 2024 09:02:07 +0800
Subject: [PATCH] 报告预览调试
---
src/util/excelFountion.js | 512 +++++++++++++++++++++++++++++++++++++-------------------
1 files changed, 335 insertions(+), 177 deletions(-)
diff --git a/src/util/excelFountion.js b/src/util/excelFountion.js
index d879f8f..05a3e7c 100644
--- a/src/util/excelFountion.js
+++ b/src/util/excelFountion.js
@@ -1,47 +1,157 @@
-
+/**
+ * 璁＄畻澶氫釜鏁板�肩殑鍜�
+ *
+ * @param val 鏁板�煎瀷鍙傛暟锛屽彲鍙橀暱鍙傛暟鍒楄〃
+ * @returns 杩斿洖鎵�鏈夊弬鏁扮殑鍜岋紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栧弬鏁扮被鍨嬮潪鏁板�煎瀷锛屽垯杩斿洖null
+ */
function SUM(...val){
- let num = 0;
- 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;
}
+/**
+ * 璁＄畻浼犲叆鍙傛暟涓殑鏈�澶у��
+ *
+ * @param ...val 鍙彉鍙傛暟鍒楄〃锛岀敤浜庤绠楁渶澶у�肩殑鏁板�兼垨鍙浆鎹负鏁板�肩殑绫诲瀷
+ * @returns 杩斿洖鏈�澶у�硷紝濡傛灉鍙傛暟鍒楄〃涓虹┖鎴栦负null/undefined/''锛屽垯杩斿洖null
+ */
function MAX(...val){
- let max = 0;
- if(val&&val.length>0){
- val = val.filter(item=>item!=null&&item!=='')
- max = Math.max(...val)
+ 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;
}
+/**
+ * 璁＄畻浼犲叆鍙傛暟涓殑鏈�灏忓��
+ *
+ * @param val 鍙彉鍙傛暟锛岀敤浜庤绠楁渶灏忓�肩殑鏁板�兼暟缁�
+ * @returns 杩斿洖浼犲叆鍙傛暟涓殑鏈�灏忓�硷紝濡傛灉浼犲叆鍙傛暟涓虹┖鎴栨墍鏈夊�煎潎涓簄ull鎴栫┖瀛楃涓诧紝鍒欒繑鍥瀗ull
+ */
function MIN(...val){
- let min = 0;
- if(val&&val.length>0){
- val = val.filter(item=>item!=null&&item!=='')
- min = Math.min(...val)
- }
- return min;
-}
+ 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) {
+ }
+}
+/**
+ * 璁＄畻缁欏畾鏁板�肩殑骞冲潎鍊�
+ *
+ * @param val 鏁板�煎垪琛紝鍙寘鍚换鎰忎釜鍙傛暟
+ * @returns 杩斿洖骞冲潎鍊硷紝濡傛灉鏁板�煎垪琛ㄤ负绌烘垨鍖呭惈闈炴暟鍊奸」锛屽垯杩斿洖null
+ */
function AVERAGE(...val){
- let num = 0;
- let arr = [];
- if(val&&val.length>0){
- arr = val.filter(item=>item!=null&&item!=='')
- arr.forEach(item=>{
- num+=item;
- })
- return num/arr.length;
- }else{
- return 0;
+ 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;
+ }
+ } catch (error) {
+
+ }
+}
+/**
+ * 璁＄畻涓�涓暟鐨勭粷瀵瑰��
+ *
+ * @param val 浠绘剰鏁板��
+ * @returns 杩斿洖璇ユ暟鍊肩殑缁濆鍊�
+ */
+function ABS(val){
+ try {
+ return Math.abs(val);
+ } catch (error) {
+
+ }
+}
+/**
+ * 璁＄畻涓�缁勬暟瀛楃殑涓綅鏁�
+ *
+ * @param val 浠绘剰涓弬鏁帮紝闇�瑕佽绠椾腑浣嶆暟鐨勬暟瀛�
+ * @returns 濡傛灉鍙傛暟涓湁鏈夋晥鐨勬暟瀛楋紝鍒欒繑鍥炶绠楀嚭鐨勪腑浣嶆暟锛涘惁鍒欒繑鍥瀗ull
+ */
+function MEDIAN(...val){
+ 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;
+ }
+ } catch (error) {
+
}
}
-function APS(val){
- return Math.abs(val);
+/**
+ * 璁＄畻骞�
+ *
+ * @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) {
+
+ }
}
@@ -51,19 +161,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,143 +185,187 @@
* @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
- }
- }
- }
- return 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{
+ // 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)
+ }
+}
+
+/**
+ * 鏇存敼鍙傛暟
+ *
+ * @param f 鍙傛暟鍒楄〃
+ * @returns 杩斿洖涓�涓寘鍚潗鏍囦俊鎭殑鏁扮粍
+ */
+function changeParameter(f){
+ 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))
+ }
+ })
+ return arr2;
+ } catch (error) {
+
+ }
+}
+/**
+ * 鑾峰彇鍖呭惈 ABC 瀛楃鐨勫垪琛�
+ *
+ * @param f 瀛楃涓诧紝鍖呭惈闇�瑕佽В鏋愮殑鍏紡鎴栬〃杈惧紡
+ * @returns 鍖呭惈 ABC 瀛楃鐨勬暟缁勫垪琛�
+ */
+function getABCList(f){
+ 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) {
+
+ }
+}
+/**
+ * 鑾峰彇鎵�鏈夊崟鍏冩牸
+ *
+ * @param f 琛ㄦ牸鏁版嵁鎴栫浉鍏冲弬鏁�
+ * @returns 杩斿洖涓�涓璞★紝鍏堕敭涓哄崟鍏冩牸鐨勫敮涓�鏍囪瘑绗︼紙鐢卞垪鍜岃ID鎷兼帴鑰屾垚锛夛紝鍊间负null
+ */
+function getAllCell(f){
+ try {
+ let arr = changeParameter(f)
+ let arr0 = {}
+ arr.forEach(item=>{
+ arr0[getColumnNameFromId(`${item.c}`+'-'+`${item.r}`)] = null
+ })
+ return arr0;
+ } catch (error) {
+
+ }
+}
+
+/**
+ * 璁＄畻鍑芥暟
+ *
+ * @param f 瀛楃涓茬被鍨嬶紝琛ㄧず寰呰绠楃殑鍏紡
+ * @param comValue 瀵硅薄绫诲瀷锛岃〃绀鸿鏇挎崲鐨勫崟鍏冩牸鍊硷紝閿负鍗曞厓鏍煎悕绉帮紝鍊间负鏇挎崲鍚庣殑鍊�
+ * @returns 杩斿洖璁＄畻鍚庣殑缁撴灉锛屽鏋滆绠楀け璐ュ垯杩斿洖0
+ */
+function compute(f,comValue){
+ 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 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