六安市城乡和建设局官方网站,wordpress服务器如何使用,旅游网站的系统建设的意义,离线推广网站规划书读Excel | Easy Excel
1、 我遇到的数据量超级大#xff0c;使用传统的POI方式来完成导入导出很明显会内存溢出#xff0c;并且效率会非常低#xff1b;2、 数据量大直接使用select * from tableName肯定不行#xff0c;一下子查出来300w条数据肯定会很慢#xff1b;3、 …读Excel | Easy Excel
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.基于maven搭建springboot工程引入easyexcel依赖这里我是用的时3.0版本 !--EasyExcel相关依赖--dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.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 初始值 abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZdeclare chars_str varchar(100) defaultabcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; 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语句repeatset i i 1;#通过前面写的函数随机产生字符串和部门编号然后加入到emp表insert into emp values ((starti) ,rand_string(6),SALESMAN,0001,curdate(),2000,400,rand_num());until i max_numend repeat;#commit整体提交所有sql语句提高效率commit;end $$#添加8000000数据
call insert_emp(100001,8000000)$$#命令结束符再重新设置为;
delimiter ; 3.实体类
Data
NoArgsConstructor
AllArgsConstructor
public class Emp implements Serializable {ExcelIgnoreprivate 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 {ExcelIgnoreprivate 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;} 5、导出核心代码 Resource
private EmpService empService;
/*** 分批次导出*/
GetMapping(/export)
public void export() throws IOException {Long startTime System.currentTimeMillis();empService.export(); //导出Long endTime System.currentTimeMillis();Long elapsedTime (endTime - startTime) / 1000;System.out.println(导出_方式耗时 elapsedTime 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 {//记录总数:实际中需要根据查询条件进行统计即可 Integer totalCount empMapper.selectCount(null);//每一个Sheet存放100w条数据Integer sheetDataRows ExcelConstants.PER_SHEET_ROW_COUNT;//每次写入的数据量20w,每页查询20WInteger writeDataRows ExcelConstants.PER_WRITE_ROW_COUNT;//计算需要的Sheet数量Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1);//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)Integer oneSheetWriteCount sheetDataRows % writeDataRows 0 ? (sheetDataRows / writeDataRows) : (sheetDataRows / writeDataRows 1);//计算最后一个sheet需要写入的次数Integer lastCountAll totalCount - (sheetNum-1)*sheetDataRows;Integer lastSheetWriteCount lastCountAll % writeDataRows 0 ? (lastCountAll / writeDataRows) : (lastCountAll / 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) {//创建SheetWriteSheet sheet new WriteSheet();sheet.setSheetName(测试Sheet1i);sheet.setSheetNo(i);//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCountfor (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) {//分页查询一次20wPageEmp page empMapper.selectPage(new Page(j 1 oneSheetWriteCount * i, writeDataRows), null);ListEmp empList page.getRecords();//使用pagehelper的如下
// int pageNum j 1 oneSheetWriteCount * i; //PageHelper.startPage(pageNum,writeDataRows,getOrderBy(pageable.getSort())).setReasonable(true);
//ListDqtbsHiddenDangerQuery list mapper.expmortByPage(dto);//封装成可以导出实体类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);}}// 下载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) {outputStream.close();}}
}
前端vue的 //导出exportExcel(values) {let this_ thisthis_.spinningExport truethis.$api.export(BASE_URL exportNew, values, 数据查询_${moment(new Date()).format(YYYY-MM-DD)}.xlsx, {success() {this_.spinningExport false},fail() {this_.spinningExport false}})}, 分批量查询例如一个sheet 页 存储 10000条分页每次查1000条每个sheet需要查10次。总数据50000条就分了5个sheet页来显示大批量数据来说easyExcel还是很好用的。
