SpringBoot整合EasyExcel的完整過程記錄

更新時間：2021年12月07日 12:53:48 作者：LL.LEBRON

easyexcel是阿里巴巴旗下開源項(xiàng)目,主要用于Excel文件的導(dǎo)入和導(dǎo)出處理,下面這篇文章主要給大家介紹了關(guān)于SpringBoot整合EasyExcel的完整過程,需要的朋友可以參考下

為什么要用EasyExcel

由于apache poi和jxl，excelPOI都有一個嚴(yán)重的問題，就是非常消耗內(nèi)存，特別處理數(shù)據(jù)量多時，速度慢并且時有異常發(fā)生，所以改用由阿里研發(fā)的easyExcel更可靠一些,它的官方建議對于1000行以內(nèi)的采用原來poi的寫法一次讀寫，但于1000行以上的數(shù)據(jù)，有用了一行行進(jìn)行解析的方案，這樣避免了內(nèi)存的溢出。

1.EasyExcel簡介

Java領(lǐng)域解析、生成Excel比較有名的框架有Apache poi、jxl等。但他們都存在一個嚴(yán)重的問題就是非常的耗內(nèi)存。如果你的系統(tǒng)并發(fā)量不大的話可能還行，但是一旦并發(fā)上來后一定會OOM或者JVM頻繁的full gc。
EasyExcel是阿里巴巴開源的一個excel處理框架，以使用簡單、節(jié)省內(nèi)存著稱。EasyExcel能大大減少占用內(nèi)存的主要原因是在解析Excel時沒有將文件數(shù)據(jù)一次性全部加載到內(nèi)存中，而是從磁盤上一行行讀取數(shù)據(jù)，逐個解析。
EasyExcel采用一行一行的解析模式，并將一行的解析結(jié)果以觀察者的模式通知處理（AnalysisEventListener）

2.使用EasyExcel實(shí)現(xiàn)寫

2.1 創(chuàng)建實(shí)體類

創(chuàng)建與Excel對應(yīng)的實(shí)體類

/**
 * @author xppll
 * @date 2021/12/3 10:05
 */
@Data
public class DemoData {
    //設(shè)置excel表頭名稱，index表示對應(yīng)的第幾列
    @ExcelProperty(value = "學(xué)生編號",index = 0)
    private Integer sno;

    @ExcelProperty(value = "學(xué)生姓名",index = 1)
    private String sname;
}

ExcelProperty：指定當(dāng)前字段對應(yīng)excel中的那一列。可以根據(jù)名字或者Index去匹配。當(dāng)然也可以不寫，默認(rèn)第一個字段就是index=0，以此類推。

2.2 測試寫Excel

/**
 * @author xppll
 * @date 2021/12/3 10:08
 */
public class TestWriteExcel {
    public static void main(String[] args) {
        //實(shí)現(xiàn)excel寫的操作
        //1.設(shè)置寫入文件夾的地址和excel文件名稱
        String fileName = "D:\\write.xlsx";

        //2.調(diào)用easyexcel里面方法實(shí)現(xiàn)寫操作
        //傳入：文件存放的路徑+對應(yīng)的實(shí)體類class
        EasyExcel.write(fileName, DemoData.class).sheet("學(xué)生列表").doWrite(getData());
    }

    //創(chuàng)建方法返回list集合（測試數(shù)據(jù)）
    private static List<DemoData> getData() {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setSno(i);
            data.setSname("lucy" + i);
            list.add(data);
        }
        return list;
    }
}

結(jié)果如下

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-R51xa0VB-1638630992973)(SpringBoot項(xiàng)目整合EasyExcel.assets/image-20211203105225367.png)]

3.使用EasyExcel實(shí)現(xiàn)讀

3.1 創(chuàng)建讀取操作的監(jiān)聽器

/**
 * @author xppll
 * @date 2021/12/3 10:29
 */
public class ExcelListener extends AnalysisEventListener<DemoData> {
    /**
     * 一行一行讀取Excel中的內(nèi)容
     *
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(DemoData data, AnalysisContext analysisContext) {
        System.out.println("****" + data);
    }


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

    /**
     * 讀取完成之后做的事
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3.2 測試讀Excel

/**
 * @author xppll
 * @date 2021/12/3 10:35
 */
public class TestReadExcel {
    public static void main(String[] args) {
        //讀取文件的路徑
        String fileName = "D:\\write.xlsx";
        //傳入三個參數(shù)：文件路徑+封裝的類的class+監(jiān)聽器
        EasyExcel.read(fileName, DemoData.class,new ExcelListener())
            .sheet()
            .doRead();
    }
}

控制臺輸出如下

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-ugT2UNpq-1638630992975)(SpringBoot項(xiàng)目整合EasyExcel.assets/image-20211203105451947.png)]

4.springboot項(xiàng)目實(shí)踐EasyExcel

通過在后臺管理上傳excel文件添加課程分類（一共兩級分類）

4.1 pom中引入相關(guān)依賴

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

4.2 創(chuàng)建數(shù)據(jù)庫表及添加數(shù)據(jù)

這里創(chuàng)建一個課程表用EasyExcel實(shí)現(xiàn)課程的分類

CREATE TABLE `edu_subject` (
  `id` char(19) NOT NULL COMMENT '課程類別ID',
  `title` varchar(10) NOT NULL COMMENT '類別名稱',
  `parent_id` char(19) NOT NULL DEFAULT '0' COMMENT '父ID',
  `sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序字段',
  `gmt_create` datetime NOT NULL COMMENT '創(chuàng)建時間',
  `gmt_modified` datetime NOT NULL COMMENT '更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='課程科目';

INSERT INTO `edu_subject` VALUES ('1178214681118568449','后端開發(fā)','0',1,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681139539969','Java','1178214681118568449',1,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681181483010','前端開發(fā)','0',3,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681210843137','JavaScript','1178214681181483010',4,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681231814658','云計(jì)算','0',5,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681252786178','Docker','1178214681231814658',5,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681294729217','Linux','1178214681231814658',6,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681324089345','系統(tǒng)/運(yùn)維','0',7,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681353449473','Linux','1178214681324089345',7,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681382809602','Windows','1178214681324089345',8,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681399586817','數(shù)據(jù)庫','0',9,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681428946945','MySQL','1178214681399586817',9,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681454112770','MongoDB','1178214681399586817',10,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681483472898','大數(shù)據(jù)','0',11,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681504444418','Hadoop','1178214681483472898',11,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681529610242','Spark','1178214681483472898',12,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681554776066','人工智能','0',13,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681584136193','Python','1178214681554776066',13,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681613496321','編程語言','0',14,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178214681626079234','Java','1178214681613496321',14,'2019-09-29 15:47:25','2019-09-29 15:47:25'),('1178585108407984130','Python','1178214681118568449',2,'2019-09-30 16:19:22','2019-09-30 16:19:22'),('1178585108454121473','HTML/CSS','1178214681181483010',3,'2019-09-30 16:19:22','2019-09-30 16:19:22');

4.3 實(shí)體類

@Data
public class SubjectData {
    @ExcelProperty(index = 0)
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;
}

/**
 * 課程科目
 * @author xppll
 * @since 2021-12-03
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="EduSubject對象", description="課程科目")
public class EduSubject implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "課程類別ID")
    @TableId(value = "id", type = IdType.ID_WORKER_STR)
    private String id;

    @ApiModelProperty(value = "類別名稱")
    private String title;

    @ApiModelProperty(value = "父ID")
    private String parentId;

    @ApiModelProperty(value = "排序字段")
    private Integer sort;

    @ApiModelProperty(value = "創(chuàng)建時間")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtCreate;

    @ApiModelProperty(value = "更新時間")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date gmtModified;
}

4.4 Controller層

@Autowired
private EduSubjectService subjectService;

//添加課程分類
//獲取前端上傳過來的excel文件，把文件內(nèi)容讀取出來保存到數(shù)據(jù)庫
@PostMapping("addSubject")
public R addSubject(MultipartFile file){

    subjectService.saveSubject(file,subjectService);
    return R.ok();
}

4.5 Service層

//添加課程分類
@Override
public void saveSubject(MultipartFile file, EduSubjectService subjectService) {
    try {
        //得到文件的輸入流
        InputStream in = file.getInputStream();
        //調(diào)用方法進(jìn)行讀取
        EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();

    } catch (Exception e) {
        e.printStackTrace();
    }

}

4.6 創(chuàng)建監(jiān)聽器（核心部分）

/**
 * @author xppll
 * @date 2021/12/3 12:02
 */
@Component
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    public EduSubjectService subjectService;

    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

    public SubjectExcelListener() {
    }

    //讀取excel內(nèi)容，一行一行讀取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData == null) {
            throw new GuliException(20001, "文件數(shù)據(jù)為空");
        }
        //一行一行讀取，第一個值為一級分類，第二個值為二級分類

        //先判斷一級分類是否重復(fù)
        EduSubject existOneSubject = this.existOneSubject(subjectData.getOneSubjectName(), subjectService);
        //沒有相同的一級分類
        if(existOneSubject==null){
            existOneSubject=new EduSubject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName());
            subjectService.save(existOneSubject);
        }

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

        //判斷二級分類是否重復(fù)
        EduSubject existTwoSubject = this.existTwoSubject(subjectData.getTwoSubjectName(), subjectService, pid);
        if(existTwoSubject==null){
            existTwoSubject=new EduSubject();
            existTwoSubject.setParentId(pid);
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());
            subjectService.save(existTwoSubject);
        }
    }

    //判斷一級分類不能重復(fù)添加
    private EduSubject existOneSubject(String name, EduSubjectService subjectService) {
        LambdaQueryWrapper<EduSubject> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(EduSubject::getTitle, name)
            .eq(EduSubject::getParentId, "0");
        EduSubject oneSubject = subjectService.getOne(queryWrapper);
        return oneSubject;
    }
    //判斷二級分類不能重復(fù)添加
    private EduSubject existTwoSubject(String name, EduSubjectService subjectService,String pid) {
        LambdaQueryWrapper<EduSubject> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(EduSubject::getTitle, name)
            .eq(EduSubject::getParentId, pid);
        EduSubject twoSubject = subjectService.getOne(queryWrapper);
        return twoSubject;
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}