java多线程导入excel的方法

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;

List rowNames;

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 < size) {

TankMovementEntity tankMovementEntity = new TankMovementEntity();

Integer cellIndex = 0;

List 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下载\\" + filvTAhLnNeString + ".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 * 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;

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。