POI的简单使用-EasyExcel 入门（完整项目测试（传智在线项目））

1. EasyExcel 入门

1.1 EasyExcel概述

​ EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

​ 官网：https://www.yuque.com/easyexcel/doc/easyexcel

​ github地址：https://github.com/alibaba/easyexcel

1.2 EasyExcel 特点

​ Java解析、生成Excel比较有名的框架有Apache poi、jxl，但他们都存在一个严重的问题就是非常的耗内存。

​ EasyExcel 重写了poi，使一个3M的excel只需要几M内存，并且再大的excel不会出现内存溢出。

​ 64M内存1分钟内读取75M(46W行25列)的Excel。

1.3.0小案例（按照下面步骤完成读写操作）

1.3 环境搭建

1.3.1 测试父项目

• 项目名：zx-test-parent

• 修改pom文件

      <dependencies>
          <!-- 测试 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
      </dependencies>
  

1.3.2 测试excel项目

• 项目名：zx-test-excel

  

  

• 修改pom，添加依赖

      <dependencies>
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>2.2.6</version>
          </dependency>
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
          </dependency>
      </dependencies>
  

1.4 基本操作

1.4.1 测试JavaBean

package com.czxy.zx.demo01;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

/**
 * Created by liangtong.
 */
@Data
public class Student {
    @ExcelProperty("编号")
    private String id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("电话")
    private String telephone;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("生日")
    private Date brithday;
}

1.4.2 测试文件路径

package com.czxy.zx.demo01;

import org.junit.jupiter.api.Test;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
public class TestExcel {
    /**
     * 获得根路径
     * @return
     */
    public String getPath() {
        return this.getClass().getResource("/").getPath();
    }

    @Test
    public void testPath() {
        // 测试文件路径
        String path = getPath() + "student_demo.xls";
        System.out.println(path);
    }


}
~~~### 1.4.3 写操作

~~~java
   /**
     * 准备数据
     * @return
     */
    private List<Student> getData(){
        List<Student> list = new ArrayList<Student>();
        for(int i = 0 ; i < 10 ; i ++){
            Student student = new Student();
            student.setId("stu" + i);
            student.setName("wang" + i);
            student.setAge( 18 + i );
            student.setTelephone("1361234" + i);
            student.setEmail("wang" + i + "@czxy.com");
            student.setBrithday(new Date());
            list.add(student);
        }
        return list;
    }

    @Test
    public void testWrite(){
        String file = getPath() + "student_demo.xls";
        //EasyExcel.write(位置,对象).sheet("表名").doWrite(数据);
        EasyExcel.write(file,Student.class).sheet("Java34").doWrite(getData());
    }

1.4.3 读操作

• 处理类：

  • 处理类需要实现 AnalysisEventListener 接口

  package com.czxy.zx.demo01;
  
  import com.alibaba.excel.context.AnalysisContext;
  import com.alibaba.excel.event.AnalysisEventListener;
  import com.czxy.zx.domain.Student;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  public class StudentListener extends AnalysisEventListener<Student> {
      @Override
      public void invoke(Student student, AnalysisContext analysisContext) {
          System.out.println(student);
      }
  
      @Override
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
          System.out.println("解析完成");
      }
  }
  
  

• 测试

      @Test
      public void testRead(){
          String file = getPath() + "student_demo.xls";
          //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();
          EasyExcel.read(file, Student.class, new StudentListener()).sheet("Java34").doRead();
      }
  

1.5 复杂操作

1.5.1 复合表头

package com.czxy.zx.demo02;

        import com.alibaba.excel.annotation.ExcelProperty;
        import com.alibaba.excel.annotation.format.DateTimeFormat;
        import com.alibaba.excel.annotation.write.style.ColumnWidth;
        import com.alibaba.excel.annotation.write.style.ContentRowHeight;
        import com.alibaba.excel.annotation.write.style.HeadRowHeight;
        import lombok.Data;

        import java.util.Date;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@Data
@ContentRowHeight(10)
@HeadRowHeight(20)  //行高
@ColumnWidth(25)    //列宽
public class Student2 {
    @ExcelProperty("编号")
    private String id;

    @ExcelProperty({"基本信息","姓名"})   //复制表头
    private String name;

    @ExcelProperty({"基本信息","年龄"})
    private Integer age;

    @ExcelProperty("电话")
    private String telephone;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("生日")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date brithday;
}

1.5.2 写操作：多表

package com.czxy.zx.demo02;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.czxy.zx.demo01.Student;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
public class TestExcel2 {
    /**
     * 获得根路径
     * @return
     */
    public String getPath() {
        return this.getClass().getResource("/").getPath();
    }

    /**
     * 准备数据
     * @return
     */
    private List<Student2> getData(Integer flag){
        List<Student2> list = new ArrayList<Student2>();
        for(int m = 0 ; m < 10 ; m ++){
            String i = "" + flag + m ;
            Student2 student = new Student2();
            student.setId("stu" + i);
            student.setName("wang" + i);
            student.setAge( 18 );
            student.setTelephone("1361234" + i);
            student.setEmail("wang" + i + "@czxy.com");
            student.setBrithday(new Date());
            list.add(student);
        }
        return list;
    }

    @Test
    public void testMoreSheetWrite(){
        String file = getPath() + "student_demo2.xls";

        ExcelWriter excelWriter = EasyExcel.write(file).build();

        for (int i = 0; i < 5; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Student2.class).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List<Student2> data = getData(i);
            excelWriter.write(data, writeSheet);
        }

        excelWriter.finish();
    }


}

1.5.3 读操作：多表

• 具有缓存处理类

  package com.czxy.zx.demo02;
  
  import com.alibaba.excel.context.AnalysisContext;
  import com.alibaba.excel.event.AnalysisEventListener;
  
  import java.util.ArrayList;
  import java.util.List;
  
  /**
   * Created by liangtong.
   */
  public class Student2Listener extends AnalysisEventListener<Student2> {
  
      // 批量操作数
      private static final int BATCH_COUNT = 10;
      // 用于缓存信息
      private List<Student2> cache = new ArrayList<Student2>();
  
      public void invoke(Student2 student, AnalysisContext analysisContext) {
          //保存学生信息
          cache.add(student);
          if(cache.size() >= BATCH_COUNT){
              // 保存数据
              saveData();
          }
      }
  
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
          //最后的不够 BATCH_COUNT 倍数
          saveData();
      }
  
      private void saveData() {
          // 集合不为空
          if(! cache.isEmpty()) {
              // 处理缓存数据
              System.out.println(cache);
              // 清空缓存
              cache.clear();
          }
      }
  }
  
  

• 读操作

  @Test
      public void testMoreRead(){
          String file = getPath() + "student_demo2.xls";
          //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();
  
          ExcelReader excelReader = EasyExcel.read(file, Student2.class, new Student2Listener()).build();
          // 确定需要解析的sheet
          for (int i = 0; i < 5; i++) {
              ReadSheet readSheet = EasyExcel.readSheet("模板" + i).build();
              excelReader.read(readSheet);
          }
  
          excelReader.finish();
      }
  

2. 课程科目操作

2.1 环境搭建

2.1.1 数据库

CREATE DATABASE zx_edu_course;
USE zx_edu_course;

CREATE TABLE `edu_subject`  (
  `id` VARCHAR(32) NOT NULL PRIMARY KEY COMMENT '课程科目ID',
  `title` VARCHAR(10) NOT NULL COMMENT '科目名称',
  `parent_id` VARCHAR(32) NOT NULL DEFAULT '0' COMMENT '父ID',
  `sort` INT(10)  NOT NULL DEFAULT 0 COMMENT '排序字段',
  `gmt_create` DATETIME NOT NULL COMMENT '创建时间',
  `gmt_modified` DATETIME NOT NULL COMMENT '更新时间'
) COMMENT = '课程科目';

INSERT INTO `edu_subject` VALUES ('1', '云计算', '0', 0, '2020-06-26 09:41:21', '2020-02-20 23:25:58');
INSERT INTO `edu_subject` VALUES ('2', '系统/运维', '0', 0, '2020-02-20 23:29:59', '2020-02-20 23:29:59');
INSERT INTO `edu_subject` VALUES ('3', '数据库', '0', 0, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('4', '服务器', '0', 0, '2020-02-20 23:30:19', '2020-02-20 23:30:19');

INSERT INTO `edu_subject` VALUES ('5', 'MySQL', '3', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('6', 'Oracle', '3', 2, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('7', 'Tomcat', '4', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('8', 'Nginx ', '4', 2, '2020-02-20 23:30:13', '2020-02-20 23:30:13');

INSERT INTO `edu_subject` VALUES ('9', 'MySQL优化', '5', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');

2.1.2 后端：环境

• 项目名：zx-service-course20

• pom文件

  <dependencies>
          <!--web起步依赖-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-web</artifactId>
          </dependency>
          <!-- Eureka客户端 -->
          <dependency>
              <groupId>org.springframework.cloud</groupId>
              <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
          </dependency>
          <!--spring boot监控（可选）-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-actuator</artifactId>
          </dependency>
  
  
          <!-- feign 远程调用 -->
          <dependency>
              <groupId>org.springframework.cloud</groupId>
              <artifactId>spring-cloud-starter-openfeign</artifactId>
          </dependency>
  
          <!--测试-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
  
          <!--熔断器-->
          <dependency>
              <groupId>org.springframework.cloud</groupId>
              <artifactId>spring-cloud-starter-netflix-hystrix</artifactId>
          </dependency>
  
          <!-- mybatis plus-->
          <dependency>
              <groupId>com.baomidou</groupId>
              <artifactId>mybatis-plus-boot-starter</artifactId>
              <version>${mybatis.plus.version}</version>
          </dependency>
          <!-- mysql驱动 -->
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
          </dependency>
          <!--自定义项目-->
          <dependency>
              <groupId>com.czxy.zx</groupId>
              <artifactId>zx-common20</artifactId>
              <version>1.0-SNAPSHOT</version>
          </dependency>
          <dependency>
              <groupId>com.czxy.zx</groupId>
              <artifactId>zx-domain20</artifactId>
              <version>1.0-SNAPSHOT</version>
          </dependency>
  
          <!-- redis 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-data-redis</artifactId>
          </dependency>
          <!-- JavaMail 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-mail</artifactId>
          </dependency>
          <!-- MQ 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-amqp</artifactId>
          </dependency>
  
          <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>fastjson</artifactId>
              <version>1.2.73</version>
          </dependency>
  
          <!--开发者工具-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-devtools</artifactId>
              <optional>true</optional>
          </dependency>
  
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>2.2.6</version>
          </dependency>
  
      </dependencies>
  

• yml文件

  # 服务端口号
  server:
    port: 9020
  # 服务名
  spring:
    application:
      name: course-service
    datasource:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/zx_edu_course?useUnicode=true&characterEncoding=utf8
      username: root
      password: 1234
      druid:    #druid 连接池配置
        initial-size: 1       #初始化连接池大小
        min-idle: 1           #最小连接数
        max-active: 20        #最大连接数
        test-on-borrow: true  #获取连接时候验证，会影响性能
    redis:
      database: 0       #数据库索引，取值0-15，表示16个库可选择
      host: 127.0.0.1   #服务器地址
      port: 6379        #服务器连接端口号
    mail:
      host: smtp.126.com          #发送邮件服务器
      username: itcast_lt@126.com #账号
      password: 1qaz2wsx          #密码
      default-encoding: UTF-8     #默认编码时
    rabbitmq:
      host: 127.0.0.1
      port: 5672
      username: guest
      passowrd: guest
      virtualHost: /
    devtools:
      restart:
        enabled: true  #设置开启热部署
        additional-paths: src/main/java #重启目录
        exclude: WEB-INF/**
    freemarker:
      cache: false    #页面不加载缓存，修改即时生效
  #eureka配置
  eureka:
    client:
      service-url:
        defaultZone: http://localhost:10086/eureka
    instance: #web页面显示效果和访问路径
      instance-id: ${spring.application.name}:${spring.cloud.client.ip-address}:${server.port}
      prefer-ip-address: true
  #开启log4j打印SQL语句
  logging:
    level:
      com:
        czxy:
          mapper: debug
  # feign远程调用,开启熔断
  feign:
    hystrix:
      enabled: true
  # mp日志打印
  mybatis-plus:
    configuration:
      log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  
  

• 启动类

  package com.czxy.zx;
  
  import org.springframework.boot.SpringApplication;
  import org.springframework.boot.autoconfigure.SpringBootApplication;
  import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
  import org.springframework.cloud.openfeign.EnableFeignClients;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  @SpringBootApplication
  @EnableEurekaClient
  @EnableFeignClients
  public class ZxCourseServiceApplication {
      public static void main(String[] args) {
          SpringApplication.run(ZxCourseServiceApplication.class,args);
      }
  }
  
  

2.1.3 后端：基本模块

• 创建JavaBean

  package com.czxy.zx.domain;
  
  import com.baomidou.mybatisplus.annotation.IdType;
  import com.baomidou.mybatisplus.annotation.TableField;
  import com.baomidou.mybatisplus.annotation.TableId;
  import com.baomidou.mybatisplus.annotation.TableName;
  import com.fasterxml.jackson.annotation.JsonFormat;
  import lombok.Data;
  import org.springframework.format.annotation.DateTimeFormat;
  
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  
  /**
   * 课程科目(EduSubject)表实体类
   *
   * @author 桐叔
   */
  @Data
  @TableName("edu_subject")
  public class EduSubject{
      @TableId(type = IdType.ASSIGN_UUID)
      //课程科目ID
      private String id;
      //科目名称
      private String title;
      //父ID
      private String parentId;
      //排序字段
      private Integer sort;
      //创建时间
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
      private Date gmtCreate;
      //更新时间
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
      private Date gmtModified;
  
      @TableField(exist = false)
      private List<EduSubject> children; // = new ArrayList<>();
  
  }
  

• 创建mapper

package com.czxy.zx.course.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.zx.domain.EduSubject;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@Mapper
public interface EduSubjectMapper extends BaseMapper<EduSubject> {
}

• 创建service

• 接口

  package com.czxy.zx.course.service;
  
  import com.baomidou.mybatisplus.extension.service.IService;
  import com.czxy.zx.domain.EduSubject;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  public interface EduSubjectService extends IService<EduSubject> {
  }
  
  

• 实现类

  package com.czxy.zx.course.service.impl;
  
  import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  import com.czxy.zx.course.mapper.EduSubjectMapper;
  import com.czxy.zx.course.service.EduSubjectService;
  import com.czxy.zx.domain.EduSubject;
  import org.springframework.stereotype.Service;
  import org.springframework.transaction.annotation.Transactional;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  @Service
  @Transactional
  public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
  }
  
  

• 创建controller

package com.czxy.zx.course.controller;

import com.czxy.zx.course.service.EduSubjectService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@RestController
@RequestMapping("/subject")
public class EduSubjectController {

    @Resource
    private EduSubjectService eduSubjectService;

    
}

• 配置类

2.1.4 前端

• 创建路由模块

/** When your routing table is too long, you can split it into small modules **/

import Layout from '@/layout'

const courseRouter = {
  path: '/course',           // 当前模块前缀路径，必须以/开头
  component: Layout,          // 采用布局组件显示当前模块【默认】
  redirect: '/course/subjectList',  // “教师管理”默认显示路由
  name: '课程管理',            // 路由名称
  meta: {
    title: '课程管理',         // 一级菜单名称，children.length==0 隐藏
    icon: 'table'             // 一级菜单图标，children.length==0 隐藏
  },
  children: [
    {
      path: 'subjectList',
      component: () => import('@/views/edu/course/subjectList.vue'),
      name: '科目列表',
      meta: { title: '科目列表', icon: 'list' }   //二级菜单名称
    }
  ]
}
export default courseRouter

• 创建 subjectList.vue 页面

• 配置路由

2.2 查询所有

• 以树形table展示数据

2.2.1 后端实现

• 修改 EduSubjectController

package com.czxy.zx.course.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.czxy.zx.course.service.EduSubjectService;
import com.czxy.zx.domain.EduSubject;
import com.czxy.zx.vo.BaseResult;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@RestController
@RequestMapping("/subject")
public class EduSubjectController {

    @Resource
    private EduSubjectService eduSubjectService;

    @GetMapping
    public BaseResult findAll() {
        //1 查询所有
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByAsc("parent_id");
        List<EduSubject> list = eduSubjectService.list(queryWrapper);

        //2 处理父子关系
        List<EduSubject> resultList = new ArrayList<>();
        Map<String,EduSubject> cache = new HashMap<>();
        list.forEach(eduSubject -> {
            // 获得父
            EduSubject parentEduSubject = cache.get(eduSubject.getParentId());
            // 如果没有父表示第一层，如果有父追加
            if(parentEduSubject != null) {
                // 如果有孩子，判断父对象的集合
                List<EduSubject> temp = parentEduSubject.getChildren();
                if(temp == null) {
                    parentEduSubject.setChildren(new ArrayList<>());
                }
                // 将孩子添加到父对象的集合中
                parentEduSubject.getChildren().add(eduSubject);
            } else {
                resultList.add(eduSubject);
            }
            // 缓存当前
            cache.put(eduSubject.getId(),eduSubject);

        });

        return BaseResult.ok("查询成功", resultList);
    }

}

2.2.2 前端接口

import axios from '@/utils/request'

// 查询所有课程科目
export function findAllSub() {
  return axios.get('/course-service/subject');
}

2.2.3 前端实现

• 修改 @/views/edu/course/subjectList.vue

<template>
  <div>
    <el-table
      v-loading="listLoading"
      :data="subjectList"
      border
      fit
      highlight-current-row
      style="width: 100%;"
      row-key="title"
      :tree-props="{children: 'children'}"
    >
      <el-table-column label="科目名称" prop="title" align="center" width="200">
      </el-table-column>
      <el-table-column label="排序" prop="sort" width="80px" min-width="50px">
      </el-table-column>
      <el-table-column label="添加时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtCreate | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="更新时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtModified | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="{row,$index}">
          <el-button type="primary" size="mini">
            修改
          </el-button>
          <el-button v-if="row.status!='deleted'" size="mini" type="danger" >
            删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>
  </div>
</template>

<script>
import { findAllSub } from '@/api/edu/course'

export default {
  data() {
    return {
      subjectList: [] ,
      listLoading: false,
    }
  },
  methods: {
    async findAllSubject() {  // 查询所有
      this.listLoading = true
      let { data } = await findAllSub()
      this.subjectList = data
      this.listLoading = false
    }
  },
  mounted() {
    this.findAllSubject()
  },
}
</script>

<style>

</style>

2.3 导入科目

2.3.1 需求

2.3.2 前端

• 使用 upload组件

      <!-- 文件上传 -->
      <el-upload
        class="upload-demo"
        :action="updateUrl"
        :limit="1"
        :on-exceed="handleExceed"
        :before-upload="beforeUpload"
        :on-remove="handleRemove"
        :on-success="handleSuccess"
        :file-list="fileList">
        <el-button size="small" type="primary">点击上传</el-button>
        <div slot="tip" class="el-upload__tip">只能上传xls或xlsx文件，且不超过500kb</div>
      </el-upload>
  

• 声明变量

    data() {
      return {
        fileList: [],     //上传文件列表
        updateUrl: process.env.VUE_APP_BASE_API + '/course-service/subject/upload',   //上传路径
      }
    },
  

• 编写处理函数

  handleExceed(files, fileList) {   // 超出个数限制
        this.$message.warning(`当前选择1个文件`);
      },
      beforeUpload(file) {      // 上传文件之前
        // 是否是 xlsx 文件（2003）
        const isXlsx = file.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        // 是否是 xls 文件（2010）
        const isXls = file.type === 'application/vnd.ms-excel'
        const isLt2M = file.size / 1024 / 1024 < 2;
  
        if (!isXlsx && !isXls) {
          this.$message.error('上传文件不是excel文件!');
        }
        if (!isLt2M) {
          this.$message.error('上传文件大小不能超过 2MB!');
        }
        return (isXlsx || isXls) && isLt2M;
      },
      handleRemove(file, fileList) {  // 文件列表移除文件
        console.log(file, fileList);
      },
      handleSuccess(response, file, fileList) { // 文件上传成功
        // 成功提示
        this.$message.success(response.message)
        // 刷新
        this.findAllSubject()
      }
  

2.3.2 前端：完整版

<template>
  <div>
    <el-table
      v-loading="listLoading"
      :data="subjectList"
      border
      fit
      highlight-current-row
      style="width: 100%;"
      row-key="title"
      :tree-props="{children: 'children'}"
    >
      <el-table-column label="科目名称" prop="title" align="left" width="200">
      </el-table-column>
      <el-table-column label="排序" prop="sort" width="80px" min-width="50px">
      </el-table-column>
      <el-table-column label="添加时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtCreate | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="更新时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtModified | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="{row}">
          <el-button type="primary" size="mini">
            修改
          </el-button>
          <el-button v-if="row.status!='deleted'" size="mini" type="danger" >
            删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>

    <!-- 文件上传 -->
    <el-upload
      class="upload-demo"
      :action="updateUrl"
      :limit="1"
      :on-exceed="handleExceed"
      :before-upload="beforeUpload"
      :on-remove="handleRemove"
      :on-success="handleSuccess"
      :file-list="fileList">
      <el-button size="small" type="primary">点击上传</el-button>
      <div slot="tip" class="el-upload__tip">只能上传xls或xlsx文件，且不超过500kb</div>
    </el-upload>
  </div>
</template>

<script>
import { findAllSub } from '@/api/edu/course'

export default {
  data() {
    return {
      subjectList: [] ,
      listLoading: false,
      fileList: [],     //上传文件列表
      updateUrl: process.env.VUE_APP_BASE_API + '/course-service/subject/upload',   //上传路径
    }
  },
  methods: {
    async findAllSubject() {  // 查询所有
      this.listLoading = true
      let { data } = await findAllSub()
      this.subjectList = data
      this.listLoading = false
    },
    handleExceed(files, fileList) {   // 超出个数限制
      this.$message.warning(`当前选择1个文件`);
    },
    beforeUpload(file) {      // 上传文件之前
      // 是否是 xlsx 文件（2003）
      const isXlsx = file.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      // 是否是 xls 文件（2010）
      const isXls = file.type === 'application/vnd.ms-excel'
      const isLt2M = file.size / 1024 / 1024 < 2;

      if (!isXlsx && !isXls) {
        this.$message.error('上传文件不是excel文件!');
      }
      if (!isLt2M) {
        this.$message.error('上传文件大小不能超过 2MB!');
      }
      return (isXlsx || isXls) && isLt2M;
    },
    handleRemove(file, fileList) {  // 文件列表移除文件
      console.log(file, fileList);
    },
    handleSuccess(response, file, fileList) { // 文件上传成功
      // 成功提示
      this.$message.success(response.message)
      // 刷新
      this.findAllSubject()
    }
  },
  mounted() {
    this.findAllSubject()
  },
}
</script>

<style>

</style>

2.3.4 后端

1） 完善JavaBean

package com.czxy.zx.domain;

import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 课程科目(EduSubject)表实体类
 *
 * @author 桐叔
 */
@Data
@TableName("edu_subject")
public class EduSubject{
    @TableId(type = IdType.ASSIGN_UUID)
    //课程科目ID
    private String id;
    //科目名称
    private String title;
    //父ID
    private String parentId;
    //排序字段
    private Integer sort;
    //创建时间
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtCreate;
    //更新时间
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtModified;

    @TableField(exist = false)
    private List<EduSubject> children = new ArrayList<>();

}

2）填充数据处理类

package com.czxy.zx.course.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@Component
public class SubjectMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        // 创建时间
        this.setFieldValByName("gmtCreate",new Date(), metaObject);
        // 修改时间
        this.setFieldValByName("gmtModified",new Date() , metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        // 修改时，填充的内容
        this.setFieldValByName("gmtModified",new Date() , metaObject);
    }
}

3）service：通过title查询

• 接口

  package com.czxy.zx.course.service;
  
  import com.baomidou.mybatisplus.extension.service.IService;
  import com.czxy.zx.domain.EduSubject;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  public interface EduSubjectService extends IService<EduSubject> {
      /**
       * 通过title查询
       * @param title
       * @return
       */
      EduSubject findByTitle(String title);
  }
  
  

• 实现类

  package com.czxy.zx.course.service.impl;
  
  import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
  import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  import com.czxy.zx.course.mapper.EduSubjectMapper;
  import com.czxy.zx.course.service.EduSubjectService;
  import com.czxy.zx.domain.EduSubject;
  import org.springframework.stereotype.Service;
  import org.springframework.transaction.annotation.Transactional;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  @Service
  @Transactional
  public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
      @Override
      public EduSubject findByTitle(String title) {
          QueryWrapper queryWrapper = new QueryWrapper();
          queryWrapper.eq("title", title);
  
          EduSubject eduSubject = baseMapper.selectOne(queryWrapper);
          return eduSubject;
      }
  }
  
  

4）controller：上传

 /**
     * 文件上传
     * @param file
     * @return
     */
    @PostMapping("/upload")
    public BaseResult upload(MultipartFile file) {
        try {
            // 解析excel
            EasyExcel.read(file.getInputStream(), UploadSubjectVo.class, eduSubjectListener).sheet(0).doRead();

            return BaseResult.ok("上传成功");
        } catch (IOException e) {
            return BaseResult.error("上传失败");
        }
    }

5）excel内容封装类

package com.czxy.zx.course.upload;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@Data
public class UploadSubjectVo {

    @ExcelProperty("一级分类")
    private String oneLevel;

    @ExcelProperty("二级分类")
    private String twoLevel;


}

6）上传内容处理类

package com.czxy.zx.course.upload;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.czxy.zx.course.service.EduSubjectService;
import com.czxy.zx.domain.EduSubject;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
@Component
public class EduSubjectListener extends AnalysisEventListener<UploadSubjectVo> {

    @Resource
    private EduSubjectService eduSubjectService;

    @Override
    public void invoke(UploadSubjectVo uploadSubjectVo, AnalysisContext analysisContext) {
        // 1. 处理一级
        // 1.1 查询一级
        EduSubject oneSubject = eduSubjectService.findByTitle(uploadSubjectVo.getOneLevel());
        // 1.2 保存一级
        if(oneSubject == null) {
            oneSubject = new EduSubject();
            oneSubject.setTitle(uploadSubjectVo.getOneLevel());
            oneSubject.setSort(0);
            oneSubject.setParentId("0");        // 一级默认0
            eduSubjectService.save(oneSubject);
        }

        // 2. 处理二级
        // 2.1 查询二级
        EduSubject twoSubject = eduSubjectService.findByTitle(uploadSubjectVo.getTwoLevel());
        // 2.2 保存二级
        if(twoSubject == null) {
            twoSubject = new EduSubject();
            twoSubject.setTitle(uploadSubjectVo.getTwoLevel());
            twoSubject.setSort(0);
            twoSubject.setParentId(oneSubject.getId()); //二级的父ID为一级的ID
            eduSubjectService.save(twoSubject);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3.POI入门(了解)

3.1 POI 概述

3.1.1 简介

​ Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

功能	描述
HSSFWorkBook	提供读写Microsoft Excel格式档案的功能
XSSFWorkBook	提供读写Microsoft Excel OOXML格式档案的功能
HWPF	提供读写Microsoft Word格式档案的功能
HSLF	提供读写Microsoft PowerPoint格式档案的功能
HDGF	提供读写Microsoft Visio格式档案的功能

3.1.2 官网

http://poi.apache.org/

3.2 入门案例

3.2.1 环境搭建

• 创建项目：

• 修改pom

  <dependencies>
          <!--xls-->
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi</artifactId>
              <version>3.9</version>
          </dependency>
          <!--xlsx-->
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.9</version>
          </dependency>
          <!--日期格式化工具-->
          <dependency>
              <groupId>joda-time</groupId>
              <artifactId>joda-time</artifactId>
              <version>2.10.1</version>
          </dependency>
          <!--test-->
          <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.12</version>
          </dependency>
      </dependencies>
  

3.2.2 xls文件写操作

• excel2003 文件扩展名为 xls

package com.zx.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class Excel03Test {

    @Test
    public void testWrite03() throws IOException {

        // 创建新的Excel 工作簿
        Workbook workbook = new HSSFWorkbook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 如要新建一名为"信息统计"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("信息统计");

        // 创建行（row 1）
        Row row1 = sheet.createRow(0);

        // 创建单元格（col 1-1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日人数");

        // 创建单元格（col 1-2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        // 创建行（row 2）
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //创建单元格（第三列）
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流（注意：要先创建文件夹）
        FileOutputStream out = new FileOutputStream("d://zx/a.xls");
        // 把相应的Excel 工作簿存盘
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

        System.out.println("文件生成成功");
    }
}

3.2.3 xlsx 文件写操作

excel2007+ 文件扩展名为 xlsx

package com.zx.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class Excel07Test {

    @Test
    public void testWrite07() throws IOException {

        // 创建新的Excel 工作簿
        Workbook workbook = new XSSFWorkbook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 如要新建一名为"信息统计"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("信息统计");

        // 创建行（row 1）
        Row row1 = sheet.createRow(0);

        // 创建单元格（col 1-1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日人数");

        // 创建单元格（col 1-2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        // 创建行（row 2）
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //创建单元格（第三列）
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流（注意：要先创建文件夹）
        FileOutputStream out = new FileOutputStream("d://zx/b.xlsx");
        // 把相应的Excel 工作簿存盘
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

        System.out.println("文件生成成功");
    }
}

3.2.4 xls 文件读操作

    @Test
    public void testRead03() throws Exception{
        // 1 创建文件流
        FileInputStream inputStream = new FileInputStream("d://zx//a.xls");
        //2 根据流创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        //3 获取sheet
        Sheet sheet = workbook.getSheet("上课人数统计");
        for(int i=0;i<5;i++){
            //4 获取行
            Row row = sheet.getRow(i);
            //5 获取列中内容
            //获取第一列
            Cell nameCell = row.getCell(0);
            String name = nameCell.getStringCellValue();
            // 获取第二列
            Cell sexCell = row.getCell(1);
            String sex = sexCell.getStringCellValue();
            //获取第三列
            Cell ageCell = row.getCell(2);
            String age = ageCell.getStringCellValue();

            System.out.println(name+"\t\t\t"+sex+"\t\t\t"+age);

            //\t 占8个字节的位置
            // abc\t     'abc     '
            //abcdefgh\t 'abcdefgh'
            //abcdefgh\t\t 'abcdefgh        '
        }



        //6 关闭流
        inputStream.close();
    }

3.2.5 xlsx 文件读操作

 @Test
    public void testRead07() throws Exception{

        InputStream is = new FileInputStream("d:/zx/b.xlsx");

        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);

        // 输出单元内容
        System.out.println(cell.getStringCellValue());

        // 操作结束，关闭文件
        is.close();
    }

3.2.6 读取不同类型的数据

@Test
public void testRead07() throws Exception{

    InputStream is = new FileInputStream("d:/0704.xlsx");

    Workbook workbook = new XSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    // 读取第一行第一列
    Row row = sheet.getRow(0);
    Cell cell1 = row.getCell(0);
    Cell cell2 = row.getCell(1);


    // 输出单元内容
    System.out.println(cell1.getStringCellValue());
    System.out.println(cell2.getNumericCellValue());

    // 操作结束，关闭文件
    is.close();
}