java多线程导入excel的方法

一、首先是依赖

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
  </dependency>
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
  </dependency>
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
  </dependency>

二、导入实现类

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<String> rowNames;
    Integer size = 0;

    TankAreaRepository tankAreaRepository;

    ReflectUtil reflectUtil;

    public volatile static List<TankMovementEntity> tankMovementEntities = new ArrayList<>();

    public void setExcelThreadAttr(Sheet sheet, Integer rowIndex, List<String> 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 < size) {
            TankMovementEntity tankMovementEntity = new TankMovementEntity();
            Integer cellIndex = 0;
            List<String> units = new ArrayList();
            Iterator<Cell> 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<String, Object> fieldsValue = reflectUtil.getFieldsValue(TankMovementEntity.class, tankMovementEntity);
                Iterator<String> 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<String> 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<String> 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<Sheet> sheetIterator = workbook.sheetIterator();
    List<String> rowNames = new ArrayList<>();
    while (sheetIterator.hasNext()) {
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.rowIterator();
        Integer rowIndex = 0;
        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor((sheet.getLastRowNum() / readNumbers) + 1, (sheet.getLastRowNum() / readNumbers) + 2, 1000, TimeUnit.MILLISECONDS, new PriorityBlockingQueue<Runnable>(), Executors.defaultThreadFactory(), new ThreadPoolExecutor.AbortPolicy());
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //从第二行开始
            if (rowIndex > 0) {
                Iterator<Cell> 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 * 2 && 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;
            }
        }
    }

到此这篇关于java多线程导入excel的方法的文章就介绍到这了,更多相关java多线程导入excel内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！