easyExcel读excel文件创建表并导入数据

easyExcel读excel文件创建表并导入数据

1.添加maven依赖

		 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.4</version>
        </dependency>

2.创建NoModleDataListener

/**
 * @description: easyExcel 无对象读取excel listener
 * @author: wzg
 * @create: 2022-02-28
 **/


/**
 * 有个很重要的点 NoModleDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 */
@Slf4j
public class NoModleDataListener extends AnalysisEventListener<Map<Integer, String>>{


    private JdbcTemplate jdbcTemplate;

    /**
     * 每隔5000条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5000;
    List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
    private int i = 0;
    Map<Integer, String> fieldMap = new HashMap<>();

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param jdbcTemplate
     */
    public NoModleDataListener(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 每一条数据解析都会来调用
     * @param data
     * @param context
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
        i++;
    }


    /**
     * 这里会一行行的返回头，获取excel第一行数据作为创建表的字段
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        fieldMap = headMap;
        log.info("解析到一条头数据:{}", headMap);

        StringBuilder sb = new StringBuilder();
        sb.append("create table ").append(dataSet.getTableName()).append(" ( ");


        // 拼接字段
        for(Integer key: headMap.keySet()){
            sb.append(headMap.get(key)).append(" ").append("TEXT,");
        }

        // 去除最后一个逗号
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" )");
        log.info(sb.toString());

        // 创建表
        jdbcTemplate.execute(sb.toString());

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成，一共{}条数据！",i);
    }


    /**
     * 数据解析导入数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());

        // 拼接insert语句
        StringBuilder insertSql = new StringBuilder();
        insertSql.append(" INSERT INTO ");
        insertSql.append(dataSet.getTableName());
        insertSql.append(" (");
        // 字段StringBuilder
        StringBuilder columnSql = new StringBuilder();
        // 参数StringBuilder
        StringBuilder paramSql = new StringBuilder();
        for(int i = 0 ; i< fieldMap.size() ; i ++){
            columnSql.append(",");
            columnSql.append(fieldMap.get(i));
            paramSql.append(",");
            paramSql.append("?");
        }
        insertSql.append(columnSql.substring(1));
        insertSql.append(") VALUES (");
        insertSql.append(paramSql.substring(1));
        insertSql.append(")");

        // 插入参数
        List<Object[]> listStr = new ArrayList<>();
        for(int i = 0 ; i<list.size(); i++){
            Map<Integer, String> dataMap = list.get(i);
            String[] strings = new String[dataMap.size()];
            for(Integer key: dataMap.keySet()){
                strings[key] = dataMap.get(key);
            }
            listStr.add(strings);
        }

        // 数据入库
        jdbcTemplate.batchUpdate(insertSql.toString(),listStr);

        log.info("存储数据库成功！");
    }

}

3.ecxel导入实现方法

 
	 /**
     * 新增excel数据集
     *
     * @param file
     * @param datasourceId
     * @return
     */
    @Override
    public void addExcelDataSet(MultipartFile file,String datasourceId) throws IOException {
       
        //获取数据源
        DatabaseInfo databaseInfo = sjztBaseDatasourceDao.getSjztBaseDatasourceById(datasourceId);
        JdbcTemplate jdbcTemplate = DataSourceUtils.getJdbcTemplate(
                sjztBaseDatasourceService.databaseInfoPwdDecode(databaseInfo));

        // 读取excel文件、创建表并写入数据
        EasyExcel.read(file.getInputStream(), new NoModleDataListener(jdbcTemplate)).sheet().doRead();

    }

4.controller代码

  @ApiOperation(value = "excel导入", notes = "excel导入", httpMethod = "POST")
  @PostMapping(value = "/addExcel")
    public Result addExcel(@ApiParam(value = "excel文件") @RequestParam(value = "file") MultipartFile file,
                                            @ApiParam(value = "数据源ID") @RequestParam(value = "datasourceId") String datasourceId) throws IOException {
        sjztDataSetService.addExcelDataSet(file,datasourceId);
        return Result.SUCCESS;
    }