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;}
}