分批将数据导入Excel，模拟数据

1、pom.xml

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.0.0</version>
    </dependency>

　　

2、BatchExcelExport类

package com.feng.controller.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class BatchExcelExport {

    public static void main(String[] args) {
        // 生成测试数据，共10000行
        List<String[]> data = generateTestData(10000);
        String filePath = "D:\\feng\\export\\data_batch.xlsx";
        int batchSize = 1000; // 每批次写入1000行

        exportExcel(data, filePath, batchSize);
    }

    public static void exportExcel(List<String[]> data, String filePath, int batchSize) {
        int totalRows = data.size();
        int totalBatches = (int) Math.ceil((double) totalRows / batchSize);

        for (int batchNumber = 0; batchNumber < totalBatches; batchNumber++) {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");

            int startRow = batchNumber * batchSize;
            int endRow = Math.min(startRow + batchSize, totalRows);

            for (int i = startRow; i < endRow; i++) {
                Row row = sheet.createRow(i - startRow);
                String[] rowData = data.get(i);
                for (int j = 0; j < rowData.length; j++) {
                    row.createCell(j).setCellValue(rowData[j]);
                }
            }

            // 将工作簿写入文件输出流
            try (FileOutputStream fileOut = new FileOutputStream(filePath.replace(".xlsx", "_" + (batchNumber + 1) + ".xlsx"))) {
                workbook.write(fileOut);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    public static List<String[]> generateTestData(int rows) {
        List<String[]> data = new ArrayList<>();
        for (int i = 0; i < rows; i++) {
            data.add(new String[]{"Name" + i, "Age" + i, "City" + i});
        }
        return data;
    }
}