import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 订单数据导出工具类
* 支持大数据量分批导出到Excel文件，每个sheet页5万条数据
*/
public class OrderExportUtil {
// 每个sheet页的最大数据量
private static final int SHEET_MAX_SIZE = 50000;
// 线程池核心线程数
private static final int CORE_POOL_SIZE = 4;
// 线程池最大线程数
private static final int MAX_POOL_SIZE = 8;
// 线程池队列大小
private static final int QUEUE_SIZE = 10;
/**
* 导出订单数据到Excel文件
* @param orders 订单数据列表
* @param filePath 导出文件路径
* @throws InterruptedException 线程中断异常
* @throws ExecutionException 执行异常
*/
public static void exportOrders(List<Order> orders, String filePath)
throws InterruptedException, ExecutionException, IOException {
// 创建线程池
ThreadPoolExecutor executor = new ThreadPoolExecutor(
CORE_POOL_SIZE,
MAX_POOL_SIZE,
60L, TimeUnit.SECONDS,
new LinkedBlockingQueue<>(QUEUE_SIZE),
new ThreadFactory() {
private final AtomicInteger threadNumber = new AtomicInteger(1);
@Override
public Thread newThread(Runnable r) {
Thread thread = new Thread(r, "OrderExportThread-" + threadNumber.getAndIncrement());
thread.setDaemon(false);
return thread;
}
},
new ThreadPoolExecutor.CallerRunsPolicy()
);
try {
// 计算需要的sheet数量
int totalRecords = orders.size();
int sheetCount = (int) Math.ceil((double) totalRecords / SHEET_MAX_SIZE);
// 使用线程安全的Excel写入器
ExcelWriter excelWriter = new ExcelWriter(filePath);
// 提交任务到线程池
List<Future<Void>> futures = new CopyOnWriteArrayList<>();
for (int i = 0; i < sheetCount; i++) {
int startIndex = i * SHEET_MAX_SIZE;
int endIndex = Math.min(startIndex + SHEET_MAX_SIZE, totalRecords);
List<Order> subList = orders.subList(startIndex, endIndex);
final int sheetIndex = i;
Future<Void> future = executor.submit(() -> {
try {
excelWriter.writeSheet("订单数据-" + (sheetIndex + 1), subList, sheetIndex);
System.out.println("Sheet页 " + (sheetIndex + 1) + " 写入完成，数据量: " + subList.size());
return null;
} catch (Exception e) {
throw new RuntimeException("写入Sheet页失败: " + e.getMessage(), e);
}
});
futures.add(future);
}
// 等待所有任务完成
for (Future<Void> future : futures) {
future.get();
}
// 完成Excel文件写入
excelWriter.finish();
System.out.println("订单数据导出完成，共导出 " + totalRecords + " 条记录，分 " + sheetCount + " 个Sheet页");
} finally {
// 关闭线程池
executor.shutdown();
try {
if (!executor.awaitTermination(60, TimeUnit.SECONDS)) {
executor.shutdownNow();
}
} catch (InterruptedException e) {
executor.shutdownNow();
Thread.currentThread().interrupt();
}
}
}
}
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
/**
* Excel写入器 - 使用SXSSFWorkbook支持大数据量写入
*/
class ExcelWriter {
private final String filePath;
private final SXSSFWorkbook workbook;
private final Lock lock = new ReentrantLock();
public ExcelWriter(String filePath) {
this.filePath = filePath;
// 创建SXSSFWorkbook，内存中只保留100行数据，避免OOM
this.workbook = new SXSSFWorkbook(100);
}
/**
* 写入单个Sheet页数据
* @param sheetName sheet页名称
* @param orders 订单数据
* @param sheetIndex sheet页索引
*/
public void writeSheet(String sheetName, List<Order> orders, int sheetIndex) {
lock.lock();
try {
Sheet sheet = workbook.createSheet(sheetName);
// 创建表头
createHeader(sheet);
// 写入数据行
int rowNum = 1;
for (Order order : orders) {
Row row = sheet.createRow(rowNum++);
fillRowData(row, order);
}
// 自动调整列宽
for (int i = 0; i < 5; i++) {
sheet.autoSizeColumn(i);
}
} finally {
lock.unlock();
}
}
/**
* 创建表头
* @param sheet sheet页
*/
private void createHeader(Sheet sheet) {
Row headerRow = sheet.createRow(0);
String[] headers = {"订单ID", "客户名称", "订单金额", "下单时间", "订单状态"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
// 设置表头样式
CellStyle headerStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
headerStyle.setFont(font);
cell.setCellStyle(headerStyle);
}
}
/**
* 填充行数据
* @param row 行对象
* @param order 订单对象
*/
private void fillRowData(Row row, Order order) {
row.createCell(0).setCellValue(order.getOrderId());
row.createCell(1).setCellValue(order.getCustomerName());
row.createCell(2).setCellValue(order.getAmount());
row.createCell(3).setCellValue(order.getOrderTime());
row.createCell(4).setCellValue(order.getStatus());
}
/**
* 完成Excel文件写入并关闭资源
* @throws IOException IO异常
*/
public void finish() throws IOException {
try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
workbook.write(outputStream);
} finally {
workbook.dispose(); // 清理临时文件
}
}
}
/**
* 订单实体类示例
*/
class Order {
private Long orderId;
private String customerName;
private Double amount;
private String orderTime;
private String status;
// 构造函数
public Order(Long orderId, String customerName, Double amount, String orderTime, String status) {
this.orderId = orderId;
this.customerName = customerName;
this.amount = amount;
this.orderTime = orderTime;
this.status = status;
}
// Getter方法
public Long getOrderId() { return orderId; }
public String getCustomerName() { return customerName; }
public Double getAmount() { return amount; }
public String getOrderTime() { return orderTime; }
public String getStatus() { return status; }
}
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
/**
* 使用示例
*/
public class OrderExportExample {
public static void main(String[] args) {
try {
// 模拟生成20万订单数据
List<Order> orders = generateMockOrders(200000);
// 导出订单数据
long startTime = System.currentTimeMillis();
OrderExportUtil.exportOrders(orders, "orders_export.xlsx");
long endTime = System.currentTimeMillis();
System.out.println("导出完成，耗时: " + (endTime - startTime) + "ms");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成模拟订单数据
* @param count 订单数量
* @return 订单列表
*/
private static List<Order> generateMockOrders(int count) {
List<Order> orders = new ArrayList<>(count);
String[] statuses = {"已下单", "已付款", "已发货", "已完成", "已取消"};
Random random = new Random();
for (long i = 1; i <= count; i++) {
Order order = new Order(
i,
"客户" + i,
100.0 + random.nextDouble() * 9900.0,
"2023-10-" + String.format("%02d", (i % 30) + 1),
statuses[random.nextInt(statuses.length)]
);
orders.add(order);
}
return orders;
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
