bjzt 的个人博客 bjzt 的个人博客

记录精彩的程序人生

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

用阿里开源的 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();
            }
        }
    }

execl1.png
测试通过后,写在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,下载成功
63X0B1O6F4AY1WRK9C.png