springBoot 通过模板导出Excel文档的实现

springBoot 通过模板导出Excel文档的实现是我们常用的功能，今天我们分享一下：

1、pom.xml中引用包

<dependencies>
    <!-- SpringBoot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- Apache POI - Excel 处理 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>
    
    <!-- 日期格式化工具 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.12.0</version>
    </dependency>
</dependencies>

2、用户实体

package com.example.demo.entity;

import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String gender;
    private Date createTime;
}

3、控制层代码

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.ExcelExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/api/excel")
public class ExcelExportController {

    @Autowired
    private ExcelExportService excelExportService;

    @GetMapping("/export")
    public void exportUsers(HttpServletResponse response) throws IOException {
        // 模拟数据
        List<User> userList = createSampleUsers();
        
        // 导出Excel
        excelExportService.exportUsersWithTemplate(userList, response);
    }

    private List<User> createSampleUsers() {
        List<User> userList = new ArrayList<>();
        
        User user1 = new User();
        user1.setId(1L);
        user1.setName("张三");
        user1.setAge(25);
        user1.setGender("男");
        user1.setCreateTime(new Date());
        
        User user2 = new User();
        user2.setId(2L);
        user2.setName("李四");
        user2.setAge(30);
        user2.setGender("女");
        user2.setCreateTime(new Date(System.currentTimeMillis() - 86400000));
        
        userList.add(user1);
        userList.add(user2);
        
        return userList;
    }
}

4、业务层代码

package com.example.demo.service;

import com.example.demo.entity.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class ExcelExportService {

    public void exportUsersWithTemplate(List<User> userList, HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用户信息_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        // 读取模板文件
        ClassPathResource resource = new ClassPathResource("templates/user_template.xlsx");
        InputStream inputStream = resource.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

        // 获取模板中的样式（假设模板第1行为表头）
        Row headerRow = sheet.getRow(0);
        CellStyle[] cellStyles = new CellStyle[headerRow.getLastCellNum()];
        for (int i = 0; i < cellStyles.length; i++) {
            cellStyles[i] = headerRow.getCell(i).getCellStyle();
        }

        // 从第2行开始写入数据
        int rowNum = 1;
        for (User user : userList) {
            Row row = sheet.createRow(rowNum++);
            
            // 创建单元格并设置值
            createCell(row, 0, user.getId(), cellStyles[0]);
            createCell(row, 1, user.getName(), cellStyles[1]);
            createCell(row, 2, user.getAge(), cellStyles[2]);
            createCell(row, 3, user.getGender(), cellStyles[3]);
            
            // 日期格式化
            if (user.getCreateTime() != null) {
                createCell(row, 4, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateTime()), cellStyles[4]);
            }
        }

        // 输出文件
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.flush();
        outputStream.close();
    }

    private void createCell(Row row, int column, Object value, CellStyle style) {
        Cell cell = row.createCell(column);
        cell.setCellStyle(style);
        
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }
}

5、创建Excel 模板

在 src/main/resources/templates 目录下创建 user_template.xlsx 文件，设置表头如下：

调用后端接口导出excel文档。

到此，springBoot 通过模板导出Excel文档的实现分享完成，下篇分享视频和图片的上传下载等功能，敬请期待！