文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

基于EasyExcel实现百万级数据导入导出

2023-09-12 18:38

关注

在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。

大数据的导入和导出,相信大家在日常的开发、面试中都会遇到。

很多问题只要这一次解决了,总给复盘记录,后期遇到同样的问题就好解决了。好啦,废话不多说开始正文!

1.传统POI的的版本优缺点比较

其实想到数据的导入导出,理所当然的会想到apache的poi技术,以及Excel的版本问题。

这个实现类是我们早期使用最多的对象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后缀还是.xls

这个实现类现在在很多公司都可以发现还在使用,它是操作的Excel2003–Excel2007之间的版本,Excel的扩展名是.xlsx

这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

HSSFWorkbook

它是POI版本中最常用的方式,不过:

XSSFWorkbook

SXSSFWorkbook

从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的SXSSF方式:

优点:

缺点:

2.使用方式哪种看情况

经过了解也知道了这三种Workbook的优点和缺点,那么具体使用哪种方式还是需要看情况的:

我一般会根据这样几种情况做分析选择:

当我们经常导入导出的数据不超过7w的情况下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;

当数据量查过7w并且导出的Excel中不牵扯对Excel的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook;

当数据量查过7w,并且我们需要操做Excel中的表头,样式,公式等,这时候我们可以使用 XSSFWorkbook 配合进行分批查询,分批写入Excel的方式来做;

3.百万数据导入导出

想要解决问题我们首先要明白自己遇到的问题是什么?

我遇到的数据量超级大,使用传统的POI方式来完成导入导出很明显会内存溢出,并且效率会非常低;

数据量大直接使用select * from tableName肯定不行,一下子查出来300w条数据肯定会很慢;

300w 数据导出到Excel时肯定不能都写在一个Sheet中,这样效率会非常低;估计打开都得几分钟;

300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行;

导入时300万数据存储到DB如果循环一条条插入也肯定不行;

导入时300w数据如果使用Mybatis的批量插入肯定不行,因为Mybatis的批量插入其实就是SQL的循环;一样很慢。

解决思路:

其实问题所在就是内存溢出,我们只要使用对上面介绍的POI方式即可,主要问题就是原生的POI解决起来相当麻烦。

经过查阅资料翻看到阿里的一款POI封装工具EasyExcel,上面问题等到解决;

不能一次性查询出全部数据,我们可以分批进行查询,只不过时多查询几次的问题,况且市面上分页插件很多。此问题好解决。

可以将300w条数据写到不同的Sheet中,每一个Sheet写一百万即可。

不能一行一行的写入到Excel上,我们可以将分批查询的数据分批写入到Excel中。

导入到DB时我们可以将Excel中读取的数据存储到集合中,到了一定数量,直接批量插入到DB中。

不能使用Mybatis的批量插入,我们可以使用JDBC的批量插入,配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。

3.1 模拟500w数据导出

需求:使用EasyExcel完成500w数据的导出。

500w数据的导出解决思路:

ps:我们需要计算Sheet个数,以及循环写入次数。特别是最后一个Sheet的写入次数

因为你不知道最后一个Sheet会写入多少数据,可能是100w,也可能是25w因为我们这里的500w只是模拟数据,有可能导出的数据比500w多也可能少

ps:我们需要计算写入次数,因为我们使用的分页查询,所以需要注意写入的次数。

其实查询数据库多少次就是写入多少次

准备工作

基于maven搭建springboot工程,引入easyexcel依赖,这里我是用的时3.0版本

<dependency>   <groupId>com.alibabagroupId>   <artifactId>easyexcelartifactId>   <version>3.0.5version>dependency>

创建海量数据的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)begindeclare 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))begindeclare 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 ((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,8000000)$$#命令结束符,再重新设置为;delimiter ;

实体类

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

vo类

@Datapublic 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;}

导出核心代码

@Resourceprivate 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;}
@Overridepublic void export() throws IOException {    OutputStream outputStream =null;    try {        //记录总数:实际中需要根据查询条件进行统计即可        //LambdaQueryWrapper lambdaQueryWrapper = new QueryWrapper().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("测试Sheet1"+i);            sheet.setSheetNo(i);            //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount            for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {                //分页查询一次20w                Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);                List<Emp> empList = page.getRecords();                List<EmpVo> 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使用情况,当然开了其他一些应用。

image-20230119203725607

导出500w数据共计耗时,可以看到差不多400s左右,当然还要考虑业务复杂度已经电脑配置,我这里只是一个导出的demo并不涉及其他业务逻辑,在实际开发中可能时间会比这个更长一些

image-20230119204722601

看下导出效果,我上面的脚本向插入了500w数据,100w一个sheet因此正好五个

image-20230119205106593

image-20230119204933287

3.2模拟500w数据导入

500W数据的导入解决思路

首先是分批读取读取Excel中的500w数据,这一点EasyExcel有自己的解决方案,我们可以参考Demo即可,只需要把它分批的参数5000调大即可。

其次就是往DB里插入,怎么去插入这20w条数据,当然不能一条一条的循环,应该批量插入这20w条数据,同样也不能使用Mybatis的批量插入语,因为效率也低。

使用JDBC+事务的批量操作将数据插入到数据库。(分批读取+JDBC分批插入+手动事务控制)

代码实现

controller层测试接口

@Resourceprivate EmpService empService;@GetMapping("/importData")public void importData() {    String fileName = "C:\\Users\\asus\\Desktop\\员工信息.xlsx";    //记录开始读取Excel时间,也是导入程序开始时间    long startReadTime = System.currentTimeMillis();    System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");    //读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll();    long endReadTime = System.currentTimeMillis();    System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");    System.out.println("------读取Excel的Sheet时间(包括导入数据)共计耗时:" + (endReadTime-startReadTime) + "ms------");}

Excel导入事件监听

// 事件监听public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {        private EmpService empService;        private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();    public EasyExceGeneralDatalListener() {    }    public EasyExceGeneralDatalListener(EmpService empService) {        this.empService = empService;    }    @Override    public void invoke(Map<Integer, String> data, AnalysisContext context) {        //数据add进入集合        dataList.add(data);        //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {            //存入数据库:数据小于1w条使用Mybatis的批量插入即可;            saveData();            //清理集合便于GC回收            dataList.clear();        }    }        private void saveData() {        empService.importData(dataList);        dataList.clear();    }        @Override    public void doAfterAllAnalysed(AnalysisContext context) {        saveData();        dataList.clear();    }}

核心业务代码

public interface EmpService {    void export() throws IOException;    void importData(List<Map<Integer, String>> dataList);}
        @Override    public void importData(List<Map<Integer, String>> dataList) {        //结果集中数据为0时,结束方法.进行下一次调用        if (dataList.size() == 0) {            return;        }        //JDBC分批插入+事务操作完成对20w数据的插入        Connection conn = null;        PreparedStatement ps = null;        try {            long startTime = System.currentTimeMillis();            System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");            conn = JDBCDruidUtils.getConnection();            //控制事务:默认不提交            conn.setAutoCommit(false);            String sql = "insert into emp (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values";            sql += "(?,?,?,?,?,?,?,?)";            ps = conn.prepareStatement(sql);            //循环结果集:这里循环不支持lambda表达式            for (int i = 0; i < dataList.size(); i++) {                Map<Integer, String> item = dataList.get(i);                ps.setString(1, item.get(0));                ps.setString(2, item.get(1));                ps.setString(3, item.get(2));                ps.setString(4, item.get(3));                ps.setString(5, item.get(4));                ps.setString(6, item.get(5));                ps.setString(7, item.get(6));                ps.setString(8, item.get(7));                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。                ps.addBatch();            }            //执行批处理            ps.executeBatch();            //手动提交事务            conn.commit();            long endTime = System.currentTimeMillis();            System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");            System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");        } catch (Exception e) {            e.printStackTrace();        } finally {            //关连接            JDBCDruidUtils.close(conn, ps);        }    }}

jdbc工具类

//JDBC工具类public class JDBCDruidUtils {    private static DataSource dataSource;        static {        Properties pro = new Properties();        //加载数据库连接池对象        try {            //获取数据库连接池对象            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));            dataSource = DruidDataSourceFactory.createDataSource(pro);        } catch (Exception e) {            e.printStackTrace();        }    }        public static Connection getConnection() throws SQLException {        return dataSource.getConnection();    }        public static void close(Connection connection, Statement statement) {        if (connection != null) {            try {                connection.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (statement != null) {            try {                statement.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }        public static void close(Connection connection, Statement statement, ResultSet resultSet) {        close(connection, statement);        if (resultSet != null) {            try {                resultSet.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }        public static DataSource getDataSource() {        return dataSource;    }}

druid.properties配置文件

这里我将文件创建在类路径下,需要注意的是连接mysql数据库时需要指定rewriteBatchedStatements=true批处理才会生效,否则还是逐条插入效率较低,allowMultiQueries=true表示可以使sql语句中有多个insert或者update语句(语句之间携带分号),这里可以忽略。

# druid.properties配置driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/llp?autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=trueusername=rootpassword=rootinitialSize=10maxActive=50maxWait=60000

测试结果

------开始读取ExcelSheet时间(包括导入数据过程):1674181403555ms------200000,开始导入到数据库时间:1674181409740ms2023-01-20 10:23:29.943  INFO 18580 --- [nio-8888-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited200000,结束导入到数据库时间:1674181413252ms200000,导入用时:3512ms200000,开始导入到数据库时间:1674181418422ms200000,结束导入到数据库时间:1674181420999ms200000,导入用时:2577ms.....200000,开始导入到数据库时间:1674181607405ms200000,结束导入到数据库时间:1674181610154ms200000,导入用时:2749ms------结束读取ExcelSheet时间(包括导入数据过程):1674181610155ms------------读取ExcelSheet时间(包括导入数据)共计耗时:206600ms------

这里我删除里部分日志,从打印结果可以看出,在我的电脑上导入500w数据差不多需要200多秒的时间。当然公司的业务逻辑很复杂,数据量也比较多,表的字段也比较多,导入和导出的速度会比现在测试的要慢一点。

4.总结

如此大批量数据的导出和导入操作,会占用大量的内存实际开发中还应限制操作人数。

在做大批量的数据导入时,可以使用jdbc手动开启事务,批量提交。

来源地址:https://blog.csdn.net/qq_44981526/article/details/128738042

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯