本项目是一个基于springboot的项目,使用阿里的easyexcel实现了从数据库将数据导出为excel表格,以及从excel表格将数据导入数据库。
导出excel表格的时候有导出到指定位置与导出到浏览器两种选择。实现了基本的查询展示。
下面有具体使用介绍以及一些不成功的原因,本人使用的数据库是5.7.17版,下面的excel展示用到的是wps不是excel,因为比较熟练wps而且个人喜欢。
excel导出实现了自适应列宽,前端展示也实现了自适应列宽,可以根据最长内容调整列宽。
软件架构说明
下载或者克隆项目到本地,修改maven配置,联网等待依赖下载完,直到项目不报错
修改配置文件里面的数据库名称,数据库用户名与密码
修改application.yml里面的excel.path的绝对路径为项目在你电脑上的绝对路径,保证能访问到项目resources下的excel文件夹与里面的文件
启动项目,访问浏览器,直接访问localhost:8080,进入默认页面则成功
本项目实现的是基于springboot,使用阿里的easyexcel实现将数据库数据导出为excel,或者将excel数据导入数据库,首先数据库如下,下载本项目有一个mysql文件夹,里面包含user.sql数据库文件。
我们新建maven项目之后一般都是导入需要的maven插件,然后对应数据库写好实体类,正如上图,导入阿里的easyexcel插件后,新建实体类与以往的实体类一样,不过加上了一些注解。
我们按照数据库表定义好实体类,为其加上注解 @ExcelProperty(value = "xxx", index = 0) ,其中value值对应的是excel表的表头,index的值为数字,代表该列在excel表里面的位置,数字越小列越靠前
上图的实体类生成的excel表格如下图,可以看见没有展示id,这是因为使用 @ExcelIgnore 注解可以忽略字段,生成表格的时候忽略掉,使用index=数字,可以定义列的位置,数字越小列越靠前
实体类定义index需要与数据库位置保持一致,也就是说需要excel表格列顺序与数据库字段顺序保持一致,这样导入导出的时候不容易出错。
当数据库存储的数据长度不一致时,会导致列宽不够,可以使用 @ColumnWidth(20) 注解自定义列宽,使用方法如2中的图1,其中20代表宽度。
但是当列很多时,而且同一列下数据长度差距过大时,通过注解定义会特别麻烦,阿里官方定义的有自适应列宽的方法,但是存在bug,网上有很多修改官方提供方法的例子,大多数都不太理想,方法都是循环一列下的数据,取最宽的一个作为列宽,但是网上的基本上都是最宽那一条还存在溢出,本项目已解决此问题。方法是util包下的Custemhandler类,在util包下的ExcelUtils类里面进行调用。
修改数据库最后一人姓名,怎么长怎么来,使用自适应列宽效果如下
不使用自适应列宽生成的表格如下,可以看见出生日期会有溢出,而姓名最长那一条则不显示完整,被隐藏了。网络上的自适应方法大都会出现最长那一条会溢出,就行下图日期一样,本项目已完善自适应宽度。
当我们使用的表涉及二级或者多级表头时,如下图,web管理下面包含独立信息
代码实现如下,依然修改实体类,@ExcelProperty(value = {"Web管理","Web IP"}, index = 12),在@ExcelProperty注解里面将value用{}括起来,前面填一样的表头,后面填不同的字段,多级表头实现方法同样如此
设置excel表头的自定义行高,内容行高,表头内容文字居中(居左/居右),内容的文字居中(居左/居右)设置如下,注意是在实体类类名方法之上写注解。
据我所知(也仅仅我知道的),导入数据时,可能会报错的情况,除了表格列与数据库对应不上这种低级问题,其他的可能性大都是数据格式错误。如时间格式。
数据库时间格式如下,格式为yyyy-MM-dd,在Java实体类中定义Date时可以使用java.util.Date和java.sql.Date两种,使用java.util.Date在easyexcel导入导出时不会报错,但是获取的时间格式与数据库不同,使用java.sql.Date获取的时间格式与数据库相同,但是easyexcel导入导出时会报错。
在实体类中使用java.sql.Date和java.util.Date时,查询出的数据分别如下(启动项目后访问localhost:8080进入默认主页,左上角有一个查询所有users按钮可以实现查询)
java.sql.Date类型的时间格式为yyyy-MM-dd,但是不能实现excel导入导出
java.util.Date类型的时间格式不是yyyy-MM-dd,与数据库对应不上,但是能实现excel导入导出
那我们既要格式和数据库一致又要能实现导入导出,还是使用java.util.Date,在查询结果返回之后在前端或者后台做出处理均可,此处我是在前端处理。前端返回的格式是json字符串,既然是字符串就可以分割,我们使用slice()方法即可。如下图,在原来的list[i].birthday的后面加上slice()方法,输入要获取的位置,变为list[i].birthday.slice(0,10),出生日期就会变成控制台输出的那样,就和数据库格式一样了。
7中说到的导入报错是指后台定义的数据类型错误导致的报错,那么本条则介绍excel格式错误。还是时间,因为其他的类型不容易出错,出错则可能是表格列与实体类的列对应不上,比如文本类型对上了实体类中的int,届时后台会报错具体需要什么类型,而提供的是什么类型,也就是类型冲突无法转换。
excel里面的时间类型大致有长日期,短日期,时间这三种,也可以直接文本格式写时间,直接放图对比这些数据的差异吧
可以看见上图第一条为短日期,数据格式为yyyy/MM/dd,剩下两条为文本格式,文本格式yyyy-MM-dd格式能导入,而日期yyyy/MM/dd能,文本yyyy/MM/dd不能。
紧接着我们继续将文本时间增加yyyy-MM-dd hh:mm:ss 格式的数据,以及添加一条时间类型的数据。注意时间类型数据是yyyy/MM/dd hh:mm:ss格式的,包含年月日1999/10/9,但是表格里面只展示时间
可以看见全部能够添加成功,注意只有全部解析成功的情况下才会导入数据库,之前有一条失败所以导致成功的两条没有导入。要想实现成功就导入失败的不导入的功能请绕道,本人能力不够,你要是知道也欢迎指点,我想学。
文本格式必须严格按照实体类里面定义的时间格式来,可以长但是不能短,长可以舍去多余的部分但是短了无法自动补全所以会报错。如yyyy-MM-dd可以写 2021-11-11,可以是2021-11-11 12:23:34,但是不能是2021或者2021-11。
长日期格式为yyyy年MM月dd日,如图,即使是年月日等中文字样,日期格式依然能存储成功。
导入成功!
上面是使用Date类型的实体类,实际操作中不考虑严格对照数据库格式的话可以使用String类型修饰实体类的参数。
时间不再使用Date,使用String,立马万能,之前失败的文本格式yyyy/MM/dd格式也能成功,
string虽万能,但是不是真万能,依然是可长不可短,时间格式要注意好。
在数据库是数字,表格里面是文字时,依旧可以使用string,比如性别我们在数据库存储的是int,以0代表男,以1代表女,但是导出到excel的时候希望转换为文字男女,可以用string获取到之后判断转换为男女,然后输出。导入也是同样,先用string获取,然后if判断男女转换为数字1、2,存入数据库。
此时实体类string是对应excel的,而不是对应数据库。如果对应数据库应该使用int,则读取excel时就会报错,因为int字段无法接收excel里面的男女。string不做转换虽然能接受到excel里面的男女,但是直接存储男女数据库里面的是int类型会报错,而转换为string类型的数字0、1,哪怕还是string类型,数据库也能接收。
基于此点我们可以实现单excel表导入多数据库。
如9最后所所说,我们已经可以根据excel表来定义实体类了,哪怕数据库并没有excel表里面的字段甚至没有数据库,我们都可以根据excel表定义一个实体类,但是这个实体类必须严格对照excel表的列顺序。
写好实体类就能保证我们读取excel表的时候不报错,然后重写存储到数据库的方法,调用多个方法即可。
如下图,excel的所有数据都会通过Listener里面的类,在监听类里面的saveData()方法里面存储,里面的list已经包含了excel里面所有的数据,只要根据excel建立的实体类拥有get、set方法,就能通过for循环加上判断来提取出来或者修改,在此处我们根据数据库的实体类建立list集合,提取出需要存储的数据添加到list集合,调用该数据库表的存储方法即可。
多数据库导出到单表并不是导出到单表的单个sheet,而是导出到多个sheet,导出到单sheet我还不会,有会的大佬欢迎指正。
在ExcelUtil类里面添加多个导出方法,如下图,添加多个sheet,每个sheet对应一个表的数据
在Controller里面定义导出时的名称,并且传入数据即可实现导出到单表多sheet
展示效果如下图,不同的sheet展示不同的表的数据。
也许你会疑惑那我全写sheetNo为0不就都在一个表了吗?就好比用户表与手机表,一个用户对应一个或者零个或者多个手机,你期望的效果是这样
但是插入同一个sheet,效果却可能是这样的,后面的数据连表头都缺失甚至连数据都缺失
展示到单excel表的单sheet的功能我还没有找到,或许由于这样或那样的原因没有来得及学下去,有大佬知道怎么解决的话望告知,感激!!!
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。