EasyExcel导入导出

阅读原文时间：2023年07月11日阅读：1

com.alibaba easyexcel 2.1.6

Data

@Data
@EqualsAndHashCode(callSuper = true)
@ColumnWidth(10) //指定全局列宽
public class RobotSummaryVO extends BaseRowModel {

@ApiModelProperty("id")  
@ExcelIgnore        //表示导出时忽略该字段  
private String id;

@ApiModelProperty("姓名")  
@ExcelProperty(index = 0,value = "姓名")    //index指定表头顺序，从0开始。value指定表头名称  
private String name;

@ApiModelProperty("年龄")  
@ExcelProperty(index = 9,value = "年龄")  
private Integer age;

@ApiModelProperty("出生时间")  
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")  
@ExcelProperty(index = 8,value = "出生时间")  
@ColumnWidth(20)    //可单独指定该列列宽  
private LocalDateTime createDate;

}

controller层：调用方法

/* ExcelTypeDTO：导入导出表标记，指定导出对应的表/菜单
*/
@ApiOperation("导出")
@GetMapping
public void exportExcel(ExcelTypeDTO dto, HttpServletRequest request, HttpServletResponse response) throws IOException,Exception {
//若菜单标记为空，则抛出异常或导出空excel。
if (StringUtils.isBlank(dto.getMenu())) {
throw new Exception(String.valueOf(ServiceErrCode.TYPE_NOT_FOUND));
}
ExcelVO excelVO = excelService.getExportList(dto); //此处根据menu获得easyexcel导出需要的一些数据
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
String fileName = URLEncoder.encode(excelVO.getName(), "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// EasyExcel.write(response.getOutputStream(), excelVO.getVoClass()).sheet("sheet").doWrite(excelVO.getList());
//增加日期类型转换器
EasyExcel.write(response.getOutputStream(), excelVO.getVoClass()).registerConverter(new LocalDateTimeConverter()).sheet("sheet").doWrite(excelVO.getList());

}

service实现层：返回ExcelVO

@Override
public ExcelVO getExportList(ExcelTypeDTO dto) {
String name = "";
List list = null;
Class voClass = null;
switch (dto.getMenu()){
case "MONITOR_EXE":
name = "病历自动质控项目";
List monitorExeVOS = monitorExeMapper.getList(new QueryMonitorExeQTO());
list = monitorExeVOS;
voClass = MonitorExeVO.class;
break; QualityScoreItemVO.class;
break;
case "QUALITY_SCORE":
name = "质控评分要求";
List qualityScores = qualityScoreMapper.getList(new QueryQualityScoreQTO());
list = qualityScores;
voClass = QualityScore.class;
break;
//此处省略… 根据业务需求自行封装ExcelVO
default:
break;
}
ExcelVO excelVO = ExcelVO.builder()
.name(name)
.list(list)
.voClass(voClass)
.build();
return excelVO;
}

ExcelVO：封装通用导出需要的一些数据

@Builder
@Data
public class ExcelVO {

@ApiModelProperty("导出表名称")  
private String name;  
@ApiModelProperty("导出表数据")  
private List list;  
@ApiModelProperty("导出表数据对应的class")  
private Class voClass;

}

ExcelTypeDTO：接受前端传值标记导入导出是哪张表

@Data
public class ExcelTypeDTO {

@ApiModelProperty("导入导出类型标记：" +  
        "单病种质控：DISEASE;" +  
        "质控评分要求：QUALITY\_SCORE;" +  
        "质控缺陷字典：QUALITY\_SCORE\_ITEM;" +  
        "诊断和手术字典：BUSI\_DICT;" +  
        "基础字典：BASE\_DICT;" +  
        "病历质控代码：QUALITY\_MONITOR;" +  
        "员工管理：STAFF;" +  
        "床位管理：BED;" +  
        "管床小组：BED\_GROUP;" +  
        "病区管理：WARD;" +  
        "科室管理：DEPARTMENT;" +  
        "分院管理：BRANCH;" +  
        "医院管理：HOSPITAL;" +  
        "机器人配置：ROBOT;" +  
        "机器人任务：JOB\_ROBOT;" )  
private String menu;

}

controller层：调用方法

/\*\*  
 \* 文件上传  
 \* 1. 创建excel对应的实体对象  
 \* 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器  
 \* 3. 直接读即可  
 \*/  
@ApiOperation("导入")  
@GetMapping("importExcel")  
public Result importExcel(ExcelTypeDTO dto, MultipartFile file) throws IOException {  
    Class clazz = excelService.getImportClass(dto); //此处根据menu获得easyexcel导入封装的数据对象class  
    //EasyExcel.read(file.getInputStream(), clazz, new EasyExcelListener()).sheet().doRead();  
    //优化：监听器传值  
    EasyExcel.read(file.getInputStream(), clazz, new EasyExcelListener(excelListenerDao)).sheet().doRead();  
    String result = String.format("共导入成功%s条，失败%s",excelListenerDao.getSuccess(),excelListenerDao.getError());  
    return Result.ok(result);  
}

因为easyexcel导入时是将表格数据转为指定的Java类，再传入监听器。所以导入前需要将数据指定对应的Javaclass

@Override
public Class getImportClass(ExcelTypeDTO dto) {
Class clazz = null;
switch (dto.getMenu()){
case "DISEASE":
clazz = DiseaseMonitorVO.class;
break;
//省略…
default:
break;
}
return clazz;
}

EasyExcelListener监听器：导入时每读一行数据将数据封装为Java类后进入监听器

@Component
@Slf4j
public class EasyExcelListener extends AnalysisEventListener {

//ExcelListenerDao获得easyexcel导入时监听器返回的值。此处返回导入成功数量和失败数量  
@Autowired  
private ExcelListenerDao excelListenerDao;  
//构造函数，一定要写，将excelListenerDao添加到监听中  
public EasyExcelListener(ExcelListenerDao excelListenerDao) {  
    this.excelListenerDao = excelListenerDao;  
}

private static int success;     //每次导入成功数量  
private static int error;       //每次导入失败数量

//此处其实也可将数据放入集合中，由外部controller获取集合列表另调方法  
@Autowired  
private InterfaceRobotMapper interfaceRobotMapper;  
@Autowired  
private JobRobotConfigMapper jobRobotConfigMapper;

//当前工具类  
private static EasyExcelListener listener;  
//解决静态方法中不能直接用mapper的问题  
@PostConstruct  
public void init() {  
    listener = this;  
}

@Override  
public void invoke(Object o, AnalysisContext analysisContext) {  
    log.info("==================导入开始=================="+ JsonUtils.write(o));  
    listener.save(o);  
}

@Override  
public void doAfterAllAnalysed(AnalysisContext analysisContext) {  
    log.info("==================导入完成==================");  
    log.info("共导入成功{}条，失败{}",success,error);  
    //将导入成功数量和失败数量返回excelListenerDao  
    excelListenerDao.setError(error);  
    excelListenerDao.setSuccess(success);  
    //将success和error清零  
    success = 0;  
    error = 0;  
}

private void save(Object o){  
    int result = 0;  
    if (o.getClass() == InterfaceRobot.class) {  
        //机器人  
        InterfaceRobot interfaceRobot = (InterfaceRobot) o;  
        InterfaceRobotExample example = new InterfaceRobotExample();  
        example.createCriteria().andCodeEqualTo(interfaceRobot.getCode());  
        List<InterfaceRobot> list = listener.interfaceRobotMapper.selectByExample(example);  
        if (list.size() > 0) {  
            log.error("导入错误：{}", ServiceErrCode.CODE\_IS\_EXIST);  
        }else {  
            //根据具体业务进行相应的数据处理  
            interfaceRobot.setId(IDGenerator.generateUUID());  
            interfaceRobot.setFlag(0);  
            result = listener.interfaceRobotMapper.insert(interfaceRobot);  
        }  
    }else if (o.getClass() == JobRobotConfigVO.class){  
        //机器人任务  
        JobRobotConfigVO vo = (JobRobotConfigVO)o;  
        result = listener.jobRobotConfigMapper.insert(vo);  
    }

    //统计成功数量和失败数量  
    if (result == 1) {  
        success = success +1;  
    }else {  
        error = error + 1;  
    }  
}

}

使用postman测试导入导出