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; } }
結果如下
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(); } }
控制臺輸出如下
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 結果展示
Excel模板如下
上傳後數據庫數據如下
如果想要詳細使用可以參考官方文檔:EasyExcel
總結
到此這篇關於SpringBoot整合EasyExcel的文章就介紹到這瞭,更多相關SpringBoot整合EasyExcel內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- SpringBoot集成EasyExcel的應用場景分析
- SpringBoot整合EasyExcel進行大數據處理的方法詳解
- Java中利用Alibaba開源技術EasyExcel來操作Excel表的示例代碼
- java操作excel表格詳解
- Java easyExcel的復雜表頭多級表頭導入