SpringBoot系列---【springboot2.x+easyexcel实现模板导出】

1.引入pom依赖(easyexcel)

看看报错了再引入:commons-io,我电脑上有jdk17也有jdk8，默认jdk17，在pom中指定jdk8。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.14</version>
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>spring-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-demo</name>
    <description>Spring Boot Demo - Excel Import &amp; Template Export</description>

    <properties>
        <java.version>1.8</java.version>
        <lombok.version>1.18.24</lombok.version>
        <hutool.version>5.8.11</hutool.version>
        <easyexcel.version>4.0.0</easyexcel.version>
        <knife4j.version>4.3.0</knife4j.version>
    </properties>

    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot Validation -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- Hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>

        <!-- EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easyexcel.version}</version>
        </dependency>

        <!-- Commons IO (required by POI 5.2.5 bundled with EasyExcel 4.x) -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.13.0</version>
        </dependency>

        <!-- Knife4j (OpenAPI 3 / Spring Boot 2.x) -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-openapi3-spring-boot-starter</artifactId>
            <version>${knife4j.version}</version>
        </dependency>

        <!-- Spring Boot Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <excludes>
                    <exclude>**/~$*</exclude>
                </excludes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                    <fork>true</fork>
                    <executable>C:\SoftWare\Devtools\JDK8\bin\javac</executable>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

2.controller

    @PostMapping("/export")
    @Operation(summary = "Template Export", description = "Export employee data using an Excel template")
    public void exportExcel(HttpServletResponse response) {
        log.info("Exporting employee data with template");
        employeeExcelService.exportWithTemplate(response, null);
    }

3.service

private static final String TEMPLATE_PATH = "templates/excel/employee_template.xlsx";

public void exportWithTemplate(HttpServletResponse response, List<EmployeeExcelDTO> dataList) {
        // If no data provided, use sample data
        if (CollUtil.isEmpty(dataList)) {
            dataList = buildSampleData();
        }

        try {
            // Load template from classpath resources
            ClassPathResource templateResource = new ClassPathResource(TEMPLATE_PATH);

            // Set response headers
            setExcelResponseHeaders(response, "员工信息导出_" + DateUtil.format(new Date(), "yyyyMMddHHmmss"));

            OutputStream outputStream = response.getOutputStream();

            if (templateResource.exists()) {
                // Use EasyExcel fill mode with placeholders
                log.info("Using classpath template with fill mode: {}", TEMPLATE_PATH);
                try (InputStream templateStream = templateResource.getInputStream()) {
                    ExcelWriter excelWriter = EasyExcel.write(outputStream)
                            .withTemplate(templateStream)
                            .build();
                    WriteSheet writeSheet = EasyExcel.writerSheet().build();

                    // Fill single-value placeholder: {exportDate}
                    Map<String, Object> dateMap = new HashMap<>();
                    dateMap.put("exportDate", DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
                    excelWriter.fill(dateMap, writeSheet);

                    // Fill list data: {.fieldName} placeholders
                    excelWriter.fill(dataList, writeSheet);
                    excelWriter.finish();
                }
            } else {
                // Fallback: standard export without template
                log.warn("Template not found in classpath: {}, using standard export", TEMPLATE_PATH);
                EasyExcel.write(outputStream, EmployeeExcelDTO.class)
                        .sheet("员工信息")
                        .doWrite(dataList);
            }

            outputStream.flush();
            IoUtil.close(outputStream);

        } catch (IOException e) {
            log.error("Failed to export Excel file", e);
            throw new RuntimeException("Failed to export Excel file: " + e.getMessage(), e);
        }
    }

private void setExcelResponseHeaders(HttpServletResponse response, String fileName) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=" + encodedFileName + ".xlsx");
    }

private List<EmployeeExcelDTO> buildSampleData() {
        List<EmployeeExcelDTO> list = new ArrayList<>();
        list.add(EmployeeExcelDTO.builder()
                .employeeId("EMP001")
                .name("张三")
                .department("技术部")
                .position("高级开发工程师")
                .salary(25000.0)
                .joinDate(DateUtil.parse("2022-03-15"))
                .phone("13800138001")
                .email("zhangsan@example.com")
                .build());
        list.add(EmployeeExcelDTO.builder()
                .employeeId("EMP002")
                .name("李四")
                .department("市场部")
                .position("市场经理")
                .salary(20000.0)
                .joinDate(DateUtil.parse("2021-07-01"))
                .phone("13800138002")
                .email("lisi@example.com")
                .build());
        list.add(EmployeeExcelDTO.builder()
                .employeeId("EMP003")
                .name("王五")
                .department("财务部")
                .position("财务分析师")
                .salary(18000.0)
                .joinDate(DateUtil.parse("2023-01-10"))
                .phone("13800138003")
                .email("wangwu@example.com")
                .build());
        list.add(EmployeeExcelDTO.builder()
                .employeeId("EMP004")
                .name("赵六")
                .department("人事部")
                .position("HR专员")
                .salary(15000.0)
                .joinDate(DateUtil.parse("2023-06-20"))
                .phone("13800138004")
                .email("zhaoliu@example.com")
                .build());
        list.add(EmployeeExcelDTO.builder()
                .employeeId("EMP005")
                .name("孙七")
                .department("技术部")
                .position("前端开发工程师")
                .salary(22000.0)
                .joinDate(DateUtil.parse("2022-09-05"))
                .phone("13800138005")
                .email("sunqi@example.com")
                .build());
        return list;
    }

4.导出模版(动态表头+模版导出示例)

注意：表头上的占位不带点.,列表中要带点。