前言
客户管理系统开发定制学习是自己的事。
客户管理系统开发定制但是跟着我学习,客户管理系统开发定制也未尝不可。
这种一对多的导出需求,好像确实也是比较常见的:
表面拒绝,反手上演一手实战示例。
内容:
① 一对多关系数据 (合并单元格)数据的 导出
②一对多关系数据 (合并单元格)数据的 导入
导入导出一块给整了,直接杀死比赛。
(看官们,收藏起来,以后备用。顺手给我点个赞。)
之前写过一篇极其简单的excel导入导出,是单个文件的:
还写过一篇单个,多个 excel文件导出,转成ZIP包的:
还有指定模板导出的:
正文
模拟一个这种数据的业务场景:
效果,数据导出:
实战:
先看看工程目录结构:
pom. 引入核心依赖:
- <dependencies>
- <dependency>
- <groupId>cn.afterturn</groupId>
- <artifactId>easypoi-spring-boot-starter</artifactId>
- <version>4.1.3</version>
- </dependency>
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <version>1.18.10</version>
- <scope>provided</scope>
- </dependency>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-web</artifactId>
- </dependency>
-
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-test</artifactId>
- <scope>test</scope>
- </dependency>
- </dependencies>
这篇文章核心就是使用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
- */
- @Data
- public 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<GroupUserExcelVO> groupUsers;
-
- }
简析:
组员的类
GroupUserExcelVO.java
- import cn.afterturn.easypoi.excel.annotation.Excel;
- import lombok.Data;
-
- /**
- * @Author: JCccc
- * @Description:
- * @Date: 1/1/1 1:11
- */
- @Data
- public 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, String title, String sheetName, Class<?> pojoClass,
- String fileName, HttpServletResponse response) {
- defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
- }
-
- /**
- * 功能描述:默认导出方法
- *
- * @param list 导出的实体集合
- * @param fileName 导出的文件名
- * @param pojoClass pojo实体
- * @param exportParams ExportParams封装实体
- * @param response
- * @return
- */
- private static void defaultExport( List<?> list, Class<?> pojoClass, String fileName,
- HttpServletResponse response, ExportParams exportParams) {
- Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
- if (workbook != null) {
- downLoadExcel(fileName, response, workbook);
- }
- }
-
- /**
- * 功能描述:Excel导出
- *
- * @param fileName 文件名称
- * @param response
- * @param workbook Excel对象
- * @return
- */
- private static void downLoadExcel( String fileName, HttpServletResponse response,
- Workbook workbook) {
- try {
- response.setCharacterEncoding("UTF-8");
- response.setHeader("content-Type", "multipart/form-data");
- response.setHeader("Content-Disposition",
- "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
- workbook.write(response.getOutputStream());
-
- } catch (IOException e) {
- throw new RuntimeException(e);
- }
- }
-
-
- /**
- * 功能描述:根据接收的Excel文件来导入Excel,并封装成实体类
- *
- * @param file 上传的文件
- * @param titleRows 表标题的行数
- * @param headerRows 表头行数
- * @param pojoClass Excel实体类
- * @return
- */
- public static <T> List<T> importExcel( MultipartFile file, Integer titleRows, Integer headerRows,
- Class<T> pojoClass) {
- if (file == null) {
- return null;
- }
- ImportParams params = new ImportParams();
- params.setTitleRows(titleRows);
- params.setHeadRows(headerRows);
- List<T> 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
- */
- @RestController
- public class TestController {
-
-
- /**
- * excel导出
- *
- * @return
- */
- @GetMapping(value = "/exportTest")
- public void export(HttpServletResponse response) {
-
-
- List<ProjectGroupExcelVO> projectGroupList=new ArrayList<>();
-
- //小组A数据模拟
- ProjectGroupExcelVO groupA=new ProjectGroupExcelVO();
- groupA.setGroupName("小组A");
- groupA.setGroupSlogan("天天向上,爱学习!");
- groupA.setGroupType("奋斗类型");
- List<GroupUserExcelVO> 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<GroupUserExcelVO> 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<ProjectGroupExcelVO> projectGroupExcelVOList = MyExcelUtils.importExcel(file, 1, 2, ProjectGroupExcelVO.class);
-
- System.out.println(projectGroupExcelVOList.toString());
-
- System.out.println("-----------------------------------");
-
- System.out.println("写入数据库");
- }
调用看看效果:
导入成功,就是如此简单。