一、环境准备
1.1 添加依赖
在 pom.xml 中添加 POI 相关依赖：
<!-- Apache POI 核心库 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.2</version>
</dependency>
<!-- POI-OOXML 支持 xlsx 格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>![图片[1]_SpringBoot 2.x 使用 Apache POI 实现数据导入到 Excel_知途无界](https://zhituwujie.com/wp-content/uploads/2025/07/d2b5ca33bd20250721190405.png)
1.2 基础配置类
创建 Excel 工具类基础配置：
@Configuration
public class ExcelConfig {
@Bean
public ExcelExportService excelExportService() {
return new ExcelExportService();
}
@Bean
public ExcelImportService excelImportService() {
return new ExcelImportService();
}
}二、基础导出功能实现
2.1 创建 Excel 导出服务
@Service
public class ExcelExportService {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportService.class);
/**
* 导出数据到 Excel 文件
* @param dataList 数据列表
* @param headers 表头数组
* @param fileName 文件名
* @return 文件字节数组
*/
public byte[] exportToExcel(List<Map<String, Object>> dataList, String[] headers, String fileName) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
// 创建表头行
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
}
// 填充数据行
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);
Map<String, Object> rowData = dataList.get(i);
int cellIndex = 0;
for (String key : rowData.keySet()) {
Object value = rowData.get(key);
if (value != null) {
row.createCell(cellIndex).setCellValue(value.toString());
} else {
row.createCell(cellIndex).setCellValue("");
}
cellIndex++;
}
}
// 自动调整列宽
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
// 转换为字节数组
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
return outputStream.toByteArray();
} catch (Exception e) {
logger.error("导出Excel失败", e);
throw new RuntimeException("导出Excel失败");
}
}
}2.2 控制器实现
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
private ExcelExportService excelExportService;
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() {
// 模拟数据
List<Map<String, Object>> dataList = new ArrayList<>();
Map<String, Object> data1 = new HashMap<>();
data1.put("name", "张三");
data1.put("age", 25);
data1.put("email", "zhangsan@example.com");
dataList.add(data1);
Map<String, Object> data2 = new HashMap<>();
data2.put("name", "李四");
data2.put("age", 30);
data2.put("email", "lisi@example.com");
dataList.add(data2);
String[] headers = {"姓名", "年龄", "邮箱"};
String fileName = "用户数据.xlsx";
byte[] excelBytes = excelExportService.exportToExcel(dataList, headers, fileName);
HttpHeaders header = new HttpHeaders();
header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
header.setContentDispositionFormData("attachment", fileName);
return new ResponseEntity<>(excelBytes, header, HttpStatus.OK);
}
}三、高级导出功能
3.1 样式定制
public class ExcelStyleUtil {
/**
* 创建表头样式
*/
public static CellStyle createHeaderStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
return style;
}
/**
* 创建数据行样式
*/
public static CellStyle createDataStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.LEFT);
return style;
}
}3.2 使用样式的导出方法
public byte[] exportWithStyle(List<Map<String, Object>> dataList, String[] headers, String fileName) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
// 创建样式
CellStyle headerStyle = ExcelStyleUtil.createHeaderStyle(workbook);
CellStyle dataStyle = ExcelStyleUtil.createDataStyle(workbook);
// 创建表头行
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
// 填充数据行
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);
Map<String, Object> rowData = dataList.get(i);
int cellIndex = 0;
for (String key : rowData.keySet()) {
Cell cell = row.createCell(cellIndex);
Object value = rowData.get(key);
if (value != null) {
cell.setCellValue(value.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
cellIndex++;
}
}
// 自动调整列宽
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
return outputStream.toByteArray();
} catch (Exception e) {
logger.error("导出Excel失败", e);
throw new RuntimeException("导出Excel失败");
}
}四、大数据量导出优化
4.1 SXSSFWorkbook 处理大数据
public byte[] exportLargeData(List<Map<String, Object>> dataList, String[] headers, String fileName) {
// 使用SXSSFWorkbook处理大数据量，设置内存中保留100行，其余写入临时文件
try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
Sheet sheet = workbook.createSheet("Sheet1");
// 创建表头行
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
}
// 分批处理数据
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);
Map<String, Object> rowData = dataList.get(i);
int cellIndex = 0;
for (String key : rowData.keySet()) {
Object value = rowData.get(key);
if (value != null) {
row.createCell(cellIndex).setCellValue(value.toString());
} else {
row.createCell(cellIndex).setCellValue("");
}
cellIndex++;
}
// 每处理1000行手动flush一次
if (i % 1000 == 0) {
((SXSSFSheet)sheet).flushRows(100);
}
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
workbook.dispose(); // 删除临时文件
return outputStream.toByteArray();
} catch (Exception e) {
logger.error("导出大数据量Excel失败", e);
throw new RuntimeException("导出Excel失败");
}
}4.2 异步导出实现
@RestController
@RequestMapping("/api/excel")
public class ExcelAsyncController {
@Autowired
private ExcelExportService excelExportService;
@Autowired
private TaskExecutor taskExecutor;
@GetMapping("/async-export")
public ResponseEntity<String> asyncExportExcel() {
String taskId = UUID.randomUUID().toString();
taskExecutor.execute(() -> {
try {
// 模拟大数据量
List<Map<String, Object>> dataList = generateLargeData(100000);
String[] headers = {"ID", "名称", "描述", "创建时间"};
byte[] excelBytes = excelExportService.exportLargeData(dataList, headers, "大数据导出.xlsx");
// 将文件保存到服务器或云存储
String filePath = "/tmp/excel-exports/" + taskId + ".xlsx";
Files.write(Paths.get(filePath), excelBytes);
// 可以在这里发送邮件通知用户下载
} catch (Exception e) {
logger.error("异步导出Excel失败", e);
}
});
return ResponseEntity.ok("{\"taskId\":\"" + taskId + "\",\"status\":\"processing\"}");
}
@GetMapping("/download/{taskId}")
public ResponseEntity<byte[]> downloadExcel(@PathVariable String taskId) {
String filePath = "/tmp/excel-exports/" + taskId + ".xlsx";
try {
byte[] excelBytes = Files.readAllBytes(Paths.get(filePath));
HttpHeaders header = new HttpHeaders();
header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
header.setContentDispositionFormData("attachment", "大数据导出.xlsx");
// 删除临时文件
Files.deleteIfExists(Paths.get(filePath));
return new ResponseEntity<>(excelBytes, header, HttpStatus.OK);
} catch (Exception e) {
throw new RuntimeException("文件不存在或已过期");
}
}
private List<Map<String, Object>> generateLargeData(int count) {
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < count; i++) {
Map<String, Object> data = new HashMap<>();
data.put("id", i + 1);
data.put("name", "项目" + (i + 1));
data.put("description", "这是第" + (i + 1) + "个项目的描述");
data.put("createTime", LocalDateTime.now().minusDays(i).toString());
dataList.add(data);
}
return dataList;
}
}五、Excel 模板导出
5.1 使用模板文件
public byte[] exportWithTemplate(List<Map<String, Object>> dataList, String templatePath) {
try (InputStream is = new FileInputStream(templatePath);
Workbook workbook = new XSSFWorkbook(is)) {
Sheet sheet = workbook.getSheetAt(0);
// 获取模板中的样式
Row templateRow = sheet.getRow(1);
CellStyle dataStyle = templateRow.getCell(0).getCellStyle();
// 从第2行开始填充数据（假设第1行是表头）
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);
Map<String, Object> rowData = dataList.get(i);
Cell cell0 = row.createCell(0);
cell0.setCellValue(rowData.get("name").toString());
cell0.setCellStyle(dataStyle);
Cell cell1 = row.createCell(1);
cell1.setCellValue(Integer.parseInt(rowData.get("age").toString()));
cell1.setCellStyle(dataStyle);
Cell cell2 = row.createCell(2);
cell2.setCellValue(rowData.get("email").toString());
cell2.setCellStyle(dataStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
return outputStream.toByteArray();
} catch (Exception e) {
logger.error("使用模板导出Excel失败", e);
throw new RuntimeException("导出Excel失败");
}
}5.2 动态生成多Sheet
public byte[] exportMultiSheet(Map<String, List<Map<String, Object>>> sheetDataMap, String fileName) {
try (Workbook workbook = new XSSFWorkbook()) {
for (Map.Entry<String, List<Map<String, Object>>> entry : sheetDataMap.entrySet()) {
String sheetName = entry.getKey();
List<Map<String, Object>> dataList = entry.getValue();
Sheet sheet = workbook.createSheet(sheetName);
// 创建表头（假设所有Sheet使用相同的表头结构）
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("姓名");
headerRow.createCell(1).setCellValue("年龄");
headerRow.createCell(2).setCellValue("邮箱");
// 填充数据
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);
Map<String, Object> rowData = dataList.get(i);
row.createCell(0).setCellValue(rowData.get("name").toString());
row.createCell(1).setCellValue(Integer.parseInt(rowData.get("age").toString()));
row.createCell(2).setCellValue(rowData.get("email").toString());
}
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
return outputStream.toByteArray();
} catch (Exception e) {
logger.error("导出多Sheet Excel失败", e);
throw new RuntimeException("导出Excel失败");
}
}六、导出性能优化建议
- 使用 SXSSFWorkbook 处理大数据：
- 设置合理的窗口大小（如100-1000行）
- 及时清理临时文件（调用 dispose() 方法）
- 样式优化：
- 复用 CellStyle 对象，避免为每个单元格创建新样式
- 将样式创建放在循环外部
- 内存管理：
- 对于超大文件，考虑分批次导出
- 使用 try-with-resources 确保资源释放
- 异步处理：
- 使用 Spring 的异步任务处理长时间导出
- 提供进度查询接口
- 文件存储：
- 将生成的文件暂存到服务器或云存储
- 设置文件过期时间，定期清理
七、常见问题解决方案
7.1 中文乱码问题
// 在响应头中设置正确的编码
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.set("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20"));7.2 日期格式处理
// 创建日期样式
CellStyle dateStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
// 应用样式
Cell dateCell = row.createCell(3);
dateCell.setCellValue(new Date());
dateCell.setCellStyle(dateStyle);7.3 导出超时处理
@GetMapping("/export-with-timeout")
public ResponseEntity<byte[]> exportWithTimeout() {
Future<byte[]> future = taskExecutor.submit(() -> {
// 长时间导出任务
return excelExportService.exportLargeData(generateLargeData(100000),
new String[]{"ID", "名称", "描述"}, "大数据.xlsx");
});
try {
byte[] result = future.get(30, TimeUnit.SECONDS); // 设置30秒超时
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"大数据.xlsx\"")
.body(result);
} catch (TimeoutException e) {
future.cancel(true);
throw new RuntimeException("导出超时，请尝试减少数据量或使用异步导出");
} catch (Exception e) {
throw new RuntimeException("导出失败");
}
}通过以上实现，您可以在 SpringBoot 2.x 项目中轻松集成 Apache POI 实现 Excel 导出功能，并根据需求进行各种定制化开发。
© 版权声明
文中内容均来源于公开资料，受限于信息的时效性和复杂性，可能存在误差或遗漏。我们已尽力确保内容的准确性，但对于因信息变更或错误导致的任何后果，本站不承担任何责任。如需引用本文内容，请注明出处并尊重原作者的版权。
THE END
暂无评论内容