欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

EasyExcel 填充模板导出 excel.xlsx

最编程 2024-10-18 18:59:22
...

菜鸟的自我救赎，自从有了GPT，还是头一次一个bug写一天。
直接贴导出excel模板的完整案例

官网冲刺 EasyExcel

EasyExcel填充模板导出excel.xlsx / 导出excel模板

一、bug(不需要请跳过)

1.1 使用apache poi操作excel报错 java.lang.NoSuchMethodError: org.apache.logging.log4j.Logger.atTrace()Lorg/apache/logging/log4j/

1.2EasyPoi读取word时报错java.util.zip.ZipException: Unexpected record signature: 0X9

二、配置文件


    <!--以下代码为 使用easyExcel需要用到的包-->
<dependencies>

<!--排除自带低版本poi,解决依赖冲突问题-->
 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.3.2</version>
      <exclusions>
        <exclusion>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
        </exclusion>
        <exclusion>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
        </exclusion>
        <exclusion>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

	<!--添加适合的版本依赖-->
	<!--排除自带低版本log4j-api,解决依赖冲突问题-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.2.2</version>
      <exclusions>
        <exclusion>
          <groupId>org.apache.logging.log4j</groupId>
          <artifactId>log4j-api</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>5.2.2</version>
      <exclusions>
        <exclusion>
          <groupId>org.apache.logging.log4j</groupId>
          <artifactId>log4j-api</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

	<!--添加适合的版本依赖-->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-api</artifactId>
      <version>2.23.1</version>
    </dependency>
  </dependencies>

三、 excel模板

在这里插入图片描述

四、 模板存放路径

在这里插入图片描述

五、 代码

5.1 Controller

参数不需要可以去掉

  @GetMapping("/excel/export/details")
    public void excelExport(@RequestParam(name = "xxxx", required = false) String purchaseSeq, HttpServletResponse response) {
        purchaseOrderService.excelExportDetails(purchaseSeq, response);
    }

5.2 实体

package com.zetian.platform.modules.order.vo;

import java.io.Serializable;
import java.util.Date;

public class AdminPurchaseOrderDetailsExportDTO  implements Serializable {
    private static final long serialVersionUID = 1L;


    /**
     * 订单号
     */
    private String purchaseSeq;

    /**
     * 下单门店
     */
    private String orderStore;

    /**
     * 门店编码
     */
    private String storeCode;

    /**
     * 联系人
     */
    private String user;

    /**
     * 联系电话
     */
    private String userMobile;

    /**
     * 门店类型
     */
    private String storeType;

    /**
     * 收货地址
     */
    private String userAddress;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 发货日期
     */
    private Date expectTime;

    /**
     * 订单状态
     */
    private String orderStatus;

    /**
     * 订单来源
     */
    private String orderSource;

    /**
     * 下单金额
     */
    private String orderPrice;

    /**
     * 发货金额
     */
    private String deliveryAmount;

    /**
     * 收货金额
     */
    private String receivedAmount;

    /**
     * 司机
     */
    private String driver;

    /**
     * 司机电话
     */
    private String driverMobile;

    /**
     * 送货时间
     */
    private Date deliveryTime;

    /**
     * 采购
     */
    private String purchaseUser;

    /**
     * 采购电话
     */
    private String purchaseMobile;


    //TODO  详情部分  待添加


    public String getPurchaseSeq() {
        return purchaseSeq;
    }

    public void setPurchaseSeq(String purchaseSeq) {
        this.purchaseSeq = purchaseSeq;
    }

    public String getOrderStore() {
        return orderStore;
    }

    public void setOrderStore(String orderStore) {
        this.orderStore = orderStore;
    }

    public String getStoreCode() {
        return storeCode;
    }

    public void setStoreCode(String storeCode) {
        this.storeCode = storeCode;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getUserMobile() {
        return userMobile;
    }

    public void setUserMobile(String userMobile) {
        this.userMobile = userMobile;
    }

    public String getStoreType() {
        return storeType;
    }

    public void setStoreType(String storeType) {
        this.storeType = storeType;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getExpectTime() {
        return expectTime;
    }

    public void setExpectTime(Date expectTime) {
        this.expectTime = expectTime;
    }

    public String getOrderStatus() {
        return orderStatus;
    }

    public void setOrderStatus(String orderStatus) {
        this.orderStatus = orderStatus;
    }

    public String getOrderSource() {
        return orderSource;
    }

    public void setOrderSource(String orderSource) {
        this.orderSource = orderSource;
    }

    public String getOrderPrice() {
        return orderPrice;
    }

    public void setOrderPrice(String orderPrice) {
        this.orderPrice = orderPrice;
    }

    public String getDeliveryAmount() {
        return deliveryAmount;
    }

    public void setDeliveryAmount(String deliveryAmount) {
        this.deliveryAmount = deliveryAmount;
    }

    public String getReceivedAmount() {
        return receivedAmount;
    }

    public void setReceivedAmount(String receivedAmount) {
        this.receivedAmount = receivedAmount;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getDriverMobile() {
        return driverMobile;
    }

    public void setDriverMobile(String driverMobile) {
        this.driverMobile = driverMobile;
    }

    public Date getDeliveryTime() {
        return deliveryTime;
    }

    public void setDeliveryTime(Date deliveryTime) {
        this.deliveryTime = deliveryTime;
    }

    public String getPurchaseUser() {
        return purchaseUser;
    }

    public void setPurchaseUser(String purchaseUser) {
        this.purchaseUser = purchaseUser;
    }

    public String getPurchaseMobile() {
        return purchaseMobile;
    }

    public void setPurchaseMobile(String purchaseMobile) {
        this.purchaseMobile = purchaseMobile;
    }
}

5.3 Service

使用 @Value(“classpath:xx/xx/xxx.xlsx”) 获取文件路径

	@Value("classpath:template/excel/ExportPurchaseOrderDetailsTemplate.xlsx")
    private org.springframework.core.io.Resource templateResource;

方法

public void excelExportDetails(String purchaseSeq, HttpServletResponse response) {

        try (InputStream templateInputStream = templateResource.getInputStream()) {
            // 检查模板文件是否存在
            if (!templateResource.exists()) {
                throw new RuntimeException("模板文件不存在！路径为：" + templateResource.getDescription());
            }

            // 设置响应头，告诉浏览器这是一个文件下载请求
            String fileName = URLEncoder.encode("导出采购订单详情", "UTF-8") + ".xlsx";
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

            AdminPurchaseOrderDetailsExportDTO adminPurchaseOrderDetailsExportDTO = new AdminPurchaseOrderDetailsExportDTO();
            adminPurchaseOrderDetailsExportDTO.setOrderStore("1");
            adminPurchaseOrderDetailsExportDTO.setStoreCode("21321");
            adminPurchaseOrderDetailsExportDTO.setUser("312321");
            adminPurchaseOrderDetailsExportDTO.setUserMobile("12321");
            adminPurchaseOrderDetailsExportDTO.setStoreType("321321");
            adminPurchaseOrderDetailsExportDTO.setUserAddress("123123");
            adminPurchaseOrderDetailsExportDTO.setCreateTime(new Date());
            adminPurchaseOrderDetailsExportDTO.setExpectTime(new Date());
            adminPurchaseOrderDetailsExportDTO.setOrderStatus("213213");
            adminPurchaseOrderDetailsExportDTO.setOrderSource("21312");
            adminPurchaseOrderDetailsExportDTO.setOrderPrice("12312");
            adminPurchaseOrderDetailsExportDTO.setDeliveryAmount("312321");
            adminPurchaseOrderDetailsExportDTO.setReceivedAmount("312321");
            adminPurchaseOrderDetailsExportDTO.setDriver("21321");
            adminPurchaseOrderDetailsExportDTO.setDriverMobile("12321");
            adminPurchaseOrderDetailsExportDTO.setDeliveryTime(new Date());
            adminPurchaseOrderDetailsExportDTO.setPurchaseUser("312312");
            adminPurchaseOrderDetailsExportDTO.setPurchaseMobile("321321321");

            try (ServletOutputStream outputStream = response.getOutputStream()) {
                EasyExcelFactory.write(outputStream) // 将文件写入输出流，而不是文件路径
                        .withTemplate(templateInputStream) // 使用模板
                        .sheet() // 填充第一个sheet
                        .doFill(adminPurchaseOrderDetailsExportDTO); // 填充数据
            }
        } catch (Exception e) {
            throw new RuntimeException("导出 Excel 文件失败", e);
        }
    }

六 测试

在这里插入图片描述

七 成果展示

在这里插入图片描述

八 导出列表数据

8.1 修改实体类

package com.zetian.platform.modules.order.vo;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @author xxxxx
 * @Description  excel采购单详情 头部信息
 * @since 2024/10/14 16:42
 **/
public class AdminPurchaseOrderDetailsExportDTO  implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 订单号
     */
    private String purchaseSeq;

    /**
     * 下单门店
     */
    private String orderStore;

    /**
     * 门店编码
     */
    private String storeCode;

    /**
     * 联系人
     */
    private String user;

    /**
     * 联系电话
     */
    private String userMobile;

    /**
     * 门店类型
     */
    private String storeType;

    /**
     * 收货地址
     */
    private String userAddress;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 发货日期
     */
    private Date expectTime;

    /**
     * 订单状态
     */
    private String orderStatus;

    /**
     * 订单来源
     */
    private String orderSource;

    /**
     * 下单金额
     */
    private String orderPrice;

    /**
     * 发货金额
     */
    private String deliveryAmount;

    /**
     * 收货金额
     */
    private String receivedAmount;

    /**
     * 司机
     */
    private String driver;

    /**
     * 司机电话
     */
    private String driverMobile;

    /**
     * 送货时间
     */
    private Date deliveryTime;

    /**
     * 采购
     */
    private String purchaseUser;

    /**
     * 采购电话
     */
    private String purchaseMobile;

    /**
     * 订单详情
     */
    private List<OrderItem> orderItems;

    public String getPurchaseSeq() {
        return purchaseSeq;
    }

    public void setPurchaseSeq(String purchaseSeq) {
        this.purchaseSeq = purchaseSeq;
    }

    public String getOrderStore() {
        return orderStore;
    }

    public void setOrderStore(String orderStore) {
        this.orderStore = orderStore;
    }

    public String getStoreCode() {
        return storeCode;
    }

    public void setStoreCode(String storeCode) {
        this.storeCode = storeCode;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getUserMobile() {
        return userMobile;
    }

    public void setUserMobile(String userMobile) {
        this.userMobile = userMobile;
    }

    public String getStoreType() {
        return storeType;
    }

    public void setStoreType(String storeType) {
        this.storeType = storeType;
																				

						

							上一篇：							
								代理 IP 在爬虫中的作用是什么？							
						

						

							下一篇：							
								破解 linux 登录密码							
						

					

															

						

							推荐阅读