99元一月做网站,pc端网站怎么做自适应,网站建设的工作,建设网站赚钱吗1. SUMIF函数
SUMIF 函数可用于计算子表单中满足某一条件的数字相加并返回和。
2. 函数用法
SUMIF(range, criteria, [sum_range])
其中各参数的含义及使用方法如下#xff1a; range#xff1a;必需#xff1b;根据 criteria 的条件规则进行检测的判断字段。支持的字段…1. SUMIF函数
SUMIF 函数可用于计算子表单中满足某一条件的数字相加并返回和。
2. 函数用法
SUMIF(range, criteria, [sum_range])
其中各参数的含义及使用方法如下 range必需根据 criteria 的条件规则进行检测的判断字段。支持的字段包括子表单中的数字、单行文本、下拉框、单选按钮组 criteria必需用于判断的条件规则。支持的形式和使用规则如下表
支持形式是否需要加引号示例注意事项数字不需要20、32表达式需要“32”、!苹果支持的运算符号包括、、、!、、文本需要“苹果”、水果字段不需要字段1在主表字段中使用 SUMIF 函数时只能选择主表字段2在子表字段中使用 SUMIF 函数时只能选择择主表字段和当前子表字段
3. 函数示例
如计算入库明细中产品类型为「水果」的全部入库数量则可以在「水果类数量总计」字段设置公式为 4. 代码实战
首先我们在function包下创建math包在math包下创建SumIfFunction类代码如下
package com.ql.util.express.self.combat.function.math;import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.ql.util.express.Operator;
import com.ql.util.express.self.combat.exception.FormulaException;import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.Predicate;
import java.util.stream.Collectors;/*** 类描述: SUMIF函数** author admin* version 1.0.0* date 2023/11/24 10:33*/
public class SumIfFunction extends Operator {public SumIfFunction(String name) {this.name name;}Overridepublic Object executeInner(Object[] lists) throws Exception {//边界判断if (lists.length 0 || lists.length3 || lists.length 4) {throw new FormulaException(操作数异常);}BigDecimal res BigDecimal.ZERO;Object range null;Object criteria null;ListMapString,Object subFormVal null;String rangeS ;String key ;if (lists.length 3) { // 两个参数// 获取参数key lists[0].toString();range lists[1];criteria lists[2];rangeS range.toString();subFormVal JSON.parseObject(key,List.class);res cal(subFormVal,rangeS,criteria.toString(),rangeS);} else {// 三个参数处理Object sumRange lists[3];key lists[0].toString();range lists[1];criteria lists[2];rangeS range.toString();subFormVal JSON.parseObject(key,List.class);// 循环subFormVal集合res cal(subFormVal,rangeS,criteria.toString(),sumRange.toString());}return res;}// 计算结果private BigDecimal cal(ListMapString, Object list, String range, String criteria, String sumRange) {// criteria判断类型boolean isNum CriteriaUtil.isNum(criteria);boolean isExpress CriteriaUtil.isExpress(criteria);// 根据criteria类型 生成PredicateListMapString, Object collect null;if (isExpress) {// 如果是表达式// 提取符号String symbol CriteriaUtil.extractSymbol(criteria);String symbol_value criteria.replaceAll(symbol,);boolean sybolValueIsNum CriteriaUtil.isNum(symbol_value);if (sybolValueIsNum) {// 如果是数字collect list.stream().filter(paramMap - {boolean res false;if (.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(symbol_value)?true:false;} else if (.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(symbol_value)?true:false;} else if (.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(symbol_value)?true:false;} else if (.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(symbol_value)?true:false;} else if (.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(symbol_value)?true:false;} else if (!.equals(symbol)) {res Double.parseDouble(paramMap.get(range).toString()) ! Double.parseDouble(symbol_value)?true:false;}return res;}).collect(Collectors.toList());} else {collect list.stream().filter(paramMap - {boolean res false;if (.equals(symbol)) {res String.valueOf(paramMap.get(range)).equals(symbol_value);} else if (!.equals(symbol)) {res !(String.valueOf(paramMap.get(range)).equals(symbol_value));} else {throw new RuntimeException(字符暂不支持的操作符号为symbol);}return res;}).collect(Collectors.toList());}} else {// 没有表达式 直接默认为if (isNum) {// 如果是数字collect list.stream().filter(paramMap - Double.parseDouble(paramMap.get(range).toString()) Double.parseDouble(criteria)?true:false).collect(Collectors.toList());} else {collect list.stream().filter(paramMap - String.valueOf(paramMap.get(range)).equals(criteria)).collect(Collectors.toList());}}// 满足条件的集合统计出来后按照sumRange字段统计求和BigDecimal sum BigDecimal.ZERO;for (MapString,Object map:collect) {BigDecimal tmp new BigDecimal(map.get(sumRange).toString());sum sum.add(tmp);}return sum;}static class CriteriaUtil {public static boolean isNum (String criteria) {return StrUtil.isNumeric(criteria);}public static boolean isExpress(String criteria) {ListString symbols Arrays.asList(,,,,,!);boolean res symbols.stream().anyMatch(s - criteria.contains(s));return res;}/**** 提取表达式中的符号* param criteria* return*/public static String extractSymbol(String criteria) {ListString symbols Arrays.asList(,,,,,!);final OptionalString first symbols.stream().filter(new PredicateString() {Overridepublic boolean test(String s) {return criteria.contains(s);}}).findFirst();return first.get();}}}
把SumIfFunction类注册到公式函数入口类中代码如下
package com.ql.util.express.self.combat.ext;import com.ql.util.express.ExpressRunner;
import com.ql.util.express.IExpressResourceLoader;
import com.ql.util.express.parse.NodeTypeManager;
import com.ql.util.express.self.combat.function.logic.*;
import com.ql.util.express.self.combat.function.math.*;/*** 类描述: 仿简道云公式函数实战入口类** author admin* version 1.0.0* date 2023/11/21 15:29*/
public class FormulaRunner extends ExpressRunner {public FormulaRunner() {super();}public FormulaRunner(boolean isPrecise, boolean isTrace) {super(isPrecise,isTrace);}public FormulaRunner(boolean isPrecise, boolean isStrace, NodeTypeManager nodeTypeManager) {super(isPrecise,isStrace,nodeTypeManager);}public FormulaRunner(boolean isPrecise, boolean isTrace, IExpressResourceLoader iExpressResourceLoader, NodeTypeManager nodeTypeManager) {super(isPrecise,isTrace,iExpressResourceLoader,nodeTypeManager);}Overridepublic void addSystemFunctions() {// ExpressRunner 的内部系统函数super.addSystemFunctions();// 扩展公式函数this.customFunction();}/**** 自定义公式函数*/public void customFunction() {// 逻辑公式函数this.addLogicFunction();// 数学公式函数this.addMathFunction();}public void addLogicFunction() {// AND函数this.addFunction(AND,new AndFunction(AND));// IF函数this.addFunction(IF,new IfFunction(IF));// IFS函数this.addFunction(IFS,new IfsFunction(IFS));// XOR函数this.addFunction(XOR,new XorFunction(XOR));// TRUE函数this.addFunction(TRUE,new TrueFunction(TRUE));// FALSE函数this.addFunction(FALSE,new FalseFunction(FALSE));// NOT函数this.addFunction(NOT,new NotFunction(NOT));// OR函数this.addFunction(OR,new OrFunction(OR));}public void addMathFunction() {// ABS函数this.addFunction(ABS,new AbsFunction(ABS));// AVERAGE函数this.addFunction(AVERAGE,new AvgFunction(AVERAGE));// CEILING函数this.addFunction(CEILING,new CeilingFunction(CEILING));// RADIANS函数this.addFunction(RADIANS,new RadiansFunction(RADIANS));// COS函数this.addFunction(COS,new CosFunction(COS));// COT函数this.addFunction(COT,new CotFunction(COT));// COUNT函数this.addFunction(COUNT,new CountFunction(COUNT));// COUNTIF函数this.addFunction(COUNTIF,new CountIfFunction(COUNTIF));// FIXED函数this.addFunction(FIXED,new FixedFunction(FIXED));// FLOOR函数this.addFunction(FLOOR,new FloorFunction(FLOOR));// INT函数this.addFunction(INT,new IntFunction(INT));// LARGE函数this.addFunction(LARGE,new LargeFunction(LARGE));// LOG函数this.addFunction(LOG,new LogFunction(LOG));// MAX函数this.addFunction(MAX,new MaxFunction(MAX));// MIN函数this.addFunction(MIN,new MinFunction(MIN));// MOD函数this.addFunction(MOD,new ModFunction(MOD));// POWER函数this.addFunction(POWER,new PowerFunction(POWER));// PRODUCT函数this.addFunction(PRODUCT,new ProductFunction(PRODUCT));// RAND函数this.addFunction(RAND,new RandFunction(RAND));// ROUND函数this.addFunction(ROUND,new RoundFunction(ROUND));// SIN函数this.addFunction(SIN,new SinFunction(SIN));// SMALL函数this.addFunction(SMALL,new SmallFunction(SMALL));// SQRT函数this.addFunction(SQRT,new SqrtFunction(SQRT));// SUM函数this.addFunction(SUM,new SumFunction(SUM));// SUMIF函数this.addFunction(SUMIF,new SumIfFunction(SUMIF));}
}
创建测试用例
package com.ql.util.express.self.combat;import com.alibaba.fastjson.JSON;
import com.ql.util.express.DefaultContext;
import com.ql.util.express.self.combat.ext.FormulaRunner;
import org.junit.Test;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** 类描述: 实战测试类** author admin* version 1.0.0* date 2023/11/21 15:45*/
public class CombatTest {Testpublic void SUMIF() throws Exception{FormulaRunner formulaRunner new FormulaRunner(true,true);// 创建上下文DefaultContextString, Object context new DefaultContext();ListMapString,Object list new ArrayList();MapString,Object map new HashMap();map.put(record.type,红富士);map.put(record.name,苹果);map.put(record.num,20.0);MapString,Object map2 new HashMap();map2.put(record.type,红富士);map2.put(record.name,苹果);map2.put(record.num,42.0);MapString,Object map3 new HashMap();map3.put(record.type,红星);map3.put(record.name,苹果);map3.put(record.num,30.0);MapString,Object map4 new HashMap();map4.put(record.type,美国);map4.put(record.name,苹果);map4.put(record.num,13000.0);MapString,Object map5 new HashMap();map5.put(record.type,夏黑);map5.put(record.name,葡萄);map5.put(record.num,15);MapString,Object map6 new HashMap();map6.put(record.type,阳光玫瑰);map6.put(record.name,葡萄);map6.put(record.num,30);MapString,Object map7 new HashMap();map7.put(record.type,芝麻蕉);map7.put(record.name,香蕉);map7.put(record.num,20);list.add(map);list.add(map2);list.add(map3);list.add(map4);list.add(map5);list.add(map6);list.add(map7);String s JSON.toJSONString(list);String express SUMIF(aa,bb,cc,dd);context.put(aa,s);context.put(bb,record.type);context.put(cc,!美国);context.put(dd,record.num);Object object formulaRunner.execute(express, context, null, true, true);System.out.println(object);}}
运行结果
