package com.whyc.service;
|
|
import com.whyc.dto.Response;
|
import com.whyc.dto.SopDto;
|
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.Cell;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.ParseException;
|
import java.util.*;
|
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();
|
//文件名
|
sop.setFileName(sheet.getRow(2).getCell(2).getStringCellValue());
|
//下面的row是动态的,因为文件类型的行数是动态拓展的
|
//锚点:文件版本
|
int fileVersionIndex = 0;
|
int fileSuitableProductIndex = 0;
|
//校准lastRowNum
|
for (int i = 4; i <= lastRowNum; i++) {
|
String stringCellValue = sheet.getRow(i).getCell(1).getStringCellValue();
|
if(stringCellValue.equals("文件版本")){
|
fileVersionIndex = i;
|
continue;
|
}else if(stringCellValue.equals("文件适用产品")){
|
fileSuitableProductIndex = i;
|
continue;
|
}
|
if(stringCellValue.equals("发布说明")){
|
lastRowNum = i;
|
break;
|
}
|
}
|
|
|
List<Map<String,List<String>>> fileTypeList = new LinkedList<>();
|
StringBuilder fileTypeSb = new StringBuilder();
|
//文件类型开始index
|
int fileTypeStartIndex = 3;
|
for (int i = fileTypeStartIndex; i < fileVersionIndex; i++) { //这里面的都是文件类型相关内容
|
//遍历每一行,index = 2,6列,搜索所有的大阶段
|
String cellValue = sheet.getRow(i).getCell(2).getStringCellValue();
|
if(!cellValue.contains("£")&& !cellValue.contains("R")&& !cellValue.equals("")){ //确定为大阶段
|
Map<String,List<String>> map = new LinkedHashMap<>();
|
List<String> cellList = new LinkedList<>();
|
for (int j = i+1; j < fileVersionIndex; j++) {
|
Cell cell2 = sheet.getRow(j).getCell(2);
|
String cellValue2 = cell2.getStringCellValue();
|
if(!cellValue2.contains("£")&& !cellValue2.contains("R")&& !cellValue2.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue2.equals("")){
|
break;
|
}
|
if(cellValue2.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue2).append(";");
|
break;
|
}
|
cellList.add(cellValue2);
|
|
}
|
|
//
|
Cell cell3 = sheet.getRow(j).getCell(3);
|
String cellValue3 = cell3.getStringCellValue();
|
if(!cellValue3.contains("£")&& !cellValue3.contains("R")&& !cellValue3.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue3.equals("")){
|
break;
|
}
|
if(cellValue3.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue3).append(";");
|
break;
|
}
|
cellList.add(cellValue3);
|
|
}
|
|
//
|
Cell cell4 = sheet.getRow(j).getCell(4);
|
String cellValue4 = cell4.getStringCellValue();
|
if(!cellValue4.contains("£")&& !cellValue4.contains("R")&& !cellValue4.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue4.equals("")){
|
break;
|
}
|
if(cellValue4.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue4).append(";");
|
break;
|
}
|
cellList.add(cellValue4);
|
|
}
|
|
//
|
Cell cell5 = sheet.getRow(j).getCell(5);
|
String cellValue5 = cell5.getStringCellValue();
|
if(!cellValue5.contains("£")&& !cellValue5.contains("R")&& !cellValue5.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue5.equals("")){
|
break;
|
}
|
if(cellValue5.contains("R")){
|
fileTypeSb.append(cellValue).append("-").append(cellValue5).append(";");
|
break;
|
}
|
cellList.add(cellValue5);
|
|
}
|
}
|
map.put(cellValue,cellList);
|
fileTypeList.add(map);
|
}
|
|
//可以提取方法
|
String cellValueRight = sheet.getRow(i).getCell(6).getStringCellValue();
|
if(!cellValueRight.contains("£")&& !cellValueRight.contains("R")&& !cellValueRight.equals("")){ //确定为大阶段
|
Map<String,List<String>> map = new LinkedHashMap<>();
|
List<String> cellList = new LinkedList<>();
|
for (int j = i+1; j < fileVersionIndex; j++) {
|
Cell cell2 = sheet.getRow(j).getCell(6);
|
String cellValue2 = cell2.getStringCellValue();
|
if(!cellValue2.contains("£")&& !cellValue2.contains("R")&& !cellValue2.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue2.equals("")){
|
break;
|
}
|
if(cellValue2.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue2).append(";");
|
break;
|
}
|
cellList.add(cellValue2);
|
|
}
|
|
//
|
Cell cell3 = sheet.getRow(j).getCell(7);
|
String cellValue3 = cell3.getStringCellValue();
|
if(!cellValue3.contains("£")&& !cellValue3.contains("R")&& !cellValue3.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue3.equals("")){
|
break;
|
}
|
if(cellValue3.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue3).append(";");
|
break;
|
}
|
cellList.add(cellValue3);
|
|
}
|
|
//
|
Cell cell4 = sheet.getRow(j).getCell(8);
|
String cellValue4 = cell4.getStringCellValue();
|
if(!cellValue4.contains("£")&& !cellValue4.contains("R")&& !cellValue4.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue4.equals("")){
|
break;
|
}
|
if(cellValue4.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue4).append(";");
|
break;
|
}
|
cellList.add(cellValue4);
|
|
}
|
|
//
|
Cell cell5 = sheet.getRow(j).getCell(9);
|
String cellValue5 = cell5.getStringCellValue();
|
if(!cellValue5.contains("£")&& !cellValue5.contains("R")&& !cellValue5.equals("")){ //确定为下一个大阶段
|
break;
|
}else{
|
if(cellValue5.equals("")){
|
break;
|
}
|
if(cellValue5.contains("R")){
|
fileTypeSb.append(cellValueRight).append("-").append(cellValue5).append(";");
|
break;
|
}
|
cellList.add(cellValue5);
|
|
}
|
}
|
map.put(cellValueRight,cellList);
|
fileTypeList.add(map);
|
}
|
}
|
|
sop.setFileTypeList(fileTypeList);
|
|
sop.setFileType(fileTypeSb.toString());
|
|
sop.setFileVersion(sheet.getRow(fileVersionIndex).getCell(2).getStringCellValue());
|
sop.setFileRelatedVersion(sheet.getRow(fileVersionIndex).getCell(7).getStringCellValue());
|
|
sop.setEditor(sheet.getRow(fileVersionIndex+1).getCell(2).getStringCellValue());
|
sop.setAuditor(sheet.getRow(fileVersionIndex+1).getCell(7).getStringCellValue());
|
|
String releaseTimeStr = sheet.getRow(fileVersionIndex+2).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行截止
|
for (int i = fileSuitableProductIndex+1; i < lastRowNum; i++) {
|
SOPProduct product = new SOPProduct();
|
//取第3列,第5列
|
Cell cell = sheet.getRow(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(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,"文件解析成功");
|
}
|
|
public Response add(SOP sop) {
|
return null;
|
}
|
|
//查询sop信息
|
public Response getSopInfo(List<SopDto> sqlList, String code, String model) {
|
String sqlStr=" ";
|
if(sqlList!=null&&sqlList.size()>0){
|
for (SopDto dto:sqlList) {
|
if(dto.getChileType().size()>0){
|
for (int i=0;i<dto.getChileType().size();i++){
|
sqlStr+=" and file_type like "+"'%"+dto.getParentType()+"-"+dto.getChileType().get(i)+"%' ";
|
}
|
}
|
}
|
}
|
List<SOP> list=mapper.getSopInfo(sqlStr,code,model);
|
return new Response().setII(1,list.size()>0,list,"查询sop信息");
|
}
|
}
|
