EasyExcel导入导出
maven依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
导出
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<MonitorExeVO> monitorExeVOS = monitorExeMapper.getList(new QueryMonitorExeQTO());
list = monitorExeVOS;
voClass = MonitorExeVO.class;
break; QualityScoreItemVO.class;
break;
case "QUALITY_SCORE":
name = "质控评分要求";
List<QualityScore> 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;
}
@Builder
@Data
public class ExcelVO {
@ApiModelProperty("导出表名称")
private String name;
@ApiModelProperty("导出表数据")
private List list;
@ApiModelProperty("导出表数据对应的class")
private Class voClass;
}
@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);
}
@Override
public Class getImportClass(ExcelTypeDTO dto) {
Class clazz = null;
switch (dto.getMenu()){
case "DISEASE":
clazz = DiseaseMonitorVO.class;
break;
//省略...
default:
break;
}
return clazz;
}
@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测试导入导出
注意
-
excel.xsl一个sheet最多存储65535条数据，超过会报错。待优化：若数据录过大时需要用多sheet导入导出
