EasyExcel 导入、导出文件，参数校验，异常返回

导入数据

实体类

package net.rjgf.wbwf.nanning.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import net.rjgf.common.domain.PageEntity;

import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.Date;

/**
 * 
 *
 * @author 
 * @date 2022-08-12
 */
public class ImportDedicatedLine implements Serializable {
    private static final long serialVersionUID = 1L;

    @ExcelIgnore
    private PageEntity page;

    /**
     * 主键
     */
    @ExcelIgnore
    private Long id;

    /**
     * 业务主键
     */
    @NotNull(message = "业务标识*不能为空")
    @ExcelProperty(value = "业务标识*")
    private String businessId;

    /**
     * 专线类型
     */
    @NotNull(message = "专线类型*不能为空")
    @ExcelProperty(value = "专线类型*")
    private String type;
}

控制器层

    /**
     * 上传数据
     *
     * @param file 支持.xlsx
     * @return 提示消息
     */
    @PostMapping("/import")
    public Message importDedicatedLine(MultipartFile file) {
        return new Message(importDedicatedLineService.importDedicatedLine(file));
    }

业务层

  /**
     * 上传数据
     *
     * @param file 支持.xlsx
     * @return 提示消息
     */
    @Override
    public List<String> importDedicatedLine(MultipartFile file) {
        List<String> messageList = new ArrayList<String>();
        try {
            //文件流
            EasyExcel.read(file.getInputStream(),
                    //实体类
                    ImportDedicatedLine.class,
                    //监听器
                    new ImportDedicatedLineListener(importDedicatedLineRepository, securityUtils))
                    .sheet("专线").doRead();
        } catch (ExcelAnalysisException e) {
            StringTokenizer st = new StringTokenizer(e.getMessage(), "|");
            while (st.hasMoreElements()) {
                messageList.add(st.nextElement().toString());
            }
            return messageList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return messageList;
    }

监听器

package net.rjgf.wbwf.nanning.listen.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import net.rjgf.common.util.SecurityUtils;
import net.rjgf.wbwf.nanning.domain.ImportDedicatedLine;
import net.rjgf.wbwf.nanning.repository.ImportDedicatedLineRepository;
import net.rjgf.wbwf.nanning.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.validation.annotation.Validated;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.constraints.NotNull;
import java.text.MessageFormat;
import java.util.*;

/**
 * 
 *
 * @author 
 * @date 2022-08-12
 */
public class ImportDedicatedLineListener extends AnalysisEventListener<ImportDedicatedLine> {


    private ImportDedicatedLineRepository importDedicatedLineRepository;

    private SecurityUtils securityUtils;

    /**
     * 读取五百条数据，批量插入一次。
     */
    private static final int BATCH_COUNT = 500;

    /**
     * 记录Excel读取的行数
     */
    private int count = 0;

    /**
     * 保存读取成功的Excel数据
     */
    private List<ImportDedicatedLine> list = new ArrayList<>(BATCH_COUNT * 2);

    /**
     * 保存读取失败的Excel消息
     */
    private List<String> errorMessage = new ArrayList<>();

    /**
     * 行头
     */
    private Map headMap = new HashMap<>();

    /**
     * Sprinboot参数校验
     */
    private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    /**
     * 构造函数初始化监听器
     */
    public ImportDedicatedLineListener(ImportDedicatedLineRepository importDedicatedLineRepository, SecurityUtils securityUtils) {
        this.importDedicatedLineRepository = importDedicatedLineRepository;
        this.securityUtils = securityUtils;
    }


    /**
     * 获取表头名
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        this.headMap = headMap;
    }

	//利用SpringBoot自带的注解进行参数校验 
    public void validate(ImportDedicatedLine importDedicatedLine) {

        Set<ConstraintViolation<ImportDedicatedLine>> validate = validator.validate(importDedicatedLine);
        if (validate.size() > 0) {
            StringBuilder sb = new StringBuilder();
            validate.forEach(it -> {
                sb.append(it.getMessage());
                sb.append("\t");
            });
            throw new ExcelAnalysisException(sb.toString());
        } else {
            importDedicatedLine.setCreateBy(securityUtils.getCurrentUser().getId().toString());
            importDedicatedLine.setCreateTime(new Date());
            this.count = this.count + 1;
        }

    }

    /**
     * 解析到一条数据,对每条数据进行操作
     */
    @Override
    public void invoke(ImportDedicatedLine importDedicatedLine, AnalysisContext analysisContext) {

        validate(importDedicatedLine);

        list.add(importDedicatedLine);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }


    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        String mes = null;
        if (exception instanceof ExcelDataConvertException) {
            //转换格式发生的异常,不会进入invoke解析,这里要++ 把解析失败数据也加上
            this.count = this.count + 1;
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            // 获取列名
            String columnName = (String) this.headMap.get(excelDataConvertException.getColumnIndex());
            // 获取行
            int row = excelDataConvertException.getRowIndex();
            // 获取出错的单元块
            String cellData = excelDataConvertException.getCellData().toString();
            mes = MessageFormat.format("错误发生在{0}行,{1}列,数据为:{2}", (row + 1), columnName, cellData);
        }
        // 自定义抛出的异常
        else {
            mes = MessageFormat.format("错误发生在{0}行,原因为:{1},", this.count, exception.getMessage());
        }
        errorMessage.add(mes);
    }


    /**
     * 读取所有数据完毕进行的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (list.size() > 0) {
            saveData();
            list.clear();
        }
        StringBuilder sb = new StringBuilder();
        String countMessage = MessageFormat.format(
                "一共{0}条数据，成功导入{1}条数据，重复数据不会插入:",
                this.count, this.count - this.errorMessage.size());
        sb.append(countMessage);
        //将错误信息用字符串返回
        if (this.errorMessage.size() > 0) {
            errorMessage.forEach((it) -> {
                sb.append("|");
                sb.append(it);
            });
        }
        throw new ExcelAnalysisException(sb.toString());
    }

    /**
     * 具体执行读取到的excel数据进行存储的逻辑，比如写入到mysql等操作
     */
    private void saveData() {
        importDedicatedLineRepository.batchInsert(list);
    }

}

导出数据

这里导出到服务器硬盘

  @Override
    public String exportDedicatedLine(ImportDedicatedLine importDedicatedLine) {

        String fileName = "xxx" + DateUtil.getNowYYYYMMDDHHMMSS2() + ExcelTypeEnum.XLSX.getValue();
        String filePath = uploadDir + fileName;

        ExcelWriter excelWriter = EasyExcel.write(filePath).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "专线").head(ImportDedicatedLine.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

        //获取总数据量
        int count = importDedicatedLineRepository.getCount(importDedicatedLine);
        //最大导出数量
        int exportCount = 500;

        if (count > exportCount) {
            //分批查询数据库次数
            int countTime = count / exportCount + 1;
            for (int i = 0; i < countTime; i++) {
                Page page = PageHelper.startPage(i + 1, exportCount);
                List<ImportDedicatedLine> exportList = importDedicatedLineRepository
                        .selectImportDedicatedLineList(importDedicatedLine);
                excelWriter.write(exportList, writeSheet);
            }
        } else {
            List<ImportDedicatedLine> exportList = importDedicatedLineRepository
                    .selectImportDedicatedLineList(importDedicatedLine);
            excelWriter.write(exportList, writeSheet);
        }
        //刷新流
        excelWriter.finish();
        return fileName;
    }