如何搭建公司内部网站,Python仿wordpress,wordpress 安装平台,wordpress旅游主题阿里开源(EasyExcel)---导出EXCEL - 请叫我猿叔叔的博客 - CSDN博客https://blog.csdn.net/qq_35206261/article/details/88579151一. 简介导出是后台管理系统的常用功能#xff0c;当数据量特别大的时候会内存溢出和卡顿页面#xff0c;曾经自己封装过一个导出#xff0c;P…阿里开源(EasyExcel)---导出EXCEL - 请叫我猿叔叔的博客 - CSDN博客https://blog.csdn.net/qq_35206261/article/details/88579151一. 简介导出是后台管理系统的常用功能当数据量特别大的时候会内存溢出和卡顿页面曾经自己封装过一个导出POI百万级大数据量EXCEL导出 采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。不过一是存在封装不太友好使用不方便的问题二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题以及存在内存溢出的隐患。无意间查询到阿里开源的EasyExcel框架发现可以将解析的EXCEL的内存占用控制在KB级别并且绝对不会内存溢出(内部实现待研究),还有就是速度极快 大概100W条记录十几个字段 只需要70秒即可完成下载。遂抛弃自己封装的转战研究阿里开源的EasyExcel. 不过 说实话当时自己封装的那个还是有些技术含量的例如 外观模式模板方法模式以及委托思想组合思想可以看看。EasyExcel的github地址是https://github.com/alibaba/easyexcel二. 案例2.1 POM依赖com.alibabaeasyexcel1.1.12.2 POJO对象package com.authorization.privilege.excel;import java.util.Date;/*** author qjwyss* date 2019/3/15* description*/public class User {private String uid;private String name;private Integer age;private Date birthday;public User() {}public User(String uid, String name, Integer age, Date birthday) {this.uid uid;this.name name;this.age age;this.birthday birthday;}public String getUid() {return uid;}public void setUid(String uid) {this.uid uid;}public String getName() {return name;}public void setName(String name) {this.name name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age age;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday birthday;}}2.3 测试环境2.3.1 数据量少的(20W以内吧)一个SHEET一次查询导出/*** 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中* 注意 一次性查询出来的记录数量不宜过大不会内存溢出即可。** throws IOException*/Testpublic void writeExcelOneSheetOnceWrite() throws IOException {// 生成EXCEL并指定输出路径OutputStream out new FileOutputStream(E:\\temp\\withoutHead1.xlsx);ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置SHEETSheet sheet new Sheet(1, 0);sheet.setSheetName(sheet1);// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(用户ID));titles.add(Arrays.asList(名称));titles.add(Arrays.asList(年龄));titles.add(Arrays.asList(生日));table.setHead(titles);// 查询数据导出即可 比如说一次性总共查询出100条数据List userList new ArrayList();for (int i 0; i 100; i) {userList.add(Arrays.asList(ID_ i, 小明 i, String.valueOf(i), new Date().toString()));}writer.write0(userList, sheet, table);writer.finish();}2.3.2 数据量适中(100W以内) 一个SHEET分批查询导出/*** 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中* 注意* 每次查询出来的记录数量不宜过大根据内存大小设置合理的每次查询记录数不会内存溢出即可。* 数据量不能超过一个SHEET存储的最大数据量105W** throws IOException*/Testpublic void writeExcelOneSheetMoreWrite() throws IOException {// 生成EXCEL并指定输出路径OutputStream out new FileOutputStream(E:\\temp\\withoutHead2.xlsx);ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置SHEETSheet sheet new Sheet(1, 0);sheet.setSheetName(sheet1);// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(用户ID));titles.add(Arrays.asList(名称));titles.add(Arrays.asList(年龄));titles.add(Arrays.asList(生日));table.setHead(titles);// 模拟分批查询总记录数50条每次查询20条 分三次查询 最后一次查询记录数是10Integer totalRowCount 50;Integer pageSize 20;Integer writeCount totalRowCount % pageSize 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize 1);// 注 此处仅仅为了模拟数据实用环境不需要将最后一次分开合成一个即可 参数为 currentPage i1; pageSize pageSizefor (int i 0; i writeCount; i) {// 前两次查询 每次查20条数据if (i writeCount - 1) {List userList new ArrayList();for (int j 0; j pageSize; j) {userList.add(Arrays.asList(ID_ Math.random(), 小明, String.valueOf(Math.random()), new Date().toString()));}writer.write0(userList, sheet, table);} else if (i writeCount - 1) {// 最后一次查询 查多余的10条记录List userList new ArrayList();Integer lastWriteRowCount totalRowCount - (writeCount - 1) * pageSize;for (int j 0; j lastWriteRowCount; j) {userList.add(Arrays.asList(ID_ Math.random(), 小明, String.valueOf(Math.random()), new Date().toString()));}writer.write0(userList, sheet, table);}}writer.finish();}2.3.3 数据量很大(几百万都行) 多个SHEET分批查询导出/*** 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中* 注意* perSheetRowCount % pageSize要能整除 为了简洁非整除这块不做处理* 每次查询出来的记录数量不宜过大根据内存大小设置合理的每次查询记录数不会内存溢出即可。** throws IOException*/Testpublic void writeExcelMoreSheetMoreWrite() throws IOException {// 生成EXCEL并指定输出路径OutputStream out new FileOutputStream(E:\\temp\\withoutHead3.xlsx);ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置SHEET名称String sheetName 测试SHEET;// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(用户ID));titles.add(Arrays.asList(名称));titles.add(Arrays.asList(年龄));titles.add(Arrays.asList(生日));table.setHead(titles);// 模拟分批查询总记录数250条每个SHEET存100条每次查询20条 则生成3个SHEET前俩个SHEET查询次数为5 最后一个SHEET查询次数为3 最后一次写的记录数是10// 注该版本为了较少数据判断的复杂度暂时perSheetRowCount要能够整除pageSize 不去做过多处理 合理分配查询数据量大小不会内存溢出即可。Integer totalRowCount 250;Integer perSheetRowCount 100;Integer pageSize 20;Integer sheetCount totalRowCount % perSheetRowCount 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount 1);Integer previousSheetWriteCount perSheetRowCount / pageSize;Integer lastSheetWriteCount totalRowCount % perSheetRowCount 0 ?previousSheetWriteCount :(totalRowCount % perSheetRowCount % pageSize 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize 1));for (int i 0; i sheetCount; i) {// 创建SHEETSheet sheet new Sheet(i, 0);sheet.setSheetName(sheetName i);if (i sheetCount - 1) {// 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行 2个SHEET合计200行 实用环境参数 currentPage: j1 previousSheetWriteCount*i, pageSize: pageSizefor (int j 0; j previousSheetWriteCount; j) {List userList new ArrayList();for (int k 0; k 20; k) {userList.add(Arrays.asList(ID_ Math.random(), 小明, String.valueOf(Math.random()), new Date().toString()));}writer.write0(userList, sheet, table);}} else if (i sheetCount - 1) {// 最后一个SHEET 实用环境不需要将最后一次分开合成一个即可 参数为 currentPage i1; pageSize pageSizefor (int j 0; j lastSheetWriteCount; j) {// 前俩次查询 每次查询20条if (j lastSheetWriteCount - 1) {List userList new ArrayList();for (int k 0; k 20; k) {userList.add(Arrays.asList(ID_ Math.random(), 小明, String.valueOf(Math.random()), new Date().toString()));}writer.write0(userList, sheet, table);} else if (j lastSheetWriteCount - 1) {// 最后一次查询 将剩余的10条查询出来List userList new ArrayList();Integer lastWriteRowCount totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;for (int k 0; k lastWriteRowCount; k) {userList.add(Arrays.asList(ID_ Math.random(), 小明1, String.valueOf(Math.random()), new Date().toString()));}writer.write0(userList, sheet, table);}}}}writer.finish();}2.4 生产环境2.4.0 Excel常量类package com.authorization.privilege.constant;/*** author qjwyss* date 2019/3/18* description EXCEL常量类*/public class ExcelConstant {/*** 每个sheet存储的记录数 100W*/public static final Integer PER_SHEET_ROW_COUNT 1000000;/*** 每次向EXCEL写入的记录数(查询每页数据大小) 20W*/public static final Integer PER_WRITE_ROW_COUNT 200000;}注 为了书写方便此处俩个必须要整除可以省去很多不必要的判断。 另外如果自己测试可以改为100,20。2.4.1 数据量少的(20W以内吧)一个SHEET一次查询导出Overridepublic ResultVO exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out null;try {out response.getOutputStream();ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置EXCEL名称String fileName new String((SystemExcel).getBytes(), UTF-8);// 设置SHEET名称Sheet sheet new Sheet(1, 0);sheet.setSheetName(系统列表sheet1);// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(系统名称));titles.add(Arrays.asList(系统标识));titles.add(Arrays.asList(描述));titles.add(Arrays.asList(状态));titles.add(Arrays.asList(创建人));titles.add(Arrays.asList(创建时间));table.setHead(titles);// 查数据写EXCELList dataList new ArrayList();List sysSystemVOList this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);if (!CollectionUtils.isEmpty(sysSystemVOList)) {sysSystemVOList.forEach(eachSysSystemVO - {dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),eachSysSystemVO.getSystemKey(),eachSysSystemVO.getDescription(),eachSysSystemVO.getState().toString(),eachSysSystemVO.getCreateUid(),eachSysSystemVO.getCreateTime().toString()));});}writer.write0(dataList, sheet, table);// 下载EXCELresponse.setHeader(Content-disposition, attachment;filename fileName .xlsx);response.setContentType(multipart/form-data);response.setCharacterEncoding(utf-8);writer.finish();out.flush();} finally {if (out ! null) {try {out.close();} catch (Exception e) {e.printStackTrace();}}}return ResultVO.getSuccess(导出系统列表EXCEL成功);}2.4.2 数据量适中(100W以内) 一个SHEET分批查询导出Overridepublic ResultVO exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out null;try {out response.getOutputStream();ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置EXCEL名称String fileName new String((SystemExcel).getBytes(), UTF-8);// 设置SHEET名称Sheet sheet new Sheet(1, 0);sheet.setSheetName(系统列表sheet1);// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(系统名称));titles.add(Arrays.asList(系统标识));titles.add(Arrays.asList(描述));titles.add(Arrays.asList(状态));titles.add(Arrays.asList(创建人));titles.add(Arrays.asList(创建时间));table.setHead(titles);// 查询总数并 【封装相关变量 这块直接拷贝就行 不要改动】Integer totalRowCount this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);Integer pageSize ExcelConstant.PER_WRITE_ROW_COUNT;Integer writeCount totalRowCount % pageSize 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize 1);// 写数据 这个i的最大值直接拷贝就行了 不要改for (int i 0; i writeCount; i) {List dataList new ArrayList();// 此处查询并封装数据即可 currentPage, pageSize这个变量封装好的 不要改动PageHelper.startPage(i 1, pageSize);List sysSystemVOList this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);if (!CollectionUtils.isEmpty(sysSystemVOList)) {sysSystemVOList.forEach(eachSysSystemVO - {dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),eachSysSystemVO.getSystemKey(),eachSysSystemVO.getDescription(),eachSysSystemVO.getState().toString(),eachSysSystemVO.getCreateUid(),eachSysSystemVO.getCreateTime().toString()));});}writer.write0(dataList, sheet, table);}// 下载EXCELresponse.setHeader(Content-disposition, attachment;filename fileName .xlsx);response.setContentType(multipart/form-data);response.setCharacterEncoding(utf-8);writer.finish();out.flush();} finally {if (out ! null) {try {out.close();} catch (Exception e) {e.printStackTrace();}}}return ResultVO.getSuccess(导出系统列表EXCEL成功);}2.4.3 数据里很大(几百万都行) 多个SHEET分批查询导出Overridepublic ResultVO exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {ServletOutputStream out null;try {out response.getOutputStream();ExcelWriter writer new ExcelWriter(out, ExcelTypeEnum.XLSX);// 设置EXCEL名称String fileName new String((SystemExcel).getBytes(), UTF-8);// 设置SHEET名称String sheetName 系统列表sheet;// 设置标题Table table new Table(1);List titles new ArrayList();titles.add(Arrays.asList(系统名称));titles.add(Arrays.asList(系统标识));titles.add(Arrays.asList(描述));titles.add(Arrays.asList(状态));titles.add(Arrays.asList(创建人));titles.add(Arrays.asList(创建时间));table.setHead(titles);// 查询总数并封装相关变量(这块直接拷贝就行了不要改)Integer totalRowCount this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);Integer perSheetRowCount ExcelConstant.PER_SHEET_ROW_COUNT;Integer pageSize ExcelConstant.PER_WRITE_ROW_COUNT;Integer sheetCount totalRowCount % perSheetRowCount 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount 1);Integer previousSheetWriteCount perSheetRowCount / pageSize;Integer lastSheetWriteCount totalRowCount % perSheetRowCount 0 ?previousSheetWriteCount :(totalRowCount % perSheetRowCount % pageSize 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize 1));for (int i 0; i sheetCount; i) {// 创建SHEETSheet sheet new Sheet(i, 0);sheet.setSheetName(sheetName i);// 写数据 这个j的最大值判断直接拷贝就行了不要改动for (int j 0; j (i ! sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j) {List dataList new ArrayList();// 此处查询并封装数据即可 currentPage, pageSize这俩个变量封装好的 不要改动PageHelper.startPage(j 1 previousSheetWriteCount * i, pageSize);List sysSystemVOList this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);if (!CollectionUtils.isEmpty(sysSystemVOList)) {sysSystemVOList.forEach(eachSysSystemVO - {dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),eachSysSystemVO.getSystemKey(),eachSysSystemVO.getDescription(),eachSysSystemVO.getState().toString(),eachSysSystemVO.getCreateUid(),eachSysSystemVO.getCreateTime().toString()));});}writer.write0(dataList, sheet, table);}}// 下载EXCELresponse.setHeader(Content-disposition, attachment;filename fileName .xlsx);response.setContentType(multipart/form-data);response.setCharacterEncoding(utf-8);writer.finish();out.flush();} finally {if (out ! null) {try {out.close();} catch (Exception e) {e.printStackTrace();}}}return ResultVO.getSuccess(导出系统列表EXCEL成功);}三、总结造的假数据100W条记录18个字段测试导出是70s。 在实际上产环境使用的时候具体的还是要看自己写的sql的性能。 sql性能快的话会很快。 有一点推荐一下 在做分页的时候使用单表查询 对于所需要处理的外键对应的冗余字段在外面一次性查出来放到map里面(推荐使用MapKey注解)然后遍历list的时候根据外键从map中获取对应的名称。一个宗旨少发查询sql, 才能更快的导出。题外话 如果数据量过大在使用count(1)查询总数的时候会很慢可以通过调整mysql的缓冲池参数来加快查询请参见博主的另一篇博文MYSQL单表数据量过大查询过慢配置优化innodb_buffer_pool_size。 还有就是遇到了一个问题使用pagehelper的时候数据量大的时候limit 0,20W; limit 20W,40W, limit 40W,60W, limit 60W,80W 查询有的时候会很快有的时候会很慢待研究。---------------------作者请叫我猿叔叔来源CSDN原文https://blog.csdn.net/qq_35206261/article/details/88579151版权声明本文为博主原创文章转载请附上博文链接
