文件类型动态解析更新

whycxzp

2023-06-21 48e2f23871edef3171447bb69a998b8f26c316cb

文件类型动态解析更新

 src/main/java/com/whyc/pojo/SOP.java

@@ -5,6 +5,7 @@

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * SOP:操作指导书
@@ -36,6 +37,14 @@
    private String uploadUser;
    /**文件url*/
    private String fileUrl;

    /**
     * [
     *      {"组装":["组1","组2"]},
     *      {"测试":["测1","测2"}
     * ]
     */
    private List<Map<String,List<String>>> fileTypeList;

    @TableField(exist = false)
    private List<SOPProduct> sopProductList;
@@ -143,4 +152,12 @@
    public void setFileUrl(String fileUrl) {
        this.fileUrl = fileUrl;
    }

    public List<Map<String, List<String>>> getFileTypeList() {
        return fileTypeList;
    }

    public void setFileTypeList(List<Map<String, List<String>>> fileTypeList) {
        this.fileTypeList = fileTypeList;
    }
}

 src/main/java/com/whyc/service/SOPService.java

@@ -1,7 +1,6 @@
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;
@@ -15,10 +14,7 @@
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.Map;
import java.util.*;
import java.util.stream.Collectors;

@Service
@@ -39,64 +35,200 @@
        //取第一个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 = 9; i <= lastRowNum; i++) {
            if(sheet.getRow(i).getCell(1).getStringCellValue().equals("发布说明")){
        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;
            }
        }
        //固定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;

        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{
            return new Response<>().set(1,false,"文件类型未勾选");
                        if(cellValue2.equals("")){
                            break;
        }
        sop.setFileType("fileType");
                        if(cellValue2.contains("R")){
                            fileTypeSb.append(cellValue).append("-").append(cellValue2).append(";");
                            break;
                        }
                        cellList.add(cellValue2);

        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());
                    //
                    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);

        String releaseTimeStr = sheet.getRow(7).getCell(2).getStringCellValue();
                    }

                    //
                    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(".")){
@@ -117,11 +249,10 @@
        sop.setReleaseNotes(releaseNotes);

        //第10行开始,倒数第2行截止
        int applyModelNum = lastRowNum-9;
        for (int i = 0; i < applyModelNum; i++) {
        for (int i = fileSuitableProductIndex+1; i < lastRowNum; i++) {
            SOPProduct product = new SOPProduct();
            //取第3列,第5列
            Cell cell = sheet.getRow(9 + i).getCell(2);
            Cell cell = sheet.getRow(i).getCell(2);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String code = cell.getStringCellValue();
            String codeAutoFill;
@@ -132,7 +263,7 @@
            }

            product.setCode(codeAutoFill);
            String model = sheet.getRow(9 + i).getCell(7).getStringCellValue();
            String model = sheet.getRow(i).getCell(7).getStringCellValue();
            if((code.equals("") && !model.equals("")) || (!code.equals("") && model.equals(""))){
                return new Response<>().set(1,false,"物料编码和型号必须同时为空");
            }else{