EasyExcel将数据库里面的数据生成excel文件

EasyExcel官方文档

1.在model模块导入依赖

<!--        生成报表-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>4.0.3</version>
</dependency>

2.修饰实体类

package com.gmgx.vo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class ExcelVo {

    @ExcelProperty({"订单报表", "订单id"})
    private String id;

    /**
     * 订单状态 1待付款，2待派送，3待取餐，4已派送，5已完成，6已取消，7待审批
     */
    @ExcelProperty({"订单报表", "订单状态"})
    private String status;

    @ExcelProperty({"订单报表", "下单用户id"})
    private String memberId;

    //TODO:根据memberId获取下单用户的名字
    //    @ExcelProperty("下单用户")
    //    private String memberName;

    @ExcelProperty({"订单报表", "店铺id"})
    private String shopId;

    //TODO:根据shopId获取店铺的名字
    //    @ExcelProperty("店铺名")
    //    private String shopName;

    @ExcelProperty({"订单报表", "下单时间"})
    private Date orderTime;

    @ExcelProperty({"订单报表", "结账时间"})
    private Date checkoutTime;

    /**
     * 支付方式 1微信,2支付宝
     */
    @ExcelProperty({"订单报表", "支付方式"})
    private String payMethod;

    @ExcelProperty({"订单报表", "实收金额"})
    private BigDecimal amount;

    @ExcelProperty({"订单报表", "备注"})
    private String remark;

    @ExcelProperty({"订单报表", "收件人手机号"})
    private String phone;

    @ExcelProperty({"订单报表", "收件地址"})
    private String address;

    @ExcelProperty({"订单报表", "收货人姓名"})
    private String consignee;

    /**
     * 0-外卖 1-堂食
     */
    @ExcelProperty({"订单报表", "就餐方式"})
    private String type;

    @ExcelProperty({"订单报表", "取消下单原因（用户端）"})
    private String cancelReasonMember;

    @ExcelProperty({"订单报表", "取消下单原因（管理端）"})
    private String cancelReason;

    @ExcelProperty({"订单报表", "商家拒单原因"})
    private String rejectionReason;

    @ExcelProperty({"订单报表", "商家拒绝用户取消订单的原因"})
    private String rejectCancelReason;
}

3.写业务代码

@Override
public Boolean exportExcel() {
    List<Orders> orderList = this.list();
    String fileName = "baoder-订单报表-" + sdf4Excel.format(System.currentTimeMillis()) + ".xlsx";
    ArrayList<ExcelVo> excelVo = new ArrayList<>();
    for (Orders order : orderList) {
        ExcelVo excelItem = new ExcelVo();
        excelItem.setId(order.getId());
        switch (order.getStatus()) {
            case 1:
                excelItem.setStatus("待付款");
                break;
            case 2:
                excelItem.setStatus("待派送");
                break;
            case 3:
                excelItem.setStatus("待取餐");
                break;
            case 4:
                excelItem.setStatus("已派送");
                break;
            case 5:
                excelItem.setStatus("已完成");
                break;
            case 6:
                excelItem.setStatus("已取消");
                break;
            case 7:
                excelItem.setStatus("待审批");
                break;
        }
        excelItem.setMemberId(order.getMemberId());
        excelItem.setShopId(order.getShopId());
        excelItem.setOrderTime(order.getOrderTime());
        excelItem.setCheckoutTime(order.getCheckoutTime());
        excelItem.setPayMethod(1 == (order.getPayMethod()) ? "微信" : "支付宝");
        excelItem.setAmount(order.getAmount());
        excelItem.setRemark(order.getRemark());
        excelItem.setPhone(order.getPhone());
        excelItem.setAddress(order.getAddress());
        excelItem.setConsignee(order.getConsignee());
        excelItem.setType("0".equals(order.getType()) ? "外卖" : "堂食");
        excelItem.setCancelReasonMember(order.getCancelReasonMember());
        excelItem.setCancelReason(order.getCancelReason());
        excelItem.setRejectionReason(order.getRejectionReason());
        excelItem.setRejectCancelReason(order.getRejectCancelReason());
        excelVo.add(excelItem);
    }
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, ExcelVo.class).sheet("模板").doWrite(excelVo);
    return true;
}

*******************************************************************

访问接口