Springboot 導入導出Excel ，一對多關系，複合表格、合并單元格資料

前言

學習是自己的事。

但是跟着我學習，也未嘗不可。

這種一對多的導出需求，好像确實也是比較常見的： 

表面拒絕，反手上演一手實戰示例。

内容：

① 一對多關系資料 （合并單元格）資料的 導出

②一對多關系資料 （合并單元格）資料的 導入

導入導出一塊給整了，直接殺死比賽。

​

正文

模拟一個這種資料的業務場景：

效果，資料導出：

實戰：

先看看工程目錄結構：

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

簡析：

組員的類

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("寫入資料庫");
    }      

調用看看效果：