bom审批-excel解析

whycxzp

2022-07-19 5f436b149b04bad65f0b66ed4c36c879fed9907f

bom审批-excel解析

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

New file
@@ -0,0 +1,47 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.pojo.ProductBom;
import com.whyc.pojo.ProductBomApproving;
import com.whyc.service.ProductBomApprovingService;
import io.swagger.annotations.Api;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.nio.channels.MulticastChannel;
import java.util.List;

/**
 * 产品bom审批
 */
@RestController
@RequestMapping("bomApproving")
@Api(tags = "产品bom审批")
public class ProductBomApprovingController {

    @Autowired
    private ProductBomApprovingService service;

    /**
     * excel解析bom
     */
    @PostMapping("excelParse")
    public Response excelParse(@RequestParam("file") MultipartFile file) throws IOException, InvalidFormatException {
        Response<Object> response = new Response<>();
        String name=file.getOriginalFilename();
        if(!name.substring(name.length()-4).equals(".xls") || !name.substring(name.length()-5).equals(".xlsx")){
            response.set(1,false,"文件解析错误");
        }else{
            List<ProductBomApproving> list = service.excelParse(file.getInputStream());
            response.setII(1,true,list,"文件解析成功");
        }
        return response;
    }

}

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

New file
@@ -0,0 +1,60 @@
package com.whyc.service;

import com.whyc.pojo.ProductBomApproving;
import com.whyc.pojo.WorksheetMain;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;

@Service
public class ProductBomApprovingService {
    public List<ProductBomApproving> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
        List<ProductBomApproving> list = new LinkedList<>();
        Workbook workbook = null;
        workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        //取第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        //取第二行,并以第二行开始
        Row row2 = sheet.getRow(1);
        short lastCellNum = row2.getLastCellNum();
        for (int i = 1; i < lastRowNum; i++) {
            ProductBomApproving bomApproving = new ProductBomApproving();
            for (int j = 0; j < lastCellNum; j++) {
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(j);
                String cellValue = cell.getStringCellValue();
                switch (j){
                    case 0:{}break;
                    case 1:{bomApproving.setParentCode(cellValue);}break;
                    case 2:{bomApproving.setParentName(cellValue);}break;
                    case 3:{bomApproving.setParentModel(cellValue);}break;
                    case 4:{bomApproving.setCategory(cellValue);}break;
                    case 5:{bomApproving.setSubCode(cellValue);}break;
                    case 6:{bomApproving.setSubName(cellValue);}break;
                    case 7:{bomApproving.setSubModel(cellValue);}break;
                    case 8:{bomApproving.setUnit(cellValue);}break;
                    case 9:{bomApproving.setQuantity(Integer.parseInt(cellValue));}break;
                    case 10:{bomApproving.setProducer(cellValue);}break;
                    case 11:{bomApproving.setMaterial(cellValue);}break;
                    case 12:{bomApproving.setThickness(cellValue);}break;
                    case 13:{bomApproving.setSurfaceDetail(cellValue);}break;
                    case 14:{bomApproving.setNotes(cellValue);}break;
                    case 15:{
                        //图片
                        System.out.println("图片列:"+cellValue);
                    }break;
                }
            }
            list.add(bomApproving);
        }

        return list;
    }
}