本项目是一个基于springboot的项目,使用阿里的easyexcel实现了单sheet表导入导出,多sheet表导入导出,单数据库表导入导出,多数据库表导入导出。
导出excel表格的时候有导出到指定位置与导出到浏览器两种选择。实现了基本的查询展示。
下面有具体使用介绍以及一些不成功的示例,本人使用的数据库是5.7.17版,下面的excel展示用到的是wps不是excel,因为比较熟练wps而且个人喜欢。
excel导出实现了自适应列宽,前端展示也实现了自适应列宽,可以根据最长内容调整列宽,不过重点在于easyexcel,所以前端随便写的,将就着看吧。
springboot+mysql5.x+easyexcel
下载或者克隆项目到本地,修改maven配置,联网等待依赖下载完,直到项目不报错,这第一步还不会我就没的办法了,转行还来得及
修改配置文件里面的数据库名称,数据库用户名与密码
修改application.yml里面的excel.path的绝对路径为项目在你电脑上的绝对路径,保证能访问到项目resources下的excel文件夹与里面的文件(实际上不修改也行,这玩意儿大多数时候是作为下载excel模板功能而存在,但是随便导出数据库数据删了数据后也是现成的好模板)
启动项目,访问浏览器,直接访问localhost:8080,进入默认页面则成功
本项目实现的是基于springboot,使用阿里的easyexcel实现将数据库数据导出为excel,或者将excel数据导入数据库,首先数据库如下,下载本项目有一个mysql文件夹,里面包含user.sql(和一个phone.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(已修改,现为UserUtils)类里面进行调用。
这里我修改数据库最后一人姓名,怎么长怎么来,使用自适应列宽之后效果如下
不使用自适应列宽生成的表格如下,可以看见出生日期会有溢出,而姓名最长那一条则不显示完整,被隐藏了。网络上的自适应方法大都会出现最长那一条会溢出,就行下图日期一样,本项目已完善自适应宽度。
当我们使用的表涉及二级或者多级表头时,如下图,web管理下面包含独立信息
代码实现如下,依然修改实体类,@ExcelProperty(value = {"Web管理","Web IP"}, index = 12),在@ExcelProperty注解里面将value用{}括起来,前面填一样的表头,后面填不同的字段,多级表头实现方法同样如此
设置excel表头的自定义行高,内容行高,表头内容文字居中(居左/居右),内容的文字居中(居左/居右)设置如下,注意是在实体类类名方法之上写注解。
据我所知(也仅仅我知道的),导入数据时,可能会报错的情况,除了表格列与数据库对应不上这种低级问题,其他的可能性大都是数据格式错误。如时间格式。
user表时间格式如下,date类型,格式为yyyy-MM-dd,即只包含年月日,在Java实体类中定义Date时可以使用java.util.Date和java.sql.Date两种。
java.util.Date包含时分秒,java.sql.Date不包含时分秒,使用java.util.Date在easyexcel导入导出时不会报错,使用java.sql.Date则会报错
在实体类中使用java.sql.Date和java.util.Date时,查询出的数据分别如下(启动项目后访问localhost:8080进入默认主页,左上角有一个查询所有users按钮可以实现查询)
java.sql.Date类型的时间格式为yyyy-MM-dd,只有年月日,不能实现excel导入导出
java.util.Date类型的时间格式不是yyyy-MM-dd,它除了包含时分秒,还包含毫秒,与数据库对应不上,读取数据时会以0自动补全时分秒和毫秒,哪怕是mysql的datetime类型,读取出来也会补全毫秒,如2022-02-24 05:18:20读取并输出,得到的结果是2022-02-24T05:18:20.000+00:00。java.util.Date能实现excel导入导出
那我们既要格式和数据库一致又要能实现导入导出,还是使用java.util.Date,在查询结果返回之后在前端或者后台做出处理均可,在后端,我们能使用SimpleDateFormat方法来转换,此方法sql.date和util.date均适用,不过需要手动去转换;前后端均可以分割字符提取我们想要的数据,也需要手动转换。最简单的当然就是在实体类中添加注解了,只需在你定义的时间上添加注解@JsonFormat即可,用法:@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
此处为方便快速展示不重启项目,我是在前端处理。前端返回的格式是json字符串,既然是字符串就可以分割,我们使用slice()方法即可。如下图,在原来的list[i].birthday的后面加上slice()方法,输入要获取的位置,变为list[i].birthday.slice(0,10),出生日期就会变成控制台输出的那样,就和数据库格式一样了,后台处理亦可去尝试截取想要的字符串。 (经过后期修改还是选择了@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")注解,2022-03-22改)
7中说到的导入报错是指后台定义的数据类型错误导致的报错,那么本条则介绍excel格式错误。还是时间,因为其他的类型不容易出错,出错则可能是表格列与实体类的列对应不上,如缺少列数,实体类的类型与excel表不一致等等问题(我也确实有过这样的经历,因为别人发给我的表格中间有几列没有宽度,隐藏了,导致缺少列数无法导入成功,后来还是全选表格自适应列宽才发现隐藏了几列),比如excel文本类型对上了实体类中的int类型,届时后台会报错需要什么类型,而提供的是什么类型,也就是类型冲突无法转换。
wps打开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日,如图,即使是年月日等中文字样,日期格式依然能存储成功,因为其本质上还是yyyy/MM/dd。
导入成功!
上面是使用Date类型的实体类,实际操作中不考虑严格对照数据库格式的话可以使用String类型修饰实体类的参数。
时间不再使用Date,使用String,立马万能,之前失败的文本格式yyyy/MM/dd格式也能成功。
鉴于这样一条条展示有些不直观,我列了一个表格并清除了数据库数据,用于观察数据插入情况:
当然,为了避免不知道错误发生在哪一条,我这菜鸡只好使用笨方法,一条条测试数据了
java.sql.date 全部报错
java.util.date 前五条成功,后面的数据无法解析
String 文本格式带中文的无法转换
从 “2000年9月2日” 这个格式开始出现错误,导致没有存储成功,但是String能解析到所有时间数据,只是文本类型的带中文的时间,格式无法转换成功
那长日期格式带中文为什么能转换成功呢?实际上excel长日期格式虽然有中文,但是其类型格式还是yyyy/MM/dd
下图即为使用String存储成功之后的数据,和上图一致
string虽万能,但是不是真万能,时间格式要注意好,不要携带中文,excel长日期格式虽然有中文,但是实际上其类型格式还是yyyy/MM/dd。
除此之外,java.util.date和String类型都要注意,数据库读取出来util.date能自动补全时分秒,但是excel读取写入数据库是无法自动补全的,只能长不能短。实体类时间那一栏有一个注解@DateTimeFormat(value = "yyyy-MM-dd"),该注解定义了转换的时间格式,则只能长于这个格式而不能短。如定义的是yyyy-MM-dd,则excel表格里面可以是2000-10-10 00:00:00,但是不能是2000-10,短于定义的时间格式则会出错。
在数据库是数字,表格里面是文字时,依旧可以使用string,比如性别我们在数据库存储的是int,以0代表男,以1代表女,但是导出到excel的时候希望转换为文字男女,可以用string获取到之后进行判断,使用实体类的set方法转换为男女,然后输出。导入也是同样,先用string获取,然后if判断男女转换为数字1、2,存入数据库。此时实体类string是对应excel的,而不是对应数据库。如果对应数据库应该使用int,则读取excel时就会报错,因为int字段无法接收excel里面的男女。string不做转换虽然能接受到excel里面的男女,但是直接存储男女到数据库,会因为数据库里面的是int类型而报错,而转换为string类型的0、1,哪怕还是string类型,但是经过sql语句导入数据库时不会报错,能存储成功。
在原有表excel表的基础上,我们再建一个表phone,代表手机,用户可以有一个或者多个手机,也可以没有手机。user、phone表数据修改如下:
我们在按照user原有方法,给phone也来一套,先实现user和phone的单独导出,然后再实现导出到单表多个sheet,效果如下:
utils方法如下:
public static void allExportToWeb(HttpServletResponse response, String excelName, String sheetName, String sheetName1, List data1, List data2) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new Custemhandler()).build();
WriteSheet writeSheet1 = EasyExcel.writerSheet(0, sheetName).head(User.class).build();
WriteSheet writeSheet2 = EasyExcel.writerSheet(1, sheetName1).head(Phone.class).build();
excelWriter.write(data1, writeSheet1);
excelWriter.write(data2, writeSheet2);
excelWriter.finish();
}
Controller里面直接调用
//导出到多个sheet
@GetMapping("/exportWeb2")
public void exportToWeb2(HttpServletResponse response) {
try {
UserphoneUtils.allExportToWeb(response, "用户手机表", //excel表格名称
"用户表", "手机表", //excel表格的sheet名称
userService.getAll(),phoneService.getAllPhones()); //导入sheet的两份数据
} catch (Exception e) {
log.error("报表导出异常:", e);
}
}
既然导入多个sheet是写入多个sheet,如下:
WriteSheet writeSheet1 = EasyExcel.writerSheet(0, sheetName).head(User.class).build(); WriteSheet writeSheet2 = EasyExcel.writerSheet(1, sheetName1).head(Phone.class).build();
那我们把sheet都写入sheet0里面,那就是导入到单表单sheet了吗?显然不是
你期望的效果是这样,赵飞燕有两个手机,张三有一个手机,李四有两个手机,导出excel表如下
但是插入同一个sheet,效果却可能是这样的(此结果经我测试得出,其他情况也有可能),后面的数据连表头都缺失甚至连数据都缺失
所以此方法行不通!
事实上,多表导入单个sheet我们使用联表查询即可。根据用户与手机里面的user_id字段,进行联表查询,sql语句如下:
SELECT a.*,b.phone_name,b.brand,b.phone_color,b.ram,b.rom FROM user a,phone b WHERE a.id = b.user_id
查询结果如下:
以此结果字段来创建实体类,我们创建的实体类就接收这样的数据,既然以此创建了实体类,那么自然就可以导出为单表了,具体方法看代码,导出到单表效果如下:
单sheet表导入单数据库很简单,单表导入多数据库就稍微难一点了。首先我们一样需要实体类来对应你所拥有的表。当然了,11点里面我们导出的表是基于已有的实体类导出的,无需再去创建实体类,如果是对应不上的表,我们再去创建对应的实体类来接收excel表的数据。
在之前只有user表的时候,读取excel表里面的user数据并保存方法是这样的
list即为我们接收到的excel表里面的数据,那么现在list的单条数据无非是这样的
里面包含了user和phone,我们只需提取出来单独存储,即可实现存储到多数据库表。
方法如下:
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
List<User> users = new ArrayList<>(); //创建需要保存的实体类User集合
List<Phone> phones = new ArrayList<>(); //创建需要保存的实体类Phone集合
for (int i = 0; i < list.size(); i++) {
try {
User user = new User();
user.setName(list.get(i).getName()); //提取需要保存的用户的属性
user.setSex(list.get(i).getSex());
user.setAge(list.get(i).getAge());
user.setBirthday(list.get(i).getBirthday());
users.add(user); //添加到集合
Phone phone = new Phone();
phone.setPhone_name(list.get(i).getPhone_name()); //提取需要保存的手机的属性
phone.setBrand(list.get(i).getBrand());
phone.setPhone_color(list.get(i).getPhone_color());
phone.setRam(list.get(i).getRam());
phone.setRom(list.get(i).getRom());
phone.setUser_id(list.get(i).getUser_id());
phones.add(phone); //添加到集合
} catch (Exception e) {
e.printStackTrace();
}
}
if (!CollectionUtils.isEmpty(users)) {
userphoneService.saveUsers(users); //集合不为空之后存储用户
}
if (!CollectionUtils.isEmpty(phones)) {
userphoneService.savePhones(phones); //集合不为空之后存储手机
}
log.info("存储数据库成功!");
}
建集合与相应的实体类提取出我们需要的数据,再调用各个实体类的存储方法来存储数据。
但是需要注意的是,这里有四条数据,但是实际上用户只有三个,从excel表获取到的用户是四个,在实际运用中需要去重,我为了方便就没有去重了,而且由于我设置了id自动递增,插入的数据并不包含id,这样会导致已有数据再次插入依旧会存储,实际运用需要去重还要考虑id。
多sheet的excel表格获取数据比较麻烦,需要重新建一个Listener,大体上和单sheet差不多,有一点略微的不同。
controller:
// easyexcel上传文件,多sheet表导入
@PostMapping("/uploadmany")
@ResponseBody
public String uploadMany(MultipartFile file) throws IOException {
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
// 读取sheet0
ReadSheet sheet0 = EasyExcel.readSheet(0).head(User.class).registerReadListener(new UpDataListener<User>(userService)).build();
// 读取sheet1
ReadSheet sheet1 = EasyExcel.readSheet(1).head(Phone.class).registerReadListener(new UpDataListener<Phone>(phoneService)).build();
excelReader.read(sheet0, sheet1);
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
return "上传成功";
}
UpDataListener如下:
@Slf4j
public class UpDataListener<T> extends AnalysisEventListener<T> {
private UserService userService;
private PhoneService phoneService;
public UpDataListener(UserService userService) {
this.userService = userService;
}
public UpDataListener(PhoneService phoneService) {
this.phoneService = phoneService;
}
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<T> list = new ArrayList<T>();
private Integer sheetNo; //获取sheetNo,sheet号,也可以获取名字sheetName
@Override
public void invoke(T t, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(t));
list.add(t);
sheetNo = analysisContext.readSheetHolder().getSheetNo();
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
if (list.size() != 0) {
saveData();
}
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
if(sheetNo==0){
List<User> users = new ArrayList<>();
for (T t : list) {
User user = (User) t;
users.add(user);
}
if (!CollectionUtils.isEmpty(users)) {
userService.saveBatch(users); //集合不为空之后存储用户
}
}
if(sheetNo==1){
List<Phone> phones = new ArrayList<>();
for (T t : list) {
Phone phone = (Phone) t;
phones.add(phone);
}
if (!CollectionUtils.isEmpty(phones)) {
phoneService.saveAllPhones(phones); //集合不为空之后存储手机
}
}
log.info("存储数据库成功!");
}
}
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。