java动态导入excel按照表头生成数据库表

1、创建接口接收文件

//controller层
@PostMapping("/importExcel1")public void importExcel1(HttpServletRequest request, MultipartFile file) {try {waterMeterService.importExcel1(request,file);} catch (Exception e) {throw new RuntimeException(e);}}//service层
void importExcel1(HttpServletRequest request, MultipartFile file);//实现类层
@Overridepublic void importExcel1(HttpServletRequest request, MultipartFile file) {try {//获取用户信息String fileName = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));//文件后缀String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));//1.首先获取文件名生成一条记录String directoryName = StringUtils.isEmpty("sheet1") ? fileName : "sheet1";
//            String uuid = createDataSets(directoryName, null, excelReq.getId(), sysUser, excelReq.getDataType(), excelReq.getAddressType());String uuid = UUID.randomUUID().toString();EasyExcel.read(file.getInputStream(), new ConfigFilterListener(waterMeterMapper)).sheet(0).doRead();} catch (IOException e) {throw new RuntimeException(e);}}

2、创建easyexcel导入监听器

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<>();@Resourceprivate WaterMeterMapper waterMeterMapper;//构造函数public ConfigFilterListener(WaterMeterMapper waterMeterMapper) {this.waterMeterMapper = waterMeterMapper;}/*** 这个每一条数据解析都会来调用* 这个接口作用是将excel数据全部添加到dataSetList中，然后达到BATCH_COUNT的时候触发新增数据操作*/@SneakyThrows@Overridepublic 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了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (dataSetList.size() >= BATCH_COUNT) {//创建插入语句StringBuffer sb = new StringBuffer("insert into ");sb.append(this.tableName + " (");sb.append(this.columnNames + " )");// 这里也要保存数据，确保最后遗留的数据也存储到数据库batchInsert(sb.toString(), dataSetList);// 存储完成清理 listdataSetList.clear();}}/*** 所有数据解析完成了 都会来调用*这个方法的作用就是将excel数据插入到生成的表中如果数据大于BATCH_COUNT，则不足与BATCH_COUNT的会做新增操作* @param analysisContext*/
//    @SneakyThrows@Overridepublic 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*/@Overridepublic 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("导入失败！请联系管理员！");}}@Overridepublic boolean hasNext(AnalysisContext context) {return true;}@Asyncpublic void batchInsert(String tableString, List<LinkedHashMap<String, String>> list){try {waterMeterMapper.insertTableData(tableString, list);} catch (Exception e) {throw new RuntimeException(e);}}
}

3、mapper层

 //判断表是否存在int existsTable(@Param("tableName") String tableName);//生成新的表int createTable(@Param("tableString")String tableString);void insertTableData(@Param("tableString") String tableString,@Param("dataSetList") List<LinkedHashMap<String, String>> dataSetList);

4、xml层

<!--    判断表是否存在--><select id="existsTable" resultType="java.lang.Integer">SELECT COUNT(*) FROM information_schema.tables WHERE table_name = #{tableName} AND table_schema = 'test';</select><!--    创建新表--><update id="createTable">${tableString}</update><!--    新增数据--><insert id="insertTableData">insert into ${tableString} values<foreach collection='dataSetList' item='line' index='index'  separator=','><foreach collection='line.values' item='value' open='(' separator=',' close=')'>#{value}</foreach></foreach></insert>

5、里面有些逻辑按照需求来处理，比如表重复，中间出现异常等
后面查询这些数据的时候，可以做配置表（建立一张表），文件名和表名做配置关系，如果要查询这些动态导入数据，则需要知道查哪些表，还有别的需求可以讨论，一起学习