打开有些网站显示建设中,网站网页怎么设计,国外网站建设素材库,网络推广公司挣钱吗一. 简介
导出是后台管理系统的常用功能#xff0c;当数据量特别大的时候会内存溢出和卡顿页面#xff0c;曾经自己封装过一个导出#xff0c;采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。
不过一是存…一. 简介
导出是后台管理系统的常用功能当数据量特别大的时候会内存溢出和卡顿页面曾经自己封装过一个导出采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。
不过一是存在封装不太友好使用不方便的问题二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题以及存在内存溢出的隐患。
无意间查询到阿里开源的EasyExcel框架发现可以将解析的EXCEL的内存占用控制在KB级别并且绝对不会内存溢出内部实现待研究,还有就是速度极快大概100W条记录十几个字段只需要70秒即可完成下载。
遂抛弃自己封装的转战研究阿里开源的EasyExcel. 不过 说实话当时自己封装的那个还是有些技术含量的例如外观模式模板方法模式以及委托思想组合思想可以看看。另外微信搜索关注Java技术栈发送设计模式可以获取我整理的 Java 设计模式实战教程。
EasyExcel的github地址是https://github.com/alibaba/easyexcel
二. 案例
2.1 POM依赖
!-- 阿里开源EXCEL --
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion1.1.1/version
/dependency2.2 POJO对象
package com.authorization.privilege.excel;import java.util.Date;/*** author qjwyss* 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一次查询导出
如果数据量过大不要用传统分页利用where id #{lastMaxId} order by id limit 100,解决分页慢的问题
/*** 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中* 注意 一次性查询出来的记录数量不宜过大不会内存溢出即可。** throws IOException*/
Test
public 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);ListListString titles new ArrayListListString();titles.add(Arrays.asList(用户ID));titles.add(Arrays.asList(名称));titles.add(Arrays.asList(年龄));titles.add(Arrays.asList(生日));table.setHead(titles);// 查询数据导出即可 比如说一次性总共查询出100条数据ListListString 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*/
Test
public 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);ListListString titles new ArrayListListString();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) {ListListString 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条记录ListListString 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*/
Test
public 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);ListListString titles new ArrayListListString();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) {ListListString 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) {ListListString 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条查询出来ListListString 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* 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。 分享给你 Spring Boot 学习笔记。 2.4.1.数据量少的(20W以内吧)一个SHEET一次查询导出
Override
public ResultVOVoid 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);ListListString titles new ArrayListListString();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);// 查数据写EXCELListListString dataList new ArrayList();ListSysSystemVO 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 new String((fileName).getBytes(gb2312), ISO-8859-1) .xls);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分批查询导出
Override
public ResultVOVoid 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);ListListString titles new ArrayListListString();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) {ListListString dataList new ArrayList();// 此处查询并封装数据即可 currentPage, pageSize这个变量封装好的 不要改动PageHelper.startPage(i 1, pageSize);ListSysSystemVO 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 new String((fileName).getBytes(gb2312), ISO-8859-1) .xls);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分批查询导出
Override
public ResultVOVoid 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);ListListString titles new ArrayListListString();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) {ListListString dataList new ArrayList();// 此处查询并封装数据即可 currentPage, pageSize这俩个变量封装好的 不要改动PageHelper.startPage(j 1 previousSheetWriteCount * i, pageSize);ListSysSystemVO 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 new String((fileName).getBytes(gb2312), ISO-8859-1) .xls);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, 才能更快的导出。 题外话如果数据量过大不要用传统分页利用where id #{lastMaxId} order by id limit 100,解决分页慢的问题
