easyExcel使用

一、带条件的数据导出为Excel

1、先确认需要导出的字段，在导出的字段上面加入@ExcelProperty(“value”)注解。
例如：

	@Data
	@ExcelIgnoreUnannotated // 忽略不需要导出的字段
	@TableName("user")
	public class User {
	
	    /**
	    * id
	    */
	    private String id;
	    
	    /**
	     * 姓名
	     */
	    @QueryField(type = QueryField.Type.LIKE)
	    @ExcelProperty(value = "人员姓名")
	    private String name;
	    
			/**
	     * 年龄
	     */
	    @ExcelProperty(value = "人员年龄")
	    private String age;
	    
			/**
	     * 性别  0: 男  1:女
	     */
	    @QueryField(type = QueryField.Type.EQUAL)
	    @ExcelProperty(value = "人员性别")
	    private String sex;
	}

2、controller接口

@Slf4j
@RestController
@RequestMapping("/user")
@Api(tags = "用户信息接口")
public class UserController{
 
    @Autowired
    private UserService userService;
 
    /**
     * 导出用户信息
     * @return
     */
    @ApiOperation(value = "导出用户信息")
    @PostMapping(value = "/exportUserList")
    public void exportUserList(@RequestBody QueryParam<User> query, HttpServletResponse response) {
        try {
        	response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), User.class).build();
            String pathName = fileName + ".xls";
        	//筛选条件
        	QueryWrapper<User> queryWrapper = query.buildQueryWrapper(User.class);
            List<User> dataList = userService.list(queryWrapper);
            // 这里注意如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet(pathName).build();
            excelWriter.write(dataList, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            log.error("导出用户信息异常",e);
        }
    }

调用接口即可完成下载

3、这里会发现：在导出的Excel中，在性别这一列的男，女显示的是0和1 ，不便于分辨，我们可以写一个工具类进行转换：

public class SexConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里是写的时候会调用，将Java的Integer对象转换成Excel中的字符串
     *
     * @return Excel中要存储的字符串
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        String gender = context.getValue().equals("0") ? "男" : "女";
        return new WriteCellData<String>(gender);
    }
}

然后我们再将这个工具类注入到实体类需要转换的字段上面：

		/**
	     * 性别  0: 男  1:女
	     */
	    @QueryField(type = QueryField.Type.EQUAL)
	    @ExcelProperty(value = "人员性别",converter = SexConverter.class)
	    private String sex;

这样导出的时候获取的性别就是所需要转换的字了。

二、复杂数据导入（参考）

1、导入接口：

	@ApiOperation("上传用户数据")
    @PostMapping(value = "/userUpload")
    public void userUpload(MultipartFile file) throws Exception {
        EasyExcel.read(file.getInputStream(), ExcelRecordsModel.class, new UserExcelEventListener(userService)).sheet().doRead();
    }

2、创建监听器

@Slf4j
public class UserExcelEventListener extends AnalysisEventListener<ExcelRecordsModel> {

    private static final int BATCH_COUNT = 100;
    List<User> dataList = new ArrayList<User>(BATCH_COUNT);
    User user;

    private UserService userService;

    public CanteenRecordExcelEventListener(UserService userService) {
        this.userService= userService;
    }

    @Override
    public void invoke(ExcelRecordsModel excelModel, AnalysisContext context) {
        ReadRowHolder readRowHolder = context.readRowHolder();
        log.info("行号: {}", readRowHolder.getRowIndex());

		//如果是第二行（第二行还是头，提取里面表示第几张表）
        if (readRowHolder.getRowIndex() == 2) {
            user = new User();
            String sheetName = context.readSheetHolder().getSheetName();
            if (sheetName.contains("1")) {
                userService.setType("1");
            } else if (sheetName.contains("2")) {
                userService.setType("2");
            } else if (sheetName.contains("3")) {
                userService.setType("3");
            }

            if (StringUtil.isNotEmpty(excelModel.getObj2())) {
                String[] checkTime = excelModel.getObj2().split("~");
                userService.setCheckStartTime(checkTime[0]);
                userService.setCheckEndTime(checkTime[1]);
                userService.setMakeTime(excelModel.getObj11());
            }
        }

        if (readRowHolder.getRowIndex() > 3) {
            if (readRowHolder.getRowIndex() % 2 == 0) {
                user= new User();
                user.setName(user.getName());
                user.setAge(user.getAge());
                user.setSex(user.getSex());
                dataList.add(canteenRecords);
            } else {
                SuperviseHelper.setValue(canteenRecords, excelModel);
            }
        }

        if (dataList.size() >= BATCH_COUNT) {
            saveData();
            dataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println(context);
        saveData();
    }

    private void saveData() {
        userService.saveOrUpdateBatch(dataList);
    }
}