easy Excel 通用解析 excel 方案。
数据库配置解析规则,可配置解析sheet名称(包含、正则),配置解析表头还是index索引方式。表头名称支持多个key匹配一个表头。自动格式转换,支持时间,字符串等。
配置入库规则,设置数据库插入语句,可自定义,可实现ON DUPLICATE KEY或者replace等高级功能,通过index对应索引即可。
配置Kafka-topic主题
配置自定义过滤Java代码
配置自定义业务boMap
1、数据库配置
CREATE TABLE `file_standard_excel_rule` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
`fa_id` bigint NOT NULL COMMENT '方案ID',
`sheet_match` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'sigel|default',
`sheet_match_model` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'index|contain|regex|code',
`sheet_match_index` int DEFAULT '0' COMMENT 'key索引位置',
`sheet_match_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'sheet包含内容',
`sheet_match_regex` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '正则表达式',
`sheet_match_code` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Java代码',
`key_conf` json NOT NULL COMMENT 'key解析配置\n[{"keys": ["订单号"], "reKey": "order_no", "keyType": "keys|”, "valueType": "string”},{“index”: 1, "reKey": "uid", "keyType": “index”, "valueType": "string"}]',
`filter` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'xxjava-codexx',
`kafka_topic` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'Kafka主题',
`save_conf` json NOT NULL COMMENT '保存配置\n"save_conf":{\n "sql":"",\n "fields":[\n {\n “Index”:0,\n "reKey":""\n }\n ]\n }',
`special_conf` json DEFAULT NULL COMMENT '业务配置,特殊字段,例如:file_id\n[{"key": "file_id", "reKey": "file_id", "keyType": "bo", "defaultVale": 0}, {"value_type": "snowId", "reKey": “id”, "keyType": "default”}]',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Excel解析配置规则';
2、模型配置
{
"confs":[
{
"sheet_match":"sigel|default",
"sheet_match_model":"index|contain|regex|code",
"sheet_match_index":0,
"sheet_match_name":"sheet包含内容",
"sheet_match_regex":"正则表达式",
"sheet_match_code":"Java代码",
"mode":"index|header" ,
"key_conf":[
{
"key_type":"index|keys",
"index":1,
"keys:":["",""],
"value_type":"字段类型(int,long,datetime,time2long,string,default)",
"default_vale":"",
"rekey":""
}
],
"special_conf":[
{
"key_type":"bo|default",
"key:":"file_id|user_id",
"value_type":"类型(snowId,nowtime,nowtimelong,default)",
"default_vale":"",
"rekey":""
}
],
"filter":"xxjava-codexx",
"kafka_topic":"",
"save_conf":{
"sql":"",
"fields":[
{
"index":0,
"rekey":""
}
]
}
}
]
}
3、Java实现
3.1 基础model
package com.baian.blockchainanalysis.entity;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baian.blockchainanalysis.pojo.parsing.excel.KeyConf;
import com.baian.blockchainanalysis.pojo.parsing.excel.SaveConf;
import com.baian.blockchainanalysis.pojo.parsing.excel.SpecialKeyConf;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.List;
import lombok.Data;
/**
* Excel解析配置规则
*
* @TableName file_standard_excel_rule
*/
@TableName(value = "file_standard_excel_rule")
@Data
public class FileStandardExcelRule implements Serializable {
/**
* 自增主键id
*/
@TableId(type = IdType.AUTO)
private Long id;
/**
* 方案ID
*/
private Long faId;
/**
* sigel|default
*/
private String sheetMatch;
/**
* contain|regex|code
*/
private String sheetMatchModel;
/**
* key索引位置
*/
private Integer sheetMatchIndex;
/**
* sheet包含内容
*/
private String sheetMatchName;
/**
* 正则表达式
*/
private String sheetMatchRegex;
/**
* Java代码
*/
private String sheetMatchCode;
/**
* key解析配置
* "key_conf":[
* {
* "index":1,
* "keys:":["",""],
* "key_type":"",
* "default_type":"",
* "default_vale":"",
* "rekey":""
* }
* ],
*/
private String keyConf;
@TableField(exist = false)
private List<KeyConf> keyConfList;
public void changeKeyConf() {
this.keyConfList = JSONArray.parseArray(keyConf, KeyConf.class);
}
private String specialConf;
@TableField(exist = false)
private List<SpecialKeyConf> specialConfList;
public void changeSpecialKeyConf() {
this.specialConfList = JSONArray.parseArray(specialConf, SpecialKeyConf.class);
}
/**
* xxjava-codexx
*/
private String filter;
/**
* Kafka主题
*/
private String kafkaTopic;
/**
* 保存配置
* "save_conf":{
* "table":"",
* "fields":[
* {
* "column":"",
* "rekey":"",
* "value_type":"雪花ID、冗余字段"
* }
* ]
* }
*/
private String saveConf;
@TableField(exist = false)
private SaveConf saveConfObj;
public void changeSaveConf() {
this.saveConfObj = JSONObject.parseObject(saveConf, SaveConf.class);
}
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
package com.baian.blockchainanalysis.pojo.parsing.excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Set;
/**
* @program: block-chain-cloud-parent
* @description:
* @author: <发哥讲Java-694204477@qq.com>
* @create: 2023-05-16 11:39
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class KeyConf {
/**
* {
* "key_type":"index|keys",
* "index":1,
* "keys:":["",""],
* "value_type":"字段类型",
* "default_vale":"",
* "rekey":""
* }
*/
private String keyType;
private Integer index;
private Set<String> keys;
private String valueType;
private String defaultVale;
private String reKey;
}
package com.baian.blockchainanalysis.pojo.parsing.excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
import java.util.Set;
/**
* @program: block-chain-cloud-parent
* @description:
* @author: <发哥讲Java-694204477@qq.com>
* @create: 2023-05-16 11:39
**/
@Data
public class SaveConf {
/**
* "save_conf":{
* "sql":"",
* "fields":[
* {
* "index":0,
* "rekey":""
* }
* ]
* }
*/
private String sql;
private List<ColumnData> fields;
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class ColumnData {
private Integer index;
private String reKey;
}
}
package com.baian.blockchainanalysis.pojo.parsing.excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Set;
/**
* @program: block-chain-cloud-parent
* @description:
* @author: <发哥讲Java-694204477@qq.com>
* @create: 2023-05-16 11:39
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SpecialKeyConf {
/**
* {
* "key_type":"bo|default",
* "key:":"file_id|user_id|id",
* "value_type":"字段类型|special(雪花ID、冗余字段)",
* "default_vale":"",
* "rekey":""
* }
*/
private String keyType;
private String key;
private String valueType;
private Object defaultVale;
private String reKey;
}
3.2 通用解析Listener
package com.baian.blockchainanalysis.service.parsingImpl;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.read.metadata.holder.ReadSheetHolder;
import com.alibaba.fastjson.JSONObject;
import com.baian.blockchainanalysis.entity.FileStandardExcelRule;
import com.baian.blockchainanalysis.pojo.parsing.excel.KeyConf;
import com.baian.blockchainanalysis.pojo.parsing.excel.SaveConf;
import com.baian.blockchainanalysis.pojo.parsing.excel.SpecialKeyConf;
import com.baian.blockchainanalysis.utils.DateConvertUtil;
import com.baian.blockchainanalysis.utils.SnowflakeIdUtil;
import com.baian.blockchainanalysis.utils.StringParsingUtil;
import com.baian.cloud.datasource.jdbc.utils.BaseDbDaoUtils;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import javax.sql.DataSource;
import java.time.LocalDateTime;
import java.util.*;
/**
* @program: block-chain-cloud-parent
* @description: 模板话解析 excel 文件 监听器
* https://safeis.cn/
* @author: <发哥讲Java-694204477@qq.com>
* @create: 2023-05-15 20:54
**/
@Slf4j
@Data
public class TemplateAnalysisEventListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(TemplateAnalysisEventListener.class);
private static final String MATCH_DEFAULT = "default";
private static final String MATCH_SINGLE = "single";
private static final String KEY_TYPE_SPECIAL = "special";
public TemplateAnalysisEventListener() {
}
public TemplateAnalysisEventListener(List<FileStandardExcelRule> fileStandardExcelRuleList, Map<String, Object> boMap, DataSource dataSource) {
this.fileStandardExcelRuleList = fileStandardExcelRuleList;
this.boMap = boMap;
this.dataSource = dataSource;
}
/**
* 所有的解析规则 需要穿参进来
*/
private List<FileStandardExcelRule> fileStandardExcelRuleList;
/**
* bo 业务传输过来的map,可以获取file_id,user_id,area_code等字段,需要穿参进来
*/
private Map<String, Object> boMap = new HashMap<>();
/**
* 数据源- 需要穿参进来
*/
private DataSource dataSource;
/**
* 当前sheet解析规则
*/
private FileStandardExcelRule executeExcelRule;
/**
* 默认的解析规则
*/
private FileStandardExcelRule defaultExcelRule;
/**
* 当前 sheet 是否解析,如果不存在 解析规则,就跳过;默认不执行
*/
private boolean currentSheetIsParsed = false;
/**
* 表头数据
*/
Map<Integer, String> headMap;
/**
* 表头数据, 名称和index换位置
*/
Map<String, Integer> swapHeadMap;
/**
* 索引-keyConf对应
*/
Map<Integer, KeyConf> indexKeyConfMap;
/**
* 特殊 key-value。业务属性的字段配置
*/
Map<String, Object> specialMap;
/**
* 定义一个集合,用于存储读取到的数据。
*/
private final List<Map<String, Object>> dataList = new ArrayList<>();
private Map<Integer, Integer> logMap = new HashMap<>();
/**
* 读取数据时的回调方法,在每读取一行数据时都会被调用。
*
* @param rowData 读取到的数据对象,是一个 Map<Integer, String> 类型的对象,其中 key 表示列索引,value 表示该单元格的值
* @param context 当前数据读取的上下文对象
*/
@Override
public void invoke(Map<Integer, String> rowData, AnalysisContext context) {
if (!this.isCurrentSheetIsParsed()) {
if (!logMap.containsKey(context.readSheetHolder().getSheetNo())) {
logMap.put(context.readSheetHolder().getSheetNo(), 1);
LOGGER.error("跳过:{}解析", context.readSheetHolder().getSheetNo() + "-" + context.readSheetHolder().getSheetName());
}
return;
}
// LOGGER.info("读取到一条数据:{}", rowData);
// 将读取到的数据对象添加到集合中。
// dataList.add(rowData);
Map<String, Object> reRowData = new HashMap<>(specialMap);
// rowData 转 map 数据
rowData.forEach((k, v) -> {
// k : index
// v: string 数据
// 1、根据index-配置找到对应的数据配置。
if (!this.indexKeyConfMap.containsKey(k)) {
return;
}
invokeKeyValue(k, v, reRowData);
});
dataList.add(reRowData);
}
public void invokeKeyValue(Integer index, String cellValue, Map<String, Object> reRowData) {
KeyConf kc = this.indexKeyConfMap.get(index);
// 转换数据
switch (kc.getValueType()) {
case "int" -> {
reRowData.put(kc.getReKey(), StringParsingUtil.getIntegerForBase(cellValue, kc.getDefaultVale()));
}
case "long" -> {
reRowData.put(kc.getReKey(), StringParsingUtil.getLongForBase(cellValue, kc.getDefaultVale()));
}
case "datetime" -> {
reRowData.put(kc.getReKey(), DateConvertUtil.getDateForBase(cellValue));
}
case "time2long" -> {
Date dateForBase = DateConvertUtil.getDateForBase(cellValue);
reRowData.put(kc.getReKey(), dateForBase != null ? dateForBase.getTime() / 1000 : 0L);
}
case "string" -> {
reRowData.put(kc.getReKey(), StringParsingUtil.getReplaceEmptyStringOrDefault(cellValue, kc.getDefaultVale()));
}
default -> {
reRowData.put(kc.getReKey(), StringParsingUtil.getReplaceEmptyStringOrDefault(cellValue, kc.getDefaultVale()));
}
}
}
/**
* 数据读取完毕后的回调方法,在整个数据读取过程结束时会被调用。
*
* @param context 当前数据读取的上下文对象
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!this.isCurrentSheetIsParsed()) {
return;
}
// 数据读取完毕后,可以对读取到的数据进行处理,例如写入数据库或输出到控制台等。
LOGGER.info("当前sheet:{},所有数据读取完成,共读取到{}条数据", context.readSheetHolder().getSheetName(),
dataList.size());
saveData();
}
/**
* 数据读取异常时的回调方法。
*
* @param exception 数据读取异常对象
* @param context 当前数据读取的上下文对象
* @throws Exception 如果处理异常时发生错误,则抛出异常
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
LOGGER.error("数据读取出现异常:{}", exception.getMessage());
exception.printStackTrace();
ReadSheetHolder readSheetHolder = context.readSheetHolder();
LOGGER.error("获取readSheetHolder:{}", readSheetHolder.getSheetNo() + "-" + readSheetHolder.getSheetName());
if (exception instanceof ExcelDataConvertException excelDataConvertException) {
ReadRowHolder readRowHolder = context.readRowHolder();
LOGGER.error("出错的数据readRowHolder行为:{}", readRowHolder);
// 如果是 ExcelAnalysisException 异常,则可以通过 getRowData() 方法获取到出错的数据行。
log.error("第{}行,第{}列解析异常,数据为:{}", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}
}
/**
* 保存数据
*
* @return 读取到的数据集合
*/
public void saveData() {
SaveConf saveConfObj = executeExcelRule.getSaveConfObj();
List<Object[]> objects = dataList.stream().map(map -> {
saveConfObj.getFields().sort(Comparator.comparing(SaveConf.ColumnData::getIndex));
List<SaveConf.ColumnData> fields = saveConfObj.getFields();
return fields.stream()
.map(columnData -> map.getOrDefault(columnData.getReKey(), null))
.toArray();
}).toList();
BaseDbDaoUtils baseDbDaoUtils = new BaseDbDaoUtils(dataSource);
baseDbDaoUtils.executeBatch(saveConfObj.getSql(), objects);
}
/**
* 表头解析完成后的回调方法。
* 解析 sheet 处理的第一个方法
*
* @param headMap 表头信息,key为表头所在列的索引,value为表头名称
* @param context 当前数据读取的上下文对象
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
dataList.clear();
// 获取当前sheet的名称
ReadSheetHolder readSheetHolder = context.readSheetHolder();
// LOGGER.info("获取表头信息:{}", headMap);
LOGGER.info("获取sheetNo序号:{},获取sheet名称:{}", readSheetHolder.getSheetNo(), readSheetHolder.getSheetName());
// 如果不存在解析规则,则当前sheet不解析
if (CollectionUtils.isEmpty(fileStandardExcelRuleList)) {
this.currentSheetIsParsed = false;
LOGGER.error("当前sheet:{},不存在匹配的解析规则,解析跳过", readSheetHolder.getSheetName());
}
// 处理表头
this.headMap = headMap;
// 转换表头
this.swapHeadMap = swapKeyValue(headMap);
// 从 fileStandardExcelRuleList 找到 解析规则
// 匹配 sheet 和配置
FileStandardExcelRule rule = getRule(readSheetHolder, fileStandardExcelRuleList);
if (rule == null) {
this.currentSheetIsParsed = false;
} else {
this.defaultExcelRule = rule;
this.executeExcelRule = rule;
this.currentSheetIsParsed = true;
rule.changeKeyConf();
rule.changeSaveConf();
rule.changeSpecialKeyConf();
packKeyCnf();
packSpecialKeyValue();
}
}
/**
* 包装所有的keyconf
*/
public void packKeyCnf() {
Map<Integer, KeyConf> indexKeyConfMap = new HashMap<>();
// 特殊数据
Map<String, Object> specialMap = new HashMap<>();
this.headMap.forEach((k, v) -> {
String excelKey = headMap.get(k);
List<KeyConf> keyConfList = this.executeExcelRule.getKeyConfList();
Optional<KeyConf> keyConfOptional = keyConfList.stream()
.filter(conf -> {
switch (conf.getKeyType()) {
case "index" -> {
return Objects.equals(conf.getIndex(), k);
}
case "keys" -> {
Optional<String> first = conf.getKeys().stream().filter(s -> s.equalsIgnoreCase(excelKey)).findFirst();
return first.isPresent();
}
default -> {
return false;
}
}
})
.findFirst();
if (keyConfOptional.isPresent()) {
KeyConf keyConf = keyConfOptional.get();
indexKeyConfMap.put(k, keyConf);
log.info("keyConf = " + keyConf);
}
});
this.indexKeyConfMap = indexKeyConfMap;
}
/**
* 特殊数据 包装
*/
public void packSpecialKeyValue() {
// 特殊数据
Map<String, Object> specialMap = new HashMap<>();
List<SpecialKeyConf> keyConfList = this.executeExcelRule.getSpecialConfList();
if (CollectionUtils.isEmpty(keyConfList)) {
this.specialMap = specialMap;
return;
}
keyConfList.forEach(conf -> {
String keyType = conf.getKeyType();
switch (keyType) {
case "bo" -> {
specialMap.put(conf.getReKey(), boMap.getOrDefault(conf.getKey(), conf.getDefaultVale()));
}
case "default" -> {
String valueType = conf.getValueType();
switch (valueType) {
case "snowId" -> {
specialMap.put(conf.getReKey(), SnowflakeIdUtil.newId());
}
case "nowtime" -> {
specialMap.put(conf.getReKey(), LocalDateTime.now());
}
case "nowtimelong" -> {
specialMap.put(conf.getReKey(), LocalDateTime.now().getSecond());
}
default -> {
specialMap.put(conf.getReKey(), conf.getDefaultVale());
}
}
}
default -> {
}
}
});
this.specialMap = specialMap;
}
public Map<String, Integer> swapKeyValue(Map<Integer, String> headMap) {
Map<String, Integer> result = new HashMap<>();
for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
result.put(entry.getValue(), entry.getKey());
}
return result;
}
/**
* 获取解析规则
*
* @param readSheetHolder
* @param fileStandardExcelRuleList
* @return
*/
public FileStandardExcelRule getRule(ReadSheetHolder readSheetHolder, List<FileStandardExcelRule> fileStandardExcelRuleList) {
// 1、判断是否 都为默认规则
if (fileStandardExcelRuleList.size() == 1) {
FileStandardExcelRule rule = fileStandardExcelRuleList.get(0);
// 默认解析规则
if (MATCH_DEFAULT.equalsIgnoreCase(rule.getSheetMatch())) {
return rule;
}
}
// 2、非 默认规则,
// 从0开始
Integer sheetNo = readSheetHolder.getSheetNo();
String sheetName = readSheetHolder.getSheetName();
return fileStandardExcelRuleList.stream()
// 独立配置
.filter(rule -> MATCH_SINGLE.equalsIgnoreCase(rule.getSheetMatch()))
.filter(rule -> {
switch (rule.getSheetMatchModel()) {
case "index" -> {
return Objects.equals(sheetNo, rule.getSheetMatchIndex());
}
case "contain" -> {
return sheetName.contains(rule.getSheetMatchName());
}
case "regex" -> {
return sheetName.matches(rule.getSheetMatchRegex());
}
case "code" -> {
// 待 实现
return false;
}
default -> {
return false;
}
}
})
.findFirst()
.orElse(null);
}
/**
* 读取到额外信息时的回调方法。
*
* @param extra 额外信息
* @param context 当前数据读取的上下文对象
*/
@Override
public void extra(CellExtra extra, AnalysisContext context) {
LOGGER.info("读取到额外信息:{}", extra);
}
/**
* 判断是否还有下一行数据。
*
* @param context 当前数据读取的上下文对象
* @return 如果还有下一行数据,则返回 true;否则返回 false
*/
@Override
public boolean hasNext(AnalysisContext context) {
return true;
}
public static void main(String[] args) {
// 测试
String fileName = "/Users/local-store-path-prefix/2023-05/168415158912212673_forensic.xlsx";
TemplateAnalysisEventListener listener = new TemplateAnalysisEventListener();
EasyExcel.read(fileName, listener).password("").doReadAll();
}
}
/**
* CREATE TABLE `file_standard_excel_rule` (
* `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
* `fa_id` bigint NOT NULL COMMENT '方案ID',
* `sheet_match` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'sigel|default',
* `sheet_match_model` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'index|contain|regex|code',
* `sheet_match_index` int DEFAULT '0' COMMENT 'key索引位置',
* `sheet_match_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'sheet包含内容',
* `sheet_match_regex` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '正则表达式',
* `sheet_match_code` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Java代码',
* `key_conf` json NOT NULL COMMENT 'key解析配置\n[{"keys": ["订单号"], "reKey": "order_no", "keyType": "keys|”, "valueType": "string”},{“index”: 1, "reKey": "uid", "keyType": “index”, "valueType": "string"}]',
* `filter` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'xxjava-codexx',
* `kafka_topic` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'Kafka主题',
* `save_conf` json NOT NULL COMMENT '保存配置\n"save_conf":{\n "sql":"",\n "fields":[\n {\n “Index”:0,\n "reKey":""\n }\n ]\n }',
* `special_conf` json DEFAULT NULL COMMENT '业务配置,特殊字段,例如:file_id\n[{"key": "file_id", "reKey": "file_id", "keyType": "bo", "defaultVale": 0}, {"value_type": "snowId", "reKey": “id”, "keyType": "default”}]',
* PRIMARY KEY (`id`) USING BTREE
* ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Excel解析配置规则';
*/
3.3 快速生成配置工具类
@SpringBootTest
public class MainTest {
@Autowired
DataSource dataSource;
@Autowired
FileStandardExcelRuleService fileStandardExcelRuleService;
@Test
void test设备指纹() {
FileStandardExcelRule rule = new FileStandardExcelRule();
rule.setFaId(25L);
// 设置 sheet 匹配模型
rule.setSheetMatch("single");
rule.setSheetMatchModel("contain");
rule.setSheetMatchName("设备指纹");
// 设置 key 模板配置
List<KeyConf> keyConfList = new ArrayList<>();
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备是否使用网络代理"), "string", null, "network_proxy"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("是否越狱"), "string", null, "prison_break"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("SIM卡序列号"), "string", null, "sim_no"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IMSI"), "string", null, "imsi"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("SIM卡国家"), "string", null, "sim_country"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("运营商"), "string", null, "operate_business"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("GPS经度"), "string", null, "gps_lng"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("GPS纬度"), "string", null, "gps_lat"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("UID"), "string", null, "uid"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("系统型号"), "string", null, "ios_type"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("系统版本"), "string", null, "ios_version"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机品牌"), "string", null, "mobile_brand"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机型号"), "string", null, "mobile_model"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备序列号"), "string", null, "device_no"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备指纹"), "string", null, "device_finger"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("网络环境"), "string", null, "network_env"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机IMEI信息"), "string", null, "mobile_imei"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机自身设备的MAC"), "string", null, "mobile_mac"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("Android ID"), "string", null, "android_id"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("连接的无线名称"), "string", null, "wifi_name"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("连接的无线MAC地址"), "string", null, "wifi_mac"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IDFA"), "string", null, "idfa"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IDFV"), "string", null, "idfv"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("时间"), "datetime", null, "time"));
rule.setKeyConf(JSONObject.toJSONString(keyConfList));
// 设置 special 配置
List<SpecialKeyConf> specialKeyConf = new ArrayList<>();
specialKeyConf.add(new SpecialKeyConf("bo", "file_id", null, 0, "file_id"));
specialKeyConf.add(new SpecialKeyConf("bo", "area_code", null, "000000", "area_code"));
rule.setSpecialConf(JSONObject.toJSONString(specialKeyConf));
// 设置入库信息
SaveConf saveConf = new SaveConf();
saveConf.setSql("insert into hb_device_record (network_proxy,prison_break,sim_no,imsi,sim_country,operate_business,gps_lng,gps_lat,uid,ios_type,ios_version,mobile_brand,mobile_model,device_no,device_finger,network_env,mobile_imei,mobile_mac,android_id,wifi_name,wifi_mac,idfa,idfv,time,file_id,area_code) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
List<SaveConf.ColumnData> sccdlist = new ArrayList<>();
sccdlist.add(new SaveConf.ColumnData(0, "network_proxy"));
sccdlist.add(new SaveConf.ColumnData(1, "prison_break"));
sccdlist.add(new SaveConf.ColumnData(2, "sim_no"));
sccdlist.add(new SaveConf.ColumnData(3, "imsi"));
sccdlist.add(new SaveConf.ColumnData(4, "sim_country"));
sccdlist.add(new SaveConf.ColumnData(5, "operate_business"));
sccdlist.add(new SaveConf.ColumnData(6, "gps_lng"));
sccdlist.add(new SaveConf.ColumnData(7, "gps_lat"));
sccdlist.add(new SaveConf.ColumnData(8, "uid"));
sccdlist.add(new SaveConf.ColumnData(9, "ios_type"));
sccdlist.add(new SaveConf.ColumnData(10, "ios_version"));
sccdlist.add(new SaveConf.ColumnData(11, "mobile_brand"));
sccdlist.add(new SaveConf.ColumnData(12, "mobile_model"));
sccdlist.add(new SaveConf.ColumnData(13, "device_no"));
sccdlist.add(new SaveConf.ColumnData(14, "device_finger"));
sccdlist.add(new SaveConf.ColumnData(15, "network_env"));
sccdlist.add(new SaveConf.ColumnData(16, "mobile_imei"));
sccdlist.add(new SaveConf.ColumnData(17, "mobile_mac"));
sccdlist.add(new SaveConf.ColumnData(18, "android_id"));
sccdlist.add(new SaveConf.ColumnData(19, "wifi_name"));
sccdlist.add(new SaveConf.ColumnData(20, "wifi_mac"));
sccdlist.add(new SaveConf.ColumnData(21, "idfa"));
sccdlist.add(new SaveConf.ColumnData(22, "idfv"));
sccdlist.add(new SaveConf.ColumnData(23, "time"));
sccdlist.add(new SaveConf.ColumnData(24, "file_id"));
sccdlist.add(new SaveConf.ColumnData(25, "area_code"));
saveConf.setFields(sccdlist);
rule.setSaveConf(JSONObject.toJSONString(saveConf));
fileStandardExcelRuleService.save(rule);
}
public static void main(String[] args) {
print1();
}
public static void print7() {
List<KeyConf> keyConfList = new ArrayList<>();
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备是否使用网络代理"), "string", null, "network_proxy"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("是否越狱"), "string", null, "prison_break"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("SIM卡序列号"), "string", null, "sim_no"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IMSI"), "string", null, "imsi"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("SIM卡国家"), "string", null, "sim_country"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("运营商"), "string", null, "operate_business"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("GPS经度"), "string", null, "gps_lng"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("GPS纬度"), "string", null, "gps_lat"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("UID"), "string", null, "uid"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("系统型号"), "string", null, "ios_type"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("系统版本"), "string", null, "ios_version"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机品牌"), "string", null, "mobile_brand"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机型号"), "string", null, "mobile_model"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备序列号"), "string", null, "device_no"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("设备指纹"), "string", null, "device_finger"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("网络环境"), "string", null, "network_env"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机IMEI信息"), "string", null, "mobile_imei"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("手机自身设备的MAC"), "string", null, "mobile_mac"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("Android ID"), "string", null, "android_id"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("连接的无线名称"), "string", null, "wifi_name"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("连接的无线MAC地址"), "string", null, "wifi_mac"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IDFA"), "string", null, "idfa"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("IDFV"), "string", null, "idfv"));
keyConfList.add(new KeyConf("keys", null, Collections.singleton("时间"), "datetime", null, "time"));
print("设备指纹", keyConfList);
}
public static void print(String name, List<KeyConf> keyConfList) {
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < keyConfList.size(); i++) {
KeyConf keyConf = keyConfList.get(i);
stringBuilder.append(String.format("sccdlist.add(new SaveConf.ColumnData(%s, \"%s\"));", i, keyConf.getReKey()));
stringBuilder.append("\n");
}
stringBuilder.append(String.format("sccdlist.add(new SaveConf.ColumnData(%s, \"%s\"));", keyConfList.size(), "file_id"));
stringBuilder.append("\n");
stringBuilder.append(String.format("sccdlist.add(new SaveConf.ColumnData(%s, \"%s\"));", keyConfList.size() + 1, "area_code"));
System.out.println(name + ": \n" + stringBuilder.toString());
// 输出 插入sql
printSave(name, keyConfList);
}
public static void printSave(String name, List<KeyConf> keyConfList) {
StringBuilder stringBuilder = new StringBuilder("insert into xxx (");
for (int i = 0; i < keyConfList.size(); i++) {
KeyConf keyConf = keyConfList.get(i);
stringBuilder.append(keyConf.getReKey());
if (i + 1 != keyConfList.size()) {
stringBuilder.append(",");
} else {
stringBuilder.append(",file_id,area_code) ");
}
}
stringBuilder.append("values(");
for (int i = 0; i < keyConfList.size(); i++) {
if (i + 1 != keyConfList.size()) {
stringBuilder.append("?,");
} else {
stringBuilder.append("?,?,?) ");
}
}
// 输出 插入sql
System.out.println(name + " - saveDb: \n" + stringBuilder.toString() + "\n");
}
}