java多线程导入excel

末蓝、 2022-11-13 06:22 188阅读 0赞

## 一、首先是依赖 ##

<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;
                }
            }
        }