Java中优化大量数据导出到Excel的内存消耗（三）：边读边写

优化大量数据导出到Excel的内存消耗（二）：如果数据超出Excel单表上限，则进行分表_txt导入excel超出最大行如何自动分表-CSDN博客

数据导出进行边读边写excel方式导出

DataSource dataSource = dataSourceService.getByDsName(requestObj.getString("dsName"));
QuerySqlVO querySqlVO = modelNativeService.buildQuerySQL(requestObj,dataSource);
                     
this.queryDataAndWriteExcel(wb,header,querySqlVO,impRelation.getLabel(),currentSheetNum);

/**
 * 查询数据并写入到Excel文件中。
 *
 * @param wb         已经存在的SXSSFWorkbook对象，用于写入数据。
 * @param header     表头信息，键为字段名，值为表头显示的文字。
 * @param querySqlVO 包含查询SQL语句、参数和数据源信息的对象。
 * @param label      用于标识Sheet的标签前缀。
 * @param sheetNum   当前Sheet的编号。
 * @return 写入数据后的SXSSFWorkbook对象。
 * @throws SQLException 如果数据库操作出现异常。
 */
public SXSSFWorkbook queryDataAndWriteExcel(SXSSFWorkbook wb, Map<String, String> header,
                                            QuerySqlVO querySqlVO, String label, int sheetNum) throws SQLException {
    // 创建一个新的Sheet，并设置其名称
    Sheet sheet = wb.createSheet(label + "_" + sheetNum);

    // 初始化行号
    int rowNum = 0;
    // 创建第一行用于写入表头
    Row row = sheet.createRow(rowNum);
    // 初始化列号
    int cellNum = 0;
    // 遍历表头信息并写入第一行
    for (Map.Entry<String, String> entry : header.entrySet()) {
        String fieldDesc = entry.getValue();
        row.createCell(cellNum).setCellValue(fieldDesc);
        cellNum++;
    }

    // 获取查询参数
    Map<String, Object> sqlParams = querySqlVO.getSqlParams();
    // 获取查询SQL语句
    String querySQL = querySqlVO.getQuerySql();
    // 获取数据源
    DataSource dataSource = querySqlVO.getDataSource();

    // 设置数据源名称
    DataSourceDefinition.setName(SunimpConsts.getDsRegisterName(dataSource.getDsName()));

    // 执行计数查询
    Long total = commonService.countLogicSql(querySQL, sqlParams);

    // 如果查询结果不为空且大于0，则继续执行
    if (ObjectKit.isNotEmpty(total) && total > 0) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSetMetaData meta = null;
        try {
            // 日志输出查询SQL语句
            logger.info("readAndWriteToData sql:{}", querySQL);

            // 替换SQL语句中的参数占位符
            for (String key : sqlParams.keySet()) {
                Object value = sqlParams.get(key);
                String fieldKey = "#{" + key + "}";
                if (value instanceof Integer) {
                    querySQL = querySQL.replace(fieldKey, String.valueOf(value));
                } else {
                    querySQL = querySQL.replace(fieldKey, "'" + String.valueOf(value) + "'");
                }
            }

            // 日志输出替换参数后的SQL语句
            logger.info("readAndWriteToData sql1:{}", querySQL);
            // 日志输出查询参数的JSON形式
            logger.info("JOSN sql:{}", JSON.toJSONString(sqlParams));

            // 获取数据源信息
            DataSourceInfo sourceInfo = DataSourceDefinition.getDataSourceInfo();
            // 建立数据库连接
            conn = DataSourceDefinition.getDataSource().getConnection();
            // 准备执行SQL语句
            ps = conn.prepareStatement(querySQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            // 设置每次获取的数据量
            int fetchSize = 2000;
            if ("POSTGRESQL".equalsIgnoreCase(sourceInfo.getDsType())) {
                conn.setAutoCommit(false);
                ps.setFetchSize(fetchSize);
            } else if ("MYSQL".equalsIgnoreCase(sourceInfo.getDsType())) {
                ps.setFetchSize(Integer.MIN_VALUE);
                ps.setFetchDirection(ResultSet.FETCH_REVERSE);
            } else {
                ps.setFetchSize(fetchSize);
            }

            // 执行查询
            rs = ps.executeQuery();
            // 获取元数据
            meta = rs.getMetaData();
            // 存储查询结果
            List<Map<String, Object>> data = new ArrayList<>();

            // 初始化行号
            int num = 0;
            // 循环读取查询结果
            while (rs.next()) {
                // 如果行数超过100万，则创建新的Sheet
                if (rowNum >= 1000000) {
                    sheetNum++;
                    sheet = wb.createSheet(label + "_" + sheetNum);

                    // 重置行号
                    rowNum = 0;
                    // 创建新Sheet的第一行
                    row = sheet.createRow(rowNum);

                    // 重置列号
                    cellNum = 0;
                    // 再次写入表头
                    for (Map.Entry<String, String> entry : header.entrySet()) {
                        String fieldDesc = entry.getValue();
                        row.createCell(cellNum).setCellValue(fieldDesc);
                        cellNum++;
                    }
                }
                // 增加行号
                rowNum++;
                // 创建新行
                row = sheet.createRow(rowNum);

                // 重置列号
                cellNum = 0;

                // 获取列数
                int length = meta.getColumnCount();
                // 创建一行数据的Map
                Map<String, Object> rowMap = new LinkedHashMap<>();
                // 遍历每一列
                for (int i = 1; i <= length; i++) {
                    String columnName = meta.getColumnName(i).toUpperCase();
                    rowMap.put(columnName, rs.getObject(i));
                }

                // 遍历表头信息，写入数据
                for (Map.Entry<String, String> entry : header.entrySet()) {
                    String fieldName = entry.getKey();
                    Object fieldValue = rowMap.get(fieldName.toLowerCase());
                    // 结果表在不同的数据库时，字段有可能为大写
                    if (ObjectKit.isEmpty(fieldValue)) {
                        fieldValue = rowMap.get(fieldName.toUpperCase());
                    }
                    row.createCell(cellNum).setCellValue(null == fieldValue ? "" : fieldValue.toString());
                    cellNum++;
                }

                // 每处理10000条记录输出一次日志
                num++;
                if (num % 10000 == 0) {
                    logger.info("conn meta meta ------------write-----:{}", num);
                }
            }
            // 处理完毕后输出日志
            logger.info("conn meta meta ------------end-----:{}", num);
        } catch (Exception e) {
            throw e;
        } finally {
            // 关闭资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    logger.warn("close Connection error", e);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    logger.warn("close Statement error", e);
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    logger.warn("close ResultSet error", e);
                }
            }
            // 清空数据源名称
            DataSourceDefinition.setName(null);
        }
    }
    // 返回写入数据后的SXSSFWorkbook对象
    return wb;
}