更新zip解析

whycxzp

2022-07-21 1dcd4d1a3a54cd8f92a1d8f19d2ca2908ddc7dbf

更新zip解析

 src/main/java/com/whyc/controller/ProductBomApprovingController.java

@@ -47,7 +47,7 @@

    @PostMapping("zipParse")
    @ApiOperation("zip解析")
    public Response zipParse(@RequestParam("file") MultipartFile file) throws IOException {
    public Response zipParse(@RequestParam("file") MultipartFile file) throws IOException, InvalidFormatException {
        return service.zipParse(file);
    }


 src/main/java/com/whyc/dto/FileUrlDTO.java

New file
@@ -0,0 +1,22 @@
package com.whyc.dto;

public class FileUrlDTO {
    private String fileName;
    private String httpFileUrl;

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getHttpFileUrl() {
        return httpFileUrl;
    }

    public void setHttpFileUrl(String httpFileUrl) {
        this.httpFileUrl = httpFileUrl;
    }
}

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

@@ -1,5 +1,6 @@
package com.whyc.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import org.apache.ibatis.type.Alias;
@@ -22,6 +23,8 @@
    private String     parentName;
    @ApiModelProperty("母料型号")
    private String     parentModel;
    @ApiModelProperty("结构件类型")
    private String type;
    @ApiModelProperty("类别")
    private String     category;
    @ApiModelProperty("子件编码")
@@ -52,6 +55,15 @@
    private Date updateDate;
    @ApiModelProperty("版本")
    private Integer   version;
    @ApiModelProperty("母料版本号(产品版本号)")
    private String parentVersion;

    @ApiModelProperty("工程图纸url")
    private String dwgUrl;

    @ApiModelProperty("工程图纸是否存在:0否1存在")
    @TableField(exist = false)
    private Integer dwgExist;

    public Integer getId() {
        return id;
@@ -228,4 +240,36 @@
    public void setVersion(Integer version) {
        this.version = version;
    }

    public String getParentVersion() {
        return parentVersion;
    }

    public void setParentVersion(String parentVersion) {
        this.parentVersion = parentVersion;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getDwgUrl() {
        return dwgUrl;
    }

    public void setDwgUrl(String dwgUrl) {
        this.dwgUrl = dwgUrl;
    }

    public Integer getDwgExist() {
        return dwgExist;
    }

    public void setDwgExist(Integer dwgExist) {
        this.dwgExist = dwgExist;
    }
}

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

@@ -1,5 +1,6 @@
package com.whyc.service;

import com.whyc.dto.FileUrlDTO;
import com.whyc.dto.Response;
import com.whyc.mapper.ProductBomApprovingMapper;
import com.whyc.pojo.DocUser;
@@ -7,7 +8,9 @@
import com.whyc.pojo.WorksheetMain;
import com.whyc.util.ActionUtil;
import com.whyc.util.CommonUtil;
import com.whyc.util.FileUtil;
import com.whyc.util.Zip4jUtil;
import io.swagger.models.auth.In;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;
@@ -20,7 +23,9 @@
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
@@ -108,7 +113,8 @@
        mapper.insertBatchSomeColumn(bomList);
    }

    public Response zipParse(MultipartFile file) throws IOException {
    public Response zipParse(MultipartFile file) throws IOException, InvalidFormatException {
        List<ProductBomApproving> list = new LinkedList<>();
        Response response = new Response();
        //检查是否为zip
        boolean isZip = Zip4jUtil.checkZipFileParam(file);
@@ -131,29 +137,133 @@
        Zip4jUtil.unPackZip(zipFile,null,filePath);
        //遍历解压后的文件夹路径,解析excel
        System.out.println(filePath);
        File[] bomFileDirArr = parentFile.listFiles();
        for (File bomFileDir : bomFileDirArr){
            String bomFileDirName = bomFileDir.getName();
            System.out.println(bomFileDirName);
            File[] bomFileArr = bomFileDir.listFiles();
            for (File bomFile : bomFileArr){
                String bomFileName = bomFile.getName();
                //解析excel
                if (bomFileName.contains(".xls")){
                    System.out.println("待解析的excel文件为:"+bomFileName);
        List<String> fileList = new ArrayList<>();
        //图纸筛选
        List<FileUrlDTO> dwgExistsList = getDwgList(fileList);
        fileList = FileUtil.getStaticFilePath(parentFile,fileList);
        for (int i = 0; i < fileList.size(); i++) {
            String fileTempUrl = fileList.get(i);
            if(fileTempUrl.contains(".xls")){
                File fileTemp = new File(fileTempUrl);
                Workbook workbook = null;
                workbook = WorkbookFactory.create(fileTemp);
                List<? extends PictureData> allPictures = workbook.getAllPictures();
                //取第一个sheet表
                Sheet sheet = workbook.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                //取第三行,并以第三行开始
                Row rowTemp = sheet.getRow(1);
                short lastCellNum = rowTemp.getLastCellNum();
                //先单独获取产品型号和版本号/分类
                Row row = sheet.getRow(1);
                String type = row.getCell(1).getStringCellValue();

                Row row2 = sheet.getRow(2);
                String productModel = row2.getCell(5).getStringCellValue();
                Row row3 = sheet.getRow(3);
                String productVersion = row3.getCell(5).getStringCellValue();
                for (int l = 9; l < lastRowNum-1; l++) {
                    ProductBomApproving bomApproving = new ProductBomApproving();
                    bomApproving.setParentModel(productModel);
                    bomApproving.setParentVersion(productVersion);
                    bomApproving.setType(type);

                    for (int m = 2; m < lastCellNum; m++) {
                        row = sheet.getRow(l);
                        Cell cell = row.getCell(m);
                        String cellValue = null;
                        int cellValueInt = 0;
                        Double cellValueDouble = null;
                        if(m == 3){
                            cellValueDouble = cell.getNumericCellValue();
                            DecimalFormat decimalFormat = new DecimalFormat("0");
                            cellValue = decimalFormat.format(cellValueDouble);
                        }
                        else if(m ==7){
                            cellValueDouble = cell.getNumericCellValue();
                            if(cellValueDouble.equals(0d)){
                                cellValue = "";
                            }else{
                                cellValue = cellValueDouble.toString();
                            }
                        }
                        else if(m==8){
                            cellValueDouble = cell.getNumericCellValue();
                        }
                        else {
                            cellValue = cell.getStringCellValue();
                        }

                        switch (m){
                            case 2:{bomApproving.setCategory(cellValue);}break;
                            case 3:{bomApproving.setSubCode(cellValue);}break;
                            case 4:{bomApproving.setSubName(cellValue);}break;
                            case 5:{
                                bomApproving.setSubModel(cellValue);
                                //判断图纸查看是否存在
                                bomApproving.setDwgExist(0);
                                String dwgFileName = cellValue+".dwg";
                                for (int n = 0; n < dwgExistsList.size(); n++) {
                                    FileUrlDTO fileUrlDTO = dwgExistsList.get(n);
                                    if(dwgFileName.equals(fileUrlDTO.getFileName())){
                                        //存在
                                        bomApproving.setDwgExist(1);
                                        bomApproving.setDwgUrl(fileUrlDTO.getHttpFileUrl());
                                        break;
                                    }
                                }
                            }break;
                            case 6:{bomApproving.setMaterial(cellValue);}break;
                            case 7:{bomApproving.setThickness(cellValue);}break;
                            case 8:{bomApproving.setQuantity(cellValueDouble.intValue());}break;
                            case 9:{bomApproving.setSurfaceDetail(cellValue);}break;
                            case 10:{bomApproving.setNotes(cellValue);}break;
                            case 11:{
                                //图片,从0开始,到图片size为止
                                int k = l-9;
                                if(k<allPictures.size()){
                                    PictureData pictureData = allPictures.get(k);
                                    //图片存储 doc_file/product_approving/${username}/{dateFormat}/${timeStamp}
                                    String suffix = pictureData.suggestFileExtension();
                                    String picturePath = filePath + File.separator + bomApproving.getSubModel() + "." + suffix;
                                    String picturePathFront = "doc_file" + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat + File.separator + timeStamp  + File.separator + bomApproving.getSubModel() + "." + suffix;
                                    byte[] data = pictureData.getData();
                                    FileOutputStream fileOutputStream = null;
                                    File pictureFile = new File(picturePath);
                                    fileOutputStream = new FileOutputStream(pictureFile);
                                    fileOutputStream.write(data);

                                    bomApproving.setPictureUrl(picturePathFront);
                                }
                            }break;
                        }
                    }
                    list.add(bomApproving);
                }
            }
        }
        return null;
    }

    private List<FileUrlDTO> getDwgList(List<String> fileList) {
        List<FileUrlDTO> list = new LinkedList<>();
        fileList.forEach(fileUrl->{
            if(fileUrl.substring(fileUrl.lastIndexOf(".")+1).equals("dwg")) {
                FileUrlDTO dto = new FileUrlDTO();
                dto.setFileName(fileUrl.substring(fileUrl.lastIndexOf(File.separator) + 1));
                dto.setHttpFileUrl(fileUrl.substring(fileUrl.lastIndexOf("doc_file" + File.separator + "product_approving")));
                list.add(dto);
            }
        });
        return list;
    }

    public static void main(String[] args) throws IOException, InvalidFormatException {
        File file = new File("C:\\Users\\29550\\Desktop\\当前项目\\202207图纸管理\\0940000072(BOM).xls");
        boolean exists = file.exists();
        System.out.println(exists);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        System.out.println(lastRowNum);
        String a = "C:\\Users\\29550\\Desktop\\当前项目\\202207泰州平台\\49泰州数据库备份-服务器从windows切换到Ubuntu时的数据库备份\\db_alarm.sql";
        String substring = a.substring(a.lastIndexOf(File.separator)+1);
       String s2 = (a.substring(a.lastIndexOf("当前项目"+File.separator+"202207泰州平台")));

        System.out.println(substring);
        System.out.println(s2);
    }
}

 src/main/java/com/whyc/util/FileUtil.java

New file
@@ -0,0 +1,44 @@
package com.whyc.util;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class FileUtil {

    public static List<String> getStaticFilePath(File file, List<String> list){

        //如果是文件的情况
        if (file.isFile()){
            list.add(file.getAbsolutePath());
        }else{
            //如果是目录的情况
            //创建一个File数组来存储当前目录下所有文件和目录的绝对路径
            File[] files = file.listFiles();
            //循环遍历files
            for (File fileTemp : files){
                if(fileTemp.getName().contains(".zip")){
                    continue;
                }
                //子级是目录
                if (fileTemp.isDirectory()){
                    //递归再次进行判断
                    getStaticFilePath(fileTemp, list);
                }else{
                    //子级是文件
                    String absolutePath = fileTemp.getAbsolutePath();
                    list.add(absolutePath);
                    //System.out.println(temp + "文件 :" + fileTemp.getName() + "\t");
                }
            }
        }
        return list;
    }

    public static void main(String[] args) {
        File file = new File("C:\\Users\\29550\\Desktop\\当前项目\\202207泰州平台");
        List list = new ArrayList<>();
        list = getStaticFilePath( file,list);
        System.out.println(list);
    }
}