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;
}
}
原文地址:https://blog.csdn.net/weixin_42477252/article/details/146208149
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.kler.cn/a/586453.html 如若内容造成侵权/违法违规/事实不符,请联系邮箱:809451989@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.kler.cn/a/586453.html 如若内容造成侵权/违法违规/事实不符,请联系邮箱:809451989@qq.com进行投诉反馈,一经查实,立即删除!