百万级数据 Excel 导入导出解决方案

前言:众所周知,大部分web端管理系统,导入导出是占比很大的一个功能。不过一般都是几万或者十几万条,到百万级甚少。

传统 POI 的版本优缺点比较

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

首先我们知道 POI 中我们最熟悉的莫过于 WorkBook 这样一个接口,我们的 POI 版本也在更新的同时对这个几口的实现类做了更新:

  • HSSFWorkbook :

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

  • XSSFWorkbook :

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

  • SXSSFWorkbook :

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

大致知道了我们在导入导出操作的时候会用到这样三个实现类以及他们可以操作的 Excel 版本和后缀之后,我们就要从优缺点分析他们了

HSSFWorkbook

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

  • 它的缺点是最多只能导出 65535 行,也就是导出的数据函数超过这个数据就会报错;

  • 它的优点是不会报内存溢出。(因为数据量还不到 7w 所以内存一般都够用,首先你得明确知道这种方式是将数据先读取到内存中,然后再操作)

XSSFWorkbook
  • 优点:这种形式的出现是为了突破HSSFWorkbook的 65535 行局限,是为了针对 Excel2007 版本的 1048576 行,16384 列,最多可以导出 104w 条数据;

  • 缺点:伴随的问题来了,虽然导出数据行数增加了好多倍,但是随之而来的内存溢出问题也成了噩梦。因为你所创建的 book,Sheet,row,cell 等在写入到 Excel 之前,都是存放在内存中的(这还没有算 Excel 的一些样式格式等等),可想而知,内存不溢出就有点不科学了!!!

SXSSFWorkbook

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

优点:

  • 这种方式不会一般不会出现内存溢出(它使用了硬盘来换取内存空间),

  • 也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来,而内存中存的都是最新的数据),

  • 并且支持大型 Excel 文件的创建(存储百万条数据绰绰有余)。

缺点:

  • 既然一部分数据持久化到了硬盘中,且不能被查看和访问那么就会导致,在同一时间点我们只能访问一定数量的数据,也就是内存中存储的数据;

  • sheet.clone()方法将不再支持,还是因为持久化的原因;

  • 不再支持对公式的求值,还是因为持久化的原因,在硬盘中的数据没法读取到内存中进行计算;

  • 在使用模板方式下载数据的时候,不能改动表头,还是因为持久化的问题,写到了硬盘里就不能改变了;

使用方式哪种看情况

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

  • 当我们经常导入导出的数据不超过 7w 的情况下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;
  • 当数据量超过 7w 且导出的 Excel 中不牵扯对 Excel 的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook;
  • 当数据量超过 7w且我们需要操作 Excel 中的表头,样式,公式等,这时候我们可以使用 XSSFWorkbook 配合进行分批查询,分批写入 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 :

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

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

针对 2:

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

针对 3:

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

针对 4:

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

针对 5:

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

针对 6:

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

实操:300w 数据导出

EasyExcel 完成 300w 数据的导出。

300w 数据的导出解决思路:
  • 首先在查询数据库层面,需要分批进行查询(我每次查询 20w)

  • 每查询一次结束,就使用 EasyExcel 工具将这些数据写入一次;

  • 当一个 Sheet 写满了 100w 条数据,开始将查询的数据写入到另一个 Sheet 中;

  • 如此循环直到数据全部导出到 Excel 完毕。

注意:

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

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

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

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

//导出逻辑代码
public void dataExport300w(HttpServletResponse response) {
    {
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println("导出开始时间:" + startTime);

            outputStream = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
            String fileName = new String(("excel100w").getBytes(), "UTF-8");

            //title
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("onlineseqid"));
            titles.add(Arrays.asList("businessid"));
            titles.add(Arrays.asList("becifno"));
            titles.add(Arrays.asList("ivisresult"));
            titles.add(Arrays.asList("createdby"));
            titles.add(Arrays.asList("createddate"));
            titles.add(Arrays.asList("updateby"));
            titles.add(Arrays.asList("updateddate"));
            titles.add(Arrays.asList("risklevel"));
            table.setHead(titles);

            //模拟统计查询的数据数量这里模拟100w
            int count = 3000001;
            //记录总数:实际中需要根据查询条件进行统计即可
            Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量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));

            //开始分批查询分次写入
            //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
            List<List<String>> dataList = new ArrayList<>();
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                Sheet sheet = new Sheet(i, 0);
                sheet.setSheetName("测试Sheet1" + i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //集合复用,便于GC清理
                    dataList.clear();
                    //分页查询一次20w
                    PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                    List<ActResultLog> reslultList = actResultLogMapper.findByPage100w();
                    if (!CollectionUtils.isEmpty(reslultList)) {
                        reslultList.forEach(item -> {
                            dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(), item.getIvisresult(), item.getCreatedby(), Calendar.getInstance().getTime().toString(), item.getUpdateby(), Calendar.getInstance().getTime().toString(), item.getRisklevel()));
                        });
                    }
                    //写数据
                    writer.write0(dataList, sheet, table);
                }
            }

            // 下载EXCEL
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.finish();
            outputStream.flush();
            //导出时间结束
            long endTime = System.currentTimeMillis();
            System.out.println("导出结束时间:" + endTime + "ms");
            System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

实操:300w 数据导入
300W 数据的导入解决思路
  1. 首先是分批读取读取 Excel 中的 300w 数据(我是用的 20w)
  2. 其次就是往 DB 里插入,怎么去插入这 20w 条数据,当然不能一条一条的循环,应该批量插入这 20w 条数据,同样也不能使用 Mybatis 的批量插入语,因为效率也低
  3. 使用 JDBC+事务的批量操作将数据插入DB。(分批读取+JDBC 分批插入+手动事务控制)

// EasyExcel的读取Excel数据的API
@Test
public void import2DBFromExcel10wTest() {
    String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.xlsx";
    //记录开始读取Excel时间,也是导入程序开始时间
    long startReadTime = System.currentTimeMillis();
    System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
    //读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(actResultLogService2)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");
}
// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 处理业务逻辑的Service,也可以是Mapper
     */
    private ActResultLogService2 actResultLogService2;

    /**
     * 用于存储读取的数据
     */
    private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();

    public EasyExceGeneralDatalListener() {
    }

    public EasyExceGeneralDatalListener(ActResultLogService2 actResultLogService2) {
        this.actResultLogService2 = actResultLogService2;
    }

    @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();
        }
    }

    /**
     * 保存数据到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        actResultLogService2.import2DBFromExcel10w(dataList);
        dataList.clear();
    }

    /**
     * Excel中所有数据解析完毕会调用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}
//JDBC工具类
public class JDBCDruidUtils {
    private static DataSource dataSource;

    /*
   创建数据Properties集合对象加载加载配置文件
    */
    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();
    }


    /**
     * 关闭conn,和 statement独对象资源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    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();
            }
        }
    }

    /**
     * 关闭 conn , statement 和resultset三个对象资源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    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配置
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORCL
username=mrkay
password=******
initialSize=10
maxActive=50
maxWait=60000
// Service中具体业务逻辑

/**
 * 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
 *
 * @param
 * @MethodName: import2DBFromExcel10w
 * @return: java.util.Map<java.lang.String, java.lang.Object>
 */
@Override
public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
    HashMap<String, Object> result = new HashMap<>();
    //结果集中数据为0时,结束方法.进行下一次调用
    if (dataList.size() == 0) {
        result.put("empty", "0000");
        return result;
    }
    //JDBC分批插入+事务操作完成对10w数据的插入
    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 ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values";
        sql += "(?,?,?,?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        //循环结果集:这里循环不支持"烂布袋"表达式
        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.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            ps.setString(7, item.get(6));
            ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
            ps.setString(9, item.get(8));
            //将一组参数添加到此 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");
        result.put("success", "1111");
    } catch (Exception e) {
        result.put("exception", "0000");
        e.printStackTrace();
    } finally {
        //关连接
        JDBCDruidUtils.close(conn, ps);
    }
    return result; 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值