easyPoi 导出，导入

1.maven依赖
 <!-- 集成EasyPOI -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
            <!-- 解决冲突问题 -->
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-full</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
2.实体类
package com.perye.dokit.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import javax.persistence.*;
import javax.validation.constraints.*;
import javax.persistence.Entity;
import javax.persistence.Table;
import org.hibernate.annotations.*;
import java.sql.Timestamp;
import java.io.Serializable;

/**

*/
@Entity
@Data
@Table(name="test")
public class Test implements Serializable {

    /** id */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Excel(name = "性别", needMerge = true, replace = {"男_1", "女_0"}, width = 15.0)
    private Integer id;

    /** 邮箱 */
    @Column(name = "email",nullable = false)
    @Excel(name = "邮箱", width = 15.0)
    private String email;

    /** 用户名 */
    @Column(name = "username",nullable = false)
    @NotBlank
    private String username;

    /** 创建时间 */
    @Column(name = "create_time")
    @Excel(name = "时间", format = "yyyy-MM-dd HH:mm:ss",  width = 20.0)
    @CreationTimestamp
    private Timestamp createTime;

    public void copy(Test source){
        BeanUtil.copyProperties(source,this, CopyOptions.create().setIgnoreNullValue(true));
    }
}
3.serviceimpl层
@Override
    public void testDownloadData(PageData pageData,HttpServletResponse response) throws Exception {
        List<Test> t = (List<Test>) dao.findForList("vueMapper.testDownloadData", pageData);
//  ================= 开始==================


        IWriter<Workbook> writer = ExcelExportUtil.exportBigExcel(new ExportParams(null, "测试"), Test.class);
        writer.write(t);
        Workbook workbook = writer.get();

        // 获取工作簿

        // ================= 结束输出 ==================
        // 输出
        FileUtil.responseWorkbook(response, workbook, "数据下载.xlsx");

    }     
4. 工具类 
  /**
     * easyPoi导出 Excel
     *
     * @param response
     * @param workbook
     * @param fileName
     */
    public static void responseWorkbook(HttpServletResponse response, Workbook workbook, String fileName) {

        try {
            response.setHeader("Content-disposition", "attachment;" + "filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/octet-stream; charset=UTF-8");
            workbook.write(response.getOutputStream());

            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }   
5. Controller层
  @Log("导出数据")
    @ApiOperation("导出数据")
    @GetMapping(value = "/testDownloadData")
    public void testDownloadData(HttpServletResponse response) throws Exception {
        PageData pageData = this.getPageData();
        testLiService.testDownloadData(pageData, response);
    }
    ------------------导入开始------------------------------   
    1.实体类
    package com.perye.dokit.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.sql.Timestamp;

/**
*/
@Getter
@Setter
public class FpequipmentExcel implements Serializable {


    @Excel(name = "设备编号",width = 20)
    private String assetNumber;


    @Excel(name = "设备名称",width = 20)
    private String deviceName;


    @Excel(name = "设备类型",width = 20)
    private String deviceType;


    @Excel(name = "规格型号",width = 20)
    private String specification;


    @Excel(name = "厂商",width = 20)
    private String manufacturer;


    @Excel(name = "管理部门",width = 20)
    private String department;


    @Excel(name = "使用年限",width = 20)
    private String serviceLife;


    @Excel(name = "使用情况",width = 20)
    private String usageCondition;


    @Excel(name = "购置日期",width = 20)
    private Timestamp purchaseDate;


    @Excel(name = "里程号",width = 20)
    private String mileageNumbe;


    @Excel(name = "是否可远控",width = 20)
    private String remoteControl;


    @Excel(name = "上下行线",width = 20)
    private String downLines;


    @Excel(name = "是否启用",replace = {"禁用_0", "启用_1"},width = 20 )
    private String enable;


    @Excel(name = "所属铁路",width = 20)
    private String resRailwayId;


    @Excel(name = "所属站防",width = 20)
    private String resTunnelId;


    @Excel(name = "所属区域",width = 20)
    private String resRegionId;


    @Excel(name = "所属子系统",width = 20)
    private String resSysId;


}
  2.导入业务层
  判断easyPoi 对象中属性是否全部为null再此定义
  https://blog.csdn.net/lang09/article/details/129062715
    //不解析空白行
    @Autowired
    private  ClassExcelVerifyHandler verifyHandler;
   /**
     * 导入excel
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    public PageData importFpEquipment(MultipartFile file) throws Exception {

        PageData  result = new PageData();
        //错位信息列表
        List<String> errorList  =new ArrayList<>();

        ImportParams params = new ImportParams();
        params.setVerifyHandler(verifyHandler);
        params.setTitleRows(0);
        params.setHeadRows(1);
        //params.setNeedVerify(true);//设置需要校验
        // 1、解析Excel里面的数据
        List<FpequipmentExcel> list = ExcelImportUtil.importExcel(file.getInputStream(),FpequipmentExcel.class, params);
       //验证表格中是否存在重复
        checkDepartmentCode(list, errorList);
        if(errorList.size()>0){
            result.put("data",errorList);
            result.put("code", HttpStatus.CREATED.value());
            result.put("message", "导入错误");
            return result;
        }

        //验证是否必填
        verifyEmpty(list, errorList);
         if(errorList.size()>0){
            result.put("data",errorList);
            result.put("code", HttpStatus.CREATED.value());
            result.put("message", "导入错误");
            return result;
          }

        //验证数据库中是否唯一
        verifyDs(list,errorList);
        if(errorList.size()>0){
            result.put("data",errorList);
            result.put("code", HttpStatus.CREATED.value());
            result.put("message", "导入错误");
            return result;
        }

      //如果 错误等于0则插入数据库
        if(errorList.size()==0){
            result.put("data","");
            result.put("code", HttpStatus.OK.value());
            result.put("message", "导入成功");
            return result;
        }

        return result;


    }
 2.1//验证表格中是否存在重复   
  /**
     * 检查重复
     * @param list
     */
    private void checkDepartmentCode(List<FpequipmentExcel> list,  List<String> errorList) {
        // 检查项目编码是否重复
        List<String> assetNumber = ListUtil.getDuplicateElements(list.stream().map(FpequipmentExcel::getAssetNumber).collect(Collectors.toList()));

        // 检查项目编码是否重复
        List<String> specification = ListUtil.getDuplicateElements(list.stream().map(FpequipmentExcel::getSpecification).collect(Collectors.toList()));

        if (assetNumber != null && assetNumber.size() > 0) {
            errorList.add("设备编号【" + assetNumber.get(0) + "】存在重复，请修正!");
        }
        if (specification != null && specification.size() > 0) {
            errorList.add("规格型号【" + assetNumber.get(0) + "】存在重复，请修正!");
        }

    }
    2.2验证表格中的数据是否必填
     /**
     *验证表格中的数据是否必填
     * @return
     * @throws Exception
     */
    public void verifyEmpty(List<FpequipmentExcel> list,List<String> errorList){
        //判断必填
        for (int i = 0; i <list.size() ; i++) {
          if(StrUtil.isBlank(list.get(i).getSpecification())){

              errorList.add("第"+(i+1)+"行规格型号必填");
          }
            if(ObjectUtil.isEmpty(list.get(i).getPurchaseDate())){
                errorList.add("第"+(i+1)+"行购置日期必填");
            }
        }
    }
    2.3验证数据库中是否重复
     /**
     *验证数据库中是否重复
     * @return
     * @throws Exception
     */
    public void verifyDs(List<FpequipmentExcel> list,List<String> errorList){

        for (int i = 0; i <list.size() ; i++) {

        }
    }
  3.导入数据控制层
   @Log("导入设备")
    @ApiOperation("导入设备")
    @PostMapping(value = "/importFpEquipment")
    public ResponseEntity<Object>  importFpEquipment(@RequestBody MultipartFile file) throws Exception {
        PageData pd = fpEquipmentService.importFpEquipment(file);
        return new ResponseEntity<>(pd,HttpStatus.OK);

    }