EasyExcel导入导出

在项目开发中往往需要使用到数据的导入和导出，导入就是从Excel中导入到DB中，而导出就是从DB中查询数据然后使用POI写到Excel上。所以今天就为大家带来一款基于阿里EasyExcel的导入导出功能，开放了一个demo，以下是gitee地址，有兴趣的可以去看看：EasyExcelDemo:基于EasyExcel实现导入导出的简易demo

该demo以及我下面所要分享的都是使用的mongodb来做的，使用mysql的小伙伴大同小异，自己改造一下，后续如果有时间的话我会在gitee以及这里更新mysql的方式

===================================================================================================

2023/07/03  更新：

百忙之中抽空更新了mysql的导入导出方式，代码已提交，放在gitee公开项目上，感兴趣的去拉取下来观看、改造、直接使用都可以，mysql的方式和mongo的方式都大同小异，就不在文章下面更新代码和使用方式了

====================================================================================================

1.引入依赖

com.alibabaeasyexcel3.0.5


com.apifan.commoncommon-random1.0.16

2.测试数据制造

可以看到，在上面的maven中，我提到了一个随机数据生成的依赖，这个依赖是我在网上找到的，简直就是解放了我们的双手(当时我是打算通过手敲方法，调用方法来生成的，下载了demo的可以在工具类中看到，我已经写了不少了手动封装数据的方法)；详细的使用方式步骤，更多的数据生成方式可以参考这篇博客：Java-随机数据生成器

调用数据生成方法：

/*** 随机生成一条用户数据** @return*/public static Map createUserdata() {//生成1个随机中文人名(性别随机)String userName = PersonInfoSource.getInstance().randomChineseName();//生成1个随机中国大陆手机号String phone = PersonInfoSource.getInstance().randomChineseMobile();//生成1个随机QQ号String qqNumber = PersonInfoSource.getInstance().randomQQAccount();//生成1个随机邮箱地址，后缀为163.com，邮箱用户名最大长度为10String email = InternetSource.getInstance().randomEmail(10, "163.com");//生成1个2000年的随机日期，日期格式为yyyy-MM-ddString borthday = DateTimeSource.getInstance().randomDate(2000, "yyyy-MM-dd");//生成过去36000秒范围内的随机时间LocalDateTime lastLogintime = DateTimeSource.getInstance().randomPastTime(LocalDateTime.now(), 36000);//生成1个随机强密码，长度为16，无特殊字符String pwd = PersonInfoSource.getInstance().randomStrongPassword(16, false);//生成1个随机中国大陆详细地址String addr = AreaSource.getInstance().randomAddress();//生成1个随机英文人名String nikName = PersonInfoSource.getInstance().randomEnglishName();//生成1个随机虚拟银联信用卡号码String bankCard = PersonInfoSource.getInstance().randomCreditCardNo(CreditCardType.UnionPay);Map data = new HashMap();data.put("userName", userName);data.put("phone", phone);data.put("qqNumber", qqNumber);data.put("email", email);data.put("borthday", borthday);data.put("pwd", pwd);data.put("lastLogintime", lastLogintime);data.put("addr", addr);data.put("nikName", nikName);data.put("bankCard", bankCard);return data;}

swagger接口调用生成100w条数据：

 生成结果：

 3.创建实体类

@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserData {@ExcelProperty(value = "姓名") // 导出后的表头名称private String userName;@ExcelProperty(value = "银行卡号")private String bankCard;@ExcelProperty(value = "电话号码")private String phone;@ExcelProperty(value = "家庭住址")private String addr;@ExcelProperty(value = "别名")private String nikName;@ExcelProperty(value = "QQ号")private String qqNumber;@ExcelProperty(value = "邮箱")private String email;@ExcelProperty(value = "生日")private String borthday;@ExcelProperty(value = "密码")private String pwd;@ExcelProperty(value = "最后登录时间")private String lastLogintime;@ExcelProperty(value = "创建人")private String creatUser;@ExcelProperty(value = "更新者")private String updateUser;
}

注意：我这里创建的实体类是与我生成数据的字段所对应的，需求不同请酌情修改

4.导出

接口：

@PostMapping("/exportExcel")@ApiOperation(value = "导出用户数据", notes = "导出用户数据详细信息")@ApiImplicitParams({@ApiImplicitParam(name = "num", value = "文件导出页数", required = true, dataType = "Long", defaultValue ="2"),@ApiImplicitParam(name = "enable", value = "是否开启分页", required = true, dataType = "Boolean", defaultValue ="false")})public ResponseEntity exportExcel(@RequestParam(defaultValue = "2") Long num,@RequestParam(defaultValue = "false") Boolean enable) {excelService.exportExcel(num, enable);ResponseEntity entity = new ResponseEntity<>("OK", HttpStatus.OK);return entity;}

核心代码：

/*** 导出表格** @param num    导出页数；若开启分页参数为false则此参数无效* @param enable 是否开启分页*/@Overridepublic void exportExcel(Long num, Boolean enable) {StopWatch stopWatch = new StopWatch();stopWatch.start();export(num, enable);stopWatch.stop();logger.info("共计耗时： " + stopWatch.getTotalTimeSeconds() + "S");}
/*** 导出所有用户数据** @param num* @param enable*/public void export(Long num, Boolean enable) {String collectionName = "test.user.manyData";OutputStream outputStream = null;try {// 获取当前线程的请求属性ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();HttpServletResponse response = requestAttributes.getResponse();outputStream = response.getOutputStream();// 创建文件导出流ExcelWriter excelWriter = EasyExcel.write(outputStream).build();// 是否开启分页if (enable) {// 统计数据库中的数据总数Integer totalCount = Math.toIntExact(mongoTemplate.count(new Query(), collectionName));// 根据总数，计算每页需要多少条数据Integer floor = (int) Math.floor(totalCount / num.intValue());// 每一个Sheet页需要存放的数据条数Integer sheetDataRows = floor;// 每次写入的数据量Integer writeDataRows = floor;// 计算需要的Sheet数量Integer sheetNum = num.intValue();// 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)Integer oneSheetWriteCount = sheetDataRows / writeDataRows;// 计算最后一个sheet需要写入的次数Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));// 开始分批查询分次写入for (int i = 0; i < sheetNum; i++) {// 创建SheetWriteSheet sheet = new WriteSheet();sheet.setSheetName("人员页数" + i);sheet.setSheetNo(i);// 循环写入次数: j是当目前不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCountfor (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {Query query = new Query();query.skip(i * floor); // 跳过前面的 pageNo-1 页（已查询的记录数）query.limit(floor); // 返回 pageSize 条记录（每页显示的记录数）List userDataList = mongoTemplate.find(query, UserData.class, collectionName);WriteSheet writeSheet = EasyExcel.writerSheet(i, "用户信息" + (i + 1)).head(UserData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();// 写数据excelWriter.write(userDataList, writeSheet);}}} else {Query query = new Query();// 全量查询所有数据List userDataList = mongoTemplate.find(query, UserData.class, collectionName);WriteSheet writerSheet = EasyExcel.writerSheet(0, "用户信息").head(UserData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();excelWriter.write(userDataList, writerSheet);}// 下载EXCELresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("用户详细信息表格", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 关流excelWriter.finish();outputStream.flush();} catch (IOException e) {e.printStackTrace();} catch (BeansException e) {e.printStackTrace();} finally {if (outputStream != null) {try {outputStream.close();} catch (IOException e) {throw new RuntimeException(e);}}}}

导出测试：

100W数据导出耗时：

文件大小：

swagger填写参数(分10页)：

 结果查看：

 可以看到，1分钟完成了百万级的数据导出，性能就不用多说了吧

5.导入

这里导入功能，我做了俩个接口，一个是获取导入的excel模板的接口，另一个则是导入excel到数据库的接口

接口：

@GetMapping("/exportTemplateExcel")@ApiOperation(value = "导出用户数据模板", notes = "导出用户数据模板")public ResponseEntity exportTemplateExcel() {excelService.exportTemplateExcel();ResponseEntity entity = new ResponseEntity<>("OK", HttpStatus.OK);return entity;}@PostMapping("/importExcel")@ApiOperation(value = "导入用户数据", notes = "导入用户数据详细信息")public ResponseEntity importExcel(@RequestParam("file") MultipartFile multipartFile) {String importExcelPath = excelService.importExcel(multipartFile);long startReadTime = System.currentTimeMillis();System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");// 读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法EasyExcel.read(importExcelPath, new EasyExceGeneralDatalListener(excelService)).doReadAll();long endReadTime = System.currentTimeMillis();System.out.println("------读取Excel的Sheet时间(包括导入数据)共计耗时:" + (endReadTime-startReadTime) + "ms------");ResponseEntity entity = new ResponseEntity<>("OK", HttpStatus.OK);return entity;}

 核心代码部分：

导出模板代码：

/*** 导出表格模板文件*/@Overridepublic void exportTemplateExcel() {OutputStream outputStream = null;try {// 获取当前线程的请求属性ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();HttpServletResponse response = requestAttributes.getResponse();// 创建一个输出流outputStream = response.getOutputStream();// build输出ExcelWriter excelWriter = EasyExcel.write(outputStream).build();// 填写sheet页WriteSheet writerSheet = EasyExcel.writerSheet(0, "用户信息模板").head(UserData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();// 写出模板文件excelWriter.write(new ArrayList<>(), writerSheet);// 下载EXCEL，设置响应参数response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 设置响应参数，这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("用户详细信息表格", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 关流excelWriter.finish();outputStream.flush();} catch (IOException e) {e.printStackTrace();} catch (BeansException e) {e.printStackTrace();} finally {if (outputStream != null) {try {outputStream.close();} catch (IOException e) {throw new RuntimeException(e);}}}}

 导入数据代码：

/*** 导入表格,保存到本地** @param multipartFile  表格参数原件* @return*/@Overridepublic String importExcel(MultipartFile multipartFile) {try {// 获取上传的文件信息String fileName = multipartFile.getOriginalFilename();// 保存上传的文件到本地磁盘Path dest = Paths.get("C:\\Users\\IDLINK\\Desktop\\文档说明\\", fileName);Files.copy(multipartFile.getInputStream(), dest);return dest.toString();} catch (IOException e) {throw new RuntimeException(e);}}
/*** 保存数据到mongo** @param dataList*/@Overridepublic void importData(List> dataList) {String collectionName = "test.user.manyData";//结果集中数据为0时,结束方法.进行下一次调用if (dataList.size() == 0) {return;}try {long startTime = System.currentTimeMillis();logger.info(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");List list = new ArrayList();for (int i = 1; i < dataList.size(); i++) {Map item = dataList.get(i);if (StringUtils.isBlank(item.get(0))||StringUtils.isBlank(item.get(1))||StringUtils.isBlank(item.get(2))||StringUtils.isBlank(item.get(3))||StringUtils.isBlank(item.get(7))||StringUtils.isBlank(item.get(8))){throw new RuntimeException(String.format("必填字段不能为空！"));}UserData userData = new UserData();userData.setUserName(item.get(0));userData.setBankCard(item.get(1));userData.setPhone(item.get(2));userData.setAddr(item.get(3));userData.setNikName(item.get(4));userData.setQqNumber(item.get(5));userData.setEmail(item.get(6));userData.setBorthday(item.get(7));userData.setPwd(item.get(8));userData.setLastLogintime(item.get(9));userData.setCreatUser(item.get(10));userData.setUpdateUser(item.get(11));list.add(userData);}mongoTemplate.insert(list, collectionName);long endTime = System.currentTimeMillis();logger.info(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");logger.info(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");} catch (Exception e) {e.printStackTrace();}}

注意：该easyexcel导入需要配合导入监听类AnalysisEventListener来实现，以下就不过多介绍，我直接把监听类代码贴在下面，可以直接使用，如果不清楚的还是建议去我提供的gitee看看

/*** 导入事件监听类，只要有文件传入就会调用read方法，就会触发该监听器*/
public class EasyExceGeneralDatalListener extends AnalysisEventListener> {/*** 处理业务逻辑的Service,也可以是Mapper*/@Autowiredprivate ExcelService empService;/*** 用于存储读取的数据*/private List> dataList = new ArrayList>();public EasyExceGeneralDatalListener() {}public EasyExceGeneralDatalListener(ExcelService empService) {this.empService = empService;}@Overridepublic void invoke(Map data, AnalysisContext context) {//数据add进入集合dataList.add(data);//size是否为10000条:这里其实就是分批.当数据等于1w的时候执行一次插入if (dataList.size() >= 10000) {//存入数据库:数据小于1w条使用Mybatis的批量插入即可;saveData();//清理集合便于GC回收dataList.clear();}}/*** 保存数据到DB** @param* @MethodName: saveData* @return: void*/private void saveData() {empService.importData(dataList);dataList.clear();}/*** Excel中所有数据解析完毕会调用此方法** @param: context* @MethodName: doAfterAllAnalysed* @return: void*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();dataList.clear();}
}

导入测试：

首先删除集合

在swagger中上传文件：

 

注意：需要在yml中配置文件上传大小，否则可能会报错(大小超过限制) ，详细配置信息如下：

spring:servlet:multipart:enabled: truefile-size-threshold: 2KBmax-file-size: 200MBmax-request-size: 200MB

导入耗时1分钟：

 刷新mongo集合，查看数据量是否正确

 

百万条数据完美导入；耗时一分钟 

6.更多使用方式可以查看官方文档：

官方文档网址：EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel