[JavaScript知识库]springboot+easyExcel+vue导出excel(可直接粘贴复制使用)

引入的依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.3</version>
            </dependency>

先给大家一个工具类,可以直接复制粘贴使用

package com.ayjmall.common.utils;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.http.HttpResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * 导出excel的一些方法,可以直接粘贴使用
 */
public class ExcelUtil {
    /**
     * 商家表导出的默认样式
     * @return
     */
    public static HorizontalCellStyleStrategy defaultTenantStyles(){
        //TODO 默认样式
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置淡蓝色
       // headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 18);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置垂直居中为居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置左右对齐为靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 设置单元格上下左右边框为细边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
        //创建字体对象
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 16);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

    /**
     * 从服务器上下载相关的代码放到这里
     */
    public static void export(String fileName,  HttpServletResponse response) {
        File excelFile = new File(fileName);
        response.setCharacterEncoding("UTF-8");
        String realFileName = excelFile.getName();
        response.setHeader("content-type", "application/octet-stream;charset=UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        //加上设置大小下载下来的.xlsx文件打开时才不会报“Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃”
        response.addHeader("Content-Length", String.valueOf(excelFile.length()));
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(realFileName.trim(), "UTF-8"));
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        byte[] buff = new byte[1024];
        BufferedInputStream bis = null;
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            bis = new BufferedInputStream(new FileInputStream(excelFile));
            int i = bis.read(buff);
            while (i != -1) {
                os.write(buff, 0, buff.length);
                os.flush();
                i = bis.read(buff);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }

    /**
     * 删除excel
     */
    public static void   deleteFile(String fileName){
        File file = new File(fileName);
       // 路径为文件且不为空则进行删除
        if (file.isFile()&& file.exists()) {
            file.delete();
        }
    }

}

java部分的代码

实体类

package com.ayjmall.common.vo.order;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.annotation.CheckForNull;
import java.util.Date;

@Data
public class OrderExcelVO {

    @ColumnWidth(30)
    @CheckForNull
    @ExcelProperty("爆品名称")
    private String productName;

    @ColumnWidth(10)
    @ExcelProperty("购买的爆品数量")
    @CheckForNull
    private String productNum;

    @ColumnWidth(10)
    @ExcelProperty("用户实付金额")
    @CheckForNull
    private String userMoney;

    @ColumnWidth(10)
    @ExcelProperty("商家实收金额")
    @CheckForNull
    private String tenantMoney;


    @ColumnWidth(10)
    @ExcelProperty("分销价")
    @CheckForNull
    private String distributionMoney;

    @ColumnWidth(30)
    @ExcelProperty("购买时间")
    @CheckForNull
    @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;


    @ColumnWidth(20)
    @ExcelProperty("用户ID")
    @CheckForNull
    private String memberId;


    @ColumnWidth(20)
    @ExcelProperty("用户名称")
    @CheckForNull
    private String memberName;


    @ColumnWidth(30)
    @ExcelProperty( "用户手机号")
    @CheckForNull
    private String phone;


    @ColumnWidth(30)
    @ExcelProperty("订单类型")
    @CheckForNull
    private String orderType;


    @ColumnWidth(30)
    @ExcelProperty("订单状态")
    @CheckForNull
    private String orderState;
}

service实现类

    /**
     * 导出平台的订单数据,excel

     * @param dto
     * @return
     */
    @Override
    public void getAllOrderListForExcel(ConditionDTO dto, HttpServletRequest request, HttpServletResponse response) {
        //根据筛选条件进行找出相应的数据进行打印
        //--------业务逻辑
        //这是最后导出的数据集合
        List<OrderExcelVO> finalList = new ArrayList<>();
        //时间戳
        long time = System.currentTimeMillis();
        //路径
        String fileName ="订单信息查询" + time + ".xlsx";
        //先到出数据放到服务器上
        //默认样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = ExcelUtil.defaultTenantStyles();
        ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.write(fileName, OrderExcelVO.class).sheet(time + "").registerWriteHandler(horizontalCellStyleStrategy);
        writerSheetBuilder.doWrite(finalList);
        //下载
        ExcelUtil.export(fileName,response);
        //删除原本放在服务器上的数据
        ExcelUtil.deleteFile(fileName);
        
    }

vue部分的代码(这个是没有封装的单独用法,也可把request封装以后在用)

 Axios({ // 用axios发送post请求
                method: 'post',
                url: 'http://127.0.0.1:xxxxxxx', // 请求地址
                data: formData, // 参数
                responseType: 'blob' // 表明返回服务器返回的数据类型
            })
                .then((res) => { // 处理返回的文件流
                    let blob = new Blob([res.data], {type: res.data.type})
                    const fileName = 'ProductTemplateCopy.xlsx';
                    let downloadElement = document.createElement('a')
                    let href = window.URL.createObjectURL(blob); //创建下载的链接
                    downloadElement.href = href;
                    downloadElement.download = fileName; //下载后文件名
                    document.body.appendChild(downloadElement);
                    downloadElement.click(); //点击下载
                    document.body.removeChild(downloadElement); //下载完成移除元素
                    window.URL.revokeObjectURL(href); //释放blob
                    this.$message.success('[订单信息查询]已成功导出!');
            })
            .catch(function (error) {
                console.log(error);
            });

最后导出的页面

?