easyPoi实现动表头Excel的导入和导出

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. 生成 Workbook
        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());
    }

    // 小工具方法：快速构造 Map
    private 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;
        }
    }



}