easyPoi實現動表頭Excel的導入和導出

Maven依賴

!-- EasyPoi 核心依賴 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version></dependency><!-- EasyPoi Web支持 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.4.0</version></dependency><!-- EasyPoi 注解支持 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.4.0</version></dependency><!-- Apache POI 基礎包 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><!-- 處理 Excel 2007+ (xlsx) 必須的包 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><!-- 處理 XMLSchema/XSSF 時需要的 (推薦加上) --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency><!-- 如果要導出圖片、圖表，推薦再加上 ooxml-schemas 全量包 --><dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.4</version></dependency>

實現類

/*** Created by ls on 2025/9/14.*/
@RestController
public class EasypoiController {/*** 導出 Excel （動態表頭）* 訪問: http://localhost:8080/exportDynamic*/@GetMapping("/exportDynamic")public void exportDynamic(HttpServletResponse response) throws Exception {// 1. 動態表頭配置（實際項目可從前端傳參）List<ExcelExportEntity> entityList = new ArrayList<>();entityList.add(new ExcelExportEntity("姓名", "name"));entityList.add(new ExcelExportEntity("年齡", "age"));entityList.add(new ExcelExportEntity("城市", "city"));entityList.add(new ExcelExportEntity("成績", "score"));// 2. 模擬數據List<Map<String, Object>> dataList = new ArrayList<>();dataList.add(mapOf("name", "張三", "age", 18, "city", "北京", "score", 95));dataList.add(mapOf("name", "李四", "age", 20, "city", "上海", "score", 88));dataList.add(mapOf("name", "王五", "age", 22, "city", "廣州", "score", 92));// 3. 生成 WorkbookExportParams params = new ExportParams("學生信息表", "Sheet1");Workbook workbook = ExcelExportUtil.exportExcel(params, entityList, dataList);// 4. 設置響應頭并下載String fileName = URLEncoder.encode("動態表頭示例.xlsx", "UTF-8");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);workbook.write(response.getOutputStream());}// 小工具方法：快速構造 Mapprivate static Map<String, Object> mapOf(Object... kv) {Map<String, Object> m = new LinkedHashMap<>();for (int i = 0; i < kv.length; i += 2) {m.put(String.valueOf(kv[i]), kv[i + 1]);}return m;}/*** 動態導出 Excel* @param response HttpServletResponse*/@GetMapping("/export")public void exportExcel(HttpServletResponse response) throws Exception {// ======= 1. 動態表頭定義（可從前端傳 JSON，這里寫死示例） =======List<ExcelExportEntity> entityList = new ArrayList<>();entityList.add(new ExcelExportEntity("姓名", "name"));entityList.add(new ExcelExportEntity("年齡", "age"));entityList.add(new ExcelExportEntity("城市", "city"));entityList.add(new ExcelExportEntity("成績", "score"));// ======= 2. 模擬數據 =======List<Map<String, Object>> dataList = new ArrayList<>();dataList.add(mapOf("name", "張三", "age", 18, "city", "北京", "score", 95));dataList.add(mapOf("name", "李四", "age", 20, "city", "上海", "score", 88));// ======= 3. 生成 Excel =======ExportParams params = new ExportParams("學生信息表", "Sheet1");Workbook workbook = ExcelExportUtil.exportExcel(params, entityList, dataList);// ======= 4. 輸出到瀏覽器 =======String fileName = URLEncoder.encode("動態導出.xlsx", "UTF-8");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);workbook.write(response.getOutputStream());}/*** 動態導入 Excel* @param file 上傳的 Excel 文件* @return 統一字段的數據*/@PostMapping("/import")public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) throws Exception {try (InputStream is = file.getInputStream();Workbook workbook = WorkbookFactory.create(is)) {Sheet sheet = workbook.getSheetAt(0);DataFormatter formatter = new DataFormatter();int headerStart = 1; // 從第二行開始，索引是 1（第一行索引是0）int headRows = 1; // 表頭占一行int firstCol = 0;int lastCol = sheet.getRow(headerStart).getLastCellNum();// 組裝表頭List<String> headers = new ArrayList<>();Row headerRow = sheet.getRow(headerStart);for (int c = firstCol; c < lastCol; c++) {Cell cell = headerRow.getCell(c);String val = cell != null ? formatter.formatCellValue(cell).trim() : "COLUMN_" + c;headers.add(val);}// 遍歷數據行List<Map<String, Object>> data = new ArrayList<>();int lastRow = sheet.getLastRowNum();for (int r = headerStart + headRows; r <= lastRow; r++) {Row row = sheet.getRow(r);if (row == null) continue;Map<String, Object> map = new LinkedHashMap<>();boolean allEmpty = true;for (int c = firstCol; c < lastCol; c++) {Cell cell = row.getCell(c);String val = cell != null ? formatter.formatCellValue(cell).trim() : "";if (!val.isEmpty()) allEmpty = false;map.put(headers.get(c - firstCol), val);}if (!allEmpty) data.add(map);}Map<String, Object> resp = new HashMap<>();resp.put("headers", headers);resp.put("data", data);return resp;}}}