# 基于springboot，使用easyexcel实现excel表格导入数据库与数据库导出到excel表格 **Repository Path**: wlbgxfc/easyexcel ## Basic Information - **Project Name**: 基于springboot，使用easyexcel实现excel表格导入数据库与数据库导出到excel表格 - **Description**: 本项目是一个基于springboot的项目，使用阿里的easyexcel实现了单sheet表导入导出，多sheet表导入导出，单数据库表导入导出，多数据库表导入导出。 导出excel表格的时候有导出到指定位置与导出到浏览器两种选择。实现了基本的查询展示。 下面有具体使用介绍以及一些不成功的示例，本人使用的数据库是5.7.17版，下面的excel展示用到的是wps不是excel，因为比较熟练wp - **Primary Language**: Java - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 67 - **Forks**: 1 - **Created**: 2021-12-14 - **Last Updated**: 2025-08-01 ## Categories & Tags **Categories**: Uncategorized **Tags**: Java, SpringBoot, Maven, easyExcel ## README # 超详细，使用easyexcel实现excel表格导入数据库与数据库导出到excel表格，包你会 #### 介绍 本项目是一个基于springboot的项目，使用阿里的easyexcel实现了单sheet表导入导出，多sheet表导入导出，单数据库表导入导出，多数据库表导入导出。 导出excel表格的时候有导出到指定位置与导出到浏览器两种选择。实现了基本的查询展示。 下面有具体使用介绍以及一些不成功的示例，本人使用的数据库是5.7.17版，下面的excel展示用到的是wps不是excel，因为比较熟练wps而且个人喜欢。 excel导出实现了自适应列宽，前端展示也实现了自适应列宽，可以根据最长内容调整列宽，不过重点在于easyexcel，所以前端随便写的，将就着看吧。 #### 软件架构 springboot+mysql5.x+easyexcel #### 安装教程 1. 下载或者克隆项目到本地，**修改maven配置**，联网等待依赖下载完，直到项目不报错，这第一步还不会我就没的办法了，转行还来得及![maven](README.assets/maven.png) 2. **修改配置文件里面的数据库名称，数据库用户名与密码** 修改application.yml里面的excel.path的绝对路径为项目在你电脑上的绝对路径，保证能访问到项目resources下的excel文件夹与里面的文件（实际上不修改也行，这玩意儿大多数时候是作为下载excel模板功能而存在，但是随便导出数据库数据删了数据后也是现成的好模板） ![application配置](README.assets/application配置.png) 3. 启动项目，访问浏览器，直接访问localhost:8080,进入默认页面则成功![index](README.assets/index.png) #### 使用说明 1. ##### 数据库 本项目实现的是基于springboot，使用阿里的easyexcel实现将数据库数据导出为excel，或者将excel数据导入数据库，首先数据库如下，下载本项目有一个mysql文件夹，里面包含user.sql（和一个phone.sql（新增的））数据库文件。![mysql](README.assets/mysql.png) 2. ##### 实体类注解 我们新建maven项目之后一般都是导入需要的maven插件，然后对应数据库写好实体类，正如上图，导入阿里的easyexcel插件后，新建实体类与以往的实体类一样，不过加上了一些注解。 我们按照数据库表定义好实体类，为其加上注解 @ExcelProperty(value = "xxx", index = 0) ，其中value值对应的是excel表的表头，index的值为数字，代表该列在excel表里面的位置，数字越小列越靠前 ![user实体类](README.assets/user实体类.png) 上图的实体类生成的excel表格如下图，可以看见没有展示id，这是因为使用 @ExcelIgnore 注解可以忽略字段，生成表格的时候忽略掉，使用index=数字，可以定义列的位置，**数字越小列越靠前** ![excel表头](README.assets/excel表头.png) 3. ##### 实体类位置 实体类定义index需要与数据库位置尽量保持一致，也就是说需要excel表格列顺序与数据库字段顺序尽量保持一致，这样导入导出的时候不容易出错，缺失字段也容易发现。 4. ##### excel列宽 当数据库存储的数据长度不一致时，会导致列宽不够，可以使用 @ColumnWidth(20) 注解自定义列宽，使用方法如2中的图1，其中20代表宽度。 但是当列很多时，而且同一列下数据长度差距过大时，通过注解定义会特别麻烦，阿里官方定义的有自适应列宽的方法，但是存在bug，网上有很多修改官方提供方法的例子，大多数都不太理想，方法都是循环一列下的数据，取最宽的一个作为列宽，但是网上的基本上都是最宽那一条还存在溢出，本项目已解决此问题。方法是util包下的Custemhandler类，在util包下的ExcelUtils（已修改，现为UserUtils）类里面进行调用。 ![列宽](README.assets/列宽.png) 这里我修改数据库最后一人姓名，怎么长怎么来，使用自适应列宽之后效果如下![自适应宽度](README.assets/自适应宽度.png) 不使用自适应列宽生成的表格如下，可以看见出生日期会有溢出，而姓名最长那一条则不显示完整，被隐藏了。网络上的自适应方法大都会出现最长那一条会溢出，就行下图日期一样，本项目已完善自适应宽度。 ![不自适应列宽](README.assets/不自适应列宽.png) 5. ##### 多级表头 当我们使用的表涉及二级或者多级表头时，如下图，web管理下面包含独立信息 ![二级表头excel](README.assets/二级表头excel.png) 代码实现如下，依然修改实体类，@ExcelProperty(value = {"Web管理","Web IP"}, index = 12)，在@ExcelProperty注解里面将value用{}括起来，前面填一样的表头，后面填不同的字段，多级表头实现方法同样如此 ![二级表头](README.assets/二级表头.png) 6. ##### 其他样式 设置excel表头的自定义行高，内容行高，表头内容文字居中（居左/居右），内容的文字居中（居左/居右）设置如下，**注意是在实体类类名方法之上写注解**。 ![其他样式](README.assets/其他样式.png) 7. ##### 使用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则会报错** ![数据库Date](README.assets/数据库Date.PNG) 在实体类中使用java.sql.Date和java.util.Date时，查询出的数据分别如下（启动项目后访问localhost:8080进入默认主页，左上角有一个查询所有users按钮可以实现查询） **java.sql.Date**类型的时间格式为yyyy-MM-dd，只有年月日，**不能实现excel导入导出** ![sql.Date](README.assets/sql.Date.png) **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导入导出**![util.Date](README.assets/util.Date.png) 那我们既要格式和数据库一致又要能实现导入导出，还是使用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改） ![util.Date处理](README.assets/util.Date处理.png) ![slice](README.assets/slice.png) 8. ##### 导入excel报错之excel格式问题 7中说到的导入报错是指后台定义的数据类型错误导致的报错，那么本条则介绍excel格式错误。还是时间，因为其他的类型不容易出错，出错则可能是表格列与实体类的列对应不上，如缺少列数，实体类的类型与excel表不一致等等问题（我也确实有过这样的经历，因为别人发给我的表格中间有几列没有宽度，隐藏了，导致缺少列数无法导入成功，后来还是全选表格自适应列宽才发现隐藏了几列），比如excel文本类型对上了实体类中的int类型，届时后台会报错需要什么类型，而提供的是什么类型，也就是类型冲突无法转换。 wps打开excel表格时，里面的时间类型大致有长日期，短日期，时间这三种，也可以直接文本格式写时间，直接放图对比这些数据的差异吧 ![日期与文本](README.assets/日期与文本.png) ![文本失败](README.assets/文本失败.png) 可以看见上图第一条为短日期，数据格式为yyyy/MM/dd,剩下两条为文本格式，文本格式yyyy-MM-dd格式能导入，而日期格式yyyy/MM/dd能，文本格式yyyy/MM/dd不能。 ![文本时间](README.assets/文本时间.png) 紧接着我们继续将文本时间增加yyyy-MM-dd hh:mm:ss 格式的数据，以及添加一条时间类型的数据。注意时间类型数据是yyyy/MM/dd hh:mm:ss格式的，包含年月日1999/10/9，可以看见转换之后表格里面只展示时间，转换成功![导入成功](README.assets/导入成功.png) 可以看见全部能够添加成功。 时间格式可以长但是不能短，长可以舍去多余的部分但是短了无法自动补全所以会报错。如yyyy-MM-dd可以写 2021-11-11，可以是2021-11-11 12:23:34，但是不能是2021或者2021-11。 长日期格式为yyyy年MM月dd日，如图，即使是年月日等中文字样，日期格式依然能存储成功，因为其本质上还是yyyy/MM/dd。 ![长日期](README.assets/长日期.png) ![长日期成功](README.assets/长日期成功.png) 导入成功！ 9. ##### 万能string 上面是使用Date类型的实体类，实际操作中不考虑严格对照数据库格式的话可以使用String类型修饰实体类的参数。 ![string成功](README.assets/string成功.png) ![万能string](README.assets/万能string.png) 时间不再使用Date，使用String，立马万能，之前失败的文本格式yyyy/MM/dd格式也能成功。 ![失败文本成功](README.assets/失败文本成功.png) ![string文本](README.assets/string文本.png) **鉴于这样一条条展示有些不直观，我列了一个表格并清除了数据库数据，用于观察数据插入情况：** ![万能string](README.assets/1647851423771.png) 当然，为了避免不知道错误发生在哪一条，我这菜鸡只好使用笨方法，一条条测试数据了 java.sql.date 全部报错 ![1647852964010](README.assets/1647852964010.png) java.util.date 前五条成功，后面的数据无法解析 ![1647853367944](README.assets/1647853367944.png) ![1647853407791](README.assets/1647853407791.png) ![1647853472185](README.assets/1647853472185.png) ![1647853542482](README.assets/1647853542482.png) ![1647853669235](README.assets/1647853669235.png) ![1647853723728](README.assets/1647853723728.png) ![1647853965758](README.assets/1647853965758.png) String 文本格式带中文的无法转换 ![1647854092217](README.assets/1647854092217.png) 从 “2000年9月2日” 这个格式开始出现错误，导致没有存储成功，但是String能解析到所有时间数据，只是文本类型的带中文的时间，格式无法转换成功 那长日期格式带中文为什么能转换成功呢？实际上excel长日期格式虽然有中文，但是其类型格式还是yyyy/MM/dd ![1647940745168](README.assets/1647940745168.png) ![1647854423876](README.assets/1647854423876.png) ![1647854549591](README.assets/1647854549591.png) ![1647854628948](README.assets/1647854628948.png) ![1647854863154](README.assets/1647854863154.png) 下图即为使用String存储成功之后的数据，和上图一致 ![1647855614267](README.assets/1647855614267.png) ###### 总结： 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语句导入数据库时不会报错，能存储成功。 10. ##### 多数据库导出到单表（多sheet） 在原有表excel表的基础上，我们再建一个表phone，代表手机，用户可以有一个或者多个手机，也可以没有手机。user、phone表数据修改如下： ![1647941100648](README.assets/1647941100648.png) ![1647941120847](README.assets/1647941120847.png) 我们在按照user原有方法，给phone也来一套，先实现user和phone的单独导出，然后再实现导出到单表多个sheet，效果如下： ![1647943072937](README.assets/1647943072937.png) ![1647943113832](README.assets/1647943113832.png) utils方法如下： ```java 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里面直接调用 ```java //导出到多个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表如下 ![shili1](README.assets/shili1.png) 但是插入同一个sheet，效果却可能是这样的（此结果经我测试得出，其他情况也有可能），后面的数据连表头都缺失甚至连数据都缺失 ![shijijieguo](README.assets/shijijieguo.png) 所以此方法行不通！ 11. ##### 多数据库导出到单表（单sheet） 事实上，多表导入单个sheet我们使用联表查询即可。根据用户与手机里面的user_id字段，进行联表查询，sql语句如下： ```mysql 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 ``` 查询结果如下： ![1647945855568](README.assets/1647945855568.png) 以此结果字段来创建实体类，我们创建的实体类就接收这样的数据，既然以此创建了实体类，那么自然就可以导出为单表了，具体方法看代码，导出到单表效果如下： ![1647946019829](README.assets/1647946019829.png) 12. ##### 单sheet表导入多数据库表 单sheet表导入单数据库很简单，单表导入多数据库就稍微难一点了。首先我们一样需要实体类来对应你所拥有的表。当然了，11点里面我们导出的表是基于已有的实体类导出的，无需再去创建实体类，如果是对应不上的表，我们再去创建对应的实体类来接收excel表的数据。 在之前只有user表的时候，读取excel表里面的user数据并保存方法是这样的 ![1647946500123](README.assets/1647946500123.png) list即为我们接收到的excel表里面的数据，那么现在list的单条数据无非是这样的 ![1647946640958](README.assets/1647946640958.png) 里面包含了user和phone，我们只需提取出来单独存储，即可实现存储到多数据库表。 方法如下： ```java private void saveData() { log.info("{}条数据，开始存储数据库！", list.size()); List users = new ArrayList<>(); //创建需要保存的实体类User集合 List 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。 13. ##### 多sheet表导入多数据库表 多sheet的excel表格获取数据比较麻烦，需要重新建一个Listener，大体上和单sheet差不多，有一点略微的不同。 controller： ```java // 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(userService)).build(); // 读取sheet1 ReadSheet sheet1 = EasyExcel.readSheet(1).head(Phone.class).registerReadListener(new UpDataListener(phoneService)).build(); excelReader.read(sheet0, sheet1); // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的 excelReader.finish(); return "上传成功"; } ``` UpDataListener如下： ```java @Slf4j public class UpDataListener extends AnalysisEventListener { 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 list = new ArrayList(); 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 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 phones = new ArrayList<>(); for (T t : list) { Phone phone = (Phone) t; phones.add(phone); } if (!CollectionUtils.isEmpty(phones)) { phoneService.saveAllPhones(phones); //集合不为空之后存储手机 } } log.info("存储数据库成功！"); } } ``` #### 参与贡献 1. Fork 本仓库 2. 新建 Feat_xxx 分支 3. 提交代码 4. 新建 Pull Request #### 特技 1. 使用 Readme\_XXX.md 来支持不同的语言，例如 Readme\_en.md, Readme\_zh.md 2. Gitee 官方博客 [blog.gitee.com](https://blog.gitee.com) 3. 你可以 [https://gitee.com/explore](https://gitee.com/explore) 这个地址来了解 Gitee 上的优秀开源项目 4. [GVP](https://gitee.com/gvp) 全称是 Gitee 最有价值开源项目，是综合评定出的优秀开源项目 5. Gitee 官方提供的使用手册 [https://gitee.com/help](https://gitee.com/help) 6. Gitee 封面人物是一档用来展示 Gitee 会员风采的栏目 [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)