由于apache poi和jxl,excelpoi都有一个严重的问题,就是非常消耗内存,特别处理数据量多时,速度慢并且时有异常发生,所以改用由阿里研发的easyexcel更可靠一些,它的官方建议对于1000行以内的采用原来poi的写法一次读写,但于1000行以上的数据,有用了一行行进行解析的方案,这样避免了内存的溢出。
创建与excel对应的实体类
/** * @author xppll * @date销售督导 2021/12/3 10:05 */@datapublic class demodata { //设置excel表头名称,index表示对应的第几列 @excelproperty(value = "学生编号",index = 0) private integer sno; @excelproperty(value = "学生姓名",index = 1) private string sname;}
excelproperty:指定当前字段对应excel中的那一列。可以根据名字或者index去匹配。当然也可以不写,默认第一个字段就是index=0,以此类推。
/** * @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.tsno(i); data.tsname("lucy" + i); list.add(data); } return list; }}
结果如下
/** * @author xppll * @date 2021/12/3 10:29 */public class excellistener extends analysiventlistener<demodata> { /** * 一行一行读取excel中的内容 * * @param data * @param analysiscontext */ @overri四冲程内燃机de 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 doafterallanalyd(analysiscontext analysiscontext) { }}
/** * @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(); }}
控制台输出如下
通过在后台管理上传excel文件添加课程分类(一共两级分类)
<dependency> <groupid>com.alibaba</groupid> <artifactid>easyexcel</artifactid> <version>3.0.5</version></dependency>
这里创建一个课程表用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 chart=utf8mb4 row_format=compact comment='课程科目';
inrt 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','117821468132408三十岁女人保养秘诀9345',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');
@datapublic class subjectdata { @excelproperty(index = 0) private string onesubjectname; @excelproperty(index = 1) private string twosubjectname;}
/** * 课程科目 * @author xppll * @since 2021-12-03 */@data@equalsandhashcode(callsuper = fal)@accessors(chain = true)@apimodel(value="edusubject对象", description="课程科目")public class edusubject implements rializable { private static final long rialversionuid = 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.inrt) private date gmtcreate; @apimodelproperty(value = "更新时间") @tablefield(fill = fieldfill.inrt_update) private date gmtmodified;}
@autowiredprivate edusubjectrvice subjectrvice;//添加课程分类//获取前端上传过来的excel文件,把文件内容读取出来保存到数据库@postmapping("addsubject")public r addsubject(multipartfile file){ subjectrvice.savesubject(file,subjectrvice); return r.ok();}
//添加课程分类@overridepublic void savesubject(multipartfile file, edusubjectrvice subjectrvice) { try { //得到文件的输入流 inputstream in = file.getinputstream(); //调用方法进行读取 easyexcel.read(in, subjectdata.class, new subjectexcellistener(subjectrvice)).sheet().doread(); } catch (exception e) { e.printstacktrace(); }}
/** * @author xppll * @date 2021/12/3 12:02 */@componentpublic class subjectexcellistener extends analysiventlistener<subjectdata> { public edusubjectrvice subjectrvice; public subjectexcellistener(edusubjectrvice subjectrvice) { this.subjectrvice = subjectrvice; } public subjectexcellistener() { } //读取excel内容,一行一行读取 @override public void invoke(subjectdata subjectdata, analysiscontext analysiscon秋天词语text) { if (subjectdata == null) { throw new guliexception(20001, "文件数据为空"); } //一行一行读取,第一个值为一级分类,第二个值为二级分类 //先判断一级分类是否重复 edusubject existonesubject = this.existonesubject(subjectdata.getonesubjectname(), subjectrvice); //没有相同的一级分类 if(existonesubject==null){ existonesubject=new edusubject(); existonesubject.tparentid("0"); existonesubject.ttitle(subjectdata.getonesubjectname()); subjectrvice.save(existonesubject); } //获取一级分类的id值 string pid=existonesubject.getid(); //判断二级分类是否重复 edusubject existtwosubject = this.existtwosubject(subjectdata.gettwosubjectname(), subjectrvice, pid); if(existtwosubject==null){ existtwosubject=new edusubject(); existtwosubject.tparentid(pid); existtwosubject.ttitle(subjectdata.gettwosubjectname()); subjectrvice.save(existtwosubject); } } //判断一级分类不能重复添加 private edusubject existonesubject(string name, edusubjectrvice subjectrvice) { lambdaquerywrapper<edusubject> querywrapper = new lambdaquerywrapper<>(); querywrapper.eq(edusubject::gettitle, name) .eq(edusubject::getparentid, "0"); edusubject onesubject = subjectrvice.getone(querywrapper); return onesubject; } //判断二级分类不能重复添加 private edusubject existtwosubject(string name, edusubjectrvice subjectrvice,string pid) { lambdaquerywrapper<edusubject> querywrapper = new lambdaquerywrapper<>(); querywrapper.eq(edusubject::gettitle, name) .eq(edusubject::getparentid, pid); edusubject twosubject = subjectrvice.getone(querywrapper); return twosubject; } @override public void doafterallanalyd(analysiscontext analysiscontext) { }}
excel模板如下
上传后数据库数据如下
如果想要详细使用可以参考官方文档:easyexcel
到此这篇关于springboot整合easyexcel的文章就介绍到这了,更多相关springboot整合easyexcel内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!
本文发布于:2023-04-04 01:27:42,感谢您对本站的认可!
本文链接:https://www.wtabcd.cn/fanwen/zuowen/efd61c5e8789200f24f97ce51ff81159.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文word下载地址:SpringBoot整合EasyExcel的完整过程记录.doc
本文 PDF 下载地址:SpringBoot整合EasyExcel的完整过程记录.pdf
留言与评论(共有 0 条评论) |