SpringBoot整合EasyExcel的完整過程記錄

為什麼要用EasyExcel

由於apache poi和jxl,excelPOI都有一個嚴重的問題,就是非常消耗內存,特別處理數據量多時,速度慢並且時有異常發生,所以改用由阿裡研發的easyExcel更可靠一些,它的官方建議對於1000行以內的采用原來poi的寫法一次讀寫,但於1000行以上的數據,有用瞭一行行進行解析的方案,這樣避免瞭內存的溢出。

1.EasyExcel簡介

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

2.使用EasyExcel實現寫

2.1 創建實體類

創建與Excel對應的實體類

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

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

ExcelProperty:指定當前字段對應excel中的那一列。可以根據名字或者Index去匹配。當然也可以不寫,默認第一個字段就是index=0,以此類推。

2.2 測試寫Excel

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

        //2.調用easyexcel裡面方法實現寫操作
        //傳入:文件存放的路徑+對應的實體類class
        EasyExcel.write(fileName, DemoData.class).sheet("學生列表").doWrite(getData());
    }

    //創建方法返回list集合(測試數據)
    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;
    }
}

結果如下

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

3.使用EasyExcel實現讀

3.1 創建讀取操作的監聽器

/**
 * @author xppll
 * @date 2021/12/3 10:29
 */
public class ExcelListener extends AnalysisEventListener<DemoData> {
    /**
     * 一行一行讀取Excel中的內容
     *
     * @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";
        //傳入三個參數:文件路徑+封裝的類的class+監聽器
        EasyExcel.read(fileName, DemoData.class,new ExcelListener())
            .sheet()
            .doRead();
    }
}

控制臺輸出如下

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

4.springboot項目實踐EasyExcel

通過在後臺管理上傳excel文件添加課程分類(一共兩級分類)

4.1 pom中引入相關依賴

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

4.2 創建數據庫表及添加數據

這裡創建一個課程表用EasyExcel實現課程的分類

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 '創建時間',
  `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','後端開發','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','前端開發','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','雲計算','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','系統/運維','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','數據庫','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','大數據','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 實體類

@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 = "創建時間")
    @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文件,把文件內容讀取出來保存到數據庫
@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();
        //調用方法進行讀取
        EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();

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

}

4.6 創建監聽器(核心部分)

/**
 * @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內容,一行一行讀取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData == null) {
            throw new GuliException(20001, "文件數據為空");
        }
        //一行一行讀取,第一個值為一級分類,第二個值為二級分類

        //先判斷一級分類是否重復
        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();

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

    //判斷一級分類不能重復添加
    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;
    }
    //判斷二級分類不能重復添加
    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) {

    }
}

4.7 結果展示

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-z8EaQ0N5-1638630992977)(SpringBoot項目整合EasyExcel.assets/image-20211204085942730.png)]

Excel模板如下

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-qndFFrE1-1638630992978)(SpringBoot項目整合EasyExcel.assets/image-20211204090109056.png)]

上傳後數據庫數據如下

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-aNvpC981-1638630992980)(SpringBoot項目整合EasyExcel.assets/image-20211204090423680.png)]

如果想要詳細使用可以參考官方文檔:EasyExcel

總結

到此這篇關於SpringBoot整合EasyExcel的文章就介紹到這瞭,更多相關SpringBoot整合EasyExcel內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: