java多線程導入excel的方法
更新時間：2021年05月26日 11:18:13 作者：旭旭1998
最近項目寫了poi導入excel數(shù)據到數(shù)據庫，想把學到的知識用于實踐，于是使用多線程方式導入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>
二、導入實現(xiàn)類
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) + "列發(fā)生錯誤：" + 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內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
Springboot關于自定義stater的yml無法提示問題解決方案
這篇文章主要介紹了Springboot關于自定義stater的yml無法提示問題及解決方案，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-06-06
詳解Spring Kafka中關于Kafka的配置參數(shù)
這篇文章主要介紹了詳解Spring Kafka中關于Kafka的配置參數(shù),小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-08-08
下面小編就為大家分享一篇JFinal極速開發(fā)框架使用筆記，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-01-01
這篇文章主要為大家介紹了LongAdder原理及創(chuàng)建使用示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-01-01
如果我們將可獨立于業(yè)務代碼之外的功配置模塊封裝成一個個starter,復用的時候只需要將其在pom中引用依賴即可,SpringBoot為我們完成自動裝配,簡直不要太爽,這篇文章主要給大家介紹了關于如何利用SpringBoot寫一個屬于自己的Starter,需要的朋友可以參考下2022-03-03
猜數(shù)字游戲是一款經典的游戲,該游戲說簡單也很簡單,說不簡單確實也很難,這篇文章主要給大家介紹了關于Java基礎入門篇之邏輯控制練習題與猜數(shù)字游戲的相關資料,需要的朋友可以參考下2023-06-06
這篇文章主要介紹了java 交換兩個數(shù)據的方法實例詳解的相關資料,需要的朋友可以參考下2016-12-12
這篇文章主要介紹了Java 讀取類路徑下的資源文件實現(xiàn)代碼的相關資料,主要實現(xiàn)工具類代碼ResourceLoadUtil.java的實例需要的朋友可以參考下2017-07-07
spring boot使用thymeleaf跳轉頁面實例代碼
本篇文章主要介紹了spring boot使用thymeleaf跳轉頁面，實例介紹了thymeleaf的原理和介紹，有興趣的可以了解一下。2017-04-04
Spring Boot處理全局統(tǒng)一異常的兩種方法與區(qū)別
這篇文章主要給大家介紹了關于Spring Boot處理全局統(tǒng)一異常的兩種方法與區(qū)別，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Spring Boot具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-06-06