Maven 依賴（POI 5.2.3+）
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</version>
<version>5.2.3</version>
</dependency>
實體類：Student
@Data @AllArgsConstructor @NoArgsConstructor
public class Student {
private String name;
private int age;
private String grade;
}
導出工具類：支持表頭樣式、列寬自適應、數據格式化
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
public class ExcelExporter {
public static <T> void export(List<T> data, Class<T> clazz, OutputStream out, String sheetName) throws Exception {
try (Workbook wb = new XSSFWorkbook()) { // .xlsx 格式
Sheet sheet = wb.createSheet(sheetName);
// 1. 表頭樣式
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setBorderBottom(BorderStyle.THIN);
// 2. 數據樣式
CellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setBorderBottom(BorderStyle.THIN);
// 3. 寫入表頭（利用反射獲取字段名，實際項目建議用註解 @ExcelColumn("姓名")）
Field[] fields = clazz.getDeclaredFields();
Row headerRow = sheet.createRow(0);
for (int i = 0; i < fields.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(fields[i].getName()); // 簡化：直接用字段名
cell.setCellStyle(headerStyle);
}
// 4. 寫入數據
for (int rowIdx = 0; rowIdx < data.size(); rowIdx++) {
T obj = data.get(rowIdx);
Row row = sheet.createRow(rowIdx + 1);
for (int colIdx = 0; colIdx < fields.length; colIdx++) {
fields[colIdx].setAccessible(true);
Object val = fields[colIdx].get(obj);
Cell cell = row.createCell(colIdx);
setCellValue(cell, val);
cell.setCellStyle(dataStyle);
}
}
// 5. 自動調整列寬
for (int i = 0; i < fields.length; i++) {
sheet.autoSizeColumn(i);
}
wb.write(out);
}
}
private static void setCellValue(Cell cell, Object val) {
if (val == null) return;
if (val instanceof Number) cell.setCellValue(((Number) val).doubleValue());
else if (val instanceof Boolean) cell.setCellValue((Boolean) val);
else if (val instanceof Date) {
cell.setCellValue((Date) val);
CellStyle style = cell.getSheet().getWorkbook().createCellStyle();
style.cloneStyleFrom(cell.getCellStyle());
CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
style.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));
cell.setCellStyle(style);
} else cell.setCellValue(val.toString());
}
}
導入工具類：校驗必填列、類型轉換、錯誤行收集
public class ExcelImporter {
public static <T> ImportResult<T> importExcel(InputStream in, Class<T> clazz, int headerRowIndex) throws Exception {
List<T> success = new ArrayList<>();
List<String> errors = new ArrayList<>();
try (Workbook wb = WorkbookFactory.create(in)) {
Sheet sheet = wb.getSheetAt(0);
Row headerRow = sheet.getRow(headerRowIndex);
if (headerRow == null) throw new IllegalArgumentException("表頭行不存在");
// 簡化：假設列順序與字段聲明順序一致
Field[] fields = clazz.getDeclaredFields();
for (int rowIdx = headerRowIndex + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {
Row row = sheet.getRow(rowIdx);
if (row == null || isEmptyRow(row)) continue;
try {
T obj = clazz.getDeclaredConstructor().newInstance();
for (int colIdx = 0; colIdx < fields.length; colIdx++) {
Cell cell = row.getCell(colIdx);
Object val = getCellValue(cell);
fields[colIdx].setAccessible(true);
// 簡單類型轉換，實際建議用 ConvertUtils
fields[colIdx].set(obj, convert(val, fields[colIdx].getType()));
}
success.add(obj);
} catch (Exception e) {
errors.add("第 " + (rowIdx + 1) + " 行解析失敗：" + e.getMessage());
}
}
}
return new ImportResult<>(success, errors);
}
private static Object getCellValue(Cell cell) {
if (cell == null) return null;
return switch (cell.getCellType()) {
case STRING -> cell.getStringCellValue();
case NUMERIC -> DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() : cell.getNumericCellValue();
case BOOLEAN -> cell.getBooleanCellValue();
case FORMULA -> cell.getCellFormula();
default -> null;
};
}
private static Object convert(Object val, Class<?> targetType) {
if (val == null) return null;
if (targetType == String.class) return val.toString();
if (targetType == Integer.class || targetType == int.class) return ((Number) val).intValue();
if (targetType == Long.class || targetType == long.class) return ((Number) val).longValue();
if (targetType == Double.class || targetType == double.class) return ((Number) val).doubleValue();
if (targetType == Date.class) return (Date) val;
return val.toString(); // fallback
}
private static boolean isEmptyRow(Row row) {
for (Cell cell : row) if (cell != null && cell.getCellType() != CellType.BLANK) return false;
return true;
}
@Data @AllArgsConstructor
public static class ImportResult<T> {
private List<T> data;
private List<String> errors;
public boolean hasErrors() { return !errors.isEmpty(); }
}
}
Controller 示例
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
List<Student> data = service.getAllStudents();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=students.xlsx");
ExcelExporter.export(data, Student.class, response.getOutputStream(), "學生表");
}
@PostMapping("/import")
public Result<ImportResult<Student>> importExcel(@RequestParam("file") MultipartFile file) throws Exception {
ImportResult<Student> result = ExcelImporter.importExcel(file.getInputStream(), Student.class, 0);
return Result.ok(result);
}
}
進階提示
- 大數據導出：使用
SXSSFWorkbook（流式寫入，僅保留滾動窗口在內存），防止 OOM。
- 樣式復用：
CellStyle 在 Workbook 級別創建，不可跨 Workbook，大量單元格務必共享樣式對象。
- 日期/數字格式：統一用
DataFormat 定義，避免 Excel 打開顯示科學計數法。
- 註解驅動：自定義
@ExcelColumn(name="姓名", width=20, format="yyyy-MM-dd") 替代反射字段名，提升可維護性。
