终于忙完有时间写Java实现多线程导入Excel文件，本次实现导入10368数据，第二天做了单线程导入和多线程导入花费时间的比较，直接上代码\r\n
UserImportController
@RestController
@CrossOrigin
@Slf4j
@RequestMapping("/user")
public class UserImportController {
@Autowired
UserService userService;
/**
* 多线程导入
* @param file
* @return
*/
@PostMapping("/importManyDataThread")
public Map importManyDataThread(@RequestPart(value = "file") MultipartFile file){
//多线程方式导入10368条数据花费时间2分15秒
Map<String, Object> map = null;
try {
map = userService.importManyThreadUser(file);
} catch (Exception e) {
e.printStackTrace();
log.warn("UserImportController--> importManyDataThread() - ", e);
map.put("code",500);
map.put("msg","数据出错");
}
return map;
}
}UserServiceImpl
@Slf4j
@Service("userService")
public class UserServiceImpl implements UserService {
@Transactional(propagation = Propagation.REQUIRED,
timeout = 30,isolation = Isolation.READ_COMMITTED)
@Override
public Integer savetUser(User user) {
Integer count = 0;
System.out.println("插进来了吗?" );
User checkUser = userMapper.selectUserById(user.getId());
if (checkUser == null){
int i = userMapper.insertUser(user);
if (i > 0) {
count++;
}
}else {
int i = userMapper.updateUser(user);
if (i > 0) {
count++;
}
}
return count;
}
@Override
public User selectUserById(int id) {
return userMapper.selectUserById(id);
}
@Override
public Map<String, Object> importManyThreadUser(MultipartFile file)
throws Exception {
final Date now = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
log.info("{},开始导入数据...", format.format(new Date()));
//设置一个信号量为10的信号量，限制同时运行的线程数量最大为10
Semaphore semaphore = new Semaphore(10);
Map<String,Object> map = new HashMap<>();
//多线程编程需要一个线程安全的ArrayList
List<User> list = Collections.synchronizedList(new ArrayList<>());
Workbook workbook = null;
String filename = file.getOriginalFilename();
if(filename.endsWith("xls")){
workbook = new HSSFWorkbook(file.getInputStream());
}else if(filename.endsWith("xlsx")){
//workbook = new XSSFWorkbook(file.getInputStream());
workbook = WorkbookFactory.create(file.getInputStream());
}else {
User u= new User();
u.setError("格式不对");
list.add(u);
map.put("code",500);
map.put("data",list);
return map;
}
Sheet sheet = workbook.getSheetAt(0);
if(sheet.getRow(1) == null){
map.put("code",500);
map.put("msg","excel第2行不能为空" );
//map.put("data",list);
return map;
}
int allNumberOfRows = sheet.getPhysicalNumberOfRows();
log.info("获取到workbook中的总行数:{}" ,allNumberOfRows);
int rows = allNumberOfRows - 1;
int threadNum = rows / 500 + 1;
CountDownLatch countDownLatch = new CountDownLatch(threadNum);
ExecutorService executor = Executors.newFixedThreadPool(threadNum);
log.info("开始创建线程,数据总行数:{},线程数量:{}",rows,threadNum);
List<Future<Integer>> futures = new ArrayList<>();
int successCount = 0;
for(int i = 1; i <= threadNum; i++){
int startRow = (i - 1) * 200 +1;
int endRow = i * 200;
if(i == threadNum){
endRow = rows;
}
log.info("开始执行线程方法,线程ID:<{}>,线程名称:<{}>",Thread.currentThread().getId(),Thread.currentThread().getName());
Future<Integer> future = executor.submit(
new ImportTask(semaphore,workbook, startRow, endRow,this,countDownLatch));
futures.add(future);
log.info("结束线程执行方法,返回结果:<{}>,当前线程ID:<{}>,当前线程名称:<{}>", JSON.toJSONString(future),Thread.currentThread().getId(),Thread.currentThread().getName());
}
for(Future<Integer> future : futures){
successCount += future.get();
}
countDownLatch.await(60,TimeUnit.SECONDS);
executor.shutdown();
/**以下看多线程导入1W多数据花的时间*/
Date endDate = new Date();
long difference = endDate.getTime() - now.getTime();
String duration = DurationFormatUtils.formatDuration(difference, "HH:mm:ss");
log.info("执行完成,错误信息:{}", JSON.toJSONString(list));
log.info("{},结束导入,共{}条数据，导入成功:{}，耗时={}", format.format(endDate), rows,successCount,duration);
map.put("code",200);
map.put("msg","结束导入,共" + rows + "条数据，导入成功" + successCount + "条，耗时:" +duration);
map.put("data",list);
return map;
}
}
ImportTask
/**
* 导入excel线程类
* @author fan
* @date 2022/4/18 18:39
*/
@Slf4j
public class ImportTask implements Callable<Integer>{
private Workbook workbook;
private Integer startRow;
private Integer endRow;
private UserService userService;
private Semaphore semaphore;
private CountDownLatch latch;
public ImportTask(Semaphore semaphore,
Workbook workbook,
Integer startRow,
Integer endRow,
UserService userService,
CountDownLatch latch){
this.workbook = workbook;
this.startRow = startRow;
this.endRow = endRow;
this.userService = userService;
this.semaphore = semaphore;
this.latch = latch;
}
@Override
public Integer call() throws Exception {
log.info("线程ID：<{}>开始运行,startRow:{},endRow:{}",Thread.currentThread().getId(),startRow,endRow);
semaphore.acquire();
log.info("消耗了一个信号量，剩余信号量为:{}",semaphore.availablePermits());
latch.countDown();
Sheet sheet = workbook.getSheetAt(0);
Integer count = 0;
int lastCell = sheet.getRow(1).getLastCellNum();
for(int i = startRow; i <= endRow; i++){
User voUser = new User();
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
for (int key = 0; key < lastCell; key++) {
Cell cell = row.getCell(key);
String value = ExcelFileUtils.getCellValueText(cell);
if (value != null) {
value = value.trim();
}
if (key == 0) {
voUser.setName(value);
}
if (key == 1) {
voUser.setAge(Integer.parseInt(value));
}
if (key == 2) {
voUser.setSex(value);
}
if (key == 3) {
if (value != null && !value.equals("")){
Date dateTime = DateUtils.covertDateStringToDate(value);
voUser.setDataTime(dateTime);
}
}
}
count += userService.savetUser(voUser);
}
semaphore.release();
return count;
}
}
DateUtils工具类：
/**
* 时间转换工具类
* @author fan
* @date 2022/4/18 19:33
*/
public class DateUtils {
private static final String format = "yyyy/MM/dd HH:mm:ss";
//这里要指定给每一个线程，各个线程之间的变量互不干扰,不然时间转换会报错
private static final ThreadLocal<SimpleDateFormat> threadLocal = new ThreadLocal<SimpleDateFormat>();
public static Date covertDateString2Date(String dateStr){
SimpleDateFormat sdf = null;
//ThreadLocal要有((get()||set()||remove())&&(引用!=null)) || ( 线程池shutdown())以防止内存泄漏
sdf = threadLocal.get();
if (sdf == null){
sdf = new SimpleDateFormat(format);
}
Date date = null;
try {
System.out.println("currentThreadName：" + Thread.currentThread().getName());
if (dateStr != null) {
date = sdf.parse(dateStr);
}
} catch (Exception e) {
e.printStackTrace();
}
return date;
}
}效果图：以下是单线程导入花费的时间对比
?多线程导入花费的时间
