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);

//实现类层
@Override
    public 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<>();

    @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);
        }
    }
}

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