使用Java實現(xiàn)百萬Excel數(shù)據(jù)導(dǎo)出

更新時間：2024年03月07日 09:54:33 作者：夢塵啊

這篇文章主要為大家詳細介紹了如何使用Java實現(xiàn)百萬Excel數(shù)據(jù)導(dǎo)出,文中的示例代碼講解詳細,具有一定的借鑒價值,有需要的小伙伴可以參考一下

可能出現(xiàn)的問題

同步導(dǎo)數(shù)據(jù)，接口很容易超時。
如果把所有數(shù)據(jù)一次性裝載到內(nèi)存，很容易引起OOM。
數(shù)據(jù)量太大sql語句慢。
如果走異步，如何通知用戶導(dǎo)出結(jié)果
如果excel文件太大，目標用戶打不開怎么辦

解決方案

問題一異步化調(diào)用接口立即返回任務(wù)生產(chǎn)成功

問題二分批查詢 poi 禁止使用XSSFWorkbook 使用SXSSFWorkbook 或 easy Excel

問題三分頁通過滾動翻頁查詢

流式查詢問題：容易長時間占用數(shù)據(jù)庫鏈接池資源。

游標查詢問題：應(yīng)用指定每次查詢獲取的條數(shù)fetchSize，MySQL服務(wù)器每次只查詢指定條數(shù)的數(shù)據(jù)，由于MySQL方不知道客戶端什么時候?qū)?shù)據(jù)消費完，MySQL需要建立一個臨時空間來存放每次查詢出的數(shù)據(jù)，大數(shù)據(jù)量時MySQL服務(wù)器、磁盤占用都會飆升。

故使用滾動翻頁查詢

問題四通過頁面或者溝通軟件通知用戶導(dǎo)出成功，并將導(dǎo)出結(jié)果上傳至oss 后續(xù)可直接下載無需重復(fù)導(dǎo)出

問題五導(dǎo)出可用戶設(shè)置最大條數(shù)

數(shù)據(jù)準備

CREATE TABLE `t_order`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵',
    `creator`      VARCHAR(16)     NOT NULL DEFAULT 'admin' COMMENT '創(chuàng)建人',
    `editor`       VARCHAR(16)     NOT NULL DEFAULT 'admin' COMMENT '修改人',
    `create_time`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
    `edit_time`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
    `version`      BIGINT          NOT NULL DEFAULT 1 COMMENT '版本號',
    `deleted`      TINYINT         NOT NULL DEFAULT 0 COMMENT '軟刪除標識',
    `order_id`     VARCHAR(32)     NOT NULL COMMENT '訂單ID',
    `amount`       DECIMAL(10, 2)  NOT NULL DEFAULT 0 COMMENT '訂單金額',
    `payment_time` DATETIME        NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '支付時間',
    `order_status` TINYINT         NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài),0:處理中,1:支付成功,2:支付失敗',
    UNIQUE uniq_order_id (`order_id`),
    INDEX idx_payment_time (`payment_time`)
) COMMENT '訂單表';

public class OrderServiceTest {
    private static final Random OR = new Random();
    private static final Random AR = new Random();
    private static final Random DR = new Random();
    @Test
    public void testGenerateTestOrderSql() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setUsername("root");
        config.setPassword("root");
        config.setJdbcUrl("jdbc:mysql://localhost:3306/local?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false");
        config.setDriverClassName("com.mysql.jdbc.Driver");
        HikariDataSource hikariDataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(hikariDataSource);
        for (int d = 0; d < 100; d++) {
            String item = "('%s','%d','2020-07-%d 00:00:00','%d')";
            StringBuilder sql = new StringBuilder("INSERT INTO t_order(order_id,amount,payment_time,order_status) VALUES ");
            for (int i = 0; i < 20_000; i++) {
                sql.append(String.format(item, UUID.randomUUID().toString().replace("-", ""),
                        AR.nextInt(100000) + 1, DR.nextInt(31) + 1, OR.nextInt(3))).append(",");
            }
            jdbcTemplate.update(sql.substring(0, sql.lastIndexOf(",")));
        }
        hikariDataSource.close();
    }
}

具體實現(xiàn)

easy Excel通過滾動翻頁

Controller

@GetMapping(path = "/export")
public void export(@RequestParam(name = "paymentDateStart") String paymentDateStart,
                   @RequestParam(name = "paymentDateEnd") String paymentDateEnd,
                   ) throws Exception {
    orderService.export(paymentDateStart, paymentDateEnd);
}

Service

@Async
public void export(String paymentDateStart, String paymentDateEnd) throws IOException {
    Date dateBefore = new Date();
    String fileName = URLEncoder.encode(String.format("%s-(%s).xlsx", "訂單數(shù)據(jù)", UUID.randomUUID()),
            StandardCharsets.UTF_8.toString());
    BufferedOutputStream outputStream = FileUtil.getOutputStream(FileUtil.file("/Users/Documents/github/"+fileName));

    ExcelWriter writer = new ExcelWriterBuilder()
            .autoCloseStream(true)
            .excelType(ExcelTypeEnum.XLSX)
            .file(outputStream)
            .head(OrderDTO.class)
            .build();
    WriteSheet writeSheet = new WriteSheet();
    writeSheet.setSheetName("target");
    long lastBatchMaxId = 0L;
    int limit = 3000;
    for (; ; ) {
        List<OrderDTO> list = queryByScrollingPagination(paymentDateTimeStart, paymentDateTimeEnd, lastBatchMaxId, limit);
      //可以添加導(dǎo)出條數(shù)限制
        if (list.isEmpty()) {
            writer.finish();
            Date dateAfter = new Date();
            System.out.println("導(dǎo)出列表共執(zhí)行" + (dateAfter.getTime() - dateBefore.getTime()) + "ms");
            //todo 上傳oss 發(fā)通知
            break;
        } else {
            lastBatchMaxId = list.stream().map(OrderDTO::getId).max(Long::compareTo).orElse(Long.MAX_VALUE);
            writer.write(list, writeSheet);
        }
    }
}

public List<OrderDTO> queryByScrollingPagination(String paymentDateTimeStart,
                                                 String paymentDateTimeEnd,
                                                 long lastBatchMaxId,
                                                 int limit) {
    LocalDateTime start = LocalDateTime.parse(paymentDateTimeStart, formatter);
    LocalDateTime end = LocalDateTime.parse(paymentDateTimeEnd, formatter);
    return orderDao.queryByScrollingPagination(lastBatchMaxId, limit, start, end).stream().map(order -> {
        OrderDTO dto = new OrderDTO();
        dto.setId(order.getId());
        dto.setAmount(order.getAmount());
        dto.setOrderId(order.getOrderId());
        dto.setCreator(order.getCreator());
        return dto;
    }).collect(Collectors.toList());
}

Repository

@Repository
public class OrderDao {

    @Resource
    private  JdbcTemplate jdbcTemplate;

    public List<Order> queryByScrollingPagination(long lastBatchMaxId,
                                                  int limit,
                                                  LocalDateTime paymentDateTimeStart,
                                                  LocalDateTime paymentDateTimeEnd) {
        return jdbcTemplate.query("SELECT id,creator,editor ,version,deleted,order_id,amount,order_status FROM t_order WHERE id > ? AND payment_time >= ? AND payment_time <= ? " +
                        "ORDER BY id ASC LIMIT ?",
                p -> {
                    p.setLong(1, lastBatchMaxId);
                    p.setTimestamp(2, Timestamp.valueOf(paymentDateTimeStart));
                    p.setTimestamp(3, Timestamp.valueOf(paymentDateTimeEnd));
                    p.setInt(4, limit);
                },
                rs -> {
                    List<Order> orders = new ArrayList<>();
                    while (rs.next()) {
                        Order order = new Order();
                        order.setId(rs.getLong("id"));
                        order.setCreator(rs.getString("creator"));
                        order.setEditor(rs.getString("editor"));
                           order.setVersion(rs.getLong("version"));
                        order.setDeleted(rs.getInt("deleted"));
                        order.setOrderId(rs.getString("order_id"));
                        order.setAmount(rs.getBigDecimal("amount"));
                       order.setOrderStatus(rs.getInt("order_status"));
                        orders.add(order);
                    }
                    return orders;
                });
    }
}