java多线程导入excel代码方法

本篇文章小编给大家分享一下java多线程导入excel代码方法，文章代码介绍的很详细，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

一、首先是依赖

   org.apache.poi
   poi
   3.17
  
  
   org.apache.poi
   poi-ooxml
   3.17
  
  
   org.apache.poi
   poi-ooxml-schemas
   3.17
  

二、导入实现类

package com.supcon.mare.tankinfo.util;

import com.alibaba.excel.util.StringUtils;
import com.supcon.mare.common.util.exception.InterfaceException;
import com.supcon.mare.tankinfo.constant.Constants;
import com.supcon.mare.tankinfo.entity.TankAreaEntity;
import com.supcon.mare.tankinfo.entity.TankMovementEntity;
import com.supcon.mare.tankinfo.repository.TankAreaRepository;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: zhaoxu
 * @description:
 */
public class ExcelThreadUtil implements Runnable {

    Sheet sheet;
    Integer rowIndex;
    List rowNames;
    Integer size = 0;

    TankAreaRepository tankAreaRepository;

    ReflectUtil reflectUtil;

    public volatile static List tankMovementEntities = new ArrayList();

    public void setExcelThreadAttr(Sheet sheet, Integer rowIndex, List rowNames, Integer size, TankAreaRepository tankAreaRepository, ReflectUtil reflectUtil) {
        this.sheet = sheet;
        this.rowIndex = rowIndex;
        this.rowNames = rowNames;
        this.size = size;
        this.tankAreaRepository = tankAreaRepository;
        this.reflectUtil = reflectUtil;
    }

    @Override
    public void run() {
        int index = 0;
        while (sheet.getRow(rowIndex) != null && index  units = new ArrayList();
            Iterator cellIterator = sheet.getRow(rowIndex).cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(CellType.STRING);
                String rowName = rowNames.get(cellIndex++);
                try {
                    //解析标题
                    judgeToParse(tankMovementEntity, rowName, cell, units, sheet);
                } catch (Exception e) {
                    throw new InterfaceException(3414, "第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列发生错误：" + cell.toString());
                }
            }
            Boolean hasValue = false;
            try {
                Map fieldsValue = reflectUtil.getFieldsValue(TankMovementEntity.class, tankMovementEntity);
                Iterator fieldKey = fieldsValue.keySet().iterator();
                //如果所有属性为空则不导入
                while (fieldKey.hasNext()) {
                    String key = fieldKey.next();
                    Object value = fieldsValue.get(key);
                    if (!"serialVersionUID".equals(key) && !StringUtils.isEmpty(fieldsValue.get(key))) {
                        hasValue = true;
                        break;
                    }
                }
                tankMovementEntity.setValid(1);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            rowIndex++;
            index++;

            if (!hasValue) {
                continue;
            }
            tankMovementEntity.setGmtCreate(Utils.getNowDate());
            tankMovementEntity.setSupportAddPump(0);
            tankMovementEntity.setSupportDelPump(0);
            tankMovementEntity.setChangeTank(0);
            tankMovementEntity.setSupportUpdatePump(0);
            //解析单元号
            if (units.size() > 1) {
                units.stream().forEach(unit -> {
                    TankAreaEntity tankAreaEntity = tankAreaRepository.findByTankAreaCodeAndValid(unit, Constants.VALID_TRUE);
                    if (tankAreaEntity == null && tankMovementEntity.getTankArea() == null) {
                        tankAreaEntity = new TankAreaEntity();
                        tankAreaEntity.setId(Long.valueOf(1));
                        tankMovementEntity.setTankArea(tankAreaEntity);
                    }
                    List unContainUnit = units.stream().filter(unit1 -> !unit1.equals(unit)).collect(Collectors.toList());
                    StringBuilder goalTankArea = new StringBuilder();
                    unContainUnit.stream().forEach(un -> goalTankArea.append(un + ","));
                    tankMovementEntity.setGoalTankArea(goalTankArea.substring(0, goalTankArea.lastIndexOf(",")));
                    tankMovementEntity.setStrideTankarea(1);
                });
            } else {
                TankAreaEntity tankAreaEntity = null;
                if (units.size() > 0) {
                    tankAreaEntity = tankAreaRepository.findByTankAreaCodeAndValid(units.get(0), Constants.VALID_TRUE);
                }
                if (tankAreaEntity == null && tankMovementEntity.getTankArea() == null) {
                    tankAreaEntity = new TankAreaEntity();

                    tankAreaEntity.setId(Long.valueOf(1));
                }
                tankMovementEntity.setTankArea(tankAreaEntity);
            }
            if (!StringUtils.isEmpty(tankMovementEntity.getGoalTankArea())) {
                tankMovementEntity.setStrideTankarea(1);
            } else {
                tankMovementEntity.setStrideTankarea(0);
            }
            synchronized (tankMovementEntities) {
                tankMovementEntities.add(tankMovementEntity);
            }
        }
    }

    public void judgeToParse(TankMovementEntity tankMovementEntity, String rowName, Cell cell, List units, Sheet sheet) {
        String stringCellValue;

        if (Utils.isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex())) {
            stringCellValue = Utils.getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex()).replaceAll("n", ",");
        } else {
            stringCellValue = cell.getStringCellValue().replaceAll("n", ",");
        }

        if (StringUtils.isEmpty(stringCellValue)) {
            return;
        }
        //判断是否需要进行转换
        if (Constants.CONTINUITY.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 0);
        } else if (Constants.BATCH.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 1);
        } else if (Constants.YES.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 1);
        } else if (Constants.NO.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 0);
        } else if (Constants.PROVIDE.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 1);
        } else if (Constants.UN_PROVIDE.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 0);
        } else if (Constants.NA.equals(stringCellValue)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, null);
        } else if (Constants.SOURCE_DEVICE.equals(rowName) || Constants.PUMP_DEVICE.equals(rowName) || Constants.GOAL_DEVICE.equals(rowName) || Constants.VALVE_DEVICE.equals(rowName)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, stringCellValue);
        } else if ((Constants.TANK_AREA).equals(rowName)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, tankAreaRepository.findByTankAreaCodeAndValid(stringCellValue, Constants.VALID_TRUE));
        } else if ((Constants.SWITCH_TANK_SOURCE).equals(rowName)) {
            reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, "目的".equals(stringCellValue) ? 1 : 0);
        } else if ((Constants.UNIT).equals(rowName)) {
            if (stringCellValue.contains(Constants.COMMA)) {
                String[] split = stringCellValue.split(",");
                units.addAll(Arrays.asList(split));
            } else {
                units.add(stringCellValue);
            }
        } else {
            Class> aClass = null;

            try {
                //反射获取字段类型
                String typeName = TankMovementEntity.class.getDeclaredField(rowName).getGenericType().getTypeName();
                aClass = Class.forName(typeName);
            } catch (Exception e) {
                System.out.println(("未找到属性类型:" + rowName));
            }

            if (aClass == Long.class) {
                reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, Long.valueOf(stringCellValue));
            } else if (aClass == Integer.class || aClass == int.class) {
                if (Integer.valueOf(stringCellValue) > 1 && !rowName.equals(Constants.MAX_PUMP)) {
                    throw new ClassCastException("解析错误");
                }
                reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, Integer.valueOf(stringCellValue));
            } else {
                reflectUtil.setValue(TankMovementEntity.class, tankMovementEntity, rowName, stringCellValue);
            }
        }
    }
}

三、调用多线程

@Override
@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
public String importFromExcel(String fileString) throws IOException {
    Workbook workbook = null;
    File file = new File("D:Google下载" + fileString + ".xlsx");
    InputStream is = new FileInputStream(file);
    if (file.getName().endsWith(Constants.XLSX)) {
        workbook = new XSSFWorkbook(is);
    } else if (file.getName().endsWith(Constants.XLS)) {
        workbook = new HSSFWorkbook(is);
    }
    Iterator sheetIterator = workbook.sheetIterator();
    List rowNames = new ArrayList();
    while (sheetIterator.hasNext()) {
        Sheet sheet = sheetIterator.next();
        Iterator rowIterator = sheet.rowIterator();
        Integer rowIndex = 0;
        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor((sheet.getLastRowNum() / readNumbers) + 1, (sheet.getLastRowNum() / readNumbers) + 2, 1000, TimeUnit.MILLISECONDS, new PriorityBlockingQueue(), Executors.defaultThreadFactory(), new ThreadPoolExecutor.AbortPolicy());
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //从第二行开始
            if (rowIndex > 0) {
                Iterator cellIterator = row.cellIterator();
                //第二行是标题
                if (rowIndex == 1) {
                    //列遍历
                    while (cellIterator.hasNext()) {
                        Cell next = cellIterator.next();
                        next.setCellType(CellType.STRING);
                        String stringCellValue = next.getStringCellValue();
                        rowNames.add(stringCellValue.replaceAll("n", ""));
                    }
                } else {
                    Cell cell = row.getCell(0);
                    cell.setCellType(CellType.STRING);
                    if (StringUtils.isEmpty(cell.getStringCellValue())) {
                        break;
                    }
                    if ((rowIndex - 2) % readNumbers == 0) {
                        ExcelThreadUtil excelThreadUtil = new ExcelThreadUtil();
                        if ((sheet.getLastRowNum() - rowIndex + 1  readNumbers)) {
                            Integer size = sheet.getLastRowNum() - rowIndex + 1;
                            excelThreadUtil.setExcelThreadAttr(sheet, rowIndex, rowNames, size, tankAreaRepository, reflectUtil);
                            Future> future = threadPoolExecutor.submit(excelThreadUtil);
                            try {
                                future.get();
                            } catch (Exception e) {
                                ExcelThreadUtil.tankMovementEntities.clear();
                                throw new InterfaceException(3414, e.getMessage());
                            }
                            break;
                        }
                        excelThreadUtil.setExcelThreadAttr(sheet, rowIndex, rowNames, readNumbers, tankAreaRepository, reflectUtil);
                        Future> future = threadPoolExecutor.submit(excelThreadUtil);
                        try {
                            future.get();
                        } catch (Exception e) {
                            ExcelThreadUtil.tankMovementEntities.clear();
                            throw new InterfaceException(3414, e.getMessage());
                        }
                    }
                }
            }
            rowIndex++;
        }
        threadStop(threadPoolExecutor);
    }
    return "true";
}

判断线程是否都停止：

private void threadStop(ThreadPoolExecutor threadPoolExecutor) {
        while (true) {
            if (threadPoolExecutor.getActiveCount() == 0) {
                ExcelThreadUtil.tankMovementEntities.stream().forEach(tankMovement -> {
                    tankMovement.setTaskDefineCode("move_" + String.valueOf((int) (Math.random() * (1600 - 1 + 1)) + 1) + System.currentTimeMillis());
                    String businessTypeName = tankMovement.getBusinessTypeCode();
                    EnumCodeEntity businessEnumEntity = enumCodeRepository.findByName(businessTypeName);
                    tankMovement.setBusinessTypeCode(businessEnumEntity == null ? businessTypeName : businessEnumEntity.getTypeCode());
                    Integer random = (int) (Math.random() * (2 - 1 + 1)) + 1;
                    tankMovement.setMaterialTypeCode(String.valueOf(random));
                    TankAreaEntity tankAreaEntity = new TankAreaEntity();
                    tankAreaEntity.setId(Long.valueOf((int) (Math.random() * (16 - 1 + 1)) + 1));
                    tankMovement.setTankArea(tankAreaEntity);
                });
                tankMovementRepository.saveAll(ExcelThreadUtil.tankMovementEntities);
                ExcelThreadUtil.tankMovementEntities.clear();
                break;
            }
        }
    }