多线程导入Excel

终于忙完有时间写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分钟内