easyexcel导出excel(easyexcel复杂导出)

前言代码实现

POM 依赖application.yamlApplication 启动类Config 相关配置类创建员工信息表导入和导出实体Controller 层Service 层easyExcel 导入导出监听器和转换器（重中之重）

导入监听器导出监听器转换器 Mapper 层mapper.xml

之前分享过一篇 easyExcel 实现导入导出的问题，最近对语雀社区多阅读了一下，想着用最新升级的版本重新做一个。框架和细节我都做了一点改造，希望各位使用这个框架的时候能收获更多。

同时温馨提示：我只会认真分享知识。作为IT这一行，不要去轻信什么P7、P8，什么架构师培训，不要被网络培训机构整的噱头骗了钱财，给你上课的人，可能自己终身都没有进过大厂，讲解的东西都是官网社区水分过来的，不要钱财骗了，时间浪费了。

我们自己自学，踏实跟着官网社区做，好好找一些实用的资料，他不香吗？

好了，话不多说，上干货。如果觉得好，请不要吝啬你的评论。

创建 springboot 项目的过程再此省略。
采用技术：
数据库：mysql
框架：springboot、mybatis-plus、easyExcel
数据连接池：durid
接口文档：knife4j，swagger的升级版

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.6.4
         
    
    com.example
    springBoot-easyExcel
    0.0.1-SNAPSHOT
    springBoot-easyExcel
    springBoot-easyExcel
    
        1.8
    
    
        
            org.springframework.boot
            spring-boot-starter-web
        

        
            mysql
            mysql-connector-java
            runtime
        

        
            org.projectlombok
            lombok
            true
        

        
            org.springframework.boot
            spring-boot-starter-test
            test
        

        
            com.baomidou
            mybatis-plus-boot-starter
            3.5.1
        

        
            com.alibaba
            druid-spring-boot-starter
            1.2.8
        

        
            com.alibaba
            easyexcel
            3.0.5
        

        
            com.alibaba
            fastjson
            1.2.79
        

        
        
            com.github.xiaoymin
            knife4j-spring-boot-starter
            2.0.9
        

        
            cn.hutool
            hutool-all
            5.7.22
        

    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
                2.6.4
                
                    
                        
                            org.projectlombok
                            lombok
                        
                    
                
            
        
    

server:
  port: 8888
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      initial-size: 10
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
  servlet:
    multipart:
      max-file-size: 200MB
      max-request-size: 200MB
  # SpringBoot2.6.0和 swagger冲突问题：
  # 原因是在springboot2.6.0中将SpringMVC 默认路径匹配策略从AntPathMatcher 更改为 PathPatternParser，
  # 导致出错，解决办法是切换回原先的AntPathMatcher
  mvc:
    pathmatch:
      matching-strategy: ant_path_matcher

mybatis-plus:
  mapper-locations: classpath*:/mapper
    public String getRandomName(){
        String[] doc = {"朝歌晚酒", "都怪时光太动听", "笑我孤陋", "水墨青花","时光清浅", "草帽撸夫", "江山如画",
        "热度不够", "盏茶浅抿", "把酒临风", "且听风吟", "梦忆笙歌", "倾城月下", "清风墨竹", "自愈心暖", "几许轻唱",
        "平凡之路", "半夏倾城", "南栀倾寒", "孤君独战", "温酒杯暖", "眉目亦如画", "旧雪烹茶", "律断华章", "清酒暖风",
        "清羽墨安", "一夕夙愿", "南顾春衫", "和云相伴", "夕颜若雪", "时城旧巷", "梦屿千寻"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    
    public String getRandomGender(){
        String[] doc = {"0", "1"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    
    public int getRandomAge(){
        int[] doc = {16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    public String getRandomEducation(){
        String[] doc = {"0", "1", "2", "3"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }

    public String getRandomDP(){
        String[] doc = {"行政部", "财务部", "技术部", "市场部", "公关部"};
        int index = (int) (Math.random() * doc.length);
        return doc[index];
    }
}

importListener.java

package cn.com.easyExcel.excel.listener;

import cn.com.easyExcel.mapper.baseDaoMapper;
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 lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.annotation.Async;

import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class importListener implements ReadListener {

    private final baseDaoMapper baseDaoMapper;

    
    private static final int BATCH_COUNT = 100;
    
    private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private final AtomicInteger count = new AtomicInteger(0);

    public importListener(baseDaoMapper baseDaoMapper) {
        this.baseDaoMapper = baseDaoMapper;
    }

    @Override
    public void invoke(T entity, AnalysisContext analysisContext) {
        count.addAndGet(1);

        log.info("解析到一条数据:{}", JSON.toJSONString(entity));
        cachedDataList.add(entity);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            batchInsert();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        batchInsert();
        log.info("所有数据解析完成！");
    }

    @Async
    public void batchInsert() {
        log.info("{}条数据，开始存储数据库!", count.get());
        baseDaoMapper.batchInsert(cachedDataList);
        log.info("存储数据库成功!");
    }
}

ExportListener.java

package cn.com.easyExcel.excel.listener;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.baseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class ExportListener {

    private baseMapper baseMapper;

    public ExportListener(baseMapper baseMapper) {
        this.baseMapper = baseMapper;
    }

    private static final String DATA_FORMAT = "yyyy-MM-dd-HH-mm-ss";

    private static final String CHARACTER_UTF_8 = "UTF-8";

    private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    private static final String CONTENT_DISPOSITION = "Content-Disposition";

    private static final String CACHE_ConTROL = "Cache-Control";

    private static final String NO_STORE = "no-store";

    private static final String MAX_AGE = "max-age=0";

    private static final Integer PAGE_SIZE = 10000;

    public void exportExcel(HttpServletResponse response, String sheetName, Class pojoClass,
                            QueryWrapper queryWrapper) throws IOException {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DATA_FORMAT);
        String nowTime = formatter.format(LocalDateTime.now());

        response.setContentType(CONTENT_TYPE);
        //设置字符集为utf-8
        response.setCharacterEncoding(CHARACTER_UTF_8);

        String fileName = sheetName.concat("_").concat(nowTime).concat(".xlsx");

        //用postman测正常，浏览器多了filename_=utf-8等字样
        response.setHeader(CONTENT_DISPOSITION,
                "attachment;filename=" + URLEncoder.encode(fileName, CHARACTER_UTF_8)
                        + ";filename*=utf-8''" + URLEncoder.encode(fileName, CHARACTER_UTF_8));

        //postman测会乱码，但浏览器下载就正常
//        response.setHeader(CONTENT_DISPOSITION,
//                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        //发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据
        response.setHeader(CACHE_CONTROL, NO_STORE);
        response.addHeader(CACHE_CONTROL, MAX_AGE);

        // 这里 需要指定写用哪个class去写
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out, pojoClass).build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();

        int totalCount = Math.toIntExact(baseMapper.selectCount(queryWrapper));

        int pageNumber = (int) Math.ceil((double) totalCount / (double) PAGE_SIZE);    //分页条数看情况

        // 去调用写入,根据数据库分页的总的页数来
        for (int i = 1; i <= pageNumber; i++) {
            //先定义一个空集合每次循环使他变成null减少内存的占用
            List pageList = new ArrayList<>();
            Page page = new Page<>(i, PAGE_SIZE);
            Page cityIPage = baseMapper.selectPage(page, queryWrapper);
            pageList = cityIPage.getRecords();
            excelWriter.write(pageList , writeSheet);
            pageList.clear();
        }
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
        out.flush();
    }
}

EducationConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

import java.util.HashMap;
import java.util.Map;

public class EducationConverter implements Converter {

    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    
    @Override
    public String convertToJavaData(ReadConverterContext context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            Map edMap = getEducation();
            for (Map.Entry entry : edMap.entrySet()) {
                if (readCellValue.equals(entry.getValue())) {
                    readCellValue = entry.getKey();
                }
            }
        }
        return readCellValue;
    }

    
    @Override
    public WriteCellData convertToExcelData(WriteConverterContext context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = getEducation().get(cellValue);
        }
        return new WriteCellData<>(cellValue);
    }

    public Map getEducation(){
        Map edMap = new HashMap<>();
        edMap.put("0", "大专");
        edMap.put("1", "本科");
        edMap.put("2", "硕士");
        edMap.put("3", "研究生");
        return edMap;
    }
}

GenderConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

public class GenderConverter implements Converter {

    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    
    @Override
    public String convertToJavaData(ReadConverterContext context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            readCellValue = ("男").equals(readCellValue) ? "1":"0";
        }
        return readCellValue;
    }

    
    @Override
    public WriteCellData convertToExcelData(WriteConverterContext context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = ("1").equals(context.getValue()) ? "男":"女";
        }
        return new WriteCellData<>(cellValue);
    }
}

MaritalStatusConverter.java

package cn.com.easyExcel.excel.converter;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

public class MaritalStatusConverter implements Converter {

    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    
    @Override
    public String convertToJavaData(ReadConverterContext context) {
//        return context.getReadCellData().getStringValue();
        String readCellValue = context.getReadCellData().getStringValue();
        if(StrUtil.isNotEmpty(readCellValue)){
            readCellValue = ("已婚").equals(readCellValue) ? "1":"0";
        }
        return readCellValue;
    }

    
    @Override
    public WriteCellData convertToExcelData(WriteConverterContext context) {
        String cellValue = context.getValue();
        if(StrUtil.isNotEmpty(cellValue)){
            cellValue = ("1").equals(context.getValue()) ? "已婚":"未婚";
        }
        return new WriteCellData<>(cellValue);
    }
}

baseDaoMapper.java

package cn.com.easyExcel.mapper;

import com.baomidou.mybatisplus.core.mapper.baseMapper;

import java.util.List;

public interface baseDaoMapper extends baseMapper {
    void batchInsert(List list);
}

EmployeeMapper.java

package cn.com.easyExcel.mapper;

import cn.com.easyExcel.pojo.EmployeeExporter;
import java.util.List;

public interface EmployeeMapper extends baseDaoMapper {

    
    void batchInsert(List employees);
}

EmployeeMapper.xml

    
        INSERT INTO `employee` (
            `user_name`,
            `gender`,
            `age`,
            `birthday`,
            `marital_status`,
            `education`,
            `blood_type`,
            `mobile`,
            `department_name`,
            `national_area`,
            `province`,
            `city`,
            `id_card_number`,
            `personal_mail_box`
        )
        VALUES
        
            (
                #{employee.userName},
                #{employee.gender},
                #{employee.age},
                #{employee.birthday},
                #{employee.maritalStatus},
                #{employee.education},
                #{employee.bloodType},
                #{employee.mobile},
                #{employee.departmentName},
                #{employee.nationalArea},
                #{employee.province},
                #{employee.city},
                #{employee.idCardNumber},
                #{employee.personalMailBox}
            )
        
    

好了，分享结束。希望对各位有帮助。