java多线程导入excel代码方法
作者：袖梨
2022-06-29
本篇文章小编给大家分享一下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; ListrowNames; 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;
}
}
}
