设备器件excel导入

whycxzp

1 天以前 aa52854f0a413e153efeea76a952dcf66d76c94a

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

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

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
@@ -11,6 +13,9 @@
import com.whyc.util.CommonUtil;
import com.whyc.util.ThreadLocalUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -18,7 +23,9 @@

import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@@ -158,4 +165,146 @@
        List<DeviceSpare> deviceSpares = mapper.selectList(query);
        return new Response<List<DeviceSpare>>().set(1,deviceSpares);
    }

    public void addBatch(List<DeviceSpare> spareList) {
        mapper.insertBatchSomeColumn(spareList);
    }

    public void addOrUpdate(List<DeviceSpare> spareList) {
        //查询库中是否存在该设备,存在则增加数量.
        for (DeviceSpare spare : spareList) {
            QueryWrapper<DeviceSpare> query = Wrappers.query();
            query.eq("name",spare.getName());
            query.eq("model",spare.getModel());
            query.eq("version",spare.getVersion());
            query.eq("brand",spare.getBrand());
            query.eq("type",spare.getType());
            query.eq("supplier",spare.getSupplier());
            query.last(" limit 1");
            DeviceSpare spareInDB = mapper.selectOne(query);
            if(spareInDB != null){
                spareInDB.setQuantity(spareInDB.getQuantity()+spare.getQuantity());
                mapper.updateById(spareInDB);

            }else{
                //不存在则新增
                mapper.insert(spare);
            }
        }

    }

    public void outBound(List<DeviceSpare> spareList) {
        mapper.outBound(spareList);
    }

    public Response addByExcel(MultipartFile file) throws IOException {
        //将文件流转化为workbook
        Workbook book = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = book.getSheetAt(0);
        //获取最后一行
        int lastRowIndex = sheet.getLastRowNum();
        List<DeviceSpare> spareList = new ArrayList<>();
        for (int i = 1; i <= lastRowIndex; i++) {
            DeviceSpare spare = new DeviceSpare();
            Row row = sheet.getRow(i);
            //对每行的前7列进行非空校验
            for (int j = 0; j < 7; j++) {
                Cell cell = row.getCell(j);
                //非图片列进行非空校验.不合规无法上传
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    return new Response().set(1, false, "第" + (i + 1) + "行第" + (j + 1) + "列数据为空");
                }
            }
            //获取列的值
            String name = row.getCell(0).getStringCellValue();
            String model = row.getCell(1).getStringCellValue();
            String version = row.getCell(2).getStringCellValue();
            int quantity = (int) row.getCell(3).getNumericCellValue();
            String brand = row.getCell(4).getStringCellValue();
            String type = row.getCell(5).getStringCellValue();
            String supplier = row.getCell(6).getStringCellValue();
            spare.setName(name);
            spare.setModel(model);
            spare.setVersion(version);
            spare.setQuantity(quantity);
            spare.setBrand(brand);
            spare.setType(type);
            spare.setSupplier(supplier);

            spareList.add(spare);

        }

        //第七列为图片-浮动式图片 TODO 待处理 处理已存在的物料
        //获取绘图对象中的所有图形
        XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
        }
        List<XSSFShape> shapes = drawing.getShapes();
        for (XSSFShape shape : shapes) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor anchor = pic.getClientAnchor();
                //获取图片所在的起始行
                int rowIndex = anchor.getRow1();
                byte[] data = pic.getPictureData().getData();

                DeviceSpare spare = spareList.get(rowIndex - 1);
                //对存储路径进行定义
                Date now = new Date();
                String timeFormat = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM_DD_HH_MM_SS_UNION, now);
                String dirMonth = ThreadLocalUtil.format(ThreadLocalUtil.TIME_YYYY_MM, now);
                String fileDirPath = CommonUtil.getRootFile() + "deviceSpare" + File.separator + dirMonth;
                File fileDir = new File(fileDirPath);
                //如果文件夹不存在则创建
                if (!fileDir.exists()) {
                    fileDir.mkdirs();
                }
                String filePath = fileDirPath + File.separator + timeFormat+"_"+spare.getName()+"_"+spare.getModel()+"_"+spare.getVersion()+".png";

                // 保存图片到本地
                try (FileOutputStream fos = new FileOutputStream(filePath)) {
                    fos.write(data);
                }
                String split = "pis_file"+File.separator+"deviceSpare";
                String picUrl = File.separator + filePath.substring(filePath.indexOf(split));
                spare.setPictureUrl(picUrl);
            }
        }

        //新增列表
        List<DeviceSpare> spareListNew = new ArrayList<>();
        //更新列表
        List<DeviceSpare> spareListUpdate = new ArrayList<>();
        //查询库中的所有设备器件
        List<DeviceSpare> deviceSpareListInDB = mapper.selectList((Wrapper<DeviceSpare>) CommonUtil.nullObject);
        for (DeviceSpare spare : spareList){
            for (DeviceSpare spareInDB : deviceSpareListInDB){
                if (spare.getName().equals(spareInDB.getName())
                        && spare.getModel().equals(spareInDB.getModel())
                        && spare.getVersion().equals(spareInDB.getVersion())
                        && spare.getBrand().equals(spareInDB.getBrand())
                        && spare.getType().equals(spareInDB.getType())
                        && spare.getSupplier().equals(spareInDB.getSupplier())
                ){
                    spare.setId(spareInDB.getId());
                    spare.setQuantity(spareInDB.getQuantity()+spare.getQuantity());
                    if (spare.getPictureUrl() != null && spareInDB.getPictureUrl()!=null){
                        spare.setPictureUrl(spareInDB.getPictureUrl()+","+spare.getPictureUrl());
                    }
                    spareListUpdate.add(spare);

                }else{
                    spareListNew.add(spare);
                }
            }
        }
        //更新TODO

        //addBatch(spareList);

        return null;
    }
}