EasyExcel快速上手

1、导入jar包

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.9</version>
        </dependency>

2、编写文本模板类

内容高度：@ContentRowHeight(14)
标题行高度：@HeadRowHeight(14)
列宽：@ColumnWidth(23)

在需要导入和导出的字段上加上以下注解，其中value代表标题，index代表列的索引值

@ExcelProperty(value= {"楼号"},index = 1)

再不需要导入和导出的字段上加上以下注解：

@ExcelIgnore

/**
 * 隔离房间信息管理(IsolationPeopleDetail)实体类的导入导出格式
 *
 * @author zyw
 * @since 2022-10-11 18:43:33
 */
@Data
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 53)
@ContentRowHeight(14)
@HeadRowHeight(14)
@ColumnWidth(23)
@AllArgsConstructor
@NoArgsConstructor
public class HotelRoomTemplate {
    /**
     * ID
     */
//    @ExcelIgnore
    @ExcelProperty(value= {"序号"},index = 0)
    private String id;
    /**
     * 隔离人员姓名
     */
    @ExcelProperty(value= {"楼号"},index = 1)
    private String buildingNo;
    /**
     * 国籍
     */
    @ExcelProperty(value= {"楼层"},index = 2)
    private String floor;
    /**
     * 证件类型  1:身份证 2： 护照
     */
    @ExcelProperty(value= {"房间号"},index = 3)
    private String roomId;
}

3、编写模板下载接口

**所有Excle导入都需要用统一的模板进行数据导入，且我们在程序中也需要对于导入Excle文件的格式和内容规范进行校验，同时我们还可以再导入业务逻辑中加入生成导入日志的功能，方便使用者尽快得知提交的文件中具体哪个位置的内容填写不规范，也可以使后台维护的工作人员能及时发现恶意的导入行为**

(1)通过解析文本模板类生成Excle对象

    @GetMapping(value = "/download")
    @ApiOperation(value="下载隔离酒店信息Excel模板",notes="下载隔离酒店信息Excel模板后端接口",response = String.class)
    public String download(HttpServletResponse response){
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode("隔离点房间模板", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), HotelRoomTemplate.class).registerWriteHandler(new HotelRoomWriteHandler()).sheet().doWrite(service.data());
            return  buildResultStr(buildSuccessResultData("下载隔离点房间信息Excel模板成功"));
        }catch (Exception e){
            log.error("下载隔离点房间信息模板失败",e);
            return buildResultStr(buildErrorResultData(e));
        }
    }

（2）编写一个模板修饰类，可以给模板增加下拉框，调整样式

//注册该普通Java类
@Component
public class HotelRoomWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { }

    //声明init()方法，进行初始化挂载
    @PostConstruct
    public void initJ(){
//        isoSpinnerWriteHandler = this;
//        isoSpinnerWriteHandler.jdEnterArriveTypeDao = this.jdEnterArriveTypeDao;
//        jdEnterArriveTypeDao = SpringUtil.getApplicationContext().getBean(JdEnterArriveTypeDao.class);
    }
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//        String[] nationality = new String[]{"中国", "中国港澳台", "外籍"};
//        String[] certificatesType = new String[]{"身份证", "港澳台通行证", "护照"};
//        String[] otherCertificatesType = new String[]{"港澳台通行证", "护照"};
        /*入境集中隔离、入境集中居家监测、入境密接、入境次密接、国内中高风险区、国内密接、国内次密接、其他*/
//        String [] personType = new String[]{"入境集中隔离","入境集中居家监测","入境密接","入境次密接","国内中高风险区","国内密接","国内次密接","其他"};

        //使用声明的该类的静态类变量，调用Dao层；
//        List<PersonType> personType1 = isoSpinnerWriteHandler.jdEnterArriveTypeDao.getPersonType();
//        List<String> list = new ArrayList<>();
//        for (int i = 0; i < personType1.size(); i++) {
//            list.add(personType1.get(i).getMeasures());
//        }
//        String[] personType = list.toArray(new String[list.size()]);

        /*String data = HttpUtil.get("https://www.jiankangjiande.cn/gateway/sysinfo/office/pub/getOfficeListByParentId?parentId=616fb07c6bb1499a8212829142bc0303");
        Map map = JSONObject.parseObject(data, Map.class);
        String rvs = JSONArray.toJSONString(map.get("resultData"));
        List<SystemOfficeVo> officeVoList = JSON.parseArray(rvs, SystemOfficeVo.class);*/
//
//        IsolatedPointHotelService isolatedPointHotelService = SpringUtil.getApplicationContext().getBean(IsolatedPointHotelService.class);
//        List<IsolatedPointHotel> isolatedPointHotels = isolatedPointHotelService.findList(new IsolatedPointHotel());


//        String[] isolatedPoint = new String[isolatedPointHotels.size()];
//        for (int i = 0; i < isolatedPointHotels.size(); i++) {
//            isolatedPoint[i] = isolatedPointHotels.get(i).getName();
//        }
        //String[] isolatedPoint = isolationPeopleDetailService.findHotelName();
        //String [] isolatedPoint = new String[]{"寿昌皇爵君廷","洋溪皇爵精品","寿昌秋林阁A","寿昌秋林阁B","寿昌玉温泉酒店","党校隔离点","红塘水库隔离点","梅城隔离点","乾潭隔离点"};
//        Map<Integer, String[]> mapDropDown = new HashMap<>();
//        mapDropDown.put(1, nationality);
//        mapDropDown.put(2, certificatesType);
//        mapDropDown.put(4, otherCertificatesType);
//        mapDropDown.put(10, personType);
//        mapDropDown.put(17, isolatedPoint);
//        Sheet sheet = writeSheetHolder.getSheet();

//        ///开始设置下拉框
//        DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
//        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
//            /***起始行、终止行、起始列、终止列**/
//            CellRangeAddressList addressList = new CellRangeAddressList(2, 1000, entry.getKey(), entry.getKey());
//            /***设置下拉框数据**/
//            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
//            DataValidation dataValidation = helper.createValidation(constraint, addressList);
//            /***处理Excel兼容性问题**/
//            if (dataValidation instanceof XSSFDataValidation) {
//                dataValidation.setSuppressDropDownArrow(true);
//                dataValidation.setShowErrorBox(true);
//            } else {
//                dataValidation.setSuppressDropDownArrow(false);
//            }
//            sheet.addValidationData(dataValidation);
//        }
    }

}

（3）在doWrite()方法中我们可以在模板中填入默认值

    public List<HotelRoomTemplate> data(){
        List<HotelRoomTemplate> list = new ArrayList<>();
        HotelRoomTemplate hrtOne = new HotelRoomTemplate("1","可不填","1楼","101");
        HotelRoomTemplate hrtTwo = new HotelRoomTemplate("2","可不填","1楼","102");
        HotelRoomTemplate hrtThree = new HotelRoomTemplate("3","可不填","1楼","103");
        HotelRoomTemplate hrtFour = new HotelRoomTemplate("4","可不填","1楼","104");
        list.add(hrtOne);
        list.add(hrtTwo);
        list.add(hrtThree);
        list.add(hrtFour);
        return list;
    }

(4)模板下载效果

4、编写Excle导入接口

思路：1、将Excle文件中我们需要的数据转换成对应的模板类泛型的Lsit集合

          2、再将其依次经过层层校验后插入数据库

          3、在校验的同时记录校验的过程，生成导入日志，插入日志表

          4、如果导入失败，接口中返回给用户文件内容错误的具体位置提示

    @PostMapping(value = "/uploadFile")
    @ApiOperation(value="上传Excel文件导入隔离酒店信息",notes="上传Excel文件导入隔离酒店信息",response = String.class)
    public String uploadFile(@ApiParam(name = "file",value = "选择文件", required = true) MultipartFile file,
                             @RequestParam @ApiParam(name="id",value="隔离点ID",required=true)String id){
        List<HotelRoomTemplate> list = new ArrayList<HotelRoomTemplate>();
        try {
            Assert.notNull(file,"文件不能为空");
            InputStream inputStream = file.getInputStream();
            //读数据
            try {
                list = EasyExcel.read(inputStream, HotelRoomTemplate.class, new HotelRoomDataListener()).
                        head(HotelRoomTemplate.class).sheet().doReadSync();
                Assert.notEmpty(list,"解析出的数据集为空，请检查文件的数据格式是否正确");
            }catch (Exception e){
                return buildResultStr(buildErrorResultData("导入数据格式不正确,请下载导入模板"));
            }
            //保存数据list = {ArrayList@8848}  size = 7
            return buildResultStr(buildSuccessResultData(service.saveData(list,id)));
        }catch (Exception e){
            log.error("上传文件失败",e);
            return buildResultStr(buildErrorResultData(e));
        }
    }

（1）编写数据侦听器

//注册该普通Java类
@Component
public class HotelRoomDataListener  extends AnalysisEventListener<HotelRoomTemplate> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);

    //声明该类自身的静态类变量
    private static HotelRoomDataListener hotelRoomDataListener;

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

    @Autowired
    private IsolationPeopleDetailService isolationPeopleDetailService;
    private static IsolationPeopleDetailService iisolationPeopleDetailService; //接收注入的service
    @Autowired
    private JudgeUtil judgeUtil;

    @PostConstruct
    public void initJ() {
        hotelRoomDataListener = this;
        hotelRoomDataListener.judgeUtil = this.judgeUtil;
        iisolationPeopleDetailService = isolationPeopleDetailService; //进行赋值,实际上调用的是ssysUserService
    }


//    @PostConstruct
//    public void initM(){
//        judgeUtil = SpringUtil.getApplicationContext().getBean(JudgeUtil.class);
//    }
    /*private IsolationPeopleDetailService isolationPeopleDetailService;
     *//**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param
     *//*
    public DemoDataListener(IsolationPeopleDetailService iso) {
        this.isolationPeopleDetailService = iso;
    }*/

    @SneakyThrows
    @Override
    public void invoke(HotelRoomTemplate data, AnalysisContext context) {
        //解析数据，对数据内容进行排查
        Map<String,Object> map = hotelRoomDataListener.judgeUtil.judgeHotelRoom(data);
        //检查数据是否正确
        String result = (String) map.get("result");
        if (StringUtils.isBlank(result)){
            //数据判断没问题，可以添加数据库
            IsolationPointRoom room = (IsolationPointRoom) map.get("room");
            saveData(room);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("所有数据解析完成！");
    }
    /*
     * 将数据保存在表中*/
    /**
     * 加上存储数据库
     */
    private void saveData(IsolationPointRoom iso) {
        /*//添加隔离对象
        if (iso.getIsolatedPoint().equals("洋溪皇爵精品酒店")){
            iso.setIsolatedPoint("2");
        }else {
            iso.setIsolatedPoint("1");
        }
        iso.setId(IdGen.uuid());
        iso.setInFrequency(1);
        isolationPeopleDetailService.addIsoD(iso);
        //改变隔离对象所在房间状态
        isolationPeopleDetailService.updateStatus(iso.getIsolatedPoint(),iso.getRoomNum());*/
    }
}

（2）封装具体的数据排查业务逻辑

 //对隔离对象数据的判断
    public Map<String,Object> judgeHotelRoom(HotelRoomTemplate data) throws Exception {
        Map<String, Object> map1 = new HashMap<>();
        String result = "";
        IsolationPointRoom room = new IsolationPointRoom();
        Map map = JSONObject.parseObject(JSON.toJSONString(data), Map.class);

        //楼层号
        String floor = (String) map.get("floor");
        if(StringUtils.isNotNullString(floor)){
            floor=floor.trim();
            room.setFloor(floor);
        }else {
            result+=",楼层不能为空";
        }

        //房间号
        String roomId = (String) map.get("roomId");
        if(StringUtils.isNotNullString(roomId)){
            roomId=roomId.trim();
            room.setRoomId(roomId);
        }else {
            result+=",房间号不能为空";
        }

        String buildingNo = (String) map.get("buildingNo");
        if(StringUtils.isNotNullString(buildingNo)){
            buildingNo=buildingNo.trim();
            room.setBuildingNo(buildingNo);
        }

        map1.put("result",result);
        if (result.equals("")){
            map1.put("room",room);
        }
        return map1;
    }

(3)编写数据插入以及生成导入日志的业务层方法

 public String saveData(List<HotelRoomTemplate> list,String id) throws Exception {
        //成功数  失败数  数据长度
        Integer success = 0,fail = 0, daLength = 10;
        List<HotelRoomTemplateJournal> das = new ArrayList<HotelRoomTemplateJournal>();
        HotelRoomTemplate hotelRoomTemplate = new HotelRoomTemplate();
        //批次号
        String bath = IdGen.uuid();
        String hotel = isolationPeopleDetailDao.getHotel(id).getName();
        IntegratedServicePoint integratedServicePoint = new IntegratedServicePoint();
        integratedServicePoint.preInsert();
        UserBase createBy = integratedServicePoint.getCreateBy();
        int i = isolationPointRoomDao.deleteForId(id);
        System.out.println("清除"+i+"条旧房间数据");

        //处理导入的数据
        try{
            Iterator<HotelRoomTemplate> iterator = list.iterator();
            while (iterator.hasNext()){
                HotelRoomTemplate data = iterator.next();
                hotelRoomTemplate.setRoomId(data.getRoomId());
                Map<String, Object> map = new JudgeUtil().judgeHotelRoom(data);
                //判断是否有数据错误
                String result = (String) map.get("result");
                if (result.equals("")){
                    success+=1;
                    IsolationPointRoom room = (IsolationPointRoom) map.get("room");
                    room.setName(hotel);
                    room.setStatus("1");
                    room.setHotelType(Integer.parseInt(id));

                    room.setDelFlag("0");
                    room.setCreateBy(createBy);
                    room.setCreateDate(new Date());
                    room.setUpdateBy(createBy);
                    room.setUpdateDate(new Date());

                    //添加隔离对象
                    room.setId(IdGen.uuid());
                    room.setFloor(room.getFloor().replaceAll("楼",""));
                    //覆盖之前的该酒店数据

                    System.out.println("room = " + room);
                    int insert = isolationPointRoomDao.insertRoom(room);
                    result = "导入成功";
                }else {
                    //截取第一个，字符串
                    result=result.substring(1);
                    fail+=1;
                }
                //转换数据类
                HotelRoomTemplateJournal db = new HotelRoomTemplateJournal(null,data.getBuildingNo(),data.getFloor(),data.getRoomId(),result);
                //将数据放入新的集合，方便存入数据库以及写Excel表
                das.add(db);
                //一次最多添加10条数据
                if (das.size() >= daLength) {
                    // 存储完成清理 list
                    //将导入数据存入错误日志表
//                    this.dao.addErrorLog(das);
                    das.clear();
                }
            }
            //统计导入成功和导入失败数据，存入日志表
            importLogService.addImpLog("隔离房间信息导入",bath,success,fail);
            //将导入数据存入错误日志表
            if (das!=null&&das.size()!=0){
//                this.dao.addErrorLog(das);
            }
            //创建临时文件
            return "共导入"+(success+fail)+"条记录: "+"导入成功"+success+"条,导入失败"+fail+"条";
        }catch (Exception e){
            //统计导入成功和导入失败数据，存入日志表
            importLogService.addImpLog("隔离房间信息导入",bath,success,fail);
            //将导入数据存入错误日志表
            if (das!=null&&das.size()!=0){
//                this.dao.addErrorLog(das);
            }
            //创建临时文件
            return "共导入"+(success+fail)+"条记录: "+"导入成功"+success+"条,导入失败"+fail+"条,导入到:"+hotelRoomTemplate.getRoomId()+"时数据出错";
        }
    }

5、导入效果

原始文件： 

导入后的展示效果：