easy excel导入导出

easy excel导入

每次都要去看easy excel的导入的例子，回回都没记性这次记录一下。

需求

将个人荣誉信息导入到数据库中，并且将对应字段查询出来方便后续查询使用。

    @Override
    public Map<String, Object> importHonorData(MultipartFile multipartFile) throws Exception {
        Map<String, Object> map = new HashMap();
        EasyExcel.read(multipartFile.getInputStream(), HonorData.class, new HonorDataListener(honorWallMapper)).sheet().headRowNumber(2).doRead();
        return map;
 }

@Slf4j
public class HonorDataListener implements ReadListener<HonorData> {

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

    /**
     * 缓存的数据
     */
    private List<HonorData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private List<Map<String,Object>> cachedDataWrongList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);


    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private HonorWallMapper demoDAO;

    public HonorDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        honorWallMapper = new HonorWallMapper();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public HonorDataListener(HonorWallMapper demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(HonorData data, AnalysisContext context) {
        List<Map<String, Object>> maps = employeeCheck(data.getEmployeeNumber());
        String employeeName = data.getEmployeeName();
        String employeeNameTrue = employeeName.replace(" ", ""); // 去除所有空格
        data.setEmployeeName(employeeNameTrue);

        if(maps.size() ==1){
            Map<String, Object> employee = maps.get(0);
            String lastName = String.valueOf(employee.get("last_name"));
            if(lastName.equals(data.getEmployeeName())){
                data.setOrgId(Integer.valueOf(String.valueOf(employee.get("organization_id"))));
                data.setOrganizationName(String.valueOf(employee.get("organization_name")));
             }
        }else{
            data.setOrganizationName(null);
        }

        data = updateHonorDataTime(data);
        data = updateHonorDatadimension(data);
        log.info(data.toString());
        log.info("data.toString()");
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

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

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.batchsImportExcel(cachedDataList);
        log.info("存储数据库成功！");
    }

    public List<Map<String, Object>> employeeCheck(String employeeNumber){
        List<Map<String, Object>> maps = demoDAO.selectEmployeeNameOrgByEmployeeNumber(employeeNumber);
        return maps;
    }

    /**
     * 年份字段判断
     * @param data
     * @return
     */
    public HonorData updateHonorDataTime (HonorData data){
        data.setYear("2023");
        String yearMonthDate = data.getYearMonthDate();

        if (yearMonthDate.contains("/")) {
            String[] split = yearMonthDate.split("/");
            if (split.length == 3) {
                LocalDate localDate = LocalDate.of(Integer.valueOf(split[0]), Integer.valueOf(split[1]), Integer.valueOf(split[2]));
                LocalDateTime localDateTime = localDate.atStartOfDay();
                java.util.Date date = java.util.Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
                data.setGetDate(new Date(date.getTime()));
            }
        }
        return data;
    }


    public HonorData updateHonorDatadimension (HonorData data){
        String dimensionName = data.getDimensionName();
        String dimensionOneName = data.getDimensionOneName();
        List<Map<String, Object>> maps = demoDAO.selectHonorDimensionByDimension(dimensionName);
        if(maps.size()>0){
            List<Map<String, Object>> dimensionNamemaps = demoDAO.selectHonorDimensionByDimension(dimensionOneName);
            if(dimensionNamemaps.size()>0){
                Map<String, Object> map = dimensionNamemaps.get(0);
                data.setDimensionId(Integer.valueOf(String.valueOf(map.get("id"))));
                return data;
            }
            Map<String, Object> map = maps.get(0);
            data.setDimensionId(Integer.valueOf(String.valueOf(map.get("id"))));
            return data;
        }

        return data;
    }

}

easy excel导出

excel多个sheet导出

需求

将通知以及具体的通知明细进行导出

public void exportExcel(HttpServletResponse response, SdssNotificationNoticeInfoQueryVo sdssNotificationBaseTableInfoQueryVo) {
        String fileName = "通知导出.xlsx";
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).build();

            WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "通知").head(SdssNotificationNoticeExcel.class).build();
            List<SdssNotificationNoticeExcel> sdssNotificationBaseTableInfoQueryVos = socialSecurityBaseMapper.noticeListNotPage(sdssNotificationBaseTableInfoQueryVo);
            excelWriter.write(sdssNotificationBaseTableInfoQueryVos, writeSheet1);

            WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "通知详情").head(SdssNotificationBaseTableExcel.class).build();
            List<SdssNotificationBaseTableExcel> noticeInfo = socialSecurityBaseMapper.findNoticeInfoByNoticeInfo(sdssNotificationBaseTableInfoQueryVo);
            excelWriter.write(noticeInfo, writeSheet2);

        } catch (Exception e) {
            System.err.println("操作 Excel 文件时出错：" + e.getMessage());
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

        // 设置响应头
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            System.err.println("不支持的字符编码：" + e.getMessage());
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        try {
            response.flushBuffer();
        } catch (IOException e) {
            System.err.println("刷新输出流时出错：" + e.getMessage());
        }
    }

@Data
public class SdssNotificationNoticeExcel {

    private static final long serialVersionUID = 1L;

    @ExcelIgnore
    private int id;

    /**
     * 通知标题
     */
    @ExcelProperty("通知标题")
    private String notificationTitle;

    /**
     * 年份
     */
    @ExcelProperty("年份")
    private int year;

    /**
     * 地市
     */
    @ExcelIgnore
    private String cityId;

    @ExcelProperty("地市")
    private String cityName;

    /**
     * 通知内容
     */
    @ExcelIgnore
    private String notificationContent;


    /**
     * 通知人
     */
    @ExcelProperty("通知人")
    private String notifierStaffNo;

    /**
     * 通知时间
     */
    @ExcelIgnore
    private Timestamp notificationTime;

    /**
     * 是否发送1已发送0未发送
     */
    @ExcelIgnore
    private String ifNotified;

    @ExcelIgnore
    private String employeeNameOrNumber;

    @ExcelIgnore
    private String taskDefinitionKey;

    @ExcelIgnore
    private String sendnoticeemployeecode;

    @ExcelProperty("发送人数")
    private int notifiedCount;

    @ExcelProperty("已读人数")
    private int readCount;

    @ExcelProperty("发送总人数")
    private int sumPerson;

    @ExcelIgnore
    private List<SdssNotificationBaseTableInfoQueryVo> sdssNotificationBaseTableInfoQueryVos;
}