前言
学习是自己的事。
但是跟着我学习,也未尝不可。
这种一对多的导出需求,好像确实也是比较常见的:
表面拒绝,反手上演一手实战示例。
内容:
① 一对多关系数据 (合并单元格)数据的 导出
②一对多关系数据 (合并单元格)数据的 导入
导入导出一块给整了,直接杀死比赛。
(看官们,收藏起来,以后备用。顺手给我点个赞。)
之前写过一篇极其简单的excel导入导出,是单个文件的:
Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入_小目标青年的博客-CSDN博客
还写过一篇单个,多个 excel文件导出,转成ZIP包的:
SpringBoot 导出多个Excel文件,压缩成.zip格式下载_小目标青年的博客-CSDN博客
还有指定模板导出的:
Springboot 指定自定义模板导出Excel文件_小目标青年的博客-CSDN博客_自定义导出excel
正文
模拟一个这种数据的业务场景:
效果,数据导出:
实战:
先看看工程目录结构:
pom.xml 引入核心依赖:
cn.afterturneasypoi-spring-boot-starter4.1.3org.projectlomboklombok1.18.10providedorg.springframework.bootspring-boot-starter-weborg.springframework.bootspring-boot-starter-testtest
这篇文章核心就是使用easypoi 的 注解
@Excel()
合并单元格、复合表格的关键注解
@ExcelCollection()
项目小组类
ProjectGroupExcelVO.java
import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelCollection;import lombok.Data;import java.util.List;/** * @Author: JCccc * @Description: * @Date: 1/1/1 1:11 */@Datapublic class ProjectGroupExcelVO {@Excel(name = "小组名称", needMerge = true, width = 20,height = 8)private String groupName;@Excel(name = "小组口号", needMerge = true, width = 20,height = 8)private String groupSlogan;@Excel(name = "小组类型", needMerge = true, width = 20,height = 8)private String groupType;@ExcelCollection(name = "组员信息")private List groupUsers;}
简析:
组员的类
GroupUserExcelVO.java
import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;/** * @Author: JCccc * @Description: * @Date: 1/1/1 1:11 */@Datapublic class GroupUserExcelVO {@Excel(name = "组员名字", width = 20,height = 8)private String name;@Excel(name = "组员电话", width = 20,height = 8)private String phone;@Excel(name = "年龄",width = 20,height = 8)private Integer age;}
导入导出工具类一个
MyExcelUtils.java
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 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.List;import java.util.NoSuchElementException;/** * @Author: JCccc * @Description: * @Date: 1/1/1 1:11 */public class MyExcelUtils {/** * 功能描述:复杂导出Excel,包括文件名以及表名,不创建表头 * * @param list 导出的实体类 * @param title 表头名称 * @param sheetName sheet表名 * @param pojoClass 映射的实体类 * @param fileName * @param response * @return */public static void exportExcel(List List importExcel(MultipartFile file, Integer titleRows, Integer headerRows,Class pojoClass) {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List list = null;try {list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (NoSuchElementException e) {throw new RuntimeException("excel文件不能为空");} catch (Exception e) {throw new RuntimeException(e.getMessage());}return list;}}
导出接口:
TestController.java
import com.jc.excel.excelVO.GroupUserExcelVO;import com.jc.excel.excelVO.ProjectGroupExcelVO;import com.jc.excel.util.MyExcelUtils;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import java.util.ArrayList;import java.util.List;/** * @Author: JCccc * @Description: * @Date: 1/1/1 1:11 */@RestControllerpublic class TestController {/** * excel导出* * @return */@GetMapping(value = "/exportTest")public void export(HttpServletResponse response) {List projectGroupList=new ArrayList();//小组A数据模拟ProjectGroupExcelVO groupA=new ProjectGroupExcelVO();groupA.setGroupName("小组A");groupA.setGroupSlogan("天天向上,爱学习!");groupA.setGroupType("奋斗类型");List groupUserAList=new ArrayList();GroupUserExcelVO groupUser1=new GroupUserExcelVO();groupUser1.setName("小收");groupUser1.setPhone("123456");groupUser1.setAge(18);GroupUserExcelVO groupUser2=new GroupUserExcelVO();groupUser2.setName("小藏");groupUser2.setPhone("654321");groupUser2.setAge(20);groupUserAList.add(groupUser1);groupUserAList.add(groupUser2);groupA.setGroupUsers(groupUserAList);//小组B数据模拟ProjectGroupExcelVO groupB=new ProjectGroupExcelVO();groupB.setGroupName("小组B");groupB.setGroupSlogan("跟着JC学java,稳!");groupB.setGroupType("努力类型");List groupBUserBList=new ArrayList();GroupUserExcelVO groupUserB1=new GroupUserExcelVO();groupUserB1.setName("小点");groupUserB1.setPhone("123456");groupUserB1.setAge(12);GroupUserExcelVO groupUserB2=new GroupUserExcelVO();groupUserB2.setName("小赞");groupUserB2.setPhone("654321");groupUserB2.setAge(15);GroupUserExcelVO groupUserB3=new GroupUserExcelVO();groupUserB3.setName("JCccc");groupUserB3.setPhone("136919xxxxx");groupUserB3.setAge(10000);groupBUserBList.add(groupUserB1);groupBUserBList.add(groupUserB2);groupBUserBList.add(groupUserB3);groupB.setGroupUsers(groupBUserBList);projectGroupList.add(groupA);projectGroupList.add(groupB);String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss")); MyExcelUtils.exportExcel(projectGroupList,"小组信息","小组信息",ProjectGroupExcelVO.class,"小组信息文件"+time+".xls",response);}}
调用一下看看导出的效果:
非常OK:
接下来是导入,写个简单接口玩一下:
数据:
导入 接口代码:
/** * excel导入 * * @return */@PostMapping(value = "/importTest")public void importTest( @RequestParam("file") MultipartFile file) {List projectGroupExcelVOList = MyExcelUtils.importExcel(file, 1, 2, ProjectGroupExcelVO.class);System.out.println(projectGroupExcelVOList.toString());System.out.println("-----------------------------------");System.out.println("写入数据库");}
调用看看效果:
导入成功,就是如此简单。