用阿里开源的 easyexcel 来写 excel 文件

easyexcel 是阿里巴巴开源的一个 JAVA 解析 Excel 工具,easyexcel 重写了 poi 对 07 版 Excel 的解析，能够原本一个 3M 的 excel 用 POI sax 依然需要 100M 左右内存降低到 KB 级别，并且再大的 excel 不会出现内存溢出，03 版依赖 POI 的 sax 模式。在上层做了模型转换的封装，让使用者更加简单方便

我今天用 easyexcel 来写入 excel
我是要导出一个用户信息表，所以使用了模型映射

pojo 类

@Data
public class User extends BasePojo {
    
    private String id;//会员卡号
    private String username;//用户名
    private String vipId;//会员对象的id
    private Float balance;//余额
    private String phone;//手机号
    private Float cumulative;//累计消费
    private int sex;//0为男1为女
    private String note; //备注
}
​

准备一个 user 的包装类

@Data
public class UserBo extends BaseRowModel {
    @ExcelProperty(value = "会员卡号" ,index = 0)
    private String id;
    @ExcelProperty(value = "姓名" ,index = 1)
    private String username;
    @ExcelProperty(value = "性别" ,index = 2)
    private String sex;
    @ExcelProperty(value = "会员级别" ,index = 3)
    private String vip;
    @ExcelProperty(value = "余额" ,index = 4)
    private Float balance;
    @ExcelProperty(value = "累计消费" ,index = 5)
    private Float cumulative;
    @ExcelProperty(value = "手机号" ,index = 6)
    private String phone;
    @ExcelProperty(value = "备注" ,index = 7)
    private String note; 
    @ExcelProperty(value = "入店时间" ,index = 8)
    private Date created;
}
​

一定要继承 BaseRowModel 类
@ExcelProperty(value = "会员卡号" ,index = 0)
value 表示 列名，index 表示写在第几列

写个单元测试来测试一下

    @Test
    public void demo() throws FileNotFoundException {
        OutputStream out = new FileOutputStream("/78.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
            Sheet sheet1 = new Sheet(1, 0, UserBo.class);
            List<UserBo> collect = userService.findAll().subList(0, 50).parallelStream().map(user -> {
                UserBo userBo = new UserBo();
                BeanUtils.copyProperties(user, userBo);
                if (user.getSex() == 0){
                    userBo.setSex("男");
                }else if (user.getSex() == 1){
                    userBo.setSex("女");
                }
                userBo.setVip(vipService.findById(user.getVipId()).getName());
                return userBo;
            }).collect(Collectors.toList());
            writer.write(collect, sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
​

测试通过后，写在 controller 里

    @GetMapping("userInfo")
    public void ExcelOut(HttpServletRequest request, HttpServletResponse response) throws IOException {
        ServletOutputStream out = response.getOutputStream();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        String filename = LocalDate.now().toString();
        response.setHeader("Content-disposition", "attachment;filename="+filename+".xlsx");
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);

        Sheet sheet1 = new Sheet(1, 0, UserBo.class);
        sheet1.setSheetName("用户信息");
        List<UserBo> collect = userService.findAll().subList(0, 50).parallelStream().map(user -> {
            UserBo userBo = new UserBo();
            BeanUtils.copyProperties(user, userBo);
            if (user.getSex() == 0){
                userBo.setSex("男");
            }else if (user.getSex() == 1){
                userBo.setSex("女");
            }
            userBo.setVip(vipService.findById(user.getVipId()).getName());
            return userBo;
        }).collect(Collectors.toList());
        writer.write(collect, sheet1);
        writer.finish();
    }
​

导出 excel，下载成功