纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

java多线程导入excel java多线程导入excel的方法

旭旭1998   2021-05-26 我要评论
想了解java多线程导入excel的方法的相关内容吗旭旭1998在本文为您仔细讲解java多线程导入excel的相关知识和一些Code实例欢迎阅读和指正我们先划重点:java多线程导入excel,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内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持! 


相关文章

猜您喜欢

  • Docker搭建开发环境 快速掌握使用Docker搭建开发环境

    想了解快速掌握使用Docker搭建开发环境的相关内容吗mb5f8e5f84eff49在本文为您仔细讲解Docker搭建开发环境的相关知识和一些Code实例欢迎阅读和指正我们先划重点:Docker搭建开发环境,Docker开发环境下面大家一起来学习吧。..
  • PHP优化巨量关键词的匹配 详解PHP优化巨量关键词的匹配

    想了解详解PHP优化巨量关键词的匹配的相关内容吗枕边书在本文为您仔细讲解PHP优化巨量关键词的匹配的相关知识和一些Code实例欢迎阅读和指正我们先划重点:php,优化,关键词,匹配下面大家一起来学习吧。..

网友评论

Copyright 2020 www.Musicdownload3mp.com 【飞音下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式