package com.whyc.service;
|
|
import com.whyc.dto.Response;
|
import com.whyc.mapper.ProductBomApprovingMapper;
|
import com.whyc.pojo.DocUser;
|
import com.whyc.pojo.ProductBomApproving;
|
import com.whyc.pojo.WorksheetMain;
|
import com.whyc.util.ActionUtil;
|
import com.whyc.util.CommonUtil;
|
import com.whyc.util.Zip4jUtil;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.stereotype.Component;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import javax.xml.crypto.Data;
|
import java.io.File;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
import java.util.LinkedList;
|
import java.util.List;
|
|
@Service
|
public class ProductBomApprovingService {
|
|
@Resource
|
private ProductBomApprovingMapper mapper;
|
|
public List<ProductBomApproving> excelParse(InputStream inputStream) throws IOException, InvalidFormatException {
|
List<ProductBomApproving> list = new LinkedList<>();
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
List<? extends PictureData> allPictures = workbook.getAllPictures();
|
inputStream.close();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(0);
|
int lastRowNum = sheet.getLastRowNum();
|
//取第三行,并以第三行开始
|
Row row2 = sheet.getRow(1);
|
short lastCellNum = row2.getLastCellNum();
|
for (int i = 2; i < lastRowNum+1; i++) {
|
ProductBomApproving bomApproving = new ProductBomApproving();
|
for (int j = 1; j < lastCellNum; j++) {
|
Row row = sheet.getRow(i);
|
Cell cell = row.getCell(j);
|
String cellValue = null;
|
Double cellValueInt = 0d;
|
if(j == 9){
|
cellValueInt = cell.getNumericCellValue();
|
}else if(j!=15){
|
cellValue = cell.getStringCellValue();
|
}
|
switch (j){
|
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(cellValueInt.intValue());}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:{
|
//图片,从0开始,到图片size为止
|
int k = i-2;
|
if(k<allPictures.size()){
|
PictureData pictureData = allPictures.get(k);
|
//图片存储 product_approving/username/2022-07/
|
DocUser user = ActionUtil.getUser();
|
String dateFormat = new SimpleDateFormat("YYYY-MM").format(new Date());
|
String rootFile = CommonUtil.getRootFile();
|
String approvingPath = rootFile + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat;
|
File provingFile = new File(approvingPath);
|
if(!provingFile.exists()){
|
provingFile.mkdirs();
|
}
|
String suffix = pictureData.suggestFileExtension();
|
String picturePath = approvingPath + File.separator + bomApproving.getSubModel() + "." + suffix;
|
String picturePathFront = "doc_file" + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat + 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 list;
|
}
|
|
public void insert(List<ProductBomApproving> bomList) {
|
mapper.insertBatchSomeColumn(bomList);
|
}
|
|
public Response zipParse(MultipartFile file) throws IOException {
|
Response response = new Response();
|
//检查是否为zip
|
boolean isZip = Zip4jUtil.checkZipFileParam(file);
|
if(!isZip){
|
return response.set(1,false,"上传的文件格式不是zip");
|
}
|
//zip存储路径:doc_file/product_approving/${username}/{dateFormat}/${timeStamp}
|
String rootFile = CommonUtil.getRootFile();
|
DocUser user = ActionUtil.getUser();
|
String dateFormat = new SimpleDateFormat("YYYY-MM").format(new Date());
|
long timeStamp = System.currentTimeMillis();
|
String zipPath = rootFile + File.separator + "product_approving" + File.separator + user.getName() + File.separator + dateFormat+ File.separator + timeStamp;
|
File zipFile = new File(zipPath);
|
if(!zipFile.exists()){
|
zipFile.mkdirs();
|
}
|
file.transferTo(zipFile);
|
//解压文件夹
|
Zip4jUtil.unPackZip(zipFile,null,zipPath);
|
//遍历解压后的文件夹路径,解析excel
|
System.out.println(zipPath);
|
File[] bomFileDirArr = zipFile.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
|
while (bomFileName.contains(".xls")){
|
System.out.println("待解析的excel文件为:"+bomFileName);
|
}
|
}
|
}
|
return null;
|
}
|
|
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);
|
}
|
}
|
