网站服务器指的是什么,如何制作公司app,怎么用家里的电脑做网站服务器,网站建设yankt在项目开发中往往需要使用到数据的导入和导出#xff0c;导入就是从Excel中导入到DB中#xff0c;而导出就是从DB中查询数据然后使用POI写到Excel上。 大数据的导入和导出#xff0c;相信大家在日常的开发、面试中都会遇到。 很多问题只要这一次解决了#xff0c;总给复盘记… 在项目开发中往往需要使用到数据的导入和导出导入就是从Excel中导入到DB中而导出就是从DB中查询数据然后使用POI写到Excel上。 大数据的导入和导出相信大家在日常的开发、面试中都会遇到。 很多问题只要这一次解决了总给复盘记录后期遇到同样的问题就好解决了。好啦废话不多说开始正文 1.传统POI的的版本优缺点比较
其实想到数据的导入导出理所当然的会想到apache的poi技术以及Excel的版本问题。 HSSFWorkbook 这个实现类是我们早期使用最多的对象它可以操作Excel2003以前包含2003的所有Excel版本。在2003以前Excel的版本后缀还是.xls XSSFWorkbook 这个实现类现在在很多公司都可以发现还在使用它是操作的Excel2003–Excel2007之间的版本Excel的扩展名是.xlsx SXSSFWorkbook 这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx HSSFWorkbook
它是POI版本中最常用的方式不过: 它的缺点是 最多只能导出 65535行也就是导出的数据函数超过这个数据就会报错; 它的优点是 不会报内存溢出。因为数据量还不到7w所以内存一般都够用首先你得明确知道这种方式是将数据先读取到内存中然后再操作 XSSFWorkbook 优点这种形式的出现是为了突破HSSFWorkbook的65535行局限是为了针对Excel2007版本的1048576行16384列最多可以导出104w条数据 缺点伴随的问题来了虽然导出数据行数增加了好多倍但是随之而来的内存溢出问题也成了噩梦。因为你所创建的bookSheetrowcell等在写入到Excel之前都是存放在内存中的这还没有算Excel的一些样式格式等等可想而知内存不溢出就有点不科学了 SXSSFWorkbook
从POI 3.8版本开始提供了一种基于XSSF的低内存占用的SXSSF方式
优点 这种方式不会一般不会出现内存溢出它使用了硬盘来换取内存空间 也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来而内存中存的都是最新的数据 并且支持大型Excel文件的创建存储百万条数据绰绰有余。 缺点 既然一部分数据持久化到了硬盘中且不能被查看和访问那么就会导致 在同一时间点我们只能访问一定数量的数据也就是内存中存储的数据; sheet.clone()方法将不再支持,还是因为持久化的原因; 不再支持对公式的求值还是因为持久化的原因在硬盘中的数据没法读取到内存中进行计算 在使用模板方式下载数据的时候不能改动表头还是因为持久化的问题写到了硬盘里就不能改变了 2.使用方式哪种看情况
经过了解也知道了这三种Workbook的优点和缺点那么具体使用哪种方式还是需要看情况的
我一般会根据这样几种情况做分析选择 1、当我们经常导入导出的数据不超过7w的情况下可以使用 HSSFWorkbook 或者 XSSFWorkbook都行 2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式公式格式等操作的情况下推荐使用SXSSFWorkbook; 3、当数据量查过7w并且我们需要操做Excel中的表头样式公式等这时候我们可以使用 XSSFWorkbook 配合进行分批查询分批写入Excel的方式来做 3.百万数据导入导出
想要解决问题我们首先要明白自己遇到的问题是什么 1、 我遇到的数据量超级大使用传统的POI方式来完成导入导出很明显会内存溢出并且效率会非常低 2、 数据量大直接使用select * from tableName肯定不行一下子查出来300w条数据肯定会很慢 3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中这样效率会非常低估计打开都得几分钟 4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行 5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行 6、导入时300w数据如果使用Mybatis的批量插入肯定不行因为Mybatis的批量插入其实就是SQL的循环一样很慢。 解决思路
针对1 其实问题所在就是内存溢出我们只要使用对上面介绍的POI方式即可主要问题就是原生的POI解决起来相当麻烦。 经过查阅资料翻看到阿里的一款POI封装工具EasyExcel上面问题等到解决 针对2 不能一次性查询出全部数据我们可以分批进行查询只不过时多查询几次的问题况且市面上分页插件很多。此问题好解决。 针对3 可以将300w条数据写到不同的Sheet中每一个Sheet写一百万即可。 针对4 不能一行一行的写入到Excel上我们可以将分批查询的数据分批写入到Excel中。 针对5 导入到DB时我们可以将Excel中读取的数据存储到集合中到了一定数量直接批量插入到DB中。 针对6 不能使用Mybatis的批量插入我们可以使用JDBC的批量插入配合事务来完成批量插入到DB。即 Excel读取分批JDBC分批插入事务。 3.1 模拟500w数据导出
需求使用EasyExcel完成500w数据的导出。
500w数据的导出解决思路 首先在查询数据库层面需要分批进行查询比如每次查询20w 每查询一次结束就使用EasyExcel工具将这些数据写入一次 当一个Sheet写满了100w条数据开始将查询的数据写入到另一个Sheet中 如此循环直到数据全部导出到Excel完毕。 ps:我们需要计算Sheet个数以及循环写入次数。特别是最后一个Sheet的写入次数 因为你不知道最后一个Sheet会写入多少数据可能是100w也可能是25w因为我们这里的500w只是模拟数据有可能导出的数据比500w多也可能少 ps:我们需要计算写入次数因为我们使用的分页查询所以需要注意写入的次数。 其实查询数据库多少次就是写入多少次
准备工作
1.基于maven搭建springboot工程引入easyexcel依赖这里我是用的时3.0版本 dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.0.5/version /dependency 2.创建海量数据的sql脚本 CREATE TABLE dept( /*部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT , loc VARCHAR(13) NOT NULL DEFAULT ) ; #创建表EMP雇员 CREATE TABLE emp (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/ job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/ mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/ hiredate DATE NOT NULL,/*入职时间*/ sal DECIMAL(7,2) NOT NULL,/*薪水*/ comm DECIMAL(7,2) NOT NULL,/*红利*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/ ) ; #工资级别表 CREATE TABLE salgrade ( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, losal DECIMAL(17,2) NOT NULL, hisal DECIMAL(17,2) NOT NULL ); #测试数据 INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); delimiter $$ #创建一个函数名字 rand_string可以随机返回我指定的个数字符串 create function rand_string(n INT) returns varchar(255) #该函数会返回一个字符串 begin #定义了一个变量 chars_str 类型 varchar(100) #默认给 chars_str 初始值 abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ; declare i int default 0; while i n do # concat 函数 : 连接函数mysql函数 set return_str concat(return_str,substring(chars_str,floor(1rand()*52),1)); set i i 1; end while; return return_str; end $$ #这里我们又自定了一个函数,返回一个随机的部门号 create function rand_num( ) returns int(5) begin declare i int default 0; set i floor(10rand()*500); return i; end $$ #创建一个存储过程 可以添加雇员 create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 0; #set autocommit 0 把autocommit设置成0 #autocommit 0 含义: 不要自动提交 set autocommit 0; #默认不提交sql语句 repeat set i i 1; #通过前面写的函数随机产生字符串和部门编号然后加入到emp表 insert into emp values ((starti) ,rand_string(6),SALESMAN,0001,curdate(),2000,400,rand_num()); until i max_num end repeat; #commit整体提交所有sql语句提高效率 commit; end $$ #添加8000000数据 call insert_emp(100001,8000000)$$ #命令结束符再重新设置为; delimiter ; 3.实体类 Data NoArgsConstructor AllArgsConstructor public class Emp implements Serializable { ExcelProperty(value 员工编号) private Integer empno; ExcelProperty(value 员工名称) private String ename; ExcelProperty(value 工作) private String job; ExcelProperty(value 主管编号) private Integer mgr; ExcelProperty(value 入职日期) private Date hiredate; ExcelProperty(value 薪资) private BigDecimal sal; ExcelProperty(value 奖金) private BigDecimal comm; ExcelProperty(value 所属部门) private Integer deptno; } 4.vo类 Data public class EmpVo { ExcelProperty(value 员工编号) private Integer empno; ExcelProperty(value 员工名称) private String ename; ExcelProperty(value 工作) private String job; ExcelProperty(value 主管编号) private Integer mgr; ExcelProperty(value 入职日期) private Date hiredate; ExcelProperty(value 薪资) private BigDecimal sal; ExcelProperty(value 奖金) private BigDecimal comm; ExcelProperty(value 所属部门) private Integer deptno; } 导出核心代码 Resource private EmpService empService; /** * 分批次导出 */ GetMapping(/export) public void export() throws IOException { StopWatch stopWatch new StopWatch(); stopWatch.start(); empService.export(); stopWatch.stop(); System.out.println(共计耗时 stopWatch.getTotalTimeSeconds()S); } public class ExcelConstants { //一个sheet装100w数据 public static final Integer PER_SHEET_ROW_COUNT 1000000; //每次查询20w数据每次写入20w数据 public static final Integer PER_WRITE_ROW_COUNT 200000; } Override public void export() throws IOException { OutputStream outputStream null; try { //记录总数:实际中需要根据查询条件进行统计即可 //LambdaQueryWrapperEmp lambdaQueryWrapper new QueryWrapperEmp().lambda().eq(Emp::getEmpno, 1000001); Integer totalCount empMapper.selectCount(null); //每一个Sheet存放100w条数据 Integer sheetDataRows ExcelConstants.PER_SHEET_ROW_COUNT; //每次写入的数据量20w,每页查询20W Integer writeDataRows ExcelConstants.PER_WRITE_ROW_COUNT; //计算需要的Sheet数量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //计算一般情况下每一个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)); ServletRequestAttributes requestAttributes (ServletRequestAttributes) RequestContextHolder.getRequestAttributes(); HttpServletResponse response requestAttributes.getResponse(); outputStream response.getOutputStream(); //必须放到循环外否则会刷新流 ExcelWriter excelWriter EasyExcel.write(outputStream).build(); //开始分批查询分次写入 for (int i 0; i sheetNum; i) { //创建Sheet WriteSheet sheet new WriteSheet(); sheet.setSheetName(测试Sheet1i); sheet.setSheetNo(i); //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //分页查询一次20w PageEmp page empMapper.selectPage(new Page(j 1 oneSheetWriteCount * i, writeDataRows), null); ListEmp empList page.getRecords(); ListEmpVo empVoList new ArrayList(); for (Emp emp : empList) { EmpVo empVo new EmpVo(); BeanUtils.copyProperties(emp, empVo); empVoList.add(empVo); } WriteSheet writeSheet EasyExcel.writerSheet(i, 员工信息 (i 1)).head(EmpVo.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); //写数据 excelWriter.write(empVoList, writeSheet); } } // 下载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) { outputStream.close(); } } } 这是我电脑测试时内存占用和CPU使用情况当然开了其他一些应用。
