- Excel文件解析工具类:ExcelUtils.java
- 保存数据到数据库的服务类:DataService.java
- 使用线程池处理数据的工具类:ThreadPoolExecutorUtil.java
- 主要的控制器类:FileUploadController.java
注意:请根据你项目中的实际需求和结构调整这些
首先,我们需要添加相关依赖到pom.xml文件中:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
ExcelUtils.java(Excel文件解析工具类)
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
public static List<List<Object>> parseExcel(InputStream inputStream, String fileName) {
Workbook workbook;
try {
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
throw new Exception("Invalid file format");
}
return readWorkbook(workbook);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
private static List<List<Object>> readWorkbook(Workbook workbook) {
List<List<Object>> dataList = new ArrayList<>();
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum() + 1;
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
List<Object> rowData = new ArrayList<>();
for (Cell cell : row) {
cell.setCellType(CellType.STRING);
rowData.add(cell.getStringCellValue());
}
dataList.add(rowData);
}
return dataList;
}
}
DataService.java(保存数据到数据库的服务类)
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DataService {
public void saveDataToDatabase(List<Object> rowData) {
// 将rowData转换为对应的实体类,并保存到数据库中。
// 请根据你项目中的实际需求和结构完成此部分。
}
}
ThreadPoolExecutorUtil.java(使用线程池处理数据的工具类)
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
public class ThreadPoolExecutorUtil {
private static ThreadPoolExecutor threadPoolExecutor;
public static ThreadPoolExecutor getInstance() {
if (threadPoolExecutor == null) {
synchronized (ThreadPoolExecutorUtil.class) {
if (threadPoolExecutor == null) {
threadPoolExecutor = new ThreadPoolExecutor(5, 10, 60, TimeUnit.SECONDS,
new LinkedBlockingQueue<>(1000));
}
}
}
return threadPoolExecutor;
}
}
FileUploadController.java(主要的控制器类)
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.List;
import java.util.concurrent.ThreadPoolExecutor;
@RestController
public class FileUploadController {
@Autowired
private DataService dataService;
@PostMapping("/upload")
public String upload(@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
return "File is empty";
}
ThreadPoolExecutor executor = ThreadPoolExecutorUtil.getInstance();
try {
InputStream inputStream = file.getInputStream();
String fileName = file.getOriginalFilename();
List<List<Object>> dataList = ExcelUtils.parseExcel(inputStream, fileName);
for (List<Object> rowData : dataList) {
executor.execute(() -> dataService.saveDataToDatabase(rowData));
}
return "Data processing in progress";
} catch (Exception e) {
e.printStackTrace();
return "Error occurred";
}
}
}
代码说明:
- 使用Apache POI库解析上传的Excel文件,并将数据存储在
List<List<Object>>
结构中。 - 使用线程池处理解析后的数据,将数据保存到数据库中。
当你完成这些步骤后,启动Spring Boot项目并访问/upload
接口,上传包含10,000条数据的Excel文件。程序会使用线程池并发地处理数据并保存到数据库。