原文：blog.csdn.net/weixin_44848900/article/details/117701981

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

写本文的背景是因为在工作中遇到了大数据的导入和导出，问题既然来了逃跑不如干掉它！！！

只要这一次解决了，后期遇到同样的问题就好解决了。

废话不多说，开始撸起来！！！

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

既然要做导入导出，那么我们就先来大致看一下传统poi技术的版本以及优缺点对比吧！

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

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

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

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

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

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

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

优点：

缺点：

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

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

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

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

3、当数据量查过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的循环；一样很慢。

解决思路：

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

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

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

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

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

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

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

附上GitHub地址：https://github.com/alibaba/easyexcel

GitHub地址上教程和说明很详细，并且附带有读和写的demo代码，这里对它的介绍我就不再详细说了。

至于EasyExcel底层怎么实现的这个还有待研究。

EasyExcel完成300w数据的导出。技术难点已经知道了，接下来就是针对这一难点提供自己的解决思路即可。

300w数据的导出解决思路：

注意：

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

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

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

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

            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 titles = new ArrayList();            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 ==  ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含后一个sheet,因为后一个sheet不确定会写入多少条数据)            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;            //计算后一个sheet需要写入的次数            Integer lastSheetWriteCount = totalCount % sheetDataRows ==  ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows ==  ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            //开始分批查询分次写入            //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数            List dataList = new ArrayList();            for (int i = ; i -- 建表语句：可以参考一下-- Create tablecreate table ACT_RESULT_LOG(  onlineseqid VARCHAR2(32),  businessid  VARCHAR2(32),  becifno     VARCHAR2(32),  ivisresult  VARCHAR2(32),  createdby   VARCHAR2(32),  createddate DATE,  updateby    VARCHAR2(32),  updateddate DATE,  risklevel   VARCHAR2(32))tablespace STUDY_KAY  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  ); 3.2.3 测试结果

下面是300w数据从DB导出到Excel所用时间

从上面结果可以看出，300w的数据导出时间用时2分15秒，并且这是在不适用实体作为映射的情况下，如果使用实体映射不适用循环封装的话速度会更快(当然这也是在没有设置表头等其他表格样式的情况下)

综合来说速度还算可以。

在网上查了很多资料有一个博主测试使用EasyExcel导出102w数据用时105秒，具体可以看一下链接：

https://blog.csdn.net/u014299266/article/details/107790561

看一下导出效果：文件还是挺大的163M

经过测试EasyExcel还是挺快的，并且使用起来相当方便，作者还专门提供了关流方法，不需要我们手动去关流了，也避免了我们经常忘记关流导致的一系列问题。

导出测试就到这里，对于数据量小于300W的数据可以使用在一个Sheet中进行导出。这里就不再演示。

代码不重要首先还是思路

300W数据的导入解决思路

1、首先是分批读取读取Excel中的300w数据，这一点EasyExcel有自己的解决方案，我们可以参考Demo即可，只需要把它分批的参数3000调大即可。我是用的20w；（一会儿代码一看就能明白）

2、其次就是往DB里插入，怎么去插入这20w条数据，当然不能一条一条的循环，应该批量插入这20w条数据，同样也不能使用Mybatis的批量插入语，因为效率也低。可以参考下面链接【Myabtis批量插入和JDBC批量插入性能对比】

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

https://www.cnblogs.com/wxw7blog/p/8706797.html

如图

    /**     * 用于存储读取的数据     */    private List dataList = new ArrayList();

    public EasyExceGeneralDatalListener() {    }

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

    @Override    public void invoke(Map 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 */        @Override        public Map import2DBFromExcel10w(List dataList) {        HashMap result = new HashMap();        //结果集中数据为时,结束方法.进行下一次调用        if (dataList.size() == ) {            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 = ; i