package com.whyc.service;
|
|
import com.whyc.dto.Response;
|
import com.whyc.mapper.SOPMapper;
|
import com.whyc.pojo.SOP;
|
import com.whyc.pojo.SOPProduct;
|
import com.whyc.util.CommonUtil;
|
import com.whyc.util.DateUtil;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.ParseException;
|
import java.util.Date;
|
import java.util.LinkedList;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class SOPService {
|
|
@Resource
|
private SOPMapper mapper;
|
|
|
public Response<Object> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
|
SOP sop = new SOP();
|
List<SOPProduct> sopProductList = new LinkedList<>();
|
|
sop.setCreateTime(new Date());
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//校准lastRowNum
|
for (int i = 9; i <= lastRowNum; i++) {
|
if(sheet.getRow(i).getCell(1).getStringCellValue().equals("发布说明")){
|
lastRowNum = i;
|
break;
|
}
|
}
|
//固定5列
|
short cellNum = 5;
|
//文件名
|
sop.setFileName(sheet.getRow(2).getCell(2).getStringCellValue());
|
|
//文件类型
|
Row rowType = sheet.getRow(4);
|
String typeStr1 = rowType.getCell(2).getStringCellValue();
|
String typeStr2 = rowType.getCell(3).getStringCellValue();
|
String typeStr3 = rowType.getCell(4).getStringCellValue();
|
String typeStr4 = rowType.getCell(5).getStringCellValue();
|
String typeStr5 = rowType.getCell(6).getStringCellValue();
|
String typeStr6 = rowType.getCell(7).getStringCellValue();
|
String typeStr7 = rowType.getCell(8).getStringCellValue();
|
String typeStr8 = rowType.getCell(9).getStringCellValue();
|
int fileType;
|
if(typeStr1.contains("R")){
|
fileType = 11;
|
}
|
else if(typeStr2.contains("R")){
|
fileType = 12;
|
}
|
else if(typeStr3.contains("R")){
|
fileType = 13;
|
}
|
else if(typeStr4.contains("R")){
|
fileType = 14;
|
}
|
else if(typeStr5.contains("R")){
|
fileType = 21;
|
}
|
else if(typeStr6.contains("R")){
|
fileType = 22;
|
}
|
else if(typeStr7.contains("R")){
|
fileType = 23;
|
}
|
else if(typeStr8.contains("R")){
|
fileType = 24;
|
}else{
|
return new Response<>().set(1,false,"文件类型未勾选");
|
}
|
sop.setFileType(fileType);
|
|
sop.setFileVersion(sheet.getRow(5).getCell(2).getStringCellValue());
|
sop.setFileRelatedVersion(sheet.getRow(5).getCell(7).getStringCellValue());
|
|
sop.setEditor(sheet.getRow(6).getCell(2).getStringCellValue());
|
sop.setAuditor(sheet.getRow(6).getCell(7).getStringCellValue());
|
|
String releaseTimeStr = sheet.getRow(7).getCell(2).getStringCellValue();
|
Date releaseTime;
|
try {
|
if(releaseTimeStr.contains(".")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION2.parse(releaseTimeStr);
|
}else if(releaseTimeStr.contains("-")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION3.parse(releaseTimeStr);
|
}else if(releaseTimeStr.contains("/")){
|
releaseTime = DateUtil.YYYY_MM_DD_UNION4.parse(releaseTimeStr);
|
}else {
|
releaseTime = DateUtil.YYYY_MM_DD_UNION.parse(releaseTimeStr);
|
}
|
}catch (ParseException e){
|
return new Response().set(1,false,"申请日期格式错误!");
|
}
|
sop.setReleaseDate(releaseTime);
|
|
String releaseNotes = sheet.getRow(lastRowNum).getCell(2).getStringCellValue();
|
sop.setReleaseNotes(releaseNotes);
|
|
//第10行开始,倒数第2行截止
|
int applyModelNum = lastRowNum-9;
|
for (int i = 0; i < applyModelNum; i++) {
|
SOPProduct product = new SOPProduct();
|
//取第3列,第5列
|
Cell cell = sheet.getRow(9 + i).getCell(2);
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String code = cell.getStringCellValue();
|
String codeAutoFill;
|
if(code.equals("")){
|
codeAutoFill = "";
|
}else {
|
codeAutoFill = CommonUtil.codeAutoFill(code);
|
}
|
|
product.setCode(codeAutoFill);
|
String model = sheet.getRow(9 + i).getCell(7).getStringCellValue();
|
if((code.equals("") && !model.equals("")) || (!code.equals("") && model.equals(""))){
|
return new Response<>().set(1,false,"物料编码和型号必须同时为空");
|
}else{
|
product.setModel(model);
|
}
|
//TODO 存储excel文件
|
|
sopProductList.add(product);
|
}
|
|
sopProductList = sopProductList.stream().filter(product -> !product.getCode().equals("")).collect(Collectors.toList());
|
sop.setSopProductList(sopProductList);
|
|
return new Response().setII(1,true,sop,"文件解析成功");
|
}
|
}
|
