一、EasyExcel 簡介

1、Excel匯入匯出的應用場景

• 資料匯入：減輕錄入作業量
• 資料匯出：統計資訊歸檔
• 資料傳輸：異構系統之間資料傳輸

2、EasyExcel特點

Java領域決議、生成Excel比較有名的框架有Apache poi、jxl等，但他們都存在一個嚴重的問題就是非常的耗記憶體，如果你的系統并發量不大的話可能還行，但是一旦并發上來后一定會OOM或者JVM頻繁的full gc，

EasyExcel是阿里巴巴開源的一個excel處理框架，以使用簡單、節省記憶體著稱，EasyExcel能大大減少占用記憶體的主要原因是在決議Excel時沒有將檔案資料一次性全部加載到記憶體中，而是從磁盤上一行行讀取資料，逐個決議，

EasyExcel采用一行一行的決議模式，并將一行的決議結果以觀察者的模式通知處理(AnalysisEventListener)，

3、案例1:EasyExcel進行Excel寫操作

1、pom中引入xml相關依賴

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
    <!-- 因為EasyExcel底層是Poi所以需要引入poi的依賴 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
</dependencies>

2、創建物體類

@Data
public class WriteData {

    //設定excel表頭名稱
    @ExcelProperty("學生編號")
    private Integer sno;

    @ExcelProperty("學生姓名")
    private String sname;
}

3、實作寫操作

public static void testWrite(){
    //實作excel的寫操作
    //1.設定寫入檔案夾地址和excel名稱
    String filename = "F:\\write.xlsx";
    //2.呼叫easyexcel里面的方法實作寫操作
    EasyExcel.write(filename, WriteData.class).sheet("學生串列").doWrite(getData());
}

public static List <WriteData> getData(){
    List <WriteData> list = new ArrayList <>();
    for(int i = 0;i < 10;i++){
        String sname = "lucy"+i;
        list.add(new WriteData(i, sname));
    }
    return list;
}

4、案例2:EasyExcel進行Excel讀操作

1、創建物體類并標記對應列關系

@Data
public class ReadData {
    //設定excel表頭名稱
    @ExcelProperty(index = 0)
    private Integer sno;

    @ExcelProperty(index = 1)
    private String sname;
}

2、創建監聽器進行excel檔案讀取

public class ExcelListner extends AnalysisEventListener<ReadData> {
   List<ReadData> list =  new ArrayList<ReadData>();

   //一行一行的去讀取
    @Override
    public void invoke(ReadData readData, AnalysisContext analysisContext) {
        System.out.println("****"+readData);
        list.add(readData);
    }

    //讀取Excel表頭資訊
    @Override
    public void invokeHeadMap(Map <Integer, String> headMap, AnalysisContext context) {
        System.out.println("表頭資訊:"+headMap);
    }

    //讀取完成之后執行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3、進行讀操作

public static void testRead(){
    String filename = "F:\\write.xlsx";
    EasyExcel.read(filename,ReadData.class,new ExcelListner()).sheet().doRead();
}

二、課程分類添加功能(后端)

核心:EasyExcel讀取excel內容實作

1、引入easyexcel依賴

2、使用代碼生成器把課程分類代碼生成

3、創建物體類和excel對應關系

@Data
public class SubjectData {
    @ExcelProperty(index = 0)
    private String oneSujectName;

    @ExcelProperty(index = 1)
    private String twoSujectName;
}

4、撰寫 EduSubjectController 類

@Api(description = "課程分類")
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {

    @Autowired
    private EduSubjectService eduSubjectService;

    //添加課程分類
    @ApiOperation(value = "添加課程分類")
    @PostMapping("addSubjects")
    public R addSubjects(MultipartFile file){
        boolean flag = eduSubjectService.saveSubjects(file);
        if(flag){
            return R.ok().message("檔案匯入成功!");
        }else{
            return R.error().message("檔案匯入失敗!");
        }
    }
}

5、撰寫EduSubjectServiceImpl 類

@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {

    @Autowired
    private EduSubjectService eduSubjectService;

    //添加課程分類
    @Override
    public boolean saveSubjects(MultipartFile file) {

        try {
            InputStream inputStream = file.getInputStream();
            //進行excel檔案讀取
            EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListner(eduSubjectService)).sheet().doRead();
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }

    }
}

6、創建讀取Excel監聽器

public class SubjectExcelListner extends AnalysisEventListener<SubjectData> {
    //因為SubjectExcelListner在EduSubjectServiceImpl每次會被new的形式使用,所以SubjectExcelListner不能交給
    //Spring進行管理,也就不能使用@Autowird或@Resource注解注入物件.
    //但是該類需要呼叫service中的方法進行資料庫操作,該如何使用呢?
    //可以通過構造方法傳遞引數的形式,使用service物件.

    private EduSubjectService eduSubjectService;

    public SubjectExcelListner(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }

    public SubjectExcelListner() {}


    //一行一行的去讀取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if(subjectData==null){
            throw new GuLiException(20001,"資料為空!");
        }
        //添加一級分類
        EduSubject oneSubject = this.existOneSubject(subjectData.getOneSubjectName());
        if(oneSubject==null){//沒有相同的一級分類
            oneSubject = new EduSubject();
            oneSubject.setTitle(subjectData.getOneSubjectName());
            oneSubject.setParentId("0");
            eduSubjectService.save(oneSubject);
        }

        //獲取一級分類id
        String pid = oneSubject.getId();

        //添加二級分類
        EduSubject twoSubject = this.existTwoSubject(subjectData.getTwoSubjectName(), pid);
        if(twoSubject==null){
            twoSubject = new EduSubject();
            twoSubject.setTitle(subjectData.getTwoSubjectName());
            twoSubject.setParentId(pid);
            eduSubjectService.save(twoSubject);
        }

    }


    //讀取Excel表頭
    @Override
    public void invokeHeadMap(Map <Integer, String> headMap, AnalysisContext context) {
        System.out.println("表頭資訊:"+headMap);
    }



    //讀取完成后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    //判斷一級分類是否重復
    private EduSubject existOneSubject(String name){
        QueryWrapper <EduSubject> wrapper = new QueryWrapper <>();
        wrapper.eq("title", name).eq("parent_id",0);
        EduSubject subject = eduSubjectService.getOne(wrapper);
        return subject;
    }

    //判斷二級分類是否重復
    private EduSubject existTwoSubject(String name,String pid){
        QueryWrapper <EduSubject> wrapper = new QueryWrapper <>();
        wrapper.eq("title", name).eq("parent_id", pid);
        EduSubject subject = eduSubjectService.getOne(wrapper);
        return subject;
    }
}

7、重啟oss服務，Swagger中測驗檔案上傳

http://localhost:8001/swagger-ui.html

三、課程分類添加功能(前端)

1、添加課程分類路由

/src/router/index.js:

{
        path: '/subject',
        component: Layout, //布局
        redirect: '/subject/table',
        name: 'SubjectSort',
        meta: { title: '課程分類管理', icon: 'nested' },
        children: [{
                path: 'list',
                name: 'EduSubjectList',
                component: () =>
                    import ('@/views/edu/subject/list'),
                meta: { title: '課程分類串列', icon: 'table' }
            },
            {
                path: 'import',
                name: 'EduSubjectImport',
                component: () =>
                    import ('@/views/edu/subject/import'),
                meta: { title: '匯入課程分類', icon: 'tree' }
            }
        ]
    }

2、創建課程分類頁面，修改路由對應的頁面路徑

3、在添加課程分類頁面實作效果

撰寫頁面:

<template>
  <div class="app-container">
    <el-form label-width="120px">
      <el-form-item label="資訊描述">
        <el-tag type="info">excel模版說明</el-tag>
        <el-tag>
          <i class="el-icon-download"/>
          <a :href="OSS_PATH + '/excel/template.xlsx'">點擊下載模版</a>
        </el-tag>

      </el-form-item>

      <el-form-item label="選擇Excel">
        <el-upload
          ref="upload"
          :auto-upload="false"
          :on-success="fileUploadSuccess"
          :on-error="fileUploadError"
          :disabled="importBtnDisabled"
          :limit="1"
          :action="BASE_API+'/eduservice/subject/addSubjects'"
          name="file"
          accept="application/vnd.ms-excel">
          <el-button slot="trigger" size="small" type="primary">選取檔案</el-button>
          <el-button
            :loading="false"
            style="margin-left: 10px;"
            size="small"
            type="success"
            @click="submitUpload">{{ fileUploadBtnText }}</el-button>
        </el-upload>
      </el-form-item>
    </el-form>
  </div>
</template>

撰寫js方法:

<script>
export default {

  data() {
    return {
      BASE_API: process.env.BASE_API, // 介面API地址
      OSS_PATH: process.env.OSS_PATH, // 阿里云OSS地址
      fileUploadBtnText: '上傳到服務器', // 按鈕文字
      importBtnDisabled: false, // 按鈕是否禁用,
      loading: false
    }
  },
  created() {
    
  },
  methods: {
    //上傳成功呼叫的方法
    fileUploadSuccess(response){
      if (response.success === true) {
        this.fileUploadBtnText = '匯入成功'
        this.loading = false
        this.$message({
            type: 'success',
            message: response.message
        })
      } 
      //跳轉到串列頁面
      this.$router.push({path:'/subject/list'})
    },
    //上傳失敗呼叫的方法
    fileUploadError(response){
      if(response.success === false){
        this.fileUploadBtnText = '匯入失敗'
        this.loading = false
        this.$message({
            type: 'error',
            message: response.message
        })
      }
      //跳轉到串列頁面
      this.$router.push({path:'/subject/list'})
    },
    //點擊按鈕上傳檔案到介面
    submitUpload(){
      this.importBtnDisabled = true //上傳按鈕禁用
      this.loading = true
      this.$refs.upload.submit()
    }
  }
}
</script>

4、啟動服務測驗

四、課程分類串列(后端）

1、根據回傳資料創建對應物體類

//一級分類
@Data
public class OneSubject {
    private String id;
    private String title;

    //一個一級分類里面有多個二級分類
    private List<TwoSubject> children = new ArrayList<>();
}

//二級分類
@Data
public class TwoSubject {
    private String id;
    private String title;
}

回傳資料格式為：

2、撰寫Controller類

@ApiOperation(value = "查詢所有課程分類")
@GetMapping("getAllSubject")
public R getAllSubject(){
    List <OneSubject> oneSubjectList =  eduSubjectService.getAllSubject();
    return R.ok().data("list",oneSubjectList);
}

3、撰寫Service類

//查詢所有的課程分類==>樹形
    @Override
    public List <OneSubject> getAllSubject() {
        //1.查詢所有一級分類  parent_id=0
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<EduSubject>();
        wrapper.eq("parent_id",0);
        List <EduSubject> oneSubjectList = this.baseMapper.selectList(wrapper);

        //2.查詢所有二級分類 parent_id!=0
        wrapper = new QueryWrapper<EduSubject>();
        wrapper.ne("parent_id",0);
        List <EduSubject> twoSubjectList = this.baseMapper.selectList(wrapper);

        //定義最終的回傳型別
        List <OneSubject> finalSubjectList = new ArrayList <OneSubject>();

        ///3 封裝一級分類
        //查詢出來所有一級分類list集合集合，得到每一個一級分類物件，回去每一個一級分類物件值，
        //封裝到要求的list集合里面  List<OneSubject> findSubjectList
        //List<EduSubject> ==> List <OneSubject> finalSubjectList
        for (EduSubject subject : oneSubjectList) {
            //將oneSubjectList封裝到finalSubjectList
            OneSubject oneSubject = new OneSubject();
            //以下兩種方法都可
            BeanUtils.copyProperties(subject,oneSubject);
            // oneSubject.setId(subject.getId());
            // oneSubject.setTitle(subject.getTitle());



            //在一級分類回圈遍歷查詢所有的二級分類
            //創建list集合封裝每一個一級分類的二級分類
            List <TwoSubject> children = new ArrayList <>();
            for (EduSubject eduSubject : twoSubjectList) {
                TwoSubject twoSubject = new TwoSubject();
                if (eduSubject.getParentId().equals(subject.getId())){
                    BeanUtils.copyProperties(eduSubject,twoSubject);
                    children.add(twoSubject);
                }
            }
            oneSubject.setChildren(children);

            finalSubjectList.add(oneSubject);//加入結果集  該陳述句放在76行之后也可以想想看為啥???

        }
        return finalSubjectList;
    }

4、使用swagger進行測驗

…

五、課程分類串列(前端)

1、撰寫前端介面

import request from '@/utils/request'

export default {
    //查詢所有的課程分類
    getAllSubject() {
        return request({
            url: `/eduservice/subject/getAllSubject`,
            method: 'get'
        })
    }
}

2、參考tree模塊把前端整合出來

<template>
  <div class="app-container">
    <el-input v-model="filterText" placeholder="Filter keyword" style="margin-bottom:30px;" />

    <el-tree
      ref="tree2"
      :data="data2"
      :props="defaultProps"
      :filter-node-method="filterNode"
      class="filter-tree"
      default-expand-all
    />

  </div>
</template>

3、前端介面呼叫

<script>
import subject from '@/api/edu/subject' 
export default {


  data() {
    return {
      filterText: '',
      data2: [],
      defaultProps: {
        children: 'children',
        label: 'title'
      }
    }
  },
  watch: {
    filterText(val) {
      this.$refs.tree2.filter(val)
    }
  },

  created() {
    //頁面加載完成后呼叫
    this.getSubjectList()
  },

  methods: {
    getSubjectList(){
        subject.getAllSubject().then(response=>{
          this.data2 = response.data.list;
        })
    },
    filterNode(value, data) {
      if (!value) return true
      return data.title.toLowerCase().indexOf(value) !== -1 //搜索不區分大小寫
    }
  }
}
</script>

4、啟動專案服務測驗