java多线程导入excel的方法
短信预约 -IT技能 免费直播动态提醒
一、首先是依赖
<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;
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内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
