项目实战--Spring Boot大数据量报表Excel优化

一、项目场景

项目中要实现交易报表，处理大规模数据导出时，出现单个Excel文件过大导致性能下降的问题，需求是导出大概四千万条数据到Excel文件，不影响正式环境的其他查询。

二、方案

1.使用读写分离，查询操作由从库处理
2.数据分批查询
3.异步导出数据
4.生成和拆分多个Excel文件

三、实现

1.pom.xml中添加以下依赖：


<dependencies>
    <!-- Spring Boot Starter Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- Spring Boot Starter Async -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Apache POI for Excel -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
    </dependency>
</dependencies>

包括SpringBoot、Spring Data JPA、异步处理相关的依赖，以及用于生成Excel文件的Apache POI库。

2.application.properties中加入数据库配置，以及异步任务执行器的配置：

# Database configuration
spring.datasource.url=jdbc:mysql://localhost:3306/yourdatabase
spring.datasource.username=yourusername
spring.datasource.password=yourpassword
# Async configuration
spring.task.execution.pool.core-size=10
spring.task.execution.pool.max-size=20
spring.task.execution.pool.queue-capacity=500
spring.task.execution.thread-name-prefix=Async-thread

3.使用从库进行查询
减轻主库的查询压力，建议在架构上使用读写分离，查询操作由从库处理。这样可以确保主库的操作性能和其他接口查询不受影响。

@Service
public class DataService {
    @Autowired
    private DataRepository dataRepository;
    public List<Data> fetchData(int offset, int limit) {
        return dataRepository.findAll(PageRequest.of(offset, limit)).getContent();
    }
}

4.数据分批查询策略
防止一次性查询大量数据导致内存溢出，采用分页查询的方式，每次查询部分数据进行处理。

@Service
public class DataExportService {
    @Autowired
    private DataService dataService;
    @Async
    public void exportData() {
        int pageSize = 10000;
        int pageNumber = 0;
        List<Data> dataBatch;
        do {
            dataBatch = dataService.fetchData(pageNumber, pageSize);
            if (!dataBatch.isEmpty()) {
                // 导出数据到Excel
                exportToExcel(dataBatch, pageNumber);
            }
            pageNumber++;
        } while (!dataBatch.isEmpty());
    }
}

5.异步任务配置
通过@EnableAsync注解启用异步任务，并配置一个任务执行线程来单独执行导出任务。

@Configuration
@EnableAsync
public class AsyncConfig implements AsyncConfigurer {
    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(10);
        executor.setMaxPoolSize(20);
        executor.setQueueCapacity(500);
        executor.setThreadNamePrefix("Async-");
        executor.initialize();
        return executor;
    }
}

6.导出任务接口实现
使用@Async注解将导出任务的方法标记为异步执行。

@Service
public class DataExportService {
    @Autowired
    private DataService dataService;
    @Async
    public void exportData() {
        // 数据查询和导出的逻辑
    }
}

7.生成和拆分Excel文件
使用Apache POI处理Excel，查询到的数据批次，将数据分成多个Excel文件，避免单个文件过大。

public void exportToExcel(List<Data> dataBatch, int batchNumber) {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Data");
    int rowNum = 0;
    for (Data data : dataBatch) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(data.getId());
        row.createCell(1).setCellValue(data.getName());
        // 其他数据列
    }
    try (FileOutputStream fos = new FileOutputStream("data_batch_" + batchNumber + ".xlsx")) {
        workbook.write(fos);
    } catch (IOException e) {
        e.printStackTrace();
    }
}