需求:
1、选择某一列结果集,选择当前值和后面某一行的某个值,两个结果做Excel公式运算,之后判断结果集是否符合条件;
2、前面的条件符合,需要再根据一行数据的某几列进行逻辑判断,是否符合条件(不涉及到Excel公式运算);
3、调用Excel公式运算当前行的某几列结果集作为X轴;
4、调用Excel公式运算当前行的某几列结果集作为Y轴;
5、根据条件过滤X轴的结果集;
6、根据条件过滤Y轴的结果集;
7、生成一个echarts图;
实现逻辑
根据提供的数据逻辑和公式,生成横纵坐标位置
1、初始化数据,筛选出有效数据信息
2、纵坐标通过偏移量,调取Excel公式,计算结果,调用JS公式排除无效的数据信息
3、根据前置筛选逻辑过滤无效的数据信息
4、通过Excel公式运算 X轴结果集,调用JS计算X轴后置结果进行数据筛选
5、通过Excel公式运算 Y轴结果集,调用JS计算Y轴后置结果进行数据筛选
6、合并X轴Y轴结果集,去除无效结果集
代码实现
定义用户规则类
定义用户上传的Excel
定义实体类对象:
/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package com.suyun.modules.vehicle.entity;
import com.thinkgem.jeesite.common.persistence.QueryEntity;
/**
* 参数分析业务Entity
*
* @author leo
* @version 2022-11-24
*/
public class VehBusModelFormula extends QueryEntity<VehBusModelFormula> {
private static final long serialVersionUID = 1L;
/**
* 计算业务
*/
private String caculateBusiness;
/**
* 车型名称
*/
private String modelName;
/**
* 车型ID
*/
private String modelId;
/**
* 涉及到的信号值
*/
private String signalName;
/**
* 切割后的信号名
*/
private String[] signalNames;
/**
* 偏移量信号名称
*/
private String delaySignalName;
/**
* 信号名-偏移量
*/
private Integer delayNum;
/**
* 信号偏移之后的逻辑公式
*/
private String delayFormula;
/**
* 信号偏移之后的记过筛选
*/
private String delaySelect;
/**
* 执行公式前的筛选
*/
private String processBefore;
/**
* 横轴计算公式或信号名
*/
private String xExcelFormula;
/**
* 纵轴计算公司或信号名
*/
private String yExcelFormula;
/**
* 横坐标执行公式后的筛选
*/
private String xProcessAfter;
/**
* 纵坐标执行公式后的筛选
*/
private String yProcessAfter;
public String getCaculateBusiness() {
return caculateBusiness;
}
public void setCaculateBusiness(String caculateBusiness) {
this.caculateBusiness = caculateBusiness;
}
public String getModelName() {
return modelName;
}
public void setModelName(String modelName) {
this.modelName = modelName;
}
public String getModelId() {
return modelId;
}
public void setModelId(String modelId) {
this.modelId = modelId;
}
public String getSignalName() {
return signalName;
}
public void setSignalName(String signalName) {
this.signalName = signalName;
}
public String[] getSignalNames() {
return signalNames;
}
public void setSignalNames(String[] signalNames) {
this.signalNames = signalNames;
}
public String getDelaySignalName() {
return delaySignalName;
}
public void setDelaySignalName(String delaySignalName) {
this.delaySignalName = delaySignalName;
}
public Integer getDelayNum() {
return delayNum;
}
public void setDelayNum(Integer delayNum) {
this.delayNum = delayNum;
}
public String getDelayFormula() {
return delayFormula;
}
public void setDelayFormula(String delayFormula) {
this.delayFormula = delayFormula;
}
public String getDelaySelect() {
return delaySelect;
}
public void setDelaySelect(String delaySelect) {
this.delaySelect = delaySelect;
}
public String getProcessBefore() {
return processBefore;
}
public void setProcessBefore(String processBefore) {
this.processBefore = processBefore;
}
public String getxExcelFormula() {
return xExcelFormula;
}
public void setxExcelFormula(String xExcelFormula) {
this.xExcelFormula = xExcelFormula;
}
public String getyExcelFormula() {
return yExcelFormula;
}
public void setyExcelFormula(String yExcelFormula) {
this.yExcelFormula = yExcelFormula;
}
public String getxProcessAfter() {
return xProcessAfter;
}
public void setxProcessAfter(String xProcessAfter) {
this.xProcessAfter = xProcessAfter;
}
public String getyProcessAfter() {
return yProcessAfter;
}
public void setyProcessAfter(String yProcessAfter) {
this.yProcessAfter = yProcessAfter;
}
}
package com.suyun.platform.threepart.util;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.suyun.common.lang.Tuple2;
import com.suyun.common.lang.Tuple3;
import com.suyun.platform.dto.VehBusModelFormulaDto;
import com.suyun.platform.threepart.entity.EchartsData;
import com.suyun.vehicle.model.VehicleDataValue;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import javax.script.Invocable;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
/**
* Description:
* <p>
* 执行Excel公式获取值信息
* </p>
*
* @Author: leo.xiong
* @CreateDate: 2022/11/24 14:33
* @Email: leo.xiong@suyun360.com
* @Since:
*/
public class ExcelFormulaUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelFormulaUtil.class);
/**
* 后置JS的参数只能为value,不区分大小写
*/
private static final String TIME = "time";
private static final String AFTER_JS_PARAM_VALUE_NAME = "value";
private static final String DELAY_AFTER_VALUE = "after";
private static final String DELAY_CURRENT_VALUE = "current";
private static final String DATE_KEY = "date";
/**
* 每次计算列个数最大为50
*/
private static final int MAX_CACULATE_COLUMN = 50;
private static final Map<String, Invocable> FUNCTION_NAME_INVOCABLE_MAP = Maps.newHashMap();
/**
* 根据提供的数据逻辑和公式,生成横纵坐标位置
* 1、初始化数据,筛选出有效数据信息
* 2、纵坐标通过偏移量,调取Excel公式,计算结果,调用JS公式排除无效的数据信息
* 3、根据前置筛选逻辑过滤无效的数据信息
* 4、通过Excel公式运算 X轴结果集,调用JS计算X轴后置结果进行数据筛选
* 5、通过Excel公式运算 Y轴结果集,调用JS计算Y轴后置结果进行数据筛选
* 6、合并X轴Y轴结果集,去除无效结果集
*
* @param dataList
* @param vehBusModelFormulaDto
* @return
*/
public static EchartsData processFormula(List<Map<String, Object>> dataList, VehBusModelFormulaDto vehBusModelFormulaDto) {
List<Map<String, BigDecimal>> dataMapList = init(dataList, vehBusModelFormulaDto);
dataMapList = processDelay(dataMapList, vehBusModelFormulaDto);
dataMapList = processBefore(dataMapList, vehBusModelFormulaDto);
EchartsData xEchartsData = getEchartsData(vehBusModelFormulaDto, vehBusModelFormulaDto.getxExcelFormula(), vehBusModelFormulaDto.getxProcessAfter(), dataMapList);
if (CollectionUtils.isEmpty(xEchartsData.getXDataList())) {
return xEchartsData;
}
EchartsData yEchartsData = getEchartsData(vehBusModelFormulaDto, vehBusModelFormulaDto.getyExcelFormula(), vehBusModelFormulaDto.getyProcessAfter(), dataMapList);
return mergeEchartsData(xEchartsData, yEchartsData);
}
/**
* 横纵坐标执行函数,结果集筛选
*
* @param vehBusModelFormulaDto
* @param excelFormula
* @param processAfter
* @param dataMapList
* @return
*/
private static EchartsData getEchartsData(VehBusModelFormulaDto vehBusModelFormulaDto, String excelFormula, String processAfter, List<Map<String, BigDecimal>> dataMapList) {
EchartsData xEchartsData = process(dataMapList, vehBusModelFormulaDto, excelFormula);
if (xEchartsData == null || CollectionUtils.isEmpty(xEchartsData.getXDataList())) {
return new EchartsData(0);
}
return processAfter(xEchartsData, vehBusModelFormulaDto.getId(), processAfter);
}
/**
* 1、排除没有信号值的数据列
* 2、有信号值的数据转为Double类型
*
* @param dataList
* @param vehBusModelFormulaDto
* @return
*/
public static List<Map<String, BigDecimal>> init(List<Map<String, Object>> dataList, VehBusModelFormulaDto vehBusModelFormulaDto) {
if (CollectionUtils.isEmpty(dataList) || vehBusModelFormulaDto == null || StringUtils.isEmpty(vehBusModelFormulaDto.getSignalName())) {
return Collections.EMPTY_LIST;
}
FUNCTION_NAME_INVOCABLE_MAP.clear();
vehBusModelFormulaDto.setSignalNames(vehBusModelFormulaDto.getSignalName().split(","));
List<Map<String, BigDecimal>> nameValueMapList = Lists.newArrayListWithExpectedSize(dataList.size());
for (Map<String, Object> dataMap : dataList) {
Object timeValue = dataMap.get(DATE_KEY);
Map<String, BigDecimal> nameValueMap = null;
if (timeValue == null) {
continue;
} else {
BigDecimal timestamp = new BigDecimal(timeValue.toString());
nameValueMap = Maps.newHashMapWithExpectedSize(dataMap.size());
nameValueMap.put(DATE_KEY, timestamp);
}
boolean valueFlag = true;
for (String name : vehBusModelFormulaDto.getSignalNames()) {
if (StringUtils.isEmpty(name)) {
continue;
}
Object value = dataMap.get(name);
if (Objects.isNull(value)) {
valueFlag = false;
break;
}
Double newValue = null;
if (value instanceof Double) {
newValue = (Double) value;
} else if (value instanceof Integer) {
newValue = Double.valueOf((Integer) value);
} else if (value instanceof VehicleDataValue) {
newValue = ((VehicleDataValue) value).getValue();
}
if (newValue == null) {
valueFlag = false;
break;
}
nameValueMap.put(name, new BigDecimal(newValue));
}
if (!valueFlag) {
continue;
}
nameValueMapList.add(nameValueMap);
}
return nameValueMapList;
}
/**
* 偏移数据过滤
*
* @param dataList
* @param vehBusModelFormulaDto
* @return
*/
public static List<Map<String, BigDecimal>> processDelay(List<Map<String, BigDecimal>> dataList, VehBusModelFormulaDto vehBusModelFormulaDto) {
if (CollectionUtils.isEmpty(dataList) || StringUtils.isEmpty(vehBusModelFormulaDto.getDelaySignalName())) {
return dataList;
}
Map<String, BigDecimal> formulaTimeMap = Maps.newHashMap();
for (int i = 0, len = dataList.size(); i < len; i++) {
Map<String, BigDecimal> dataMap = dataList.get(i);
if (i + vehBusModelFormulaDto.getDelayNum() > len) {
break;
}
BigDecimal currentValue = dataMap.get(vehBusModelFormulaDto.getDelaySignalName());
BigDecimal afterValue = dataList.get(i + vehBusModelFormulaDto.getDelayNum() - 1).get(vehBusModelFormulaDto.getDelaySignalName());
String delayFormula = vehBusModelFormulaDto.getDelayFormula().toLowerCase()
.replace(DELAY_AFTER_VALUE, afterValue.toPlainString())
.replace(DELAY_CURRENT_VALUE, currentValue.toPlainString());
formulaTimeMap.put(delayFormula, dataMap.get(DATE_KEY));
}
if (CollectionUtils.isEmpty(formulaTimeMap)) {
return null;
}
Map<String, BigDecimal> formulaValueMap = caculateFormula(Lists.newArrayList(formulaTimeMap.keySet()));
if (CollectionUtils.isEmpty(formulaValueMap)) {
return null;
}
int i = buildAfterParamValues(vehBusModelFormulaDto.getDelaySelect());
if (i == 0) {
return null;
}
List<BigDecimal> timeList = Lists.newArrayListWithExpectedSize(formulaValueMap.size());
for (Map.Entry<String, BigDecimal> formulaValueEntry : formulaValueMap.entrySet()) {
BigDecimal value = formulaValueEntry.getValue();
if (value == null) {
continue;
}
Double[] values = new Double[i];
for (int k = 0; k < i; k++) {
values[k] = value.doubleValue();
}
boolean isSuccess = isValidSuccess("d" + vehBusModelFormulaDto.getId(), AFTER_JS_PARAM_VALUE_NAME, vehBusModelFormulaDto.getDelaySelect(), values);
if (isSuccess) {
timeList.add(formulaTimeMap.get(formulaValueEntry.getKey()));
}
}
if (CollectionUtils.isEmpty(timeList)) {
return null;
}
dataList.removeIf(dataMap -> !timeList.contains(dataMap.get(DATE_KEY)));
return dataList;
}
/**
* Excel做逻辑判断
* 判断条件不能大于10000个字符
*
* @param dataList
* @param vehBusModelFormulaDto
* @return
*/
public static List<Map<String, BigDecimal>> processBefore(List<Map<String, BigDecimal>> dataList, VehBusModelFormulaDto vehBusModelFormulaDto) {
if (CollectionUtils.isEmpty(dataList) || StringUtils.isEmpty(vehBusModelFormulaDto.getProcessBefore())) {
return dataList;
}
dataList.removeIf(dataMap -> {
Tuple3<String, String, Double[]> paramValuesTuple3 = buildBeforeParamValues(vehBusModelFormulaDto.getSignalNames(), dataMap, vehBusModelFormulaDto.getProcessBefore());
return !isValidSuccess("p" + vehBusModelFormulaDto.getId(), paramValuesTuple3._1(), paramValuesTuple3._2(), paramValuesTuple3._3());
});
return dataList;
}
/**
* 根据公式获取结果集
* 公式:SUM(1,2,3)
* 信号名:PK_Mileage
*
* @param dataList 结果集
* @param vehBusModelFormulaDto
* @param excelFormula Excel计算公式
* @return KEY 为横轴 Value 为纵轴
*/
public static EchartsData process(List<Map<String, BigDecimal>> dataList, VehBusModelFormulaDto vehBusModelFormulaDto, String excelFormula) {
if (CollectionUtils.isEmpty(dataList) || StringUtils.isEmpty(excelFormula)) {
return null;
}
boolean isFormula = true;
if (!TIME.equalsIgnoreCase(excelFormula)) {
for (String signalName : vehBusModelFormulaDto.getSignalNames()) {
if (signalName.equalsIgnoreCase(excelFormula)) {
isFormula = false;
break;
}
}
}
EchartsData echartsData = new EchartsData(dataList.size());
List<BigDecimal> timeList = Lists.newArrayListWithExpectedSize(dataList.size());
Map<BigDecimal, String> timeFormulaMap = Maps.newHashMapWithExpectedSize(dataList.size());
for (Map<String, BigDecimal> dataMap : dataList) {
BigDecimal timestamp = dataMap.get(DATE_KEY);
if (TIME.equalsIgnoreCase(excelFormula)) {
echartsData.getXDataList().add(timestamp);
echartsData.getYDataList().add(timestamp);
continue;
}
timeList.add(timestamp);
if (isFormula) {
timeFormulaMap.put(timestamp, replaceSignalName(vehBusModelFormulaDto.getSignalNames(), dataMap, excelFormula).toLowerCase());
continue;
}
echartsData.getXDataList().add(timestamp);
echartsData.getYDataList().add(dataMap.get(excelFormula));
}
if (!isFormula) {
return echartsData;
}
Map<String, BigDecimal> formulaValueMap = caculateFormula(Lists.newArrayList(timeFormulaMap.values()));
for (BigDecimal time : timeList) {
BigDecimal value = formulaValueMap.get(timeFormulaMap.get(time));
if (value == null) {
continue;
}
echartsData.getXDataList().add(time);
echartsData.getYDataList().add(value);
}
return echartsData;
}
/**
* 排除不符合条件的结果集
*
* @param echartsData
* @param id
* @param processAfter
* @return KEY 为横轴 Value 为纵轴
*/
public static EchartsData processAfter(EchartsData echartsData, String id, String processAfter) {
if (echartsData == null || StringUtils.isEmpty(processAfter)) {
return echartsData;
}
int i = buildAfterParamValues(processAfter);
if (i == 0) {
return echartsData;
}
List<BigDecimal> newXDataList = Lists.newArrayListWithExpectedSize(echartsData.getXDataList().size());
List<BigDecimal> newYDataList = Lists.newArrayListWithExpectedSize(echartsData.getYDataList().size());
for (int j = 0, len = echartsData.getYDataList().size(); j < len; j++) {
Double value = echartsData.getYDataList().get(j).doubleValue();
Double[] values = new Double[i];
for (int k = 0; k < i; k++) {
values[k] = value;
}
boolean isSuccess = isValidSuccess("f" + id, AFTER_JS_PARAM_VALUE_NAME, processAfter, values);
if (isSuccess) {
newXDataList.add(echartsData.getXDataList().get(j));
newYDataList.add(echartsData.getYDataList().get(j));
}
}
echartsData.setxDataList(newXDataList);
echartsData.setyDataList(newYDataList);
return echartsData;
}
/**
* 根据时间结果,合并结果集
*
* @param xEchartsData
* @param yEchartsData
* @return
*/
private static EchartsData mergeEchartsData(EchartsData xEchartsData, EchartsData yEchartsData) {
if (CollectionUtils.isEmpty(xEchartsData.getXDataList()) || CollectionUtils.isEmpty(yEchartsData.getXDataList())) {
return new EchartsData(0);
}
List<BigDecimal> xxValueList = xEchartsData.getXDataList();
List<BigDecimal> xyValueList = xEchartsData.getYDataList();
Map<String, BigDecimal> timeValueMap = Maps.newHashMapWithExpectedSize(yEchartsData.getXDataList().size());
for (int i = 0, len = yEchartsData.getXDataList().size(); i < len; i++) {
timeValueMap.put(yEchartsData.getXDataList().get(i).toPlainString(), yEchartsData.getYDataList().get(i));
}
EchartsData newEchartsData = new EchartsData(xxValueList.size());
for (int i = 0, len = xxValueList.size(); i < len; i++) {
BigDecimal yValue = timeValueMap.get(xxValueList.get(i).toPlainString());
if (yValue == null) {
continue;
}
newEchartsData.getXDataList().add(xyValueList.get(i));
newEchartsData.getYDataList().add(yValue);
}
return newEchartsData;
}
/**
* 通过JS计算是否符合条件
* 需要用到缓存,JS编译过多,容易造成对外内存溢出,且性能过低
*
* @param functionName
* @param param
* @param functionBody
* @param values
* @return
*/
private static boolean isValidSuccess(String functionName, String param, String functionBody, Double... values) {
Invocable in = FUNCTION_NAME_INVOCABLE_MAP.get(functionName);
if (in == null) {
ScriptEngineManager manager = new ScriptEngineManager();
ScriptEngine engine = manager.getEngineByName("js");
try {
engine.eval(buildFunctionStr(functionName, param, functionBody).toLowerCase());
if (engine instanceof Invocable) {
in = (Invocable) engine;
FUNCTION_NAME_INVOCABLE_MAP.put(functionName, in);
}
} catch (ScriptException e) {
LOGGER.error("编译JS出错 id: {} functionBody: {}", functionName, functionBody, e);
return false;
}
}
try {
Object transferValueObj = in.invokeFunction(functionName, values);
if (transferValueObj == null) {
return false;
}
if (transferValueObj instanceof Boolean) {
return (Boolean) transferValueObj;
}
} catch (ScriptException | NoSuchMethodException e) {
LOGGER.warn("执行的js方法错误 functionName: {} functionBody: {} values: {}", functionName, functionBody, StringUtils.join(values, ","), e);
}
return false;
}
/**
* 创建JS方法,进行逻辑判断
*
* @param functionName
* @param param
* @param functionBody
* @return
*/
private static String buildFunctionStr(String functionName, String param, String functionBody) {
StringBuffer sb = new StringBuffer();
sb.append("function ");
sb.append(functionName);
sb.append("(" + param + ")");
sb.append("{");
sb.append("return ");
sb.append(functionBody);
sb.append(";");
sb.append("}");
return sb.toString();
}
/**
* 拼接前置JS出参数和values集合,用于掺入js调用
*
* @param signalNames
* @param dataMap
* @param baseJsFormula
* @return
*/
private static Tuple3<String, String, Double[]> buildBeforeParamValues(String[] signalNames, Map<String, BigDecimal> dataMap, String baseJsFormula) {
Map<String, Tuple2<String, Double>> signalNameKeyValueTuple2Map = Maps.newLinkedHashMapWithExpectedSize(signalNames.length);
for (int i = 0; i < signalNames.length; i++) {
String signalName = signalNames[i];
if (!baseJsFormula.contains(signalName)) {
continue;
}
BigDecimal value = dataMap.get(signalName);
while (baseJsFormula.indexOf(signalName) != -1) {
signalNameKeyValueTuple2Map.put(signalName, new Tuple2<>("KEY" + i, value.doubleValue()));
break;
}
}
StringBuffer paramSb = new StringBuffer();
Double[] values = new Double[signalNameKeyValueTuple2Map.size()];
int i = 0;
for (Map.Entry<String, Tuple2<String, Double>> stringTuple2Entry : signalNameKeyValueTuple2Map.entrySet()) {
String signalName = stringTuple2Entry.getKey();
Tuple2<String, Double> tuple2 = stringTuple2Entry.getValue();
baseJsFormula = baseJsFormula.replace(signalName, tuple2._1());
values[i++] = tuple2._2();
paramSb.append(tuple2._1() + ",");
}
return new Tuple3<>(paramSb.substring(0, paramSb.length() - 1).toLowerCase(), baseJsFormula.toLowerCase(), values);
}
/**
* 基础公式里面的信号名做替换
*
* @param dataMap
* @param excelFormula
* @return
*/
private static String replaceSignalName(String[] signalNames, Map<String, BigDecimal> dataMap, String excelFormula) {
for (String signalName : signalNames) {
if (!excelFormula.contains(signalName)) {
continue;
}
String value = dataMap.get(signalName).toPlainString();
excelFormula = excelFormula.replace(signalName, value);
}
return excelFormula;
}
/**
* 拼接后置JS的参数信息
*
* @param processAfter
* @return
*/
private static int buildAfterParamValues(String processAfter) {
processAfter = processAfter.toLowerCase();
int i = 0;
while (processAfter.indexOf(AFTER_JS_PARAM_VALUE_NAME) != -1) {
Integer index = processAfter.indexOf(AFTER_JS_PARAM_VALUE_NAME) + 1;
processAfter = processAfter.substring(index);
i++;
}
return i;
}
/**
* 公式计算
*
* @param formulaList
* @return
*/
public static Map<String, BigDecimal> caculateFormula(List<String> formulaList) {
if (CollectionUtils.isEmpty(formulaList)) {
return Collections.EMPTY_MAP;
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFFormulaEvaluator hssfFormulaEvaluator = new HSSFFormulaEvaluator(workbook);
int rows = (formulaList.size() / MAX_CACULATE_COLUMN) + 1;
AtomicInteger dataIndexAtomicInteger = new AtomicInteger(0);
Map<String, BigDecimal> formulaBigDecimalMap = Maps.newConcurrentMap();
int len = formulaList.size();
for (int row = 0; row < rows; row++) {
HSSFRow hssfRow = sheet.createRow(row);
for (int column = 0; column < MAX_CACULATE_COLUMN; column++) {
int index = dataIndexAtomicInteger.getAndIncrement();
//考虑是否需要多线程并行计算
if (index >= len) {
break;
}
HSSFCell hssfCell = hssfRow.createCell(column);
hssfCell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula = formulaList.get(index);
hssfCell.setCellFormula(formula);
BigDecimal value = null;
try {
value = new BigDecimal(hssfFormulaEvaluator.evaluate(hssfCell).getNumberValue())
.setScale(10, BigDecimal.ROUND_HALF_UP)
.stripTrailingZeros();
} catch (Exception e) {
LOGGER.warn("计算错误 formula: {}", formula, e);
return null;
}
formulaBigDecimalMap.put(formula, value);
}
}
return formulaBigDecimalMap;
}
}
测试类(需要先注释掉缓存,否则Main方法报错):
package com.thinkgem.jeesite.test;
import com.google.common.collect.Lists;
import com.suyun.modules.vehicle.dto.EchartsData;
import com.suyun.modules.vehicle.entity.VehBusModelFormula;
import com.suyun.modules.vehicle.utils.ExcelFormulaUtil ;
import com.suyun.vehicle.utils.DatetimeUtil;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Description:
* <p>
*
* </p>
*
* @Author: leo.xiong
* @CreateDate: 2022/11/24 10:59
* @Email: leo.xiong@suyun360.com
* @Since:
*/
public class ExcelFormulaTest {
public static void main(String[] args) {
List<Map<String, Object>> dataList = Lists.newArrayList();
Map<String, Object> one = new HashMap() {{
put("date", DatetimeUtil.changeToDate("2022-11-25 09:18:23", DatetimeUtil.TIME_FORMAT_Y_M_D).getTime());
put("a", 1);
put("b", 5);
put("c", 5);
put("d", 5);
put("f", 5);
}};
Map<String, Object> two = new HashMap() {{
put("date", DatetimeUtil.changeToDate("2022-11-25 09:18:24", DatetimeUtil.TIME_FORMAT_Y_M_D).getTime());
put("a", 2);
put("b", 10);
put("c", 10);
put("d", 10);
put("f", 10);
}};
Map<String, Object> three = new HashMap() {{
put("date", DatetimeUtil.changeToDate("2022-11-25 09:18:25", DatetimeUtil.TIME_FORMAT_Y_M_D).getTime());
put("a", 3);
put("b", 1);
put("c", 5);
put("d", 1);
put("f", 5);
}};
Map<String, Object> four = new HashMap() {{
put("date", DatetimeUtil.changeToDate("2022-11-25 09:18:26", DatetimeUtil.TIME_FORMAT_Y_M_D).getTime());
put("a", 3);
put("b", 1);
put("d", 1);
put("f", 5);
}};
dataList.add(one);
dataList.add(two);
dataList.add(three);
dataList.add(four);
VehBusModelFormula vehBusModelFormula = new VehBusModelFormula();
vehBusModelFormula.setId("0124c25449fa4c17992f45ba2bb00b88");
vehBusModelFormula.setSignalName("a,b,c,d,f");
vehBusModelFormula.setProcessBefore("a>3 || (b<2 && c>50) || (f>10 || d<2)");
vehBusModelFormula.setExcelFormula("a+b+c+d+f");
vehBusModelFormula.setProcessAfter("value<20 && value>10");
EchartsData echartsData = ExcelFormulaUtil.processFormula(dataList, vehBusModelFormula);
System.out.println(echartsData);
}
}