当前位置：首页 > news >正文

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

news 2025/6/8 5:35:03

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 {@Autowiredprivate ExcelExportService excelExportService;@GetMapping("/export")public void exportUsers(HttpServletResponse response) throws IOException {// 模拟数据List<User> userList = createSampleUsers();// 导出ExcelexcelExportService.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 文件，设置表头如下：