springboot+vue3使用EasyExcel导出excel数据

BinaryBlade

已于 2024-08-28 20:27:10 修改

阅读量329

点赞数 9

于 2024-08-28 20:21:14 首次发布

本文链接：https://blog.csdn.net/qq_56740577/article/details/141648610

版权

导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.11</version>
        </dependency>

编辑实体类

package com.zero.springbootmall.entity;

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

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

public class PmsProduct implements Serializable {
    @ExcelIgnore
    private Integer productId;
    @ExcelIgnore
    private Integer categoryId;
    @ExcelProperty("商品名称")
    private String productName;
    @ExcelProperty("商品副标题")
    private String subtitle;
    @ExcelIgnore
    private Integer brandId;

    @ExcelProperty("商品描述")
    private String description;

    @ExcelProperty("商品货号")
    private String productSn;

    @ExcelProperty("商品原价")
    private BigDecimal originalPrice;

    @ExcelProperty("商品市场价")
    private BigDecimal price;

    @ExcelProperty("商品库存")
    private Integer stock;

    @ExcelProperty("计量单位")
    private String unit;

    @ExcelProperty("商品重量")
    private BigDecimal weight;

    @ExcelIgnore
    private Integer sort;

    @ExcelIgnore
    private BigDecimal promotionPrice;

    @ExcelIgnore
    private Integer giftGrowth;

    @ExcelIgnore
    private Integer giftPoint;

    @ExcelIgnore
    private Integer usePointLimit;

    @ExcelIgnore
    private Integer previewStatus;

    @ExcelIgnore
    private Integer publishStatus;

    @ExcelIgnore
    private Integer newStatus;

    @ExcelIgnore
    private Integer recommandStatus;

    @ExcelIgnore
    private String detailTitle;

    @ExcelIgnore
    private String detailDesc;

    @ExcelIgnore
    private String keywords;

    @ExcelIgnore
    private String note;

    @ExcelIgnore
    private Date promotionStartTime;

    @ExcelIgnore
    private Date promotionEndTime;

    @ExcelIgnore
    private Integer promotionId;

    @ExcelIgnore
    private String picture;

    @ExcelIgnore
    private String brandName;

    @ExcelIgnore
    private String productAttribute;

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getProductAttribute() {
        return productAttribute;
    }

    public void setProductAttribute(String productAttribute) {
        this.productAttribute = productAttribute;
    }

    public Integer getProductId() {
        return productId;
    }

    public void setProductId(Integer productId) {
        this.productId = productId;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName == null ? null : productName.trim();
    }

    public String getSubtitle() {
        return subtitle;
    }

    public void setSubtitle(String subtitle) {
        this.subtitle = subtitle == null ? null : subtitle.trim();
    }

    public Integer getBrandId() {
        return brandId;
    }

    public void setBrandId(Integer brandId) {
        this.brandId = brandId;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description == null ? null : description.trim();
    }

    public String getProductSn() {
        return productSn;
    }

    public void setProductSn(String productSn) {
        this.productSn = productSn == null ? null : productSn.trim();
    }

    public BigDecimal getOriginalPrice() {
        return originalPrice;
    }

    public void setOriginalPrice(BigDecimal originalPrice) {
        this.originalPrice = originalPrice;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public Integer getStock() {
        return stock;
    }

    public void setStock(Integer stock) {
        this.stock = stock;
    }

    public String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit == null ? null : unit.trim();
    }

    public BigDecimal getWeight() {
        return weight;
    }

    public void setWeight(BigDecimal weight) {
        this.weight = weight;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public BigDecimal getPromotionPrice() {
        return promotionPrice;
    }

    public void setPromotionPrice(BigDecimal promotionPrice) {
        this.promotionPrice = promotionPrice;
    }

    public Integer getGiftGrowth() {
        return giftGrowth;
    }

    public void setGiftGrowth(Integer giftGrowth) {
        this.giftGrowth = giftGrowth;
    }

    public Integer getGiftPoint() {
        return giftPoint;
    }

    public void setGiftPoint(Integer giftPoint) {
        this.giftPoint = giftPoint;
    }

    public Integer getUsePointLimit() {
        return usePointLimit;
    }

    public void setUsePointLimit(Integer usePointLimit) {
        this.usePointLimit = usePointLimit;
    }

    public Integer getPreviewStatus() {
        return previewStatus;
    }

    public void setPreviewStatus(Integer previewStatus) {
        this.previewStatus = previewStatus;
    }

    public Integer getPublishStatus() {
        return publishStatus;
    }

    public void setPublishStatus(Integer publishStatus) {
        this.publishStatus = publishStatus;
    }

    public Integer getNewStatus() {
        return newStatus;
    }

    public void setNewStatus(Integer newStatus) {
        this.newStatus = newStatus;
    }

    public Integer getRecommandStatus() {
        return recommandStatus;
    }

    public void setRecommandStatus(Integer recommandStatus) {
        this.recommandStatus = recommandStatus;
    }

    public String getDetailTitle() {
        return detailTitle;
    }

    public void setDetailTitle(String detailTitle) {
        this.detailTitle = detailTitle == null ? null : detailTitle.trim();
    }

    public String getDetailDesc() {
        return detailDesc;
    }

    public void setDetailDesc(String detailDesc) {
        this.detailDesc = detailDesc == null ? null : detailDesc.trim();
    }

    public String getKeywords() {
        return keywords;
    }

    public void setKeywords(String keywords) {
        this.keywords = keywords == null ? null : keywords.trim();
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }

    public Date getPromotionStartTime() {
        return promotionStartTime;
    }

    public void setPromotionStartTime(Date promotionStartTime) {
        this.promotionStartTime = promotionStartTime;
    }

    public Date getPromotionEndTime() {
        return promotionEndTime;
    }

    public void setPromotionEndTime(Date promotionEndTime) {
        this.promotionEndTime = promotionEndTime;
    }

    public Integer getPromotionId() {
        return promotionId;
    }

    public void setPromotionId(Integer promotionId) {
        this.promotionId = promotionId;
    }

    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture == null ? null : picture.trim();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", productId=").append(productId);
        sb.append(", categoryId=").append(categoryId);
        sb.append(", productName=").append(productName);
        sb.append(", subtitle=").append(subtitle);
        sb.append(", brandId=").append(brandId);
        sb.append(", description=").append(description);
        sb.append(", productSn=").append(productSn);
        sb.append(", originalPrice=").append(originalPrice);
        sb.append(", price=").append(price);
        sb.append(", stock=").append(stock);
        sb.append(", unit=").append(unit);
        sb.append(", weight=").append(weight);
        sb.append(", sort=").append(sort);
        sb.append(", promotionPrice=").append(promotionPrice);
        sb.append(", giftGrowth=").append(giftGrowth);
        sb.append(", giftPoint=").append(giftPoint);
        sb.append(", usePointLimit=").append(usePointLimit);
        sb.append(", previewStatus=").append(previewStatus);
        sb.append(", publishStatus=").append(publishStatus);
        sb.append(", newStatus=").append(newStatus);
        sb.append(", recommandStatus=").append(recommandStatus);
        sb.append(", detailTitle=").append(detailTitle);
        sb.append(", detailDesc=").append(detailDesc);
        sb.append(", keywords=").append(keywords);
        sb.append(", note=").append(note);
        sb.append(", promotionStartTime=").append(promotionStartTime);
        sb.append(", promotionEndTime=").append(promotionEndTime);
        sb.append(", promotionId=").append(promotionId);
        sb.append(", picture=").append(picture);
        sb.append("]");
        return sb.toString();
    }
}

需要的成员属性，在上方用@ExcelProperty注解指定表头的名字，不需要的成员属性，在上方用@ExcelIgnore注解即可，在导出时会自动忽略该成员属性的值。

后端接口

    @Override
    public void exportData(HttpServletResponse resp) {
        List<PmsProduct> pmsProducts = productMapper.selectAllData();
            // 设置文本内省
            resp.setContentType("application/vnd.ms-excel");
            // 设置字符编码
            resp.setCharacterEncoding("utf-8");
            // 设置响应头
            resp.setHeader("Content-disposition", "attachment;filename=demo.xlsx");
            try {
                EasyExcel.write(resp.getOutputStream(), PmsProduct.class).sheet("商品数据").doWrite(pmsProducts);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
    }

首先你需要将Excel表格中的数据放入一个List集合(我这里是通过MyBatis查询获得的数据)，然后setContentType是设置响应数据的类型，setCharacterEncoding设置响应数据的编码格式，setHeader是设置响应数据的响应头，其中filename为文件的名字，可以修改为需要的名字。最后通过EasyExcel提供的静态方法write，write方法中的第一个参数为HttpServletResponse对象的输出流，第二个参数为集合中元素的类型的类(这里是反射部分的知识)。最后将集合对象传入dowrite中即可。

前端接口

const exportData = async()=>{
		 await axios({
			method: "GET", //方法
			url: "http://localhost:8080/product/exportData", //请求路径
			//data:"XXX"  请求参数 没有可以不写
			responseType:'blob'
		}).then(response => {
			console.log(response);
			 if (response) {
					   const elink = document.createElement('a')
					   elink.style.display = 'none'
					   const blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
        // word文件  let blob = new Blob([res], { type: 'application/msword' })
		// zip文件  let blob = new Blob([response], { type: 'application/zip' })
					   const blobUrl = URL.createObjectURL(blob)
					   elink.href = blobUrl
					   elink.download = '商品信息.xlsx' // 重命名文件
					   document.body.appendChild(elink)
					   elink.click()
					   document.body.removeChild(elink)
				}
		})
	}

接收后端返回的Excel文件对象的时候，此时需要在请求中指定响应数据的类型为blob。剩下的直接复制即可，需要修改的就是文件的名字，以及new Blob中的第一个参数必须为Blob类型，可以打印输出response，查看返回的响应数据内容。