Springboot 指定自定义模板导出Excel文件

客官°小女子只卖身不卖艺 2023-09-28 15:17 81阅读 0赞

# 前言 #

之前写过一篇极其简单的excel导入导出，是单个文件的：  
[Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入\_小目标青年的博客-CSDN博客][Springboot _MYSQL_EXCEL_-CSDN]

还写过一篇单个，多个 excel文件导出，转成ZIP包的：  
[Springboot 导出Excel文件，多个需要压缩成 zip 包\_小目标青年的博客-CSDN博客][Springboot _Excel_ zip _-CSDN]

然后：  
![335eb04356334483951b8f8450007fb6.png][]

于是该篇就来了， 指定模板（自定义）导出数据，就像：

![4c4896d0481340d9a9f0669d76866a83.png][]

# 正文 #

开始实战：

pom.xml依赖：

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.0.3</version>
            </dependency>

我们导出数据的实体类 UserExcelDTO.java

import cn.afterturn.easypoi.excel.annotation.Excel;
    
    /**
     * @Author: JCccc
     * @Date: 2022-7-14 10:58
     * @Description:
     */
    public class UserExcelDTO {
        @Excel(name = "学号", height = 8, width = 13, isImportField = "true")
        private Integer id;
        @Excel(name = "姓名", height = 8, width = 13, isImportField = "true")
        private String  userName;
        @Excel(name = "年龄", height = 8, width = 13, isImportField = "true")
        private String  userAge;
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", userName='" + userName + '\'' +
                    ", userAge='" + userAge + '\'' +
                    '}';
        }
    
        public UserExcelDTO() {
        }
    
        public UserExcelDTO(Integer id, String userName, String userAge) {
            this.id = id;
            this.userName = userName;
            this.userAge = userAge;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUserName() {
            return userName;
        }
    
        public void setUserName(String userName) {
            this.userName = userName;
        }
    
        public String getUserAge() {
            return userAge;
        }
    
        public void setUserAge(String userAge) {
            this.userAge = userAge;
        }
    }

> **里面使用了 @Excel注解标记一下相关字段**

然后自定义模板， 注意里面细节:

![ee9654c2284043d7ae7ebdce9d7a8730.png][]

每一行数据都是一个对象，都在list 里面，

所以看到 首个字段 和 末尾最后的字段 是有 括号的 ｛｝：  
  
![3f5df0eb1b874236a1c458b1c1729c34.png][]

示例格式：

> \{ \{$fe: list t.id

> t.userName

> t.userAge\}\}

然后把自定义模板文件丢到 静态资源路径下：

![fae5f484900f4040b9cdfe20e13cee1c.png][]

然后是实现使用自定义模板，填充list数据导出excel文件：

/**
         * excel导出 列表 指定模板
         *
         * @return
         */
        @GetMapping(value = "/exportAssignTemplateExcel")
        public void opportunityExport(HttpServletResponse response) {
            List<UserExcelDTO> exportList = new ArrayList<>();
    
            UserExcelDTO userExcel1=new UserExcelDTO(1001,"JCccc","18");
            UserExcelDTO userExcel2=new UserExcelDTO(1002,"ACccc","29");
            UserExcelDTO userExcel3=new UserExcelDTO(1003,"GCccc","50");
    
            exportList.add(userExcel1);
            exportList.add(userExcel2);
            exportList.add(userExcel3);
    
            Map map = Maps.newHashMap();
            map.put("list", exportList);
            //获取导出模板地址
            ClassPathResource classPathResource = new ClassPathResource("static/export/template/MyUserTemplate.xlsx");
            String path = classPathResource.getPath();
            TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
            Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
            String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
            String fileName = "用户数据"+time+".xlsx";
            try {
                response.setContentType("application/octet-stream;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
                response.flushBuffer();
                wb.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

代码简析：  
![1a71aeb3ecdd45ae8915c392323bf16c.png][]

调用一下接口，看看效果：

![d5688480278c4387834bd808f42e77cd.png][]

excel文件内容：  
  
![2aecc037cca2460194b31b9dc7b7cf8d.png][]

好了，该篇就到这。

[Springboot _MYSQL_EXCEL_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/88967572
[Springboot _Excel_ zip _-CSDN]: https://blog.csdn.net/qq_35387940/article/details/125790362
[335eb04356334483951b8f8450007fb6.png]: https://img-blog.csdnimg.cn/335eb04356334483951b8f8450007fb6.png
[4c4896d0481340d9a9f0669d76866a83.png]: https://img-blog.csdnimg.cn/4c4896d0481340d9a9f0669d76866a83.png
[ee9654c2284043d7ae7ebdce9d7a8730.png]: https://img-blog.csdnimg.cn/ee9654c2284043d7ae7ebdce9d7a8730.png
[3f5df0eb1b874236a1c458b1c1729c34.png]: https://img-blog.csdnimg.cn/3f5df0eb1b874236a1c458b1c1729c34.png
[fae5f484900f4040b9cdfe20e13cee1c.png]: https://img-blog.csdnimg.cn/fae5f484900f4040b9cdfe20e13cee1c.png
[1a71aeb3ecdd45ae8915c392323bf16c.png]: https://img-blog.csdnimg.cn/1a71aeb3ecdd45ae8915c392323bf16c.png
[d5688480278c4387834bd808f42e77cd.png]: https://img-blog.csdnimg.cn/d5688480278c4387834bd808f42e77cd.png
[2aecc037cca2460194b31b9dc7b7cf8d.png]: https://img-blog.csdnimg.cn/2aecc037cca2460194b31b9dc7b7cf8d.png