文章目录

        Maven依赖

        配置模板

         结果展示

         配置规则

        controller

        Service

        Impl

        Util

官方文档地址

---

Maven依赖

    com.alibaba
    easyexcel
    2.2.7



    org.projectlombok
    lombok
    1.18.4

配置模板

 结果展示

 配置规则

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

• 模板注意：用{}来表示你要用的变量，如果本来就有"{","}"， 特殊字符 用"{","}"代替。
• {} 代表普通变量，{.} 代表是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;


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;


@Service
public interface ExportService {

    
    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;


public class ExportServiceImpl implements ExportService {
    
    @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 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;


public class HttpUtil {


    
    public static InputStream getExcelInputStream(String templateUrl) throws IOException {
        URL url = new URL(templateUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("GET");
        //设置通用的请求属性
        conn.setRequestProperty("accept", "*
    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 · 语雀