Springboot 导入导出Excel ，一对多关系，复合表格、合并单元格数据

古城微笑少年丶 2023-09-28 16:27 49阅读 0赞

# 前言 #

> **学习是自己的事。**

> **但是跟着我学习，也未尝不可。**

这种一对多的导出需求，好像确实也是比较常见的：

![b2a5fbb24d2141be8ee13644c118f88f.jpeg][]

> 表面拒绝，反手上演一手实战示例。

## **内容：** ##

###       ① 一对多关系数据 （合并单元格）数据的 导出 ###

### **②一对多关系数据 （合并单元格）数据的 导入** ###

**导入导出一块给整了，直接杀死比赛。**

**（看官们，收藏起来，以后备用。顺手给我点个赞。）**

之前写过一篇极其简单的excel导入导出，是单个文件的：

[Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入\_小目标青年的博客-CSDN博客][Springboot _MYSQL_EXCEL_-CSDN]

还写过一篇单个，多个 excel文件导出，转成ZIP包的：

[SpringBoot 导出多个Excel文件，压缩成.zip格式下载\_小目标青年的博客-CSDN博客][SpringBoot _Excel_.zip_-CSDN]

还有指定模板导出的：  
[Springboot 指定自定义模板导出Excel文件\_小目标青年的博客-CSDN博客\_自定义导出excel][Springboot _Excel_-CSDN_excel]

--------------------

#  #

# 正文 #

模拟一个这种数据的业务场景：

![1cff7f8ced8f41f09a20fe761a157054.png][]

效果，数据导出：

![8877037637a0463ea4ed7e66acbdd746.png][]

实战：

先看看工程目录结构：  
![c26b321614934317adc4c009175b8700.png][]

pom.xml 引入核心依赖：

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

简析：  
![d41f8c9bb6c44fc6a91af46b0d02bcec.png][]

组员的类

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

调用一下看看导出的效果：

![8bd127fdda2a4ad48733865b2cf7e58c.png][]

非常OK：

![4bb8ce9bb7434a20b5d6ebbd0d1d8ff9.png][]

接下来是导入，写个简单接口玩一下：  
  
数据：

![8b9e178b79d847e6acfdaf2172be4487.png][]

导入 接口代码：

/**
         * 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("写入数据库");
        }

调用看看效果：

![bcfb86245bf747f1ab84a58e03367dc5.png][]

![f0c8dcf791764b9aa3116807706e99fd.png][]

导入成功，就是如此简单。

[b2a5fbb24d2141be8ee13644c118f88f.jpeg]: https://img-blog.csdnimg.cn/b2a5fbb24d2141be8ee13644c118f88f.jpeg
[Springboot _MYSQL_EXCEL_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/88967572
[SpringBoot _Excel_.zip_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/125790362
[Springboot _Excel_-CSDN_excel]: https://blog.csdn.net/qq_35387940/article/details/125804467
[1cff7f8ced8f41f09a20fe761a157054.png]: https://img-blog.csdnimg.cn/1cff7f8ced8f41f09a20fe761a157054.png
[8877037637a0463ea4ed7e66acbdd746.png]: https://img-blog.csdnimg.cn/8877037637a0463ea4ed7e66acbdd746.png
[c26b321614934317adc4c009175b8700.png]: https://img-blog.csdnimg.cn/c26b321614934317adc4c009175b8700.png
[d41f8c9bb6c44fc6a91af46b0d02bcec.png]: https://img-blog.csdnimg.cn/d41f8c9bb6c44fc6a91af46b0d02bcec.png
[8bd127fdda2a4ad48733865b2cf7e58c.png]: https://img-blog.csdnimg.cn/8bd127fdda2a4ad48733865b2cf7e58c.png
[4bb8ce9bb7434a20b5d6ebbd0d1d8ff9.png]: https://img-blog.csdnimg.cn/4bb8ce9bb7434a20b5d6ebbd0d1d8ff9.png
[8b9e178b79d847e6acfdaf2172be4487.png]: https://img-blog.csdnimg.cn/8b9e178b79d847e6acfdaf2172be4487.png
[bcfb86245bf747f1ab84a58e03367dc5.png]: https://img-blog.csdnimg.cn/bcfb86245bf747f1ab84a58e03367dc5.png
[f0c8dcf791764b9aa3116807706e99fd.png]: https://img-blog.csdnimg.cn/f0c8dcf791764b9aa3116807706e99fd.png