Springboot整合EasyExcel，实现Excel文件上传

utopia

发布于 2023-03-21 10:53:55

1.3K0

发布于 2023-03-21 10:53:55

文章被收录于专栏：UtopiaUtopia

一、概念

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。它能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。EasyExcel是在尽可能节约内存的情况下支持读写百M的Excel。

二、Excel的上传(读Excel)

1.Excel读取的实现方案

实现Springboot结合EasyExcel实现对Excel中数据的读取，并且将读取的数据通过Mybatis-plus保存到Mysql数据库。

2. maven依赖，pom文件

<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.8</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.76</version> </dependency> </dependencies>

3. pom文件，加载xml文件

由于用到MyBatisplus，所以一定不要忘记加下面的这段代码，否则你的mapper是编译不到你的classpath中的。

<build>  <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> </resources> </build>

4. application.yml文件配置mybatis-plus

server: port: 80 spring: datasource: druid: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/redhorse?serverTimezone=UTC username: root password: root mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

5. 表格和表对应的实体类

既然要读取Excel，同时存入数据库，那么就必然需要对应的表，以及表对应的实体类，而Excel也需要对应的实体类。因为Excel表格会增加一些不必要的字段，而这些字段并不需要存入数据库中，同理数据库实体类同样存在一些字段不是从表格中获取。

5.1 表格对应的实体类

package com.atorientsec.entities;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import lombok.Data;

@Data
public class ExcelAttdnOver {

    @ExcelProperty(index =2)
    private String department;

    @ExcelProperty(index = 0)
    private String name;

    /**
     * 这里用String去接日期，才能格式化，接收年月日的格式
     */
    @ExcelProperty(index = 3)
    @DateTimeFormat(value = "yyyy-MM-dd")
    private String overDate;

    @ExcelProperty(index = 4)
    private Double overHours;

    /**
    *接收百分比的数字
    */
    @ExcelProperty(index = 7)
    @NumberFormat("#.##%")
    private String rate;
}

5.2 数据库对应的实体类

package com.atorientsec.entities;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

@Data
public class AttdnOver {

    @TableId(type = IdType.AUTO)
    private Integer id;

    private String department;

    private String name;

    private String attdnMonth;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date overDate;

    private Double overHours;


    private Double rate;
}

注释：数据库实体类的overDate是Date类型，而Excel对应的类中overDate是String类型，只有String去接日期才能格式化。

6. 默认一行行的读取excel，所以需要创建excel一行一行的回调监听器

package com.atorientsec.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.atorientsec.entities.AttdnOver;
import com.atorientsec.entities.ExcelAttdnOver;
import com.atorientsec.service.AttdnOverService;
import lombok.extern.slf4j.Slf4j;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class AttdnDataListener implements ReadListener<ExcelAttdnOver> {
    /**
     * Excel模板的读取类
     * 有个很重要的点，AttdnDataListener不能被Spring管理
     * 要每次读取excel都要new，然后里面用到spring可以构造方法传进去
     * @param excelAttdnOver
     * @param analysisContext
     */

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    private int count = 0;
    /**
     * 缓存的数据,List<AttdnOver>
     */
    private List<AttdnOver> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private AttdnOverService attdnOverService;
    private String month;

    public AttdnDataListener(){

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

    /**
     * 这个每一条数据解析都会来调用
     *
     */
    @Override
    public void invoke(ExcelAttdnOver excelAttdnOver, AnalysisContext analysisContext) {
        log.info("解析到第 {} 条数据:{}", (++count), JSON.toJSONString(excelAttdnOver));
        try {
            //把表格对应的实体类对象转化成数据库表对应的对象
            AttdnOver attdnOver = new AttdnOver();
            attdnOver.setDepartment(excelAttdnOver.getDepartment());
            attdnOver.setName(excelAttdnOver.getName());
            attdnOver.setAttdnMonth(this.month);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse(excelAttdnOver.getOverDate());
            attdnOver.setOverDate(date);
            attdnOver.setOverHours(excelAttdnOver.getOverHours());
            attdnOver.setRate(Double.parseDouble(excelAttdnOver.getRate().replace("%", "")));
            cachedDataList.add(attdnOver);
            if(cachedDataList.size()>=BATCH_COUNT){
                saveData();
                // 存储完成清理 list
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }
    }
    /**
     * 接收表头信息
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        Map<Integer,String> StringMap = ConverterUtils.convertToStringMap(headMap,context);
        Set<Integer> keySet = StringMap.keySet();
        System.out.println("该Excel表头信息是：");
        for(int i=0;i<keySet.size();i++){
            System.out.println("第 "+(i+1)+" 列 = "+StringMap.get(i));
        }
        ReadListener.super.invokeHead(headMap, context);
    }
    */
    /**
     * 所有数据解析完成了 都会来调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("表格中的所有数据解析完成！！！");
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }

    /**
     * 存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        attdnOverService.batchSave(cachedDataList);
        log.info("存储数据库成功！");
    }
}

7. mapper-继承Mybatis-plus的baseMapper

package com.atorientsec.mapper;

import com.atorientsec.entities.AttdnOver;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface AttdnOverMapper extends BaseMapper<AttdnOver> {

}

8. service：Mybatis-plus实现批量插入，并开启事务

package com.atorientsec.service;

import com.atorientsec.entities.AttdnOver;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;

public interface AttdnOverService extends IService<AttdnOver> {

    @Transactional
    boolean batchSave(ArrayList<AttdnOver> attdnOverArrayList);
}

package com.atorientsec.service.impl;

import com.atorientsec.entities.AttdnOver;
import com.atorientsec.mapper.AttdnOverMapper;
import com.atorientsec.service.AttdnOverService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

import java.util.ArrayList;

@Service
public class AttdnOverServiceImpl extends ServiceImpl<AttdnOverMapper, AttdnOver> implements AttdnOverService {

    public boolean batchSave(ArrayList<AttdnOver> attdnOverArrayList){
        //saveBatch是mybatisplus的批量插入方法
        boolean status = saveBatch(attdnOverArrayList);
        return status;
    }
}

9. Controller：MultipartFile上传文件

EasyExcel读文件

package com.atorientsec.controller;
import com.alibaba.excel.EasyExcel;
import com.atorientsec.entities.ExcelAttdnOver;
import com.atorientsec.listener.AttdnDataListener;
import com.atorientsec.service.AttdnOverService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;

@RestController
@RequestMapping("/excel")
public class AttdnOverController {
    @Autowired
    private AttdnOverService attdnOverService;

    @PostMapping("/upload/{month}")
    public String upload(MultipartFile file, @PathVariable String month) throws IOException {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(file.getInputStream(), ExcelAttdnOver.class,new AttdnDataListener(attdnOverService,month))
                .sheet().headRowNumber(1).doRead();
        return "success";
    }
}