SpringBoot整合EasyExcel的完整過(guò)程記錄
為什么要用EasyExcel
由于apache poi和jxl,excelPOI都有一個(gè)嚴(yán)重的問(wèn)題,就是非常消耗內(nèi)存,特別處理數(shù)據(jù)量多時(shí),速度慢并且時(shí)有異常發(fā)生,所以改用由阿里研發(fā)的easyExcel更可靠一些,它的官方建議對(duì)于1000行以內(nèi)的采用原來(lái)poi的寫(xiě)法一次讀寫(xiě),但于1000行以上的數(shù)據(jù),有用了一行行進(jìn)行解析的方案,這樣避免了內(nèi)存的溢出。
1.EasyExcel簡(jiǎn)介
- Java領(lǐng)域解析、生成Excel比較有名的框架有Apache poi、jxl等。但他們都存在一個(gè)嚴(yán)重的問(wèn)題就是非常的耗內(nèi)存。如果你的系統(tǒng)并發(fā)量不大的話可能還行,但是一旦并發(fā)上來(lái)后一定會(huì)OOM或 者JVM頻繁的full gc。
- EasyExcel是阿里巴巴開(kāi)源的一個(gè)excel處理框架,以使用簡(jiǎn)單、節(jié)省內(nèi)存著稱。EasyExcel能大大減少占用內(nèi)存的主要原因是在解析Excel時(shí)沒(méi)有將文件數(shù)據(jù)一次性全部加載到內(nèi)存中,而是從磁盤(pán)上一 行行讀取數(shù)據(jù),逐個(gè)解析。
- EasyExcel采用一行一行的解析模式,并將一行的解析結(jié)果以觀察者的模式通知處理 (AnalysisEventListener)
2.使用EasyExcel實(shí)現(xiàn)寫(xiě)
2.1 創(chuàng)建實(shí)體類
創(chuàng)建與Excel對(duì)應(yīng)的實(shí)體類
/** * @author xppll * @date 2021/12/3 10:05 */ @Data public class DemoData { //設(shè)置excel表頭名稱,index表示對(duì)應(yīng)的第幾列 @ExcelProperty(value = "學(xué)生編號(hào)",index = 0) private Integer sno; @ExcelProperty(value = "學(xué)生姓名",index = 1) private String sname; }
ExcelProperty:指定當(dāng)前字段對(duì)應(yīng)excel中的那一列??梢愿鶕?jù)名字或者Index去匹配。當(dāng)然也可以不寫(xiě),默認(rèn)第一個(gè)字段就是index=0,以此類推。
2.2 測(cè)試寫(xiě)Excel
/** * @author xppll * @date 2021/12/3 10:08 */ public class TestWriteExcel { public static void main(String[] args) { //實(shí)現(xiàn)excel寫(xiě)的操作 //1.設(shè)置寫(xiě)入文件夾的地址和excel文件名稱 String fileName = "D:\\write.xlsx"; //2.調(diào)用easyexcel里面方法實(shí)現(xiàn)寫(xiě)操作 //傳入:文件存放的路徑+對(duì)應(yīng)的實(shí)體類class EasyExcel.write(fileName, DemoData.class).sheet("學(xué)生列表").doWrite(getData()); } //創(chuàng)建方法返回list集合(測(cè)試數(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é)果如下
3.使用EasyExcel實(shí)現(xiàn)讀
3.1 創(chuàng)建讀取操作的監(jiān)聽(tīng)器
/** * @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 測(cè)試讀Excel
/** * @author xppll * @date 2021/12/3 10:35 */ public class TestReadExcel { public static void main(String[] args) { //讀取文件的路徑 String fileName = "D:\\write.xlsx"; //傳入三個(gè)參數(shù):文件路徑+封裝的類的class+監(jiān)聽(tīng)器 EasyExcel.read(fileName, DemoData.class,new ExcelListener()) .sheet() .doRead(); } }
控制臺(tái)輸出如下
4.springboot項(xiàng)目實(shí)踐EasyExcel
通過(guò)在后臺(tái)管理上傳excel文件添加課程分類(一共兩級(jí)分類)
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ù)庫(kù)表及添加數(shù)據(jù)
這里創(chuàng)建一個(gè)課程表用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)建時(shí)間', `gmt_modified` datetime NOT NULL COMMENT '更新時(shí)間', PRIMARY KEY (`id`), KEY `idx_parent_id` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='課程科目';
INSERT INTO `edu_subject` VALUES ('1178214681118568449','后端開(kāi)發(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','前端開(kāi)發(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ù)庫(kù)','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','編程語(yǔ)言','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對(duì)象", 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)建時(shí)間") @TableField(fill = FieldFill.INSERT) private Date gmtCreate; @ApiModelProperty(value = "更新時(shí)間") @TableField(fill = FieldFill.INSERT_UPDATE) private Date gmtModified; }
4.4 Controller層
@Autowired private EduSubjectService subjectService; //添加課程分類 //獲取前端上傳過(guò)來(lái)的excel文件,把文件內(nèi)容讀取出來(lái)保存到數(shù)據(jù)庫(kù) @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)聽(tīng)器(核心部分)
/** * @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ù)為空"); } //一行一行讀取,第一個(gè)值為一級(jí)分類,第二個(gè)值為二級(jí)分類 //先判斷一級(jí)分類是否重復(fù) EduSubject existOneSubject = this.existOneSubject(subjectData.getOneSubjectName(), subjectService); //沒(méi)有相同的一級(jí)分類 if(existOneSubject==null){ existOneSubject=new EduSubject(); existOneSubject.setParentId("0"); existOneSubject.setTitle(subjectData.getOneSubjectName()); subjectService.save(existOneSubject); } //獲取一級(jí)分類的id值 String pid=existOneSubject.getId(); //判斷二級(jí)分類是否重復(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); } } //判斷一級(jí)分類不能重復(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; } //判斷二級(jí)分類不能重復(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) { } }
4.7 結(jié)果展示
Excel模板如下
上傳后數(shù)據(jù)庫(kù)數(shù)據(jù)如下
如果想要詳細(xì)使用可以參考官方文檔:EasyExcel
總結(jié)
到此這篇關(guān)于SpringBoot整合EasyExcel的文章就介紹到這了,更多相關(guān)SpringBoot整合EasyExcel內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SSH框架實(shí)現(xiàn)表單上傳圖片實(shí)例代碼
本篇文章主要介紹了SSH框架實(shí)現(xiàn)表單上傳圖片實(shí)例代碼,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-09-09使用SpringBoot整合Activiti6工作流的操作方法
這篇文章主要介紹了使用SpringBoot整合Activiti6工作流,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-07-07Java原生方法實(shí)現(xiàn) AES 算法示例
這篇文章主要介紹了Java原生方法實(shí)現(xiàn) AES 算法,結(jié)合實(shí)例形式分析了Java實(shí)現(xiàn)AES加密算法的相關(guān)操作技巧,需要的朋友可以參考下2019-03-03Spring獲取當(dāng)前類在容器中的beanname實(shí)現(xiàn)思路
這篇文章主要介紹了Spring獲取當(dāng)前類在容器中的beanname,實(shí)現(xiàn)思路只需繼承BeanNameAware接口,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-07-07