项目实战--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 {@Autowiredprivate DataRepository dataRepository;public List<Data> fetchData(int offset, int limit) {return dataRepository.findAll(PageRequest.of(offset, limit)).getContent();}
}

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

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

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

@Configuration
@EnableAsync
public class AsyncConfig implements AsyncConfigurer {@Overridepublic 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 {@Autowiredprivate DataService dataService;@Asyncpublic 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();}
}