使用VUE+SpringBoot+EasyExcel整合导入导出数据

使用VUE+SpringBoot+EasyExcel 整合导入导出数据

创建一个普通的maven项目即可

项目目录结构

1 前端

存放在resources/static 下

index.html

2 数据库

CREATE TABLE `student` (
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `salary` decimal(10,4) DEFAULT NULL COMMENT '薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3 后端

3.1 contrller

StudentController

@Slf4j
@RestController
@CrossOrigin
@RequestMapping("/api/excel/")
public class StudentController {

    @Resource
    private StudentMapper studentMapper;

    @GetMapping("list")
    public HashMap list(@RequestParam int pageNum,@RequestParam int pageSize){
        // 分页查询
        Page page = new Page<>(pageNum, pageSize);
        studentMapper.selectPage(page,null);
        
        // 封装数据
        HashMap map = new HashMap<>();
        ArrayList excelDictDTOList = new ArrayList<>();
        // 转换数据
        page.getRecords().forEach(student -> {
            ExcelStudentDTO studentDTO = new ExcelStudentDTO();
            BeanUtils.copyProperties(student,studentDTO);
            excelDictDTOList.add(studentDTO);
        });
        
        map.put("list",excelDictDTOList);
        map.put("size",page.getTotal());
        return map;
    }

    /**
     * 导入
     * @param file 文件对象
     */
    @RequestMapping("import")
    @Transactional(rollbackFor = {Exception.class})
    public String importData( @RequestParam("file") MultipartFile file){
        try {
            // 读取文件流
            EasyExcel.read
                    (file.getInputStream(),// 前端上传的文件
                            ExcelStudentDTO.class,// 跟excel对应的实体类
                            new ExcelDictDTOListener(studentMapper))// 监听器 
                    .excelType(ExcelTypeEnum.XLSX)// excel的类型
                    .sheet("模板").doRead();
            log.info("importData finished");
        } catch (IOException e) {
           log.info("失败");
           e.printStackTrace();
        }
        return "上传成功";
    }

    /**
     * 导入
     */
    @GetMapping("export")
    public String exportData(HttpServletResponse response){

        try {
            // 设置响应体内容
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("myStu", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream()
                    ,ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "上传成功";
    }

}

3.2 mapper

StudentMapper

@Mapper
public interface StudentMapper extends BaseMapper {
    void insertBatch(List list);
}

StudentMapper.xml




  
    insert into student(name, birthday, salary)
    values
    
               (
        #{item.name} ,
        #{item.birthday} ,
        #{item.salary} 
        )

3.3 bean

ExcelStudentDTO

导入数据时要保证excel中列名和ExcelStudentDTO一致奥

/**
 * excel对应的实体类
 * @author jiejie
 */
@Data
public class ExcelStudentDTO {
	// excel中的列名
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("生日")
    private Date birthday;

    @ExcelProperty("薪资")
    private BigDecimal salary;
}

Student

/**
 * 数据库对应的实体类
 * @author jiejie
 */
@Data
@TableName(value = "student")
public class Student {
    /**
     * 姓名
     */
    @TableField(value = "name")
    private String name;

    /**
     * 生日
     */
    @TableField(value = "birthday")
    private Date birthday;

    /**
     * 薪资
     */
    @TableField(value = "salary")
    private BigDecimal salary;

    public static final String COL_NAME = "name";

    public static final String COL_BIRTHDAY = "birthday";

    public static final String COL_SALARY = "salary";
}

3.3 listener

官方文档

EasyExcel读取文件需要用到

ExcelDictDTOListener

/**
 * 监听
 * 再读取数据的同时 对数据进行插入操作
 * @author : look-word
 * @date : 2022-05-10 21:35
 **/
@Slf4j
//@AllArgsConstructor //全参
@NoArgsConstructor //无参
public class ExcelDictDTOListener extends AnalysisEventListener {


    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();

    private StudentMapper studentMapper;

    //传入mapper对象
    public ExcelDictDTOListener(StudentMapper studentMapper) {
        this.studentMapper = studentMapper;
    }

    /**
     *遍历每一行的记录
     * @param data
     * @param context
     */
    @Override
    public void invoke(ExcelStudentDTO data, AnalysisContext context) {
        log.info("解析到一条记录: {}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        studentMapper.insertBatch(list);  //批量插入
        log.info("存储数据库成功！");
    }
}

3.5 config

mybatisPlus分页插件

MybatisPlusConfig

@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,
     * 需要设置 MybatisConfiguration#useDeprecatedExecutor = false
     * 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginatiOnInnerInterceptor= new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

3.6 配置文件

application.yaml

server:
  port: 8811
spring:
  datasource: # mysql数据库连接
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezOne=GMT%2B8&characterEncoding=utf-8
    username: root
    password: 317311
mybatis-plus:
  configuration:# sql日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations:
    - classpath:mapper/*.xml