当前位置：首页 > news >正文

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

news 2025/7/4 2:55:08

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;
}

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

访问接口