java动态导入excel按照表头生成数据库表
package com.wang.test.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.wang.test.mapper.WaterMeterMapper;
import lombok.SneakyThrows;
import org.springframework.scheduling.annotation.Async;
import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;
/**
* @BelongsPackage: com.wang.test.listener
* @Author: wangqian
* @CreateTime: 2024-01-24 09:51:59
* @Describe:
*/
public class ConfigFilterListener implements ReadListener<LinkedHashMap<String, String>> {
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 1000;
private String tableName;//表名
private String columnNames;//字段名
private List<LinkedHashMap<String, String>> dataSetList = new ArrayList<>();
@Resource
private WaterMeterMapper waterMeterMapper;
//构造函数
public ConfigFilterListener(WaterMeterMapper waterMeterMapper) {
this.waterMeterMapper = waterMeterMapper;
}
/**
* 这个每一条数据解析都会来调用
* 这个接口作用是将excel数据全部添加到dataSetList中，然后达到BATCH_COUNT的时候触发新增数据操作
*/
@SneakyThrows
@Override
public void invoke(LinkedHashMap<String, String> linkedHashMap, AnalysisContext analysisContext) {
//log.info("解析到一条数据:{}", linkedHashMap);
LinkedHashMap<String, String> map = new LinkedHashMap<>();
map.put("uuid", UUID.randomUUID().toString());
Set set = linkedHashMap.keySet();
Iterator iterator = set.iterator();
while (iterator.hasNext()) {
Object next = iterator.next();
map.put(next.toString(), linkedHashMap.get(next));
}
dataSetList.add(map);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (dataSetList.size() >= BATCH_COUNT) {
//创建插入语句
StringBuffer sb = new StringBuffer("insert into ");
sb.append(this.tableName + " (");
sb.append(this.columnNames + " )");
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
batchInsert(sb.toString(), dataSetList);
// 存储完成清理 list
dataSetList.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*这个方法的作用就是将excel数据插入到生成的表中
如果数据大于BATCH_COUNT，则不足与BATCH_COUNT的会做新增操作
* @param analysisContext
*/
// @SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (dataSetList.size() > 0) {
//创建插入语句
StringBuffer sb = new StringBuffer("insert into ");
sb.append(this.tableName + " (");//表名称
sb.append(this.columnNames + " )");//插入的数据
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
batchInsert(sb.toString(), dataSetList);
dataSetList.clear();
}
}
/***
* 读取Excel表格表头
* 这个方法的作用就是获取表头，创建数据库表
* @param headMap
* @param context
*/
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
try {
//每次执行前需求清除上次的结果
this.columnNames = null;
// 当前sheet的名称 编码获取类似
String tableName = context.readSheetHolder().getSheetName();
int tableCount = waterMeterMapper.existsTable(tableName);//判断表名是否存在
List<String> heads = new ArrayList<>();
heads.add("uuid");
if (tableCount > 0) {//按照业务需求是创建新表还是提示错误
tableName += "_"+System.currentTimeMillis();
}
StringBuffer createTableStr = new StringBuffer("CREATE TABLE ");
createTableStr.append(tableName);
createTableStr.append(" (uuid varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,");
Collection<ReadCellData<?>> values = headMap.values();
//这个是我自己写的，大家按照自己的需求来设置
for (int i = 0; i < values.size(); i++) {
createTableStr.append("column_"+(i+1) + " varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,");
heads.add("column_"+(i+1));
}
//这个value.getStringValue()会获取表头的数据，生成的表字段则是按照表头配置的
// for (ReadCellData<?> value : values) {
// createTableStr.append(value.getStringValue() + " varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,");
// heads.add(value.getStringValue());
// }
createTableStr.append("PRIMARY KEY (`uuid`) USING BTREE)");
int updateCount = waterMeterMapper.createTable(createTableStr.toString());
if (updateCount != 0) {
throw new RuntimeException("创建数据库表失败！");
}
//创建成功后，得插入一条对应记录
// createDataSets(tableName, tableName, uuid, sysUser, dataType, sort, addressType);
this.tableName = tableName;
this.columnNames = heads.stream().collect(Collectors.joining(","));
} catch (Exception ex) {
//waterMeterService.removeById(uuid);
//throw new RuntimeException("导入失败！请联系管理员！");
}
}
@Override
public boolean hasNext(AnalysisContext context) {
return true;
}
@Async
public void batchInsert(String tableString, List<LinkedHashMap<String, String>> list){
try {
waterMeterMapper.insertTableData(tableString, list);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}