easy-poi导出and导入一对多数据excel

easy-poi导出and导入一对多数据excel

一、导入jar包

        <!-- easy-poi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

二、创建excel对象

father-obj

package com.example.excel.easypoi.entity.my;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Data
public class Father implements Serializable {

    @Excel(name = "编号", needMerge = true)
    private String id;

    @Excel(name = "姓名", needMerge = true)
    private String name;

    @Excel(name = "头像",type = 2,imageType = 2,width = 20,height = 15, needMerge = true)
    private byte[] logo;

    @Excel(name="年龄", orderNum="3", suffix="岁",needMerge = true)
    private Integer age;

    @Excel(name="生日", width=20.0, format="yyyy-MM-dd HH:mm:ss", orderNum="2",needMerge = true)
    private Date bir;

    @Excel(name = "状态", width = 25, replace = {"待审_0", "通过_1"}, addressList = true, needMerge = true)
    private String status;

    @ExcelCollection(name = "子列表")
    private List<Son> sonList;
}

son-obj

package com.example.excel.easypoi.entity.my;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;
@Data
public class Son  implements Serializable {

    @Excel(name="子编号")
    private String id;

    @Excel(name="子姓名")
    private String name;

}

三、工具类

package com.example.excel.easypoi.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: reshui
 * description:
 * DateTime:2025/2/20-11:17
 */
@Slf4j
public class EasyPoiExcelUtil {
    /**
     * 通用的 Excel 导入方法
     * @param file 上传的 Excel 文件
     * @param clazz 要导入的数据类型的 Class 对象
     * @param titleRows 标题所占的行数
     * @param headRows 表头所占的行数
     * @param <T> 泛型类型，表示要导入的数据类型
     * @return 包含导入数据的列表
     * @throws Exception 当读取文件输入流出现异常时抛出
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz, int titleRows, int headRows) throws Exception {
        // 导入配置参数
        ImportParams params = new ImportParams();
        // 标题占几行
        params.setTitleRows(titleRows);
        // 表头占几行
        params.setHeadRows(headRows);
        // 参数1:输入流  参数2:导入的数据类型  参数3:导入配置参数
        return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
    }
    public static void download(HttpServletResponse response, ExportParams exportParams,Class<?> clazz, List<?> data,String fileName) throws IOException {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, data);
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            log.error("导出Excel异常:", e);
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

}

四、controller-api接口层

package com.example.excel.easypoi.controller;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.example.excel.easypoi.entity.my.Father;
import com.example.excel.easypoi.entity.my.Son;
import com.example.excel.easypoi.util.EasyPoiExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @author: ly
 * description:
 * DateTime:2025/2/20-10:25
 */
@RestController
@RequestMapping("/easyCommon")
public class EasyPoiCommonController {


    @PostMapping("import")
    public List<Father> importData(MultipartFile file) throws Exception {
        List<Father> dataList = EasyPoiExcelUtil.importExcel(file, Father.class, 0, 2);
        dataList.forEach(System.out::println);
        return dataList;
    }

    @GetMapping("export")
    public void exportData(HttpServletResponse response, Integer x) throws Exception {
        List<Father> fatherList = getFatherList(x);
        EasyPoiExcelUtil.download(response, new ExportParams(), Father.class, fatherList, "用户信息列表");
    }

    public List<Father> getFatherList(Integer x) {
        List<Father> userList = new ArrayList<>();
        for (int i = 1; i <= x; i++) {
            Father user = new Father();
            user.setId("编号" + i);
            user.setName("姓名-" + i);
            user.setStatus(i % 2 == 0 ? "1" : "0");
            user.setBir(new Date());
            user.setAge(i);
            user.setLogo(null);

            Random rand = new Random();
            int num = rand.nextInt(5) + 1;
            List<Son> orderList = new ArrayList<>(num);
            for (int j = 1; j <= num; j++) {
                Son order = new Son();
                order.setId("订单号" + j);
                order.setName("商品" + j);
                orderList.add(order);
            }
            user.setSonList(orderList);
            userList.add(user);
        }
        return userList;
    }
}