serviceImpl层
/**
* 导入excel
* @param fileBytes
* @return
* @throws IOException
*/
@Override
public Object insertInfo(byte[] fileBytes) throws IOException {
ByteArrayInputStream stream = new ByteArrayInputStream(fileBytes);
HSSFWorkbook sheets = new HSSFWorkbook(stream);
HSSFSheet sheet = sheets.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
List<TCLlorderDeviceInfo> infos = new ArrayList<TCLlorderDeviceInfo>();
List<TCLlorderAddressLibrary> libraries = new ArrayList<TCLlorderAddressLibrary>();
for (int i = 1; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
if (row == null){
break;
}
TCLlorderDeviceInfo info = new TCLlorderDeviceInfo();
info.setCreateTime(new Date());
info.setUpdateTime(new Date());
TCLlorderAddressLibrary addressLibrary = new TCLlorderAddressLibrary();
addressLibrary.setCreateTime(new Date());
boolean valid = true;
String raw1="";
String raw4 = "";
String raw5 = "";
String raw6 = "";
for (int j = 0; j < 6; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
valid = false;
break;
}
switch (j) {
case 0:
String raw = cell.getStringCellValue();
if (raw == null || raw.isEmpty()) {
valid = false;
} else {
info.setDeviceTypename(raw);
}
break;
case 1:
raw1 = cell.getStringCellValue();
info.setDeviceNumber(raw1);
break;
case 2:
String raw3 = cell.getStringCellValue();
info.setDeviceAlias(raw3);
break;
case 3:
raw4 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw4);*/
addressLibrary.setProvince(raw4);
break;
case 4:
raw5 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw5);*/
addressLibrary.setCity(raw5);
break;
case 5:
raw6 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw6);*/
addressLibrary.setArea(raw6);
break;
}
String device = raw4 + "/" + raw5 + "/" + raw6;
info.setDeviceBelonging(device);
}
if (!valid) {
break;
}else {
infos.add(info);
//遍历libraries集合 进行判断数组中是否有重复的地址
libraries.add(addressLibrary);
System.out.println(libraries.size());
}
}
List<TCLlorderDeviceInfo> list=new ArrayList<TCLlorderDeviceInfo>();
for (TCLlorderDeviceInfo infoDevice: infos) {
List<TCLlorderDeviceInfo> tcLlorderDeviceInfos = mapper.selectInfoDevice(infoDevice.getDeviceNumber());
if (tcLlorderDeviceInfos.size()==0){
list.add(infoDevice);
}
}
List<TCLlorderAddressLibrary> libraryList=new ArrayList<>();
for (TCLlorderAddressLibrary library:libraries) {
List<TCLlorderAddressLibrary> addressLibraries=addressMapper.countProject(library.getProvince(),library.getCity(),library.getArea());
if(addressLibraries.size()==0){
libraryList.add(library);
}
}
if(list.size()!=0) {
mapper.insertProjectItem(list);
if(libraryList.size()!=0) {
addressMapper.insertProject(libraryList);
}
}
if(list.size()>0) {
return ResultVOUtil.success(list.size());
}
return ResultVOUtil.error(801,"导入失败,表中数据已存在");
}
//后缀名为.xlsx
@Override
public Object insertInfoDevice(byte[] fileBytes) throws IOException {
ByteArrayInputStream stream = new ByteArrayInputStream(fileBytes);
XSSFWorkbook sheets = new XSSFWorkbook(stream);
XSSFSheet sheet = sheets.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
List<TCLlorderDeviceInfo> infos = new ArrayList<TCLlorderDeviceInfo>();
List<TCLlorderAddressLibrary> libraries = new ArrayList<TCLlorderAddressLibrary>();
String raw1 = "";
String raw4 = "";
String raw5 = "";
String raw6 = "";
for (int i = 1; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row == null) {
break;
}
TCLlorderDeviceInfo info = new TCLlorderDeviceInfo();
info.setCreateTime(new Date());
info.setUpdateTime(new Date());
TCLlorderAddressLibrary addressLibrary = new TCLlorderAddressLibrary();
addressLibrary.setCreateTime(new Date());
boolean valid = true;
for (int j = 0; j < 6; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
valid = false;
break;
}
switch (j) {
case 0:
String raw = cell.getStringCellValue();
if (raw == null || raw.isEmpty()) {
valid = false;
} else {
info.setDeviceTypename(raw);
}
break;
case 1:
raw1 = cell.getStringCellValue();
info.setDeviceNumber(raw1);
break;
case 2:
String raw3 = cell.getStringCellValue();
info.setDeviceAlias(raw3);
break;
case 3:
raw4 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw4);*/
addressLibrary.setProvince(raw4);
break;
case 4:
raw5 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw5);*/
addressLibrary.setCity(raw5);
break;
case 5:
raw6 = cell.getStringCellValue();
/* info.setDeviceBelonging(raw6);*/
addressLibrary.setArea(raw6);
break;
}
String device = raw4 + "/" + raw5 + "/" + raw6;
info.setDeviceBelonging(device);
}
if (!valid) {
break;
}else {
infos.add(info);
libraries.add(addressLibrary);
System.out.println(libraries.size());
}
}
//遍历infos集合 进行判断数组中是否有重复的地址
List<TCLlorderDeviceInfo> list=new ArrayList<TCLlorderDeviceInfo>();
for (TCLlorderDeviceInfo infoDevice: infos) {
List<TCLlorderDeviceInfo> tcLlorderDeviceInfos = mapper.selectInfoDevice(infoDevice.getDeviceNumber());
if (tcLlorderDeviceInfos.size()==0){
list.add(infoDevice);
}
}
//遍历libraries集合 进行判断数组中是否有重复的地址
List<TCLlorderAddressLibrary> libraryList=new ArrayList<>();
for (TCLlorderAddressLibrary library:libraries) {
List<TCLlorderAddressLibrary> addressLibraries=addressMapper.countProject(library.getProvince(),library.getCity(),library.getArea());
if(addressLibraries.size()==0) {
libraryList.add(library);
}
}
if(list.size()!=0) {
mapper.insertProjectItem(list);
}
if(list.size()!=0 && libraryList.size()!=0) {
addressMapper.insertProject(libraryList);
}
if(list.size()>0) {
return ResultVOUtil.success(list.size());
}
return ResultVOUtil.error(801,"导入失败,表中数据已存在");
}
//controller层
@Autowired
private ContractManagementService contractManagementService;
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
@PostMapping("readExcel")
public Object siteInfoImport(MultipartFile file) {
String fileName = file.getOriginalFilename();
logger.info("Data import of computer room site" + file);
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
logger.error("导入失败,请选择正确的文件格式支持xlsx或xls");
}
try {
byte[] bytes = file.getBytes();
if (fileName.endsWith(EXCEL2003)) {
return service.insertInfo(bytes);
} else if (fileName.endsWith(EXCEL2007)) {
return service.insertInfoDevice(bytes);
}
} catch (Exception e) {
/* e.printStackTrace();*/
return ResultVOUtil.error(500, e.getMessage());
}
return ResultVOUtil.error(801, "导入失败,请选择正确的文件格式支持xlsx或xls");
}