Java EasyExcel模板存放在OSS上赋值导出

文章目录

        Maven依赖

        配置模板

         结果展示

         配置规则

        controller

        Service

        Impl

        Util

官方文档地址

---

Maven依赖

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

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.4</version>
</dependency>

配置模板

 结果展示

 配置规则

在需要填充的数据上配置对应的参数。

• 模板注意：用{}来表示你要用的变量，如果本来就有"{","}"， 特殊字符 用"\{","\}"代替。
• {} 代表普通变量，{.} 代表是list的变量。

controller

package com.example.easyexcel.controller;

import com.example.easyexcel.service.ExportService;
import org.springframework.web.bind.annotation.PostMapping;
import wiremock.javax.servlet.http.HttpServletResponse;

import javax.annotation.Resource;
import java.io.IOException;

/**
 * 导出Excel控制层
 *
 * @author 🦆
 * @date 2022-05-09 13:35
 */
public class ExportController {
    @Resource
    private ExportService exportService;

    @PostMapping(value = "export")
    public void export(HttpServletResponse response) throws IOException {
        exportService.export(response);
    }
}

Service

package com.example.easyexcel.service;

import org.springframework.stereotype.Service;
import wiremock.javax.servlet.http.HttpServletResponse;

import java.io.IOException;

/**
 * @author 🦆
 * @date 2022-05-09 13:37
 */
@Service
public interface ExportService {

    /**
     * 导出Excel方法
     * @param httpResponse:
     * @date 2022-05-09 13:45
     * @author 🦆
     */
    void export(HttpServletResponse httpResponse) throws IOException;
}

Impl

package com.example.easyexcel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.easyexcel.dto.StudentDto;
import com.example.easyexcel.service.ExportService;
import com.example.easyexcel.util.HttpUtil;
import org.springframework.beans.factory.annotation.Value;
import wiremock.javax.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author 🦆
 * @date 2022-05-09 13:37
 */
public class ExportServiceImpl implements ExportService {
    /**
     * 上传到OSS的模板下载地址
     * */
    @Value("${excel.export.templateUrl:#{''}}")
    private String templateUrl;

    @Override
    public void export(HttpServletResponse httpResponse) throws IOException {
        //获取模板并转换成InputStream
        InputStream excelInputStream = HttpUtil.getExcelInputStream(templateUrl);
        //为导出excel命名
        HttpUtil.getExcelOutputStream("学生信息详情表", httpResponse);

        StudentDto studentDto = new StudentDto();
        List<StudentDto.StudentInfo> studentInfoList = studentDto.getStudentInfoList();

        //输出到页面下载
        try (OutputStream outputStream = httpResponse.getOutputStream()) {
            //创建一个Excel写入器
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(excelInputStream).build();
            //创建一个Sheet
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //为每列数据添加序号
            Integer[] arr = {1};
            studentInfoList = studentInfoList.stream().peek(e -> e.setOrder(String.valueOf(arr[0]++))).collect(Collectors.toList());

            //填充基本信息
            excelWriter.fill(studentDto, writeSheet);
            //填充列表信息
            excelWriter.fill(studentInfoList, writeSheet);
            //关闭流
            excelWriter.finish();
        }
    }
}

Util

package com.example.easyexcel.util;

import wiremock.javax.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;

/**
 * HttpUtil工具类
 * @author 🦆
 * @date 2022-05-09 13:40
 */
public class HttpUtil {


    /**
     * 根据模板地址获取InputStream
     *
     * @param templateUrl: 模板url地址
     * @date 2022-05-09 13:40
     * @author 🦆
     */
    public static InputStream getExcelInputStream(String templateUrl) throws IOException {
        URL url = new URL(templateUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("GET");
        //设置通用的请求属性
        conn.setRequestProperty("accept", "*/*");
        conn.setRequestProperty("connection", "Keep-Alive");
        conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)");
        conn.setRequestProperty("Content-Type", "application/json;charset=utf-8");
        return conn.getInputStream();
    }


    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param fileName: 文件名称
     * @param response:
     * @date 2022-05-09 13:40
     * @author 🦆
     */
    public static void getExcelOutputStream(String fileName,
                                            HttpServletResponse response) throws IOException {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
        } catch (IOException e) {
            throw new IOException("导出excel表格失败!", e);
        }
    }
}

官方文档地址

填充Excel · 语雀