终于忙完有时间写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
import com.alibaba.fastjson.JSON;
import com.fan.li.srpingbootfile.dao.UserMapper;
import com.fan.li.srpingbootfile.importThread.ImportTask;
import com.fan.li.srpingbootfile.pojo.User;
import com.fan.li.srpingbootfile.service.UserService;
import com.fan.li.srpingbootfile.util.ExcelUtilServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DurationFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
@Slf4j
@Service("userService")
public class UserServiceImpl implements UserService {
private static final int rowNum = 100;
@Transactional(propagation = Propagation.REQUIRED,
timeout = 30,isolation = Isolation.READ_COMMITTED)
@Override
public Integer savetUser(User user) {
Integer count = 0;
log.info("你插进来了吗?" );
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++;
}
}
log.info("你插进来的次数" + 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 / rowNum + 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) * rowNum +1;
int endRow = i * rowNum ;
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
import com.fan.li.srpingbootfile.pojo.User;
import com.fan.li.srpingbootfile.service.UserService;
import com.fan.li.srpingbootfile.util.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.Callable;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Semaphore;
/**
* 导入excel线程类
* @author fan
* @date 2022/4/18 18:39
*/
@Slf4j
public class ImportTask implements Callable<Integer>{
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
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();//得到列数,默认数据从excel第2行开始
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 = 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;
}
public static String doZero(String value) {
String val = value;
if (value.indexOf(".") != -1) {
val = value.replaceAll("0+?$", "");
val = val.replaceAll("[.]$", "");
}
return val;
}
/**
* @desc:获取单元格内容
* @param cell
* @return
* 异常时返回null
*/
public static String getCellValueText(Cell cell) {
try {
String text = "";
if (cell == null) {
return text;
} else {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC://0
if (DateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
//SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
return sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
}
Double value = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(value + "");
text = bd.toPlainString();
text = doZero(text);
break;
case Cell.CELL_TYPE_STRING://1
text = cell.getStringCellValue().trim();
case Cell.CELL_TYPE_FORMULA://2
try {
text = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
text = String.valueOf(cell.getRichStringCellValue());
}
break;
}
return text;
}
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
}
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;
}
//threadlocal,线程本地变量。一个共享变量存进该容器相当于在线程内部拷贝了一个副本。
/*//threadlocal实现隔离性源码
public void set(T value){
Threadt t = Thread.currentThread();
ThreadtLocalMap map = getMap(t);
if(map != null){
map.put(this , value);
}else{
createMap(t , value);
}
}
public T get(){
Threadt t = Thread.currentThread();
ThreadLocalMap map = getMap(t);
if(map != null){
ThreadLocalMap.Entry e = map.getEntry(this);
if(e != null){
T result = (T)e.value;
return result;
}
}
}*/
}
效果图:以下是单线程导入花费的时间对比
当把rowNum改成200时,多线程导入花费的时间
当把rowNum改成100时,多线程导入花费的时间
当把rowNum改成150时,多线程导入花费的时间
可控在2分钟内