百万数据导出

标签：honzhen com 导出 list import 数据 response 百万

需求分析：

没啥的，看公司给你安排这个活不，安排了就干！！！以简单的实例加上你博大的智慧，必须拿捏。现在只单单用了EasyExcel依赖，多了还没有练，后期合并单元格啥的就啥吧(配合poi依赖还是什么来着)。

四种方法导出：

直接查全表数据写入一个sheet
直接查全表数据写入多个sheet
分页查询，每页数据写入多个sheet
多线程分页查询，每页写入多个sheet

开始

数据库表：

一张表和百万数据导入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(1+rand()*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(10+rand()*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 ;#默认不要自动提交
	REPEAT
	set i = i+1;
	#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
	insert into emp values ((start+i),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,1000000)$$
#命令结束符，再重新设置为;
delimiter;

部门表和公司表留着扩展练习；

这两条sql语句，一个快速删除表数据，一个查询表数据

truncate emp #快速清理百万数据
SELECT count(*) FROM emp #查询有多少数据

项目准备：springboot

项目结构图

实体类

package com.honzhen.domain.DTO;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @Author: honzhen
 * @Date: 2024/3/10 09:44
 * @Description:
 **/

@Data
@AllArgsConstructor
@NoArgsConstructor
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;
}

@ExcelProperty注解就是Excel表里面的列，value就是列名，注意注解路径！！！

依赖和配置

配置有注释，依赖就没写了，请谅解

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- Pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <!-- mysql数据库依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.29</version>
            <scope>runtime</scope>
        </dependency>
        <!--    lombok小辣椒依赖    -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- 添加servlet依赖模块 -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
    </dependencies>

配置：

# åºç¨æå¡ WEB è®¿é®ç«¯å£
server.port=8080

#mybatis插件扫描xml文件路径
mybatis.mapper-locations=classpath:mappers/*xml
#??扫描对应的实体类路径
mybatis.type-aliases-package=com.honzhen.domain
# 数据库字段和实体类属性之间自动转驼峰
mybatis.configuration.map-underscore-to-camel-case=true

# mybatis执行的sql执行日志
logging.level.com.honzhen.dao = debug

# 数据源驱动
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 连接地址加上等等配置
spring.datasource.url=jdbc:mysql://localhost:3306/ceshi?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
# 密码和用户名
spring.datasource.username=root
spring.datasource.password=root

# 分页
pagehelper.reasonable=true
# 数据库分页
pagehelper.auto-dialect=true

#导出最大文件大小
spring.servlet.multipart.max-file-size=100MB
#上传最大文件大小
spring.servlet.multipart.max-request-size=100MB

okk！！！建好了就可以开始干了

导出

导出简简单单，我学了四种方法

Controller层

package com.honzhen.controller;

import com.honzhen.service.ExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StopWatch;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @Author: honzhen
 * @Date: 2024/3/10 15:05
 * @Description:
 **/

@RestController
@RequestMapping("/export")
public class ExportController {

    @Autowired
    private ExportService service;

    /*
        查全表
        写入一个Sheet
     */
    @GetMapping("/export1")
    public void exportExcel1(HttpServletResponse response) throws IOException {
        System.out.println ("全部写入一个sheet开始导出");
        StopWatch stopWatch = new StopWatch ();
        stopWatch.start ();
        service.exportExcel1(response);
        stopWatch.stop ();
        double totalTimeSeconds = stopWatch.getTotalTimeSeconds ();
        System.out.println ("全部写入一个sheet导出时间为："+totalTimeSeconds+"s");
    }

    /*
        查全部
        写入多个Sheet
     */
    @GetMapping("export2")
    public void export2(HttpServletResponse response) throws IOException {
        System.out.println ("查全部写入多个sheet导出开始");
        StopWatch stopWatch = new StopWatch ();
        stopWatch.start ();
        service.exportExcel2(response);
        stopWatch.stop ();
        System.out.println ("查全部写入多个sheet导出时间为"+stopWatch.getTotalTimeSeconds ()+"s");

    }

    /*
        分页查询
        每页数据写入每个sheet
     */
    @GetMapping("/export3")
    public void export3(HttpServletResponse response, @RequestParam Integer pages) throws IOException {
        System.out.println ("分页查询导出sheet开始");
        StopWatch stopWatch = new StopWatch ();
        stopWatch.start ();
        service.exportExcel3(response,pages);
        stopWatch.stop ();
        System.out.println ("分页查询导出时间为"+stopWatch.getTotalTimeSeconds ()+"s");
    }

    /*
        多线程分页查询
        每页写入每个sheet
     */
    @GetMapping("/export4")
    public void export4(HttpServletResponse response,Integer pages) throws IOException, InterruptedException {
        System.out.println ("多线程分页查询导出开始");
        StopWatch stopWatch = new StopWatch ();
        stopWatch.start ();
        service.exportExcel4(response,pages);
        stopWatch.stop ();
        System.out.println ("多线程分页查询导出时间为"+stopWatch.getTotalTimeSeconds ()+"s");
    }

}

service层

package com.honzhen.service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public interface ExportService {
    void exportExcel1(HttpServletResponse response) throws IOException;

    void exportExcel2(HttpServletResponse response) throws IOException;

    void exportExcel3(HttpServletResponse response, Integer pages) throws IOException;

    void exportExcel4(HttpServletResponse response, Integer pages) throws InterruptedException, IOException;
}

serviceImpl实现层

package com.honzhen.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.github.pagehelper.PageHelper;
import com.honzhen.domain.DTO.Emp;
import com.honzhen.mapper.ExportMapper;
import com.honzhen.service.ExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * @Author: honzhen
 * @Date: 2024/3/10 15:06
 * @Description:
 **/

@Service
public class ExportServiceImpl implements ExportService {
    public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    @Autowired
    private ExportMapper mapper;

    @Override
    public void exportExcel1(HttpServletResponse response) throws IOException {
	//导出的文件配置
        setExportHeader (response);
	//查询所有数据
        List<Emp> list = mapper.listAll();
	//直接导出Excel
        EasyExcel.write (response.getOutputStream (), Emp.class).sheet ().doWrite (list);
    }

    @Override
    public void exportExcel2(HttpServletResponse response) throws IOException {
        setExportHeader (response);
	//查询所有数据
        List<Emp> list = mapper.listAll ();
	//先构键一个导出Excel的对象
        ExcelWriter excelWriter = EasyExcel.write (response.getOutputStream (), Emp.class).build ();
        try {
	    //构键导出对象的sheet，静态分sheet
            WriteSheet writeSheet1 = EasyExcel.writerSheet (1, "模版1").build ();
            WriteSheet writeSheet2 = EasyExcel.writerSheet (2, "模版2").build ();
            WriteSheet writeSheet3 = EasyExcel.writerSheet (3, "模版3").build ();
		//每个sheet瓜分数据
            List<Emp> list1 = list.subList (0, list.size () / 3);
            List<Emp> list2 = list.subList (list.size () / 3, list.size () * 2 / 3);
            List<Emp> list3 = list.subList (list.size () * 2 / 3, list.size ());
		//依次写出数据
            excelWriter.write (list1,writeSheet1);
            excelWriter.write (list2,writeSheet2);
            excelWriter.write (list3,writeSheet3);
		//完成，也是开始导出
            excelWriter.finish ();
        } finally {
            excelWriter.finish ();
        }


    }

    @Override
    public void exportExcel3(HttpServletResponse response, Integer pages) throws IOException {
        setExportHeader (response);
        ExcelWriter excelWriter = EasyExcel.write (response.getOutputStream (), Emp.class).build ();
        Long count = mapper.selectCount ();
        long size = count / pages;
        for (Integer i = 0; i < pages; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet (i, "模版" + i).build ();
            PageHelper.startPage (i+1, (int) size);
            List<Emp> list = mapper.listPage ();
            excelWriter.write (list,writeSheet);
        }
        excelWriter.finish ();
    }

    @Override
    public void exportExcel4(HttpServletResponse response, Integer pages) throws InterruptedException, IOException {
        setExportHeader (response);
        Long count = mapper.selectCount ();
        long size = count / pages;
	//线程池导出
        ExecutorService executorService = Executors.newFixedThreadPool (pages);
	//同步工具类 page是要等待的数量或倒计数
        CountDownLatch countDownLatch = new CountDownLatch (pages);
        HashMap<Integer, List<Emp>> map = new HashMap<> ();
        for (Integer i = 0; i < pages; i++) {
            int finall = i;
            executorService.submit (new Runnable () {
                @Override
                public void run() {
                    PageHelper.startPage (finall+1, (int) size);
                    List<Emp> list = mapper.listPage ();
                    map.put (finall,list);
			//任务同步量减一
                    countDownLatch.countDown ();
                }
            });
        }
	//阻塞方法，就是等上面的所有线程走完了才能继续走下面的
        countDownLatch.await ();
        ExcelWriter excelWriter = EasyExcel.write (response.getOutputStream (), Emp.class).build ();
        for (Map.Entry<Integer, List<Emp>> entry : map.entrySet ()) {
            Integer num = entry.getKey ();
            List<Emp> list = entry.getValue ();
            WriteSheet writeSheet = EasyExcel.writerSheet (num, "模版" + num).build ();
            excelWriter.write (list,writeSheet);
        }
        excelWriter.finish ();

    }

    private static void setExportHeader(HttpServletResponse response) {
        //告诉浏览器响应的内容是一个Excel文件
        response.setContentType(CONTENT_TYPE);
        //设置了HTTP响应的字符编码为UTF-8。UTF-8是一种常用的字符编码方式，可以表示多种语言字符，包括中文
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        //设置了HTTP响应的Content-disposition头。Content-disposition头通常用于告诉浏览器如何处理响应的内容
        //设置了Content-disposition头的值为attachment，表示响应的内容应该被作为附件处理。
        // 同时，它还设置了附件的文件名为honzhen.xlsx，并使用UTF-8编码
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "honzhen.xlsx");
    }

}

mapper层

package com.honzhen.mapper;

import com.honzhen.domain.DTO.Emp;

import java.util.List;

/**
 * @Author: honzhen
 * @Date: 2024/3/10 15:05
 * @Description:
 **/


public interface ExportMapper {
    List<Emp> listAll();

    Long selectCount();

    List<Emp> listPage();
}

mybatis层

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.honzhen.mapper.ExportMapper">


    <select id="listAll" resultType="com.honzhen.domain.DTO.Emp">
        select *
        from emp
    </select>
    <select id="selectCount" resultType="java.lang.Long">
        select count(*)
        from emp
    </select>
    <select id="listPage" resultType="com.honzhen.domain.DTO.Emp">
        select *
        from emp
    </select>
</mapper>

导出已完成，看看测试效果