//導(dǎo)出邏輯代碼
public void dataExport300w(HttpServletResponse response) {
    {
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println("導(dǎo)出開(kāi)始時(shí)間:" + 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);

            //模擬統(tǒng)計(jì)查詢(xún)的數(shù)據(jù)數(shù)量這里模擬100w
            int count = 3000001;
            //記錄總數(shù):實(shí)際中需要根據(jù)查詢(xún)條件進(jìn)行統(tǒng)計(jì)即可
            Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
            //每一個(gè)Sheet存放100w條數(shù)據(jù)
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次寫(xiě)入的數(shù)據(jù)量20w
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //計(jì)算需要的Sheet數(shù)量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //計(jì)算一般情況下每一個(gè)Sheet需要寫(xiě)入的次數(shù)(一般情況不包含最后一個(gè)sheet,因?yàn)樽詈笠粋€(gè)sheet不確定會(huì)寫(xiě)入多少條數(shù)據(jù))
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //計(jì)算最后一個(gè)sheet需要寫(xiě)入的次數(shù)
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            //開(kāi)始分批查詢(xún)分次寫(xiě)入
            //注意這次的循環(huán)就需要進(jìn)行嵌套循環(huán)了,外層循環(huán)是Sheet數(shù)目,內(nèi)層循環(huán)是寫(xiě)入次數(shù)
            List<List<String>> dataList = new ArrayList<>();
            for (int i = 0; i < sheetNum; i++) {
                //創(chuàng)建Sheet
                Sheet sheet = new Sheet(i, 0);
                sheet.setSheetName("測(cè)試Sheet1" + i);
                //循環(huán)寫(xiě)入次數(shù): j的自增條件是當(dāng)不是最后一個(gè)Sheet的時(shí)候?qū)懭氪螖?shù)為正常的每個(gè)Sheet寫(xiě)入的次數(shù),如果是最后一個(gè)就需要使用計(jì)算的次數(shù)lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //集合復(fù)用,便于GC清理
                    dataList.clear();
                    //分頁(yè)查詢(xún)一次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()));
                        });
                    }
                    //寫(xiě)數(shù)據(jù)
                    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();
            //導(dǎo)出時(shí)間結(jié)束
            long endTime = System.currentTimeMillis();
            System.out.println("導(dǎo)出結(jié)束時(shí)間:" + endTime + "ms");
            System.out.println("導(dǎo)出所用時(shí)間:" + (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();
                }
            }
        }
    }
}

<select id="findByPage300w" resultType="show.mrkay.pojo.ActResultLog">
    select *
    from ACT_RESULT_LOG
    where rownum <![CDATA[<]]> 3000001
</select>
-- 建表語(yǔ)句：可以參考一下
-- Create table
create 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
  );

// EasyExcel的讀取Excel數(shù)據(jù)的API
@Test
public void import2DBFromExcel10wTest() {
    String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.xlsx";
    //記錄開(kāi)始讀取Excel時(shí)間,也是導(dǎo)入程序開(kāi)始時(shí)間
    long startReadTime = System.currentTimeMillis();
    System.out.println("------開(kāi)始讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):" + startReadTime + "ms------");
    //讀取所有Sheet的數(shù)據(jù).每次讀完一個(gè)Sheet就會(huì)調(diào)用這個(gè)方法
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(actResultLogService2)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):" + endReadTime + "ms------");
}
// 事件監(jiān)聽(tīng)
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 處理業(yè)務(wù)邏輯的Service,也可以是Mapper
     */
    private ActResultLogService2 actResultLogService2;

    /**
     * 用于存儲(chǔ)讀取的數(shù)據(jù)
     */
    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) {
        //數(shù)據(jù)add進(jìn)入集合
        dataList.add(data);
        //size是否為100000條:這里其實(shí)就是分批.當(dāng)數(shù)據(jù)等于10w的時(shí)候執(zhí)行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            //存入數(shù)據(jù)庫(kù):數(shù)據(jù)小于1w條使用Mybatis的批量插入即可;
            saveData();
            //清理集合便于GC回收
            dataList.clear();
        }
    }

    /**
     * 保存數(shù)據(jù)到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        actResultLogService2.import2DBFromExcel10w(dataList);
        dataList.clear();
    }
    
    /**
     * Excel中所有數(shù)據(jù)解析完畢會(huì)調(diào)用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}
//JDBC工具類(lèi)
public class JDBCDruidUtils {
    private static DataSource dataSource;

    /*
   創(chuàng)建數(shù)據(jù)Properties集合對(duì)象加載加載配置文件
    */
    static {
        Properties pro = new Properties();
        //加載數(shù)據(jù)庫(kù)連接池對(duì)象
        try {
            //獲取數(shù)據(jù)庫(kù)連接池對(duì)象
            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();
    }


    /**
     * 關(guān)閉conn,和 statement獨(dú)對(duì)象資源
     *
     * @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();
            }
        }
    }

    /**
     * 關(guān)閉 conn , statement 和resultset三個(gè)對(duì)象資源
     *
     * @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();
            }
        }
    }

    /*
    獲取連接池對(duì)象
     */
    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中具體業(yè)務(wù)邏輯

/**
 * 測(cè)試用Excel導(dǎo)入超過(guò)10w條數(shù)據(jù),經(jīng)過(guò)測(cè)試發(fā)現(xiàn),使用Mybatis的批量插入速度非常慢,所以這里可以使用 數(shù)據(jù)分批+JDBC分批插入+事務(wù)來(lái)繼續(xù)插入速度會(huì)非?？? *
 * @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<>();
    //結(jié)果集中數(shù)據(jù)為0時(shí),結(jié)束方法.進(jìn)行下一次調(diào)用
    if (dataList.size() == 0) {
        result.put("empty", "0000");
        return result;
    }
    //JDBC分批插入+事務(wù)操作完成對(duì)10w數(shù)據(jù)的插入
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long startTime = System.currentTimeMillis();
        System.out.println(dataList.size() + "條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:" + startTime + "ms");
        conn = JDBCDruidUtils.getConnection();
        //控制事務(wù):默認(rèn)不提交
        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);
        //循環(huán)結(jié)果集:這里循環(huán)不支持"爛布袋"表達(dá)式
        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));
            //將一組參數(shù)添加到此 PreparedStatement 對(duì)象的批處理命令中。
            ps.addBatch();
        }
        //執(zhí)行批處理
        ps.executeBatch();
        //手動(dòng)提交事務(wù)
        conn.commit();
        long endTime = System.currentTimeMillis();
        System.out.println(dataList.size() + "條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:" + endTime + "ms");
        System.out.println(dataList.size() + "條,導(dǎo)入用時(shí):" + (endTime - startTime) + "ms");
        result.put("success", "1111");
    } catch (Exception e) {
        result.put("exception", "0000");
        e.printStackTrace();
    } finally {
        //關(guān)連接
        JDBCDruidUtils.close(conn, ps);
    }
    return result;
}

------開(kāi)始讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1623127873630ms------
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127880632ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127881513ms
200000條,導(dǎo)入用時(shí):881ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127886945ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127887429ms
200000條,導(dǎo)入用時(shí):484ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127892894ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127893397ms
200000條,導(dǎo)入用時(shí):503ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127898607ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127899066ms
200000條,導(dǎo)入用時(shí):459ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127904379ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127904855ms
200000條,導(dǎo)入用時(shí):476ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127910495ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127910939ms
200000條,導(dǎo)入用時(shí):444ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127916271ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127916744ms
200000條,導(dǎo)入用時(shí):473ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127922465ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127922947ms
200000條,導(dǎo)入用時(shí):482ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127928260ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127928727ms
200000條,導(dǎo)入用時(shí):467ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127934374ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127934891ms
200000條,導(dǎo)入用時(shí):517ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127940189ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127940677ms
200000條,導(dǎo)入用時(shí):488ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127946402ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127946925ms
200000條,導(dǎo)入用時(shí):523ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127952158ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127952639ms
200000條,導(dǎo)入用時(shí):481ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127957880ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127958925ms
200000條,導(dǎo)入用時(shí):1045ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127964239ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127964725ms
200000條,導(dǎo)入用時(shí):486ms
------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1623127964725ms------