【界面】：
【一些说明】：
【提示用户是否覆盖数据】：
//对于订单明细表，假如价格相关的一系列字段是NULL，则直接填入；如果已经有数据了，就提示给用户，是否覆盖掉原数据呢？YES—调后台接口给盖掉，NO—前台取消掉；
【可以限制用户导出的数据】：
【外协商】：
//后台会根据Excel中外协商的名字去数据库中查询，所以填写的外协商名字，必须得是之前录入数据库的。
【所有非空判断格式】：
//所有必填项目的非空校验都类似上图的格式；
【去数据库中检验的字段】：
【非法的数据格式】：
【交期】：
不填写，则不会检验，一旦填写之后，将采用easyExcel的检验格式；
【具体流程】：
【1】：query（查询）
this.$ajax.post('oeOemCheck/queryOrderCheck', this.formData).then( )
后台接口
【前端对象】：
custId-----------
checkDate-----
【总结】：
将 checkDate 拆分成当天最初时间，和最末时间，一拆二查当天 。
custId用了好几次 。
custId + 2个日期 查到一个List 。
@RequestMapping(value="/queryOrderCheck", method = RequestMethod.POST)
public ResultInfo queryOrderCheck(@RequestBody JSONObject data, HttpServletRequest request){
JSONObject retObj = new JSONObject();
Long custId = data.getLong("custId");
if(custId == null)
return ResultInfo.error("vmes.parameter.isNotNull", new String[] { "custId" });
Date checkDate = data.getDate("checkDate");
if(checkDate == null)
return ResultInfo.error("vmes.parameter.isNotNull", new String[] { "checkDate" });
CoOemCustomer coOemCustomer = coOemCustomerService.getByKey(custId);
//co_oem_customer 外协商资料（ 其主键是custId ）
//
Date startTime = DateTime.getFirstDayOfMonth(checkDate);
//checkDate所在月份的第一天，时间并不重置；
Date endTime = DateTime.getLastDayOfMonth(checkDate);
//checkDate所在月份的最后一天，时间并不重置；
List<OeOemOrderDetail> oodList = oeOemOrderDetailService.oemOrderCheck(startTime, endTime, custId);
//oe_oem_order_detail 外协订单明细表。
//【分析】：根据两个时间，外协商ID 来查询出 OeOemOrderDetail 并组成一个集合。
Double initMoney = coOemCustomer.getInitMoney(); //初始应付；
Double payMoney = plOemPayService.getPayedMoney(custId);
//获取供应商付款总金额；
Double totalMoney = oeOemOrderDetailService.getTotalMoney(custId);
//获取供应商总金额；
retObj.put("oodList", oodList); //根据2日期和外协商ID 查到的外协订单明细集合；
retObj.put("initMoney", initMoney); //初始应付金额；
retObj.put("payMoney", payMoney); //供应商付款总金额；
retObj.put("totalMoney", totalMoney); //供应商总金额；
return ResultInfo.ok(retObj).addExclude(PrWorkPlan.class, "previewWorkPlan", "nextWorkPlan");
}
【oemOrderCheck】:
public List<OeOemOrderDetail> oemOrderCheck(Date startTime, Date endTime, Long custId) {
StringBuffer hql = new StringBuffer();
hql.append("select a from OeOemOrderDetail a where a.curStatus >= ").append(OeOemOrderDetail.CUR_STATUS_SH);
// [ 收回 , 送检 , 验收 , 返工 , 报废 ]
/**
* 已收回
*/
// public static Short CUR_STATUS_SH = 1;
// * 已送检
*/
// public static Short CUR_STATUS_SJ = 2;
/**
* 已验收
*/
//public static Short CUR_STATUS_YS = 3;
/**
* 需返工
*/
// public static Short CUR_STATUS_FG = 4;
/**
* 已报废
*/
// public static Short CUR_STATUS_BF = 5;
if (startTime != null) {
String start = DateTime.date2String(DateTime.getDateWithoutTime(startTime), true, true);
//年月日时分秒全部设置为0；
hql.append(" and a.factDeliveryDate >= '" + start + "'");
//实际到货日期 >= start
}
if (endTime != null) {
String end = DateTime.date2String(DateTime.getDateWithMaxTime(endTime), true, true);
//当日最后一毫秒；
hql.append(" and a.factDeliveryDate < '" + end + "'");
//实际到货日期 < end
}
if (custId != null)
hql.append(" and a.oeOemOrder.coOemCustomer.custId=").append(custId);
//detail 下的外协订单 oeOemOrder（ oe_oem_order ）的 外协商 的 主键。
hql.append(" order by a.oeOemOrder.ooDate asc ");
//外协日期----按照外协日期来升序。
Query<OeOemOrderDetail> query = this.getSession().createQuery(hql.toString(), OeOemOrderDetail.class);
return query.list();
}
【 getPayedMoney 】：
public Double getPayedMoney(Long custId) {
String hql="select sum(a.totalMoney) from PlOemPay a where a.coOemCustomer.custId = :custId ";
Query<Double> query = this.getSession().createQuery(hql, Double.class);
query.setParameter("custId", custId);
return query.uniqueResult()==null ? 0d : query.uniqueResult();
}
PlOemPay ( pl_oem_pay ) #外协付款；
sum(a.totalMoney) #对 付款金额 进行累加求和；
/**
* @Fields totalMoney : 付款金额
*/
@Column(name = “total_money”)
private Double totalMoney;
【总结】：
根据custID来查出该ID下的所有付款金额。
【 getTotalMoney 】：
public Double getTotalMoney(Long custId) {
// ifnull只适用于mysql
String hql = "select sum(ifnull(a.factMoney,0)+ifnull(a.taxMoney,0)) from OeOemOrderDetail a where a.oeOemOrder.coOemCustomer.custId = :custId ";
Query<Double> query = this.getSession().createQuery(hql, Double.class);
query.setParameter("custId", custId);
return query.uniqueResult() == null ? 0d : query.uniqueResult();
}
【 总结 】：
factMoney : 实际费用
taxMoney : 税金
【2】：editCheckageDate（对账）
【前端】：
this.$ajax.post(‘oeOemCheck/editCheckageDate’, {
oodIdList: this.oodIdList
}).then()…
【后台接口】：
@RequestMapping(value=“/editCheckageDate”, method = RequestMethod.POST)
public ResultInfo editCheckageDate(@RequestBody JSONObject data){
Object oodIdListObj = data.get("oodIdList"); //拿出前端传过来的对象。
List<Long> oodIdList = JSONArray.parseArray(oodIdListObj.toString(), Long.class);
//将前端对象解析成一个 List<Long> .
if(oodIdList == null || oodIdList.size() == 0){
return ResultInfo.error("vmes.parameter.isNotNull", new String[] { "oodIdList" });
}
oeOemOrderDetailService.editCheckageDate(oodIdList);
return ResultInfo.ok();
}
【 editCheckageDate 】：
public void editCheckageDate(List oodIdList) {
for (Long oodId : oodIdList) {
OeOemOrderDetail ood = oeOemOrderDetailDao.getByKey(oodId);
if (ood.getCheckageDate() == null) {
ood.setCheckageDate(new Date()); //重新去数据库设置上了日期；
//不点击【对账】：这个字段好像一直都是 null
}
}
}
【3】：exportExcel
exportExcel
【I.测试用】：
【1】：前端接口
if (data.oodList.length > 0) {
for(var i=0;i<this.oodList.length;i++){
this.oodId.push(data.oodList[i].oeOemOrder.ooCode) //把订单号全插入到 ooId数组，传给后台；
}
}
exportExcel() {
this.KaTeX parse error: Expected '}', got 'EOF' at end of input: …fileName: this.t(“design.helpOrder”, [this.mcode]),
data: { ooId: this.oodId },
});
},
【2】：ExcelModel类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import java.util.Date;
/**
- @author YGY
- @version 1.0
- @date 2022/7/7 10:41
/
public class OeOemOrderDetailExcelImport {
/*-
*******模具号
/
@ExcelProperty(index = 1)
private String pjMoldMcode;
/ -
*******球标号
/
@ExcelProperty(index = 2)
private String dsPartMcode ;
/
物料名称
/
@ExcelProperty(index = 3)
private String goodsName ;
/ -
******件数
/
@ExcelProperty(index = 4)
private String quantity;
/
计价方式
/
@ExcelProperty(index = 5)
private String priceType;
/ -
*******计价数量
/
@ExcelProperty(index = 6)
private String priceNum;
/
规格
/
@ExcelProperty(index = 7)
private String goodsType;
/
材质
/
@ExcelProperty(index = 8)
private String gcName;
/
加工要求
/
@ExcelProperty(index = 9)
private String memo;
/ -
*******单价（含税）
/
@ExcelProperty(index = 10)
private String containsTax;
/
单价（未税）
/
@ExcelProperty(index = 11)
private String noContainsTax;
/ -
*******小计价格
/
@ExcelProperty(index = 12)
private String minPrice;
/
交期
/
@ExcelProperty(index = 13)
@DateTimeFormat(“yyyy/MM/dd”)
private Date deliveryDate;
/
外协单号
/
private String ooCode;
/
外协日期
/
private String ooDate;
/
工序类型
/
private String processName;
/
*外协商
*/
private String coOemCustomer;
//【 下面是一堆不值钱的GETTER / SETTER方法 】：
}
【3】：后端接口
@RequestMapping(value = “/excelReconciliation” , method = RequestMethod.POST)
public void excelReconciliation(@RequestBody JSONObject data, HttpServletResponse response) throws IOException, BusinessException {
List list= data.getJSONArray(“ooId”).toJavaList(String.class);
HashSet hashSet=new HashSet(list);
ArrayList arrayList=new ArrayList();
hashSet.stream().forEach( e->{
arrayList.add(e);
}
);
Collections.sort(arrayList); // Integer , String 类型的可以不添加比较器直接进行比较~~~！！！
// arrayList.stream().forEach(e->{
// System.out.println( e );
// });
//将按照升序的List传下去：
exportBomService.excelReconciliation(arrayList, response);
}
【4】：Service中的抽象方法
//此接口勿要删除，导出测试数据用，否则手输逆天麻烦！！！
void excelReconciliation(ArrayList ooId, HttpServletResponse response) throws IOException;
【5】：Impl
@Override
public void excelReconciliation(ArrayList ooCode, HttpServletResponse response) throws IOException {
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/vnd.ms-excel”);
ArrayList longArrayList=new ArrayList<>();
ooCode.stream().forEach(e->{
Long detail = iOeOemOrderService.queryByooCode(e);
longArrayList.add(detail);
});
List list=new ArrayList<>();
longArrayList.stream().forEach(e->{
list.addAll( iOeOemOrderDetailService.listOemOrderDetail( e ) );
});
response.addHeader(“Content-Disposition”,
“attachment;filename=” + URLEncoder.encode(“GYY测试d？？？？？？？？？？？？？？？？” + “的BOM清单.xlsx”, “UTF-8”));
XSSFWorkbook wb = new XSSFWorkbook();
ExcelUtil eu = new ExcelUtil();
File tmpF = eu.getCutomerLogo();
if (list != null && list.size() > 0) {
XSSFCellStyle titleStyle = ExcelUtil.twoRowTitleStyle(wb);
XSSFCellStyle contextStyle = ExcelUtil.contextStyle(wb);
Short fontSize14 = 14;
XSSFCellStyle contextStyle14 = ExcelUtil.contextStyleFontSizeDiy(wb, fontSize14, false);
XSSFCellStyle contextStyle14Cu = ExcelUtil.contextStyleFontSizeDiy(wb, fontSize14, true);
XSSFCellStyle contextStyle14Red = ExcelUtil.contextStyleRedDiy(wb,fontSize14,false);
XSSFCellStyle contextStyle14CuRed = ExcelUtil.contextStyleRedDiy(wb,fontSize14,true);
XSSFCellStyle contextStyleOnlyYellow = ExcelUtil.contextTitleOnlyYellow(wb);
XSSFCellStyle contextTitle = ExcelUtil.redContextTitle(wb);
// 模具原材料清单
XSSFSheet sheet1 = wb.createSheet(“《外协对账》格式模板”);
writeExcelReconciliation(sheet1, titleStyle, contextTitle, contextStyle,
contextStyle14, contextStyle14Cu, contextStyle14Red, contextStyle14CuRed,
contextStyleOnlyYellow,tmpF, wb,list);
}
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.close();
wb.close();
}
【6】：Excel精确到每一格
private void writeExcelReconciliation(XSSFSheet sheet,
XSSFCellStyle titleStyle, XSSFCellStyle contextTitle,
XSSFCellStyle contextStyle, XSSFCellStyle contextStyle14,
XSSFCellStyle contextStyle14Cu, XSSFCellStyle contextStyle14Red,
XSSFCellStyle contextStyle14CuRed, XSSFCellStyle contextStyleOnlyYellow,
File custLogoFile, XSSFWorkbook wb,List detailListlist)
throws IOException {
int newRow = 0;
Row row = sheet.createRow(newRow);
Cell cell = null;
row.setHeight((short) 500);
cell = row.createCell(newRow);
cell.setCellValue(“委外加工订单”);
cell.setCellStyle(titleStyle);
newRow++;
Row row2 = sheet.createRow(newRow);
row2.setHeight((short) 400);
newRow++;
Row row3 = sheet.createRow(newRow);
row3.setHeight((short) 400);
newRow++;
Row row4 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row5 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row6 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row7 = sheet.createRow(newRow);
row4.setHeight((short) 400);
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(0);
cell.setCellValue(“外协单号 ：”);
cell.setCellStyle(contextStyle14CuRed);
region = new CellRangeAddress(2, 2, 0, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(2);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(2, 2, 2, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
// sheet.setColumnWidth(1, 17 * 256);
cell = row3.createCell(9);
cell.setCellValue(“外协日期 ：”);
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(2, 2, 9, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(11);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(2, 2, 11, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(0);
cell.setCellValue(“工序类型 ：”);
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(3, 3, 0, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(2);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(3, 3, 2, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(9);
cell.setCellValue(“外协商 ：”);
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(3, 3, 9, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(11);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(3, 3, 11, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(0);
cell.setCellValue(“序号”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 0, 0);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(1);
cell.setCellValue(“项目号（模具、\n检具或备件）”);
cell.setCellStyle(contextStyle);
cell.setCellStyle(contextTitle);
region = new CellRangeAddress(4, 6, 1, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(1, 19* 256);
cell = row5.createCell(2);
cell.setCellValue(“球标号”);
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 2, 2);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(2, 23 * 256);
cell = row5.createCell(3);
cell.setCellValue(“物料名称”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 3, 3);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(3, 23 * 256);
cell = row5.createCell(4);
cell.setCellValue(“件数”);
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 4, 4);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(5);
cell.setCellValue(“计价方式（\n/重量/面积\n/工时/数量 ）”);
cell.setCellStyle(contextStyle);
region = new CellRangeAddress(4, 6, 5, 5);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(5, 16 * 256);
cell = row5.createCell(6);
cell.setCellValue(“计价数量”);
cell.setCellStyle(contextTitle);
region = new CellRangeAddress(4, 6, 6, 6);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(7);
cell.setCellValue(“规格”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 7, 7);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(7, 12* 256);
cell = row5.createCell(8);
cell.setCellValue(“材质”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 8, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(9);
cell.setCellValue(“加工要求”);
cell.setCellStyle(contextStyle);
region = new CellRangeAddress(4, 6, 9, 9);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(10);
cell.setCellValue(“单价（含税）”);
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 10, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(10, 17 * 256);
cell = row5.createCell(11);
cell.setCellValue(“单价（未税）”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 11, 11);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(11, 17 * 256);
cell = row5.createCell(12);
cell.setCellValue(“小计价格”);
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 12, 12);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(12, 18 * 256);
cell = row5.createCell(13);
cell.setCellValue(“交期”);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 13, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(13, 14 * 256);
HashSet gongXuSet=new HashSet();
int countt=1; //序号
boolean flag=true;
String onlyOneProcess = null;
boolean ifQuan = false;
//【此时遍历list】：
for (OeOemOrderDetail o : detailListlist){
//上来先判断外协类型
if ( o.getOeOemOrder().getComeFrom()==1 ){
ifQuan=true;
}
newRow++;
Row newRoww = sheet.createRow(newRow);
newRoww.setHeight((short) 400);
cell = newRoww.createCell(0);
cell.setCellValue(countt);
countt++;
cell = newRoww.createCell(1);
cell.setCellValue(o.getPjMold().getMcode());
cell = newRoww.createCell(2);
cell.setCellValue(o.getPrWorkpiece().getDsPart().getMcode());
cell = newRoww.createCell(3);
cell.setCellValue(o.getPrWorkpiece().getDsPart().getCoGoods().getGoodsCode());
cell = newRoww.createCell(4);
cell.setCellValue( o.getQuantity() );
cell = newRoww.createCell(5);
cell.setCellValue( o.getPrWorkPlan().getCoProcess().getPriceTypeTitle() );
cell = newRoww.createCell(7);
cell.setCellValue( o.getPrWorkpiece().getDsPart().getCoGoods().getGoodsType() );
cell = newRoww.createCell(8);
cell.setCellValue(o.getPrWorkpiece().getDsPart().getCoGoods().getGoodsName());
cell = newRoww.createCell(9);
cell.setCellValue(o.getPrWorkpiece().getDsPart().getMemo());
//加工要求
cell = newRoww.createCell(13);
cell.setCellValue( DateTime.date2String(o.getDeliveryDate(),false,false,“/”) );
gongXuSet.add(o.getPrWorkPlan().getCoProcess().getPname());
if (flag) {
onlyOneProcess=o.getPrWorkPlan().getCoProcess().getPname();
//在这里把其他的地方都给补充完整
cell = row3.getCell(2);
cell.setCellValue(o.getOeOemOrder().getOoCode());
flag = false;
cell = row3.getCell(11);
cell.setCellValue(DateTime.date2String(o.getOeOemOrder().getOoDate(),true,true,“/”));
cell = row4.getCell(11);
cell.setCellValue(o.getOeOemOrder().getCoOemCustomer().getCustName());
}
}
cell = row4.getCell(2);
//SET自身具有去重特性，如果SET的长度大于1，多工序
if (gongXuSet.size()>1){
cell.setCellValue(“多工序”);
}else {
cell.setCellValue(onlyOneProcess);
}
if ( ifQuan ){
cell.setCellValue(“全工序”);
}
newRow++;
Row row12 = sheet.createRow(newRow);
row12.setHeight((short) 400);
cell = row12.createCell(0);
cell.setCellValue(“合计”);
cell.setCellStyle(contextStyleOnlyYellow);
for (int i = 1; i <= 13 ; i++) {
cell = row12.createCell(i);
cell.setCellStyle(contextStyleOnlyYellow);
}
ExcelUtil.regionCellBorder(region, sheet);
}
-
【II.导出空模板】：
【1】：前端接口
exportExcel() {
this.KaTeX parse error: Expected '}', got 'EOF' at end of input: …fileName: this.t(“design.helpOrder”, [this.mcode]),
});
}
【2】：ExcelModel类同上同上同上
【3】：后端接口
@RequestMapping(value = "/nullExcelExport" , method = RequestMethod.POST)
public void nullExcelExport(HttpServletResponse response) throws IOException, BusinessException {
exportBomService.nullExcelExport( response);
}
【4】：Service中的抽象方法
void nullExcelExport(HttpServletResponse response) throws IOException;
【5】：Impl
@Override
public void nullExcelExport(HttpServletResponse response) throws IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("GYY测试d？？？？？？？？？？？？？？？？" + "的BOM清单.xlsx", "UTF-8"));
XSSFWorkbook wb = new XSSFWorkbook();
ExcelUtil eu = new ExcelUtil();
File tmpF = eu.getCutomerLogo();
XSSFCellStyle titleStyle = ExcelUtil.twoRowTitleStyle(wb);
XSSFCellStyle contextStyle = ExcelUtil.contextStyle(wb);
Short fontSize14 = 14;
XSSFCellStyle contextStyle14 = ExcelUtil.contextStyleFontSizeDiy(wb, fontSize14, false);
XSSFCellStyle contextStyle14Cu = ExcelUtil.contextStyleFontSizeDiy(wb, fontSize14, true);
XSSFCellStyle contextStyle14Red = ExcelUtil.contextStyleRedDiy(wb,fontSize14,false);
XSSFCellStyle contextStyle14CuRed = ExcelUtil.contextStyleRedDiy(wb,fontSize14,true);
XSSFCellStyle contextStyleOnlyYellow = ExcelUtil.contextTitleOnlyYellow(wb);
XSSFCellStyle contextTitle = ExcelUtil.redContextTitle(wb);
// 模具原材料清单
XSSFSheet sheet1 = wb.createSheet("《外协对账》格式模板");
writeExcelReconciliation(sheet1, titleStyle, contextTitle, contextStyle,
contextStyle14, contextStyle14Cu, contextStyle14Red, contextStyle14CuRed,
contextStyleOnlyYellow,tmpF, wb);
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.close();
wb.close();
}
【6】：Excel精确到每一格
private void writeExcelReconciliation(XSSFSheet sheet,
XSSFCellStyle titleStyle, XSSFCellStyle contextTitle,
XSSFCellStyle contextStyle, XSSFCellStyle contextStyle14,
XSSFCellStyle contextStyle14Cu, XSSFCellStyle contextStyle14Red,
XSSFCellStyle contextStyle14CuRed, XSSFCellStyle contextStyleOnlyYellow,
File custLogoFile, XSSFWorkbook wb)
throws IOException {
int newRow = 0;
Row row = sheet.createRow(newRow);
Cell cell = null;
row.setHeight((short) 500);
cell = row.createCell(newRow);
cell.setCellValue("委外加工订单");
cell.setCellStyle(titleStyle);
newRow++;
Row row2 = sheet.createRow(newRow);
row2.setHeight((short) 400);
newRow++;
Row row3 = sheet.createRow(newRow);
row3.setHeight((short) 400);
newRow++;
Row row4 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row5 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row6 = sheet.createRow(newRow);
row4.setHeight((short) 400);
newRow++;
Row row7 = sheet.createRow(newRow);
row4.setHeight((short) 400);
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(0);
cell.setCellValue("外协单号 ：");
cell.setCellStyle(contextStyle14CuRed);
region = new CellRangeAddress(2, 2, 0, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(2);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(2, 2, 2, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(9);
cell.setCellValue("外协日期 ：");
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(2, 2, 9, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row3.createCell(11);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(2, 2, 11, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(0);
cell.setCellValue("工序类型 ：");
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(3, 3, 0, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(2);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(3, 3, 2, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(9);
cell.setCellValue("外协商 ：");
cell.setCellStyle(contextStyle14Cu);
region = new CellRangeAddress(3, 3, 9, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row4.createCell(11);
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(3, 3, 11, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 0, 0);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(1);
cell.setCellValue("项目号（模具、\n检具或备件）");
cell.setCellStyle(contextStyle);
cell.setCellStyle(contextTitle);
region = new CellRangeAddress(4, 6, 1, 1);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(1, 19* 256);
cell = row5.createCell(2);
cell.setCellValue("球标号");
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 2, 2);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(2, 23 * 256);
cell = row5.createCell(3);
cell.setCellValue("物料名称");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 3, 3);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(3, 23 * 256);
cell = row5.createCell(4);
cell.setCellValue("件数");
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 4, 4);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(5);
cell.setCellValue("计价方式（\n/重量/面积\n/工时/数量 ）");
cell.setCellStyle(contextStyle);
region = new CellRangeAddress(4, 6, 5, 5);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(5, 16 * 256);
cell = row5.createCell(6);
cell.setCellValue("计价数量");
cell.setCellStyle(contextTitle);
region = new CellRangeAddress(4, 6, 6, 6);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(7);
cell.setCellValue("规格");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 7, 7);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(7, 12* 256);
cell = row5.createCell(8);
cell.setCellValue("材质");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 8, 8);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(9);
cell.setCellValue("加工要求");
cell.setCellStyle(contextStyle);
region = new CellRangeAddress(4, 6, 9, 9);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
cell = row5.createCell(10);
cell.setCellValue("单价（含税）");
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 10, 10);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(10, 17 * 256);
cell = row5.createCell(11);
cell.setCellValue("单价（未税）");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 11, 11);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(11, 17 * 256);
cell = row5.createCell(12);
cell.setCellValue("小计价格");
cell.setCellStyle(contextStyle14Red);
region = new CellRangeAddress(4, 6, 12, 12);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(12, 18 * 256);
cell = row5.createCell(13);
cell.setCellValue("交期");
cell.setCellStyle(contextStyle14);
region = new CellRangeAddress(4, 6, 13, 13);
sheet.addMergedRegion(region);
ExcelUtil.regionCellBorder(region, sheet);
sheet.setColumnWidth(13, 14 * 256);
for (int i = 1; i <=5 ; i++) {
newRow++;
Row newRoww = sheet.createRow(newRow);
newRoww.setHeight((short) 400);
cell = newRoww.createCell(0);
cell.setCellValue(i);
}
newRow++;
Row row12 = sheet.createRow(newRow);
row12.setHeight((short) 400);
cell = row12.createCell(0);
cell.setCellValue("合计");
cell.setCellStyle(contextStyleOnlyYellow);
for (int i = 1; i <= 13 ; i++) {
cell = row12.createCell(i);
cell.setCellStyle(contextStyleOnlyYellow);
}
ExcelUtil.regionCellBorder(region, sheet);
}
【4】：importHandle
:http-request="importHandle"
【1】：前端接口
importHandle(item) {
const fileObj = item.file;
// 获取文件名称
const fileNameArr = fileObj.name.split('.');
if (
fileNameArr[fileNameArr.length - 1] !== 'xlsx' &&
fileNameArr[fileNameArr.length - 1] !== 'xls'
) {
return this.$message.error('请导入excel文件！');
}
// 通过FormData将文件转成二进制数据
const formData = new FormData();
formData.append('file', fileObj);
// 上传文件之前的钩子
this.dataListLoading = true;
this.$ajax
.post('bom/helpOrderExcelImport',formData, {
headers: {
'Content-Type': 'multipart/form-data'
}
})
.then(res => {
if (res.code !== 0) {
this.dataListLoading = false;
// this.$message.error('导入失败' + res.message);
this.$refs.upload.clearFiles();
} else {
this.dataListLoading = false;
this.$message.success('导入成功');
item.onSuccess();
// this.queryTree(); //原先craftTree文件里自己写的一个函数~ ~ ~ ！ ！ ！
}
})
.catch(() => {
// 文件上传成功时的钩子
this.dataListLoading = false;
});
}
【2】：后端接口
@ApiOperation(value = "外协对账excel导入")
@RequestMapping(value = "/helpOrderExcelImport", method = RequestMethod.POST)
public ResultInfo helpOrderExcelImport( @ApiIgnore @RequestParam("file") MultipartFile uploadFile, HttpServletRequest request) throws Exception {
PfUserInfo pfUserInfo = (PfUserInfo) ControllerUtils.getCurUserInfo(request);
InputStream inputStream = null;
inputStream = uploadFile.getInputStream(); //#################获取用户上传的文件；
//获取数据
List<OeOemOrderDetailExcelImport> list = EasyExcel.read(inputStream)
.registerConverter(new DoubleConverter())
.registerReadListener(new EventListener())
.head(OeOemOrderDetailExcelImport.class)
.sheet()
.headRowNumber(7) //从 （ n+1 ） 行开始读取；
.doReadSync();
//EasyExcel会自动关闭IO流。
oeOemOrderDetailService.insertInfo(list ,uploadFile, pfUserInfo);
return ResultInfo.ok();
}
【3】：insertInfo
接口
void insertInfo(List<OeOemOrderDetailExcelImport> list, MultipartFile uploadFile, PfUserInfo pfUserInfo) throws Exception;
具体实现
@Override
public void insertInfo(List<OeOemOrderDetailExcelImport> list, MultipartFile uploadFile,PfUserInfo pfUserInfo) throws Exception{
OeOemOrderDetailExcelImport oeOemOrderDetailExcelImport = null;
InputStream gyystream=uploadFile.getInputStream();
XSSFWorkbook wb = new XSSFWorkbook(gyystream);
XSSFSheet sheet = wb.getSheetAt(0);
Row row2=sheet.getRow(2);
Row row3=sheet.getRow(3);
//【1】:外协单号
Cell cell = row2.getCell(2);
String cell11=cell.getStringCellValue();
if (StringUtils.isBlank( cell11 )) {
throw new BusinessException("请填写外协单号！！！");
}
//【2】：外协日期
cell = row2.getCell(11);
cell.setCellType(CellType.STRING);
String cell12 = cell.getStringCellValue();
//【3】：工序类型
cell = row3.getCell(2);
String cell21 = cell.getStringCellValue();
//【4】：外协商
cell = row3.getCell(11);
String cell22 = cell.getStringCellValue();
gyystream.close(); //关闭IO流，减少与内核交互。
list.remove(list.size()-1); //必须拿掉最后一行标注的全都是黄色的那一行！！！
//首先看一下有多少条数据重复，有重复数据应当告知用户，防止用户多次导入一张表！！！
// 【将三个序号作为唯一标识~~~！！！】：
//首先告知用户的修改成本；
String message="";
int numAllRepeat=0;
for (int i = 0; i <list.size() ; i++) {
oeOemOrderDetailExcelImport = list.get(i);
String pjMoldMcode = oeOemOrderDetailExcelImport.getPjMoldMcode(); //模具号（**必填**）
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode(); //球标号（**必填**）
// if ( 记录是重复的----三号作为唯一标识~ ~ ~ ){
if ( oeOemOrderDetailDao.ifThreeIdRepeat(cell11,pjMoldMcode,dsPartMcode)){
numAllRepeat++;
}
}
if (numAllRepeat>0){
message=(" ， 本次导入共有 【 " + ( numAllRepeat ) + " 】 条数据重复。");
}
int count=0;
//【未填写检验】 ： ----这个for循环只起检验必填项作用，不进行存储！！！
for (int i = 0; i <list.size() ; i++) {
count++; //计数器得放在外面标记行数
OeOemOrderDetail oeOemOrderDetail = new OeOemOrderDetail();
oeOemOrderDetailExcelImport = list.get(i);
String pjMoldMcode = oeOemOrderDetailExcelImport.getPjMoldMcode(); //模具号（**必填**）
if (StringUtils.isBlank( pjMoldMcode )) {
throw new BusinessException("第【"+(count+7)+"】行 B 列的项目号（模具号）未填，请填写该数据。 ");
}
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode(); //球标号（**必填**）
if (StringUtils.isBlank( dsPartMcode )) {
throw new BusinessException("第【"+(count+7)+"】行 C 列的球标号未填，请填写该数据。 ");
}
// if ( 记录是重复的----三号作为唯一标识~ ~ ~ ){
if ( oeOemOrderDetailDao.ifThreeIdRepeat(cell11,pjMoldMcode,dsPartMcode)){
throw new BusinessException("第【"+(count+7)+"】行已经录入过数据库了，请核对数据 "+message);
}
String quantity=oeOemOrderDetailExcelImport.getQuantity();
if (StringUtils.isBlank( quantity )) {
throw new BusinessException("第【"+(count+7)+"】行 E 列的件数未填，请填写该数据。 ");
}
//【计价数量】：
String priceNum=oeOemOrderDetailExcelImport.getPriceNum();
if (StringUtils.isBlank( priceNum )) {
throw new BusinessException("第【"+(count+7)+"】行 G 列的计价数量未填，请填写该数据。 ");
}
//【单价（含税）】：
String containsTax = oeOemOrderDetailExcelImport.getContainsTax();
if (StringUtils.isBlank( containsTax )) {
throw new BusinessException("第【"+(count+7)+"】行 K 列的单价（含税）未填，请填写该数据。 ");
}
//【小计价格】：
String minPrice = oeOemOrderDetailExcelImport.getMinPrice();
if (StringUtils.isBlank( minPrice )) {
throw new BusinessException("第【"+(count+7)+"】行 M 列的小计价格未填，请填写该数据。 ");
}
}
//【进行拦截操作！！！】 ：- - - - 如果存在重复导入的情况，必须得告知用户！！！
//得让用户先修改掉重复的信息才能进行导入~~~！！！
//如果数据出现错误，是不会出现重复导入的情况的！！！
List<OeOemOrderDetail > orderDetailList =new ArrayList<>();
int numCount=0;
if (StringUtils.isNotBlank( cell11 )) {
//然后再进行插入操作
for (int i = 0; i < list.size(); i++) {
//单独写的一个保存方法。
numCount++;
this.checkHelpOrder(list.get(i), cell11, cell12, cell21, cell22,numCount,orderDetailList);
}
this.saveHelpOrder(orderDetailList);
}
}
【4】：checkHelpOrder
到数据库中进行检验，是否重复；
//进行检验；
public void checkHelpOrder(OeOemOrderDetailExcelImport oeOemOrderDetailExcelImport
,String oneCell,String twoCell,String threeCell,String fourCell,Integer count,List<OeOemOrderDetail> orderDetailList) throws Exception {
OeOemOrderDetail oeOemOrderDetail = new OeOemOrderDetail();
//（一）【外协单号】：
String orderCode = oneCell;
if (StringUtils.isNotBlank(orderCode)){
OeOemOrder oeOemOrder = iOeOemOrderService.queryByooooCode(orderCode);
if ( oeOemOrder == null ){
throw new BusinessException("外协单号在数据库中不存在。 ");
}
oeOemOrderDetail.setOeOemOrder( oeOemOrder );
}
//（1）【项目号】：
String pjMoldmcode = oeOemOrderDetailExcelImport.getPjMoldMcode();
PjMold pjMold=null;
if ( StringUtils.isNotBlank( pjMoldmcode ) ){
//进行存储；
pjMold = pjMoldService.getByMcode(pjMoldmcode);
if ( pjMold==null ){
throw new BusinessException("第"+(count+7)+"行B列的项目号（模具号）在数据库中不存在。 ");
}
}
if (pjMold!=null){
oeOemOrderDetail.setPjMold(pjMold);
}
//（2）【球标号】：
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode();
DsPart dsPart=null;
if ( StringUtils.isNotBlank( dsPartMcode ) ){
//进行存储；
dsPart=dsPartService.getByPartsCode(dsPartMcode);
if (dsPart==null){
throw new BusinessException("第"+(count+7)+"行C列的球标号在数据库中不存在。 ");
}
//能运行到这一行说明在数据库中找到了~~~！！！
PrWorkpiece prWorkpiece=prWorkpieceService.getByPartId(dsPart.getPartId());
if (prWorkpiece == null){
throw new BusinessException("第"+(count+7)+"行C列的球标号无对应的工件信息。 ");
}
oeOemOrderDetail.setPrWorkpiece(prWorkpiece);
oeOemOrderDetail.getPrWorkpiece().setDsPart(dsPart);
}
//（二）【外协日期】：----Date
Date ooDate=null;
if ( StringUtils.isNotBlank(twoCell) ){
try {
ooDate=DateTime.parse( ExcelUtils.excelStr2NormString( twoCell ) );
} catch (Exception e) {
throw new BusinessException("第【3】行 L 列的外协日期格式有误，请修改该数据的格式。 ");
}
}
if (ooDate!=null){
oeOemOrderDetail.getOeOemOrder().setOoDate( ooDate );
}
//（三）【工序类型】：
if ( StringUtils.isNotBlank( threeCell ) ){
if ( !"全工序".equals(threeCell) && !"多工序".equals(threeCell)){
oeOemOrderDetail.getPjMold().setPcode( threeCell );
}
}
//（四）【外协商】：
if ( StringUtils.isNotBlank( fourCell ) ){
//首先需要根据外协商来进行查找
CoOemCustomer customer = coOemCustomerService.queryByCustomerName(fourCell);
if ( customer == null ){
throw new BusinessException("外协商 在数据库中不存在，请检查该项数据。 ");
}
if (customer!=null){
oeOemOrderDetail.getOeOemOrder().setCoOemCustomer(customer);
}
}
//（3）【物料名称】：
String goodsName = oeOemOrderDetailExcelImport.getGoodsName();
if ( StringUtils.isNotBlank( goodsName ) ){
//进行存储；
oeOemOrderDetail.getPrWorkpiece().getDsPart().getCoGoods().setGoodsName( goodsName );
}
//(4)【件数】：
String quantityStr = oeOemOrderDetailExcelImport.getQuantity();
Double quantity = null;
try {
quantity = Double.valueOf( quantityStr );
} catch (NumberFormatException e) {
throw new BusinessException(" 第"+(count+7)+"行， E列件数的数字格式有误！！！ ");
}
if ( quantity!=null ){
//对qqquantity 进行存储————存储到数据库里；
oeOemOrderDetail.setQuantity( quantity );
}
//(5)【计价方式】：
String priceTypeStr=oeOemOrderDetailExcelImport.getPriceType();
if (StringUtils.isNotBlank( priceTypeStr )){
//进行存储；
// 需要进行一下转换 ===》 该字段是设置数字的，而用户传过来的是文字~ ~ ~
// oeOemOrderDetail.getPrWorkPlan().getCoProcess().setPriceType();
}
//（6）【计价数量】：
String priceNum = oeOemOrderDetailExcelImport.getPriceNum();
Double priceNumCount = null;
try {
priceNumCount = Double.valueOf( priceNum );
} catch (NumberFormatException e) {
throw new BusinessException(" 第"+(count+7)+"行， G列 计价数量 的数字格式有误！！！ ");
}
if ( priceNumCount!=null ){
//priceNumCount 进行存储————存储到数据库里；
oeOemOrderDetail.setCalculateNum( priceNumCount );
}
//(7)【规格】：
String goodsType = oeOemOrderDetailExcelImport.getGoodsType();
if (StringUtils.isNotBlank( goodsType )){
//进行存储；
oeOemOrderDetail.getPrWorkpiece().getDsPart().getCoGoods().setGoodsType( goodsType );
}
//(8)【材质】：
String gcName = oeOemOrderDetailExcelImport.getGcName();
if (StringUtils.isNotBlank( gcName )){
//进行存储；
oeOemOrderDetail.getPrWorkpiece().getDsPart().getCoGoods().getCoGoodsClass().setGcName(gcName);
}
//(9)【加工要求】：
String memo = oeOemOrderDetailExcelImport.getMemo();
if (StringUtils.isNotBlank( memo )){
//进行存储；
oeOemOrderDetail.getPrWorkpiece().getDsPart().setMemo( memo );
}
//(10)【单价（含税）】：
String containsTaxStr = oeOemOrderDetailExcelImport.getContainsTax();
Double containsTax = null;
try {
containsTax= Double.valueOf(containsTaxStr);
} catch (NumberFormatException e) {
throw new BusinessException(" 第"+(count+7)+"行， K列 单价（含税） 的数字格式有误！！！ ");
}
if ( containsTax!=null ){
//containsTax 进行存储————存储到数据库里；
oeOemOrderDetail.setTaxPrice( containsTax );
}
//(11)【单价（未税）】：
String noContainsTaxStr = oeOemOrderDetailExcelImport.getNoContainsTax();
Double noContainsTax = null;
if (StringUtils.isNotBlank( noContainsTaxStr )){
try {
noContainsTax=Double.valueOf( noContainsTaxStr );
} catch (NumberFormatException e) {
throw new BusinessException(" 第"+(count+7)+"行， L列 单价（未税） 的数字格式有误！！！ ");
}
if ( noContainsTax!=null ){
//noContainsTax 进行存储————存储到数据库里；
oeOemOrderDetail.setNotaxPrice( noContainsTax );
}
}
//(12)【小计价格】：
String minPriceStr = oeOemOrderDetailExcelImport.getMinPrice();
Double minPrice = null;
if ( StringUtils.isNotBlank(minPriceStr) ){
try {
minPrice=Double.valueOf( minPriceStr );
} catch (NumberFormatException e) {
throw new BusinessException(" 第"+(count+7)+"行， K列 单价（含税） 的数字格式有误！！！ ");
}
}
if ( minPrice!=null ){
//minPrice 进行存储————存储到数据库里；
oeOemOrderDetail.setSubtotalPrice( minPrice );
}
//（13）【交期】：
Date deliveryDate=oeOemOrderDetailExcelImport.getDeliveryDate();
//如果用户未填写的话，这一个格子会装上null
if ( deliveryDate!=null ){
//不空且数据格式正确，才能够进行存储；
oeOemOrderDetail.setDeliveryDate( deliveryDate );
}
orderDetailList.add(oeOemOrderDetail);
//先把ExcelModel类中的数据过一遍，确保全部没问题；---> 再一口气进行存储！！！
}
【5】：saveHelpOrder
保存到数据库中；
//进行存储；
public void saveHelpOrder(List<OeOemOrderDetail> orderDetailList) throws Exception {
orderDetailList.stream().forEach(e->{
try {
this.save(e); //框架自带的保存方法；
} catch (Exception ex) {
ex.printStackTrace();
}
});
}
【最终版】：
【导入功能】：
【1】：前端接口
importHandle(item) {
this.fileData = item;
const fileObj = item.file;
// 获取文件名称
const fileNameArr = fileObj.name.split(".");
if (
fileNameArr[fileNameArr.length - 1] !== "xlsx" &&
fileNameArr[fileNameArr.length - 1] !== "xls"
) {
return this.$message.error("请导入excel文件！");
}
// 通过FormData将文件转成二进制数据
const formData = new FormData();
formData.append("file", fileObj);
const houtai = {
excel: formData,
flag: true,
};
// 上传文件之前的钩子
this.dataListLoading = true;
// formData
this.$ajax
.post("bom/helpOrderExcelImport", formData, {
headers: {
"Content-Type": "multipart/form-data",
},
})
.then((res) => {
// if( res.code == 0 ){
if (res.data && res.data.ifDialog) {
const rowMessage = res.data.dialogMessage;
this.$confirm(
"数据库中之前存在数据 , 是否覆盖 ? Excel产生覆盖的行数为 ：" +
rowMessage,
"提示",
{
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning",
}
)
.then(() => {
this.$ajax.post("bom/helpOrderExcelImport22", formData, {
headers: {
"Content-Type": "multipart/form-data",
},
});
this.$message({
type: "success",
message: "覆盖成功!",
});
// this.importHandle(this.fileData)
})
.catch(() => {
//删除
this.$message({
type: "info",
message: "已取消插入",
});
});
return;
}
if (res.code !== 0) {
this.dataListLoading = false;
// this.$message.error('导入失败' + res.message);
this.$refs.upload.clearFiles();
} else {
this.dataListLoading = false;
this.$message.success("导入成功");
item.onSuccess();
// this.queryTree(); //原先craftTree文件里自己写的一个函数~ ~ ~ ！ ！ ！
}
})
.catch(() => {
// 文件上传成功时的钩子
this.dataListLoading = false;
});
},
【2】：（后端接口一） helpOrderExcelImport
@ApiOperation(value = "外协对账excel导入")
@RequestMapping(value = "/helpOrderExcelImport", method = RequestMethod.POST)
public ResultInfo helpOrderExcelImport(@ApiIgnore @RequestParam("file") MultipartFile uploadFile, HttpServletRequest request) throws Exception {
PfUserInfo pfUserInfo = (PfUserInfo) ControllerUtils.getCurUserInfo(request);
InputStream inputStream = null;
inputStream = uploadFile.getInputStream();
//获取数据
List<OeOemOrderDetailExcelImport> list = EasyExcel.read(inputStream)
.registerConverter(new DoubleConverter())
.registerReadListener(new EventListener())
.head(OeOemOrderDetailExcelImport.class)
.sheet()
.headRowNumber(7) //从 （ n+1 ） 行开始读取；
.doReadSync();
//EasyExcel会自动关闭IO流。
OeOemOrderDetailDto dto = oeOemOrderDetailService.checkInfo(list ,uploadFile, pfUserInfo,1);
JSONObject object=new JSONObject();
if (dto.getIfCheck()){ //------------dto的一个开关 ==> 要到前台提示用户 ~ ~ ~
object.put("ifDialog",true);
object.put("dialogMessage",dto.getMessageHashmap().get(2)); //EXCEL 中哪些行导致的数据覆盖，提示给用户~~~
return ResultInfo.ok( object );
}
//【程序运行到这里】：说明通过了检验关卡，可以全部将数据导入数据库，无须提示用户；
//下面将数据直接插入数据库即可~~~
List<OeOemOrderDetailExcelDto> excelDtoList = dto.getDetailDtoList( );
if (excelDtoList!=null && excelDtoList.size()>0){
oeOemOrderDetailService.insertInfo( dto );
}
object.put("ifDialog",false); //不让用户做选择题了 ~ ~ ~
return ResultInfo.ok( object );
}
【3】：（后端接口二） helpOrderExcelImport22
@ApiOperation(value = "外协对账excel导入第二次")
@RequestMapping(value = "/helpOrderExcelImport22", method = RequestMethod.POST)
public ResultInfo helpOrderExcelImport22( @ApiIgnore @RequestParam("file") MultipartFile uploadFile, HttpServletRequest request) throws Exception {
PfUserInfo pfUserInfo = (PfUserInfo) ControllerUtils.getCurUserInfo(request);
InputStream inputStream = null;
inputStream = uploadFile.getInputStream();
//获取数据
List<OeOemOrderDetailExcelImport> list = EasyExcel.read(inputStream)
.registerConverter(new DoubleConverter())
.registerReadListener(new EventListener())
.head(OeOemOrderDetailExcelImport.class)
.sheet()
.headRowNumber(7) //从 （ n+1 ） 行开始读取；
.doReadSync();
OeOemOrderDetailDto dto = oeOemOrderDetailService.checkInfo(list ,uploadFile, pfUserInfo,2);
JSONObject object=new JSONObject();
object.put("ifDialog",false);
//【程序运行到这里】：说明 ---> 要直接覆盖掉！！！！！！
//【程序运行到这里】：说明 ---> 要直接覆盖掉！！！！！！
//【程序运行到这里】：说明 ---> 要直接覆盖掉！！！！！！
List<OeOemOrderDetailExcelDto> excelDtoList = dto.getDetailDtoList( );
if (excelDtoList!=null && excelDtoList.size()>0){
oeOemOrderDetailService.insertInfo( dto );
}
object.put("ifDialog",false); //不让用户做选择题了 ~ ~ ~
return ResultInfo.ok( object );
}
【4】：checkInfo
@Override
public OeOemOrderDetailDto checkInfo(List<OeOemOrderDetailExcelImport> list, MultipartFile uploadFile, PfUserInfo pfUserInfo, Integer integer) throws Exception {
OeOemOrderDetailDto dto = new OeOemOrderDetailDto();
dto.setPfUserInfo(pfUserInfo);
dto.setExcelList(list);
OeOemOrderDetailExcelImport oeOemOrderDetailExcelImport = null;
String cell11 = null;
String cell12 = null;
String cell21 = null;
String cell22 = null;
InputStream excelStream = uploadFile.getInputStream();
XSSFWorkbook wb = new XSSFWorkbook(excelStream); //BOOK需要用stream流来构建！！！
XSSFSheet sheet = wb.getSheetAt(0);
Row row2 = sheet.getRow(2);
Row row3 = sheet.getRow(3);
//【1】:外协单号
Cell cell = row2.getCell(2);
cell11 = cell.getStringCellValue();
if (integer == 1) {
if (StringUtils.isBlank(cell11)) {
throw new BusinessException("请填写外协单号！！！");
}
}
//【2】：外协日期
cell = row2.getCell(11);
cell.setCellType(CellType.STRING);
cell12 = cell.getStringCellValue();
//【3】：工序类型
cell = row3.getCell(2);
cell21 = cell.getStringCellValue();
//【4】：外协商
cell = row3.getCell(11);
cell22 = cell.getStringCellValue();
excelStream.close(); //关闭IO流，减少与内核交互。
list.remove(list.size() - 1); //必须拿掉最后一行标注的全都是黄色的那一行！！！
HashSet<String> partCodeSet = new HashSet<>();
int numAllRepeat = 0;
for (int i = 0; i < list.size(); i++) {//List<OeOemOrderDetailExcelImport> list------遍历excel类
oeOemOrderDetailExcelImport = list.get(i);
//球标号（零件编码）不能够重复！！！
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode(); //球标号（**必填**）
partCodeSet.add(dsPartMcode);
numAllRepeat++;
if (integer == 1) {
if (partCodeSet.size() < numAllRepeat) {
throw new BusinessException("第【 " + (numAllRepeat + 7) + " 】行 C 列的球标号存在重复 ，请核对该数据。 ");
}
}
//【！！！球标号验重成功！！！】
}
int count = 0;
for (int i = 0; i < list.size(); i++) {
count++; //计数器得放在外面标记行数
OeOemOrderDetail oeOemOrderDetail = new OeOemOrderDetail();
oeOemOrderDetailExcelImport = list.get(i);
String pjMoldMcode = oeOemOrderDetailExcelImport.getPjMoldMcode(); //模具号（**必填**）
if (integer == 1) {
if (StringUtils.isBlank(pjMoldMcode)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 B 列的项目号（模具号）未填，请填写该数据。 ");
}
}
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode(); //球标号（**必填**）
if (integer == 1) {
if (StringUtils.isBlank(dsPartMcode)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 C 列的球标号未填，请填写该数据。 ");
}
}
String quantity = oeOemOrderDetailExcelImport.getQuantity();
if (integer == 1) {
if (StringUtils.isBlank(quantity)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 E 列的件数未填，请填写该数据。 ");
}
}
//【计价数量】：
String priceNum = oeOemOrderDetailExcelImport.getPriceNum();
if (integer == 1) {
if (StringUtils.isBlank(priceNum)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 G 列的计价数量未填，请填写该数据。 ");
}
}
//【单价（含税）】：
String containsTax = oeOemOrderDetailExcelImport.getContainsTax();
if (integer == 1) {
if (StringUtils.isBlank(containsTax)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 K 列的单价（含税）未填，请填写该数据。 ");
}
}
//【小计价格】：
String minPrice = oeOemOrderDetailExcelImport.getMinPrice();
if (integer == 1) {
if (StringUtils.isBlank(minPrice)) {
throw new BusinessException("第【 " + (count + 7) + " 】行 M 列的小计价格未填，请填写该数据。 ");
}
}
}
List<OeOemOrderDetailExcelDto> detailDtoList = new ArrayList<>();
HashMap<Integer, String> stringHashMap = new HashMap<>();
HashMap<String, List<OeOemOrderDetail>> dspartMap = new HashMap<>();
int numCount = 0;
//然后再进行插入操作
for (int i = 0; i < list.size(); i++) {
//单独写的一个保存方法。
numCount++;
stringHashMap = this.secondcheckHelpOrder(list.get(i), cell11, cell12, cell21, cell22, numCount, detailDtoList, integer, stringHashMap, dto, dspartMap);
}
if (stringHashMap.size() > 1) {
dto.setIfCheck(true);
}
return dto;
}
【 secondcheckHelpOrder 】：
//进行检验；
public HashMap<Integer, String> secondcheckHelpOrder(OeOemOrderDetailExcelImport oeOemOrderDetailExcelImport
, String oneCell, String twoCell, String threeCell, String fourCell,
Integer count, //EXCEL中第几条数据；
List<OeOemOrderDetailExcelDto> ddtoList, //为插入方法作准备；
Integer integer, //第几次进入；
HashMap<Integer, String> strhashMap, //给前台信息；
OeOemOrderDetailDto dto,
HashMap<String, List<OeOemOrderDetail>> dspartMap //为插入作准备
) throws Exception {
//一旦出现重复的情况----返回true！！！！
Long ooId = null;
Long mid = null;
Long wppId = null;
PjMold pjMold = null;
DsPart dsPart = null;
PrWorkpiece prWorkpiece = null;
Date ooDate = null;
CoOemCustomer customer = null;
String goodsName = null;
Double quantity = null;
Short type = null;
Double priceNumCount = null;
Double containsTax = null;
String goodsType = null;
String gcName = null;
String memo = null;
Double noContainsTax = null;
Double minPrice = null;
Date deliveryDate = null;
OeOemOrderDetailExcelDto oeOemOrderDetailExcelDto = new OeOemOrderDetailExcelDto();
//（一）【外协单号】：
String orderCode = oneCell;
OeOemOrder oeOemOrder = null;
if (StringUtils.isNotBlank(orderCode)) {
oeOemOrder = iOeOemOrderService.queryByooooCode(orderCode);
if (integer == 1) {
if (oeOemOrder == null) {
throw new BusinessException("外协单号在数据库中不存在。 ");
}
}
ooId = oeOemOrder.getOoId();
}
//（1）【项目号】：
String pjMoldmcode = oeOemOrderDetailExcelImport.getPjMoldMcode();
if (StringUtils.isNotBlank(pjMoldmcode)) {
//进行存储；
pjMold = pjMoldService.getByMcode(pjMoldmcode);
if (integer == 1) {
if (pjMold == null) {
throw new BusinessException("第【 " + (count + 7) + " 】行 B 列的项目号（模具号）在数据库中不存在。 ");
}
}
}
if (pjMold != null) {
mid = pjMold.getMid();
}
//（2）【球标号】：
String dsPartMcode = oeOemOrderDetailExcelImport.getDsPartMcode();
if (StringUtils.isNotBlank(dsPartMcode)) {
//进行存储；
dsPart = dsPartService.getByPartsCode(dsPartMcode);
if (integer == 1) {
if (dsPart == null) {
throw new BusinessException("第【 " + (count + 7) + " 】行 C 列的球标号在数据库中不存在。 ");
}
}
oeOemOrderDetailExcelDto.setDsPartMcode(dsPartMcode); //DTO中设置好球标号~~~
//能运行到这一行说明在数据库中找到了~~~！！！
prWorkpiece = prWorkpieceService.getByPartId(dsPart.getPartId());
if (integer == 1) {
if (prWorkpiece == null) {
throw new BusinessException("第【 " + (count + 7) + " 】行 C 列的球标号无对应的工件信息。 ");
}
}
//防止出现空指针异常~~~！！！
wppId = prWorkpiece.getWppId();
}
//（二）【外协日期】：----Date
if (StringUtils.isNotBlank(twoCell)) {
try {
ooDate = DateTime.parse(ExcelUtils.excelStr2NormString(twoCell));
oeOemOrderDetailExcelDto.setOoDate(ooDate);
} catch (Exception e) {
throw new BusinessException("第【 3 】行 L 列的外协日期格式有误，请修改该数据的格式。 ");
}
}
//[TODO]: 工序类型没有完全利用
//（三）【工序类型】：
// if (StringUtils.isNotBlank(threeCell)) {
// if (!"全工序".equals(threeCell) && !"多工序".equals(threeCell)) {
oeOemOrderDetail.getPjMold().setPcode( threeCell );
// }
// }
//（四）【外协商】：
if (StringUtils.isNotBlank(fourCell)) {
//首先需要根据外协商来进行查找
customer = coOemCustomerService.queryByCustomerName(fourCell);
if (integer == 1) {
if (customer == null) {
throw new BusinessException("外协商 在数据库中不存在，请检查该项数据。 ");
}
}
oeOemOrderDetailExcelDto.setCoOemCustomer(customer);
}
//（3）【物料名称】：
goodsName = oeOemOrderDetailExcelImport.getGoodsName();
if (goodsName != null) {
oeOemOrderDetailExcelDto.setGoodsName(goodsName);
}
//(4)【件数】：
String quantityStr = oeOemOrderDetailExcelImport.getQuantity();
try {
quantity = Double.valueOf(quantityStr);
if (quantity != null) {
oeOemOrderDetailExcelDto.setQuantity(quantity);
}
} catch (NumberFormatException e) {
throw new BusinessException(" 第【 " + (count + 7) + " 】行， E 列件数的数字格式有误！！！ ");
}
//(5)【计价方式】：
String priceTypeStr = oeOemOrderDetailExcelImport.getPriceType();
if (StringUtils.isNotBlank(priceTypeStr)) {
// 需要进行一下转换 ===》 该字段是设置数字的，而用户传过来的是文字~ ~ ~
HashMap<String, Short> hashMap = new HashMap<>();
CoProcess.priceTypeMap.forEach((k, v) -> {
hashMap.put(v, k);
});
type = hashMap.get(priceTypeStr);
if (integer == 1) {
if (type == null) {
throw new BusinessException("第【 " + (count + 7) + " 】行， F 列 计价方式 的格式有误！！！ ");
}
}
oeOemOrderDetailExcelDto.setPriceType(type);
}
//（6）【计价数量】：
String priceNum = oeOemOrderDetailExcelImport.getPriceNum();
try {
priceNumCount = Double.valueOf(priceNum);
if (priceNumCount != null) {
oeOemOrderDetailExcelDto.setCalculateNum(priceNumCount);
}
} catch (NumberFormatException e) {
throw new BusinessException("第【 " + (count + 7) + " 】行， G 列 计价数量 的数字格式有误！！！ ");
}
//(7)【规格】：
goodsType = oeOemOrderDetailExcelImport.getGoodsType();
if (goodsType != null) {
oeOemOrderDetailExcelDto.setGoodsType(goodsType);
}
//(8)【材质】：
gcName = oeOemOrderDetailExcelImport.getGcName();
if (gcName != null) {
oeOemOrderDetailExcelDto.setGcName(gcName);
}
//(9)【加工要求】：
memo = oeOemOrderDetailExcelImport.getMemo();
if (memo != null) {
oeOemOrderDetailExcelDto.setMemo(memo);
}
//(10)【单价（含税）】：
String containsTaxStr = oeOemOrderDetailExcelImport.getContainsTax();
try {
containsTax = Double.valueOf(containsTaxStr);
if (containsTax != null) {
oeOemOrderDetailExcelDto.setContainsTax(containsTax);
}
} catch (NumberFormatException e) {
throw new BusinessException("第【 " + (count + 7) + " 】行， K 列 单价（含税） 的数字格式有误！！！ ");
}
//(11)【单价（未税）】：
String noContainsTaxStr = oeOemOrderDetailExcelImport.getNoContainsTax();
if (StringUtils.isNotBlank(noContainsTaxStr)) {
try {
noContainsTax = Double.valueOf(noContainsTaxStr);
if (noContainsTax != null) {
oeOemOrderDetailExcelDto.setNoContainsTax(noContainsTax);
}
} catch (NumberFormatException e) {
throw new BusinessException("第【 " + (count + 7) + " 】行， L 列 单价（未税） 的数字格式有误！！！ ");
}
}
//(12)【小计价格】：
String minPriceStr = oeOemOrderDetailExcelImport.getMinPrice();
if (StringUtils.isNotBlank(minPriceStr)) {
try {
minPrice = Double.valueOf(minPriceStr);
if (minPrice != null) {
oeOemOrderDetailExcelDto.setMinPrice(minPrice);
}
} catch (NumberFormatException e) {
throw new BusinessException("第【 " + (count + 7) + " 】行， M 列 小计价格 的数字格式有误！！！ ");
}
}
//（13）【交期】：
deliveryDate = oeOemOrderDetailExcelImport.getDeliveryDate();
if (deliveryDate != null) {
oeOemOrderDetailExcelDto.setDeliveryDate(deliveryDate);
}
//如果用户未填写的话，这一个格子会装上null
ddtoList.add(oeOemOrderDetailExcelDto);
List<OeOemOrderDetail> oeOemOrderDetailList = ooOemOrderDetailService.listByThreeId(ooId, mid, wppId);
Collections.sort(oeOemOrderDetailList, new Comparator<OeOemOrderDetail>() {
@Override
public int compare(OeOemOrderDetail o1, OeOemOrderDetail o2) {
return o2.getPrWorkPlan().getOrders() - o1.getPrWorkPlan().getOrders();
}
});
//如此一来，第一条一定是最大的（orders）；
dspartMap.put(dsPartMcode, oeOemOrderDetailList);
//第一条仍然是工序号最大的，第一条设置上价格，其余全设置0即可~ ~ ~
strhashMap.put(1, "F");
int ifSaved = 0;
if (integer == 1) { //第二次进来就不用这个检验了~ ~ ~ ！！！
StringBuilder message = new StringBuilder("[ ");
for (int i = 0; i < oeOemOrderDetailList.size(); i++) {
OeOemOrderDetail orderDetail = oeOemOrderDetailList.get(i);
if (orderDetail.getTaxPrice() != null || orderDetail.getNotaxPrice() != null || orderDetail.getSubtotalPrice() != null
|| orderDetail.getCalculateNum() != null) {
ifSaved++;
}
}
if (ifSaved > 0) {
message.append(count + 7);
message.append(",");
}
String beforeStr = " ";
//数据库全是NULL的话 ， 这一段就不进去~ ~ ~ ！ ！ ！
if (ifSaved > 0 && strhashMap != null) {
if (strhashMap.size() > 1) {
beforeStr = strhashMap.get(2);
beforeStr += " , " + (count + 7);
} else {
beforeStr += (count + 7);
}
strhashMap.put(2, beforeStr);
}
}
//先把ExcelModel类中的数据过一遍，确保全部没问题；---> 再一口气进行存储！！！
dto.setDspartKeyMap(dspartMap);
dto.setMessageHashmap(strhashMap);
dto.setDetailDtoList(ddtoList);
return strhashMap;
}
【5】：insertInfo
根据dto的两个属性 ， set完成即可 ~
@Override
public void insertInfo(OeOemOrderDetailDto dto) throws Exception {
List<OeOemOrderDetailExcelDto> excelList = dto.getDetailDtoList();
Map<String, List<OeOemOrderDetail>> dspartKeyMap = dto.getDspartKeyMap();
PfUserInfo priceAddMan = dto.getPfUserInfo();
Date priceAddDate = new Date(System.currentTimeMillis());
Date ooDate = null;
CoOemCustomer customer = null;
String goodsName = null;
Double quantity = null;
Short priceType = null;
Double calculateNum = null;
Double containsTax = null;
String goodsType = null;
String gcName = null;
String memo = null;
Double noContainsTax = null;
Double minPrice = null;
Date deliveryDate = null;
for (int i = 0; i < excelList.size(); i++) {
OeOemOrderDetailExcelDto excelDto = excelList.get(i);
//【1】： ooDate
//【2】： customer
//【3】： goodsName
//【4】： quantity
//【5】： priceType
//【6】： calculateNum
//【7】： goodsType
//【8】： gcName
//【9】： memo
//【10】： deliveryDate
ooDate = excelDto.getOoDate();
customer = excelDto.getCoOemCustomer();
goodsName = excelDto.getGoodsName();
quantity = excelDto.getQuantity();
priceType = excelDto.getPriceType();
calculateNum = excelDto.getCalculateNum();
goodsType = excelDto.getGoodsType();
gcName = excelDto.getGcName();
memo = excelDto.getMemo();
deliveryDate = excelDto.getDeliveryDate();
minPrice = excelDto.getMinPrice();
containsTax = excelDto.getContainsTax();
noContainsTax = excelDto.getNoContainsTax();
String dspartCode = excelList.get(i).getDsPartMcode();
List<OeOemOrderDetail> list = dspartKeyMap.get(dspartCode);
for (int j = 0; j < list.size(); j++) {
OeOemOrderDetail orderDetail = list.get(j);
if (ooDate != null) {
orderDetail.getOeOemOrder().setOoDate(ooDate);
}
if (customer != null) {
orderDetail.getOeOemOrder().setCoOemCustomer(customer);
}
if (goodsName != null) {
orderDetail.getPrWorkpiece().getDsPart().getCoGoods().setGoodsName(goodsName);
}
if (quantity != null) {
orderDetail.setQuantity(quantity);
}
if (calculateNum != null) {
orderDetail.setCalculateNum(calculateNum);
}
if (priceType != null) {
orderDetail.getPrWorkPlan().getCoProcess().setPriceType(priceType);
}
if (goodsType != null) {
orderDetail.getPrWorkpiece().getDsPart().getCoGoods().setGoodsType(goodsType);
}
if (gcName != null) {
orderDetail.getPrWorkpiece().getDsPart().getCoGoods().getCoGoodsClass().setGcName(gcName);
}
if (memo != null) {
orderDetail.getPrWorkpiece().getDsPart().setMemo(memo);
}
if (deliveryDate != null) {
orderDetail.setDeliveryDate(deliveryDate);
}
if (minPrice != null) {
orderDetail.setSubtotalPrice(minPrice);
}
if (j == 0) { //第一条数据是工序号最大的，这个时候设置上价格，其余的数据全部设置为0即可；
//(10)【单价（含税）】：
if (containsTax != null) {
//containsTax 进行存储————存储到数据库里；
orderDetail.setTaxPrice(containsTax);
}
//(11)【单价（未税）】：
if (noContainsTax != null) {
//noContainsTax 进行存储————存储到数据库里；
orderDetail.setNotaxPrice(noContainsTax);
}
} else {
orderDetail.setTaxPrice(0.0);
orderDetail.setNotaxPrice(0.0);
}
if ( priceAddMan != null ){
orderDetail.setPriceAddMan( priceAddMan );
}
orderDetail.setPriceAddDate( priceAddDate );
this.update(orderDetail);
}
}
}
【 END 】：DTO
【OODExcelImport】：
/**
* @author YGY
* @version 1.0
* @date 2022/7/7 10:41
*/
public class OeOemOrderDetailExcelImport {
/**
* ********模具号
*/
@ExcelProperty(index = 1)
private String pjMoldMcode;
/**
* ********球标号
*/
@ExcelProperty(index = 2)
private String dsPartMcode ;
/**
*物料名称
*/
@ExcelProperty(index = 3)
private String goodsName ;
/**
* *******件数
*/
@ExcelProperty(index = 4)
private String quantity;
/**
*计价方式
*/
@ExcelProperty(index = 5)
private String priceType;
/**
* ********计价数量
*/
@ExcelProperty(index = 6)
private String priceNum;
/**
*规格
*/
@ExcelProperty(index = 7)
private String goodsType;
/**
*材质
*/
@ExcelProperty(index = 8)
private String gcName;
/**
*加工要求
*/
@ExcelProperty(index = 9)
private String memo;
/**
* ********单价（含税）
*/
@ExcelProperty(index = 10)
private String containsTax;
/**
*单价（未税）
*/
@ExcelProperty(index = 11)
private String noContainsTax;
/**
* ********小计价格
*/
@ExcelProperty(index = 12)
private String minPrice;
/**
*交期
*/
@ExcelProperty(index = 13)
@DateTimeFormat("yyyy/MM/dd")
private Date deliveryDate;
/**
*外协单号
*/
private String ooCode;
/**
*外协日期
*/
private String ooDate;
/**
*工序类型
*/
private String processName;
/**
*外协商
*/
private String coOemCustomer;
public String getPjMoldMcode() {
return pjMoldMcode;
}
public void setPjMoldMcode(String pjMoldMcode) {
this.pjMoldMcode = pjMoldMcode;
}
public String getDsPartMcode() {
return dsPartMcode;
}
public void setDsPartMcode(String dsPartMcode) {
this.dsPartMcode = dsPartMcode;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
public String getQuantity() {
return quantity;
}
public void setQuantity(String quantity) {
this.quantity = quantity;
}
public String getPriceType() {
return priceType;
}
public void setPriceType(String priceType) {
this.priceType = priceType;
}
public String getPriceNum() {
return priceNum;
}
public void setPriceNum(String priceNum) {
this.priceNum = priceNum;
}
public String getGoodsType() {
return goodsType;
}
public void setGoodsType(String goodsType) {
this.goodsType = goodsType;
}
public String getGcName() {
return gcName;
}
public void setGcName(String gcName) {
this.gcName = gcName;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public String getContainsTax() {
return containsTax;
}
public void setContainsTax(String containsTax) {
this.containsTax = containsTax;
}
public String getNoContainsTax() {
return noContainsTax;
}
public void setNoContainsTax(String noContainsTax) {
this.noContainsTax = noContainsTax;
}
public String getMinPrice() {
return minPrice;
}
public void setMinPrice(String minPrice) {
this.minPrice = minPrice;
}
public Date getDeliveryDate() {
return deliveryDate;
}
public void setDeliveryDate(Date deliveryDate) {
this.deliveryDate = deliveryDate;
}
public String getOoCode() {
return ooCode;
}
public void setOoCode(String ooCode) {
this.ooCode = ooCode;
}
public String getOoDate() {
return ooDate;
}
public void setOoDate(String ooDate) {
this.ooDate = ooDate;
}
public String getProcessName() {
return processName;
}
public void setProcessName(String processName) {
this.processName = processName;
}
public String getCoOemCustomer() {
return coOemCustomer;
}
public void setCoOemCustomer(String coOemCustomer) {
this.coOemCustomer = coOemCustomer;
}
}
【OODExcelDto】：
/**
* @author YGY
* @version 1.0
* @date 2022/7/13 16:52
*/
public class OeOemOrderDetailExcelDto {
/**
*外协日期
*/
private Date ooDate=null;
/**
*外协商
*/
private CoOemCustomer coOemCustomer=null;
/**
* ********球标号
*/
private String dsPartMcode =null;
/**
*物料名称
*/
private String goodsName =null;
/**
* *******件数
*/
private Double quantity=null;
/**
*计价方式
*/
private Short priceType=null;
/**
* ********计价数量
*/
private Double calculateNum=null;
/**
*规格
*/
private String goodsType=null;
/**
*材质
*/
private String gcName=null;
/**
*加工要求
*/
private String memo=null;
/**
* ********单价（含税）
*/
private Double containsTax=null;
/**
*单价（未税）
*/
private Double noContainsTax=null;
/**
* ********小计价格
*/
private Double minPrice=null;
/**
*交期
*/
private Date deliveryDate=null;
/**
*get / set
*/
public Date getOoDate() {
return ooDate;
}
public void setOoDate(Date ooDate) {
this.ooDate = ooDate;
}
public CoOemCustomer getCoOemCustomer() {
return coOemCustomer;
}
public void setCoOemCustomer(CoOemCustomer coOemCustomer) {
this.coOemCustomer = coOemCustomer;
}
public String getDsPartMcode() {
return dsPartMcode;
}
public void setDsPartMcode(String dsPartMcode) {
this.dsPartMcode = dsPartMcode;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
public Double getQuantity() {
return quantity;
}
public void setQuantity(Double quantity) {
this.quantity = quantity;
}
public Short getPriceType() {
return priceType;
}
public void setPriceType(Short priceType) {
this.priceType = priceType;
}
public Double getCalculateNum() {
return calculateNum;
}
public void setCalculateNum(Double calculateNum) {
this.calculateNum = calculateNum;
}
public String getGoodsType() {
return goodsType;
}
public void setGoodsType(String goodsType) {
this.goodsType = goodsType;
}
public String getGcName() {
return gcName;
}
public void setGcName(String gcName) {
this.gcName = gcName;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public Double getContainsTax() {
return containsTax;
}
public void setContainsTax(Double containsTax) {
this.containsTax = containsTax;
}
public Double getNoContainsTax() {
return noContainsTax;
}
public void setNoContainsTax(Double noContainsTax) {
this.noContainsTax = noContainsTax;
}
public Double getMinPrice() {
return minPrice;
}
public void setMinPrice(Double minPrice) {
this.minPrice = minPrice;
}
public Date getDeliveryDate() {
return deliveryDate;
}
public void setDeliveryDate(Date deliveryDate) {
this.deliveryDate = deliveryDate;
}
}
【OOODDto】：
package com.videasoft.vmes.m.design.model.spec;
import com.videasoft.vmes.m.oem.model.OeOemOrderDetail;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @author YGY
* @version 1.0
* @date 2022/7/13 13:49
*/
public class OeOemOrderDetailDto {
private boolean ifCheck = false;
//控制前台是否弹出弹框；
private List<OeOemOrderDetailExcelDto> detailDtoList = new ArrayList<>();
//为后面插入方法做准备；
//（将EXCEL模板类 ==> 稍微做浅一层的封装 ）
private List<OeOemOrderDetailExcelImport> excelList = new ArrayList<>();
//原EXCEL模型类List；
private HashMap<Integer,String> messageHashmap = new HashMap<>();
//如果将要覆盖==》前台提示用户；
private HashMap<String , List<OeOemOrderDetail>> dspartKeyMap = new HashMap<>();
//零件码为KEY；
public List<OeOemOrderDetailExcelImport> getExcelList() {
return excelList;
}
public void setExcelList(List<OeOemOrderDetailExcelImport> excelList) {
this.excelList = excelList;
}
public HashMap<String, List<OeOemOrderDetail>> getDspartKeyMap() {
return dspartKeyMap;
}
public void setDspartKeyMap(HashMap<String, List<OeOemOrderDetail>> dapartKeyMap) {
this.dspartKeyMap = dapartKeyMap;
}
public boolean getIfCheck() {
return ifCheck;
}
public OeOemOrderDetailDto() {
}
public void setIfCheck(boolean ifCheck) {
this.ifCheck = ifCheck;
}
public List<OeOemOrderDetailExcelDto> getDetailDtoList() {
return detailDtoList;
}
public void setDetailDtoList(List<OeOemOrderDetailExcelDto> detailDtoList) {
this.detailDtoList = detailDtoList;
}
public HashMap<Integer, String> getMessageHashmap() {
return messageHashmap;
}
public void setMessageHashmap(HashMap<Integer, String> messageHashmap) {
this.messageHashmap = messageHashmap;
}
}
【OOOD】： Model类
package com.videasoft.vmes.m.oem.model;
import com.alibaba.fastjson.annotation.JSONField;
import com.videasoft.utils.date.DateTime;
import com.videasoft.vmes.m.produce.model.PrWorkPlan;
import com.videasoft.vmes.m.produce.model.PrWorkpiece;
import com.videasoft.sysmgr.model.PfUserInfo;
import com.videasoft.vmes.m.project.model.PjMold;
import com.videasoft.webframework.based.HBaseModel;
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import javax.persistence.*;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
@Entity
@Cache(usage = CacheConcurrencyStrategy.NONE)
@Table(name = "oe_oem_order_detail")
@DynamicUpdate
@DynamicInsert
public class OeOemOrderDetail extends HBaseModel {
/**
* @Fields
*/
private static final long serialVersionUID = -8064962066129297649L;
/**
* @Fields bcId : 流水号
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ood_id")
private Long oodId;
/**
* @Fields oeOemOrder : 外协订单
*/
@ManyToOne
@JoinColumn(name = "oo_id")
private OeOemOrder oeOemOrder;
/**
* @Fields oeOemTask : 外协任务
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ot_id")
private OeOemTask oeOemTask;
/**
* @Fields pjMold : 模具
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "mid")
private PjMold pjMold;
/**
* @Fields prWorkpiece : 工件
*/
@ManyToOne
@JoinColumn(name = "wpp_id")
private PrWorkpiece prWorkpiece;
/**
* @Fields prWorkpiece : 工件
*/
// @ManyToOne(fetch=FetchType.LAZY)
// @JoinColumn(name = "wpp_id")
// private PrOtherWork prOtherWork;
/**
* @Fields prWorkPlan : 生产作业计划
*/
@ManyToOne
@JoinColumn(name = "wp_id")
private PrWorkPlan prWorkPlan;
/**
* @Fields quantity : 数量
*/
@Column(name = "quantity", precision = 18, scale = 6, nullable = false)
private Double quantity;
/**
* @Fields quantity : 计价数量
*/
@Column(name = "calculate_num", precision = 18, scale = 6, nullable = false)
private Double calculateNum;
/**
* @Fields deliveryDate : 要求交货日期
*/
@Column(name = "delivery_date")
@JSONField(format = "yyyy-MM-dd")
private Date deliveryDate;
/**
* @Fields allWeight : 估计总重
*/
@Column(name = "all_weight", precision = 18, scale = 6)
private Double allWeight;
/**
* @Fields allHour : 估计总工时
*/
@Column(name = "all_hour", precision = 18, scale = 6)
private Double allHour;
/**
* @Fields allMoney : 估计费用
*/
@Column(name = "all_money", precision = 18, scale = 6)
private Double allMoney;
/**
* @Fields factDeliveryDate : 实际到货日期
*/
@Column(name = "fact_delivery_date")
@JSONField(format = DATEFORMAT_DATE)
private Date factDeliveryDate;
/**
* @Fields factWeight : 实际总重
*/
@Column(name = "fact_weight", precision = 18, scale = 6)
private Double factWeight;
/**
* @Fields factHour : 实际总工时
*/
@Column(name = "fact_hour", precision = 18, scale = 6)
private Double factHour;
/**
* @Fields factMoney : 实际费用
*/
@Column(name = "fact_money", precision = 18, scale = 6)
private Double factMoney;
/**
* @Fields factMoney : 单价（含税）
*/
@Column(name = "tax_price", precision = 18, scale = 6)
private Double taxPrice;
/**
* @Fields factMoney : 单价（未税）
*/
@Column(name = "notax_price", precision = 18, scale = 6)
private Double notaxPrice;
/**
* @Fields factMoney : 小计价格
*/
@Column(name = "subtotal_price", precision = 18, scale = 6)
private Double subtotalPrice;
/**
* @Fields curStatus : 状态
*/
@Column(name = "cur_status")
private Short curStatus;
/**
* @Fields checkageDate : 对账日期
*/
@Column(name = "checkage_date")
@JSONField(format = DATEFORMAT_DATE)
private Date checkageDate;
/**
* @Fields sendDate : 送出日期
*/
@Column(name = "send_date")
@JSONField(format = DATEFORMAT_DATE)
private Date sendDate;
/**
* @Fields sendCheckDate :送检日期
*/
@Column(name = "send_check_date")
@JSONField(format = DATEFORMAT_DATE)
private Date sendCheckDate;
/**
* @Fields taxMoney : 税金
*/
@Column(name = "tax_money", precision = 18, scale = 6)
private Double taxMoney;
/**
* @Fields continueSend : 继续送出
*/
@Column(name = "continue_send", precision = 18, scale = 6)
private Boolean continueSend;
/**
* @Fields weight : 重量
*/
@Column(name = "weight", precision = 18, scale = 6)
private Double weight;
/**
* @Fields area : 面积
*/
@Column(name = "area", precision = 18, scale = 6)
private Double area;
/**
* @Fields proTime : 加工工时
*/
@Column(name = "pro_time", precision = 18, scale = 6)
private Double proTime;
/**
* @Fields signRed : 在订单跟踪时是否标红
*/
@Transient
private boolean signRed = false;
/**
* @Fields state:生产退料状态
*/
@Column(name = "state")
private Boolean state = false;
/**
* @Fields picstate:生产领料状态
*/
@Column(name = "picstate")
private Boolean picState = false;
/**
* @Fields repState:生产超耗领料状态
*/
@Column(name = "repState")
private Boolean repState = false;
/**
* @Fields deliveryMan:收回人员
*/
@ManyToOne
@JoinColumn(name = "delivery_man")
private PfUserInfo deliveryMan;
public static Map<Short, String> curStatusMap = new HashMap<Short, String>();
/**
* 未送出
*/
public static Short CUR_STATUS_CREATE = -1;
/**
* 已送出
*/
public static Short CUR_STATUS_SC = 0;
/**
* 已收回
*/
public static Short CUR_STATUS_SH = 1;
/**
* 已送检
*/
public static Short CUR_STATUS_SJ = 2;
/**
* 已验收
*/
public static Short CUR_STATUS_YS = 3;
/**
* 需返工
*/
public static Short CUR_STATUS_FG = 4;
/**
* 已报废
*/
public static Short CUR_STATUS_BF = 5;
static {
curStatusMap.put(CUR_STATUS_SC, "已送出");
curStatusMap.put(CUR_STATUS_SH, "已收回");
curStatusMap.put(CUR_STATUS_SJ, "已送检");
curStatusMap.put(CUR_STATUS_YS, "已验收");
curStatusMap.put(CUR_STATUS_FG, "需返工");
curStatusMap.put(CUR_STATUS_BF, "已报废");
}
// Constructors
public Double getCalculateNum() {
return calculateNum;
}
public void setCalculateNum(Double calculateNum) {
this.calculateNum = calculateNum;
}
public Double getTaxPrice() {
return taxPrice;
}
public void setTaxPrice(Double taxPrice) {
this.taxPrice = taxPrice;
}
public Double getNotaxPrice() {
return notaxPrice;
}
public void setNotaxPrice(Double notaxPrice) {
this.notaxPrice = notaxPrice;
}
public Double getSubtotalPrice() {
return subtotalPrice;
}
public void setSubtotalPrice(Double subtotalPrice) {
this.subtotalPrice = subtotalPrice;
}
/**
* default constructor
*/
public OeOemOrderDetail() {
}
public Boolean getRepState() {
return repState;
}
public void setRepState(Boolean repState) {
this.repState = repState;
}
public Boolean getPicState() {
return picState;
}
public void setPicState(Boolean picState) {
this.picState = picState;
}
public Boolean getState() {
return state;
}
public void setState(Boolean state) {
this.state = state;
}
public String getCurStatusTitle() {
String curStatusTitle = curStatusMap.get(this.curStatus);
if (CUR_STATUS_SC.equals(curStatus)) {
return curStatusTitle += "(" + DateTime.date2String(sendDate) + ")";
}
return curStatusTitle;
}
public Long getOodId() {
return oodId;
}
public void setOodId(Long oodId) {
this.oodId = oodId;
}
public OeOemOrder getOeOemOrder() {
return oeOemOrder;
}
public void setOeOemOrder(OeOemOrder oeOemOrder) {
this.oeOemOrder = oeOemOrder;
}
public OeOemTask getOeOemTask() {
return oeOemTask;
}
public void setOeOemTask(OeOemTask oeOemTask) {
this.oeOemTask = oeOemTask;
}
public PjMold getPjMold() {
return pjMold;
}
public void setPjMold(PjMold pjMold) {
this.pjMold = pjMold;
}
public PrWorkpiece getPrWorkpiece() {
return prWorkpiece;
}
public void setPrWorkpiece(PrWorkpiece prWorkpiece) {
this.prWorkpiece = prWorkpiece;
}
public Double getQuantity() {
return quantity;
}
public void setQuantity(Double quantity) {
this.quantity = quantity;
}
public Date getDeliveryDate() {
return deliveryDate;
}
public void setDeliveryDate(Date deliveryDate) {
this.deliveryDate = deliveryDate;
}
public Double getAllWeight() {
return allWeight;
}
public void setAllWeight(Double allWeight) {
this.allWeight = allWeight;
}
public Double getAllHour() {
return allHour;
}
public void setAllHour(Double allHour) {
this.allHour = allHour;
}
public Double getAllMoney() {
return allMoney;
}
public void setAllMoney(Double allMoney) {
this.allMoney = allMoney;
}
public Date getFactDeliveryDate() {
return factDeliveryDate;
}
public void setFactDeliveryDate(Date factDeliveryDate) {
this.factDeliveryDate = factDeliveryDate;
}
public Double getFactWeight() {
return factWeight;
}
public void setFactWeight(Double factWeight) {
this.factWeight = factWeight;
}
public Double getFactHour() {
return factHour;
}
public void setFactHour(Double factHour) {
this.factHour = factHour;
}
public Double getFactMoney() {
return factMoney;
}
public void setFactMoney(Double factMoney) {
this.factMoney = factMoney;
}
public Short getCurStatus() {
return curStatus;
}
public void setCurStatus(Short curStatus) {
this.curStatus = curStatus;
}
public Date getCheckageDate() {
return checkageDate;
}
public void setCheckageDate(Date checkageDate) {
this.checkageDate = checkageDate;
}
public Date getSendDate() {
return sendDate;
}
public void setSendDate(Date sendDate) {
this.sendDate = sendDate;
}
public Date getSendCheckDate() {
return sendCheckDate;
}
public void setSendCheckDate(Date sendCheckDate) {
this.sendCheckDate = sendCheckDate;
}
public Double getTaxMoney() {
return taxMoney;
}
public Double getNoTaxMoney() {
if (this.factMoney == null) {
return 0d;
}
if (this.taxMoney == null) {
return this.factMoney;
}
return this.factMoney - this.taxMoney;
}
public void setTaxMoney(Double taxMoney) {
this.taxMoney = taxMoney;
}
public Boolean getContinueSend() {
return continueSend;
}
public void setContinueSend(Boolean continueSend) {
this.continueSend = continueSend;
}
public PrWorkPlan getPrWorkPlan() {
return prWorkPlan;
}
public void setPrWorkPlan(PrWorkPlan prWorkPlan) {
this.prWorkPlan = prWorkPlan;
}
public String getProcessName() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return this.prWorkPlan.getCoProcess().getPname();
} else if (this.pjMold != null) {
return "模具【" + this.pjMold.getMcode() + "】";
}
return "整体外协";
}
public boolean isSignRed() {
return signRed;
}
public void setSignRed(boolean signRed) {
this.signRed = signRed;
}
// 用于前端导出内容展示
public String getExeclWppCode() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return prWorkPlan.getPrWorkpiece().getWppCode();
} else if (this.prWorkpiece != null && this.prWorkpiece.getWppId() != null) {
return prWorkpiece.getWppCode();
}
return "";
}
// 用于前端导出内容展示
public String getExeclPartMname() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return prWorkPlan.getPrWorkpiece().getDsPart().getMname();
} else if (this.prWorkpiece != null && this.prWorkpiece.getWppId() != null) {
return prWorkpiece.getDsPart().getMname();
}
return "";
}
// 用于前端导出内容展示
public String getExeclPartMcode() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return prWorkPlan.getPrWorkpiece().getDsPart().getMcode();
} else if (this.prWorkpiece != null && this.prWorkpiece.getWppId() != null) {
return prWorkpiece.getDsPart().getMcode();
}
return "";
}
// 用于前端导出内容展示
public String getExeclMoldMcode() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return prWorkPlan.getPrWorkpiece().getDsPart().getPjMold().getMcode();
} else if (this.prWorkpiece != null && this.prWorkpiece.getWppId() != null) {
return prWorkpiece.getDsPart().getPjMold().getMcode();
}
return "";
}
// 用于前端导出内容展示
public String getExeclDesignSize() {
if (this.prWorkPlan != null && this.prWorkPlan.getWpId() != null) {
return prWorkPlan.getPrWorkpiece().getDsPart().getDesignSize();
} else if (this.prWorkpiece != null && this.prWorkpiece.getWppId() != null) {
return prWorkpiece.getDsPart().getDesignSize();
}
return "";
}
public Double getWeight() {
return weight;
}
public void setWeight(Double weight) {
this.weight = weight;
}
public Double getArea() {
return area;
}
public void setArea(Double area) {
this.area = area;
}
public Double getProTime() {
return proTime;
}
public void setProTime(Double proTime) {
this.proTime = proTime;
}
public PfUserInfo getDeliveryMan() {
return deliveryMan;
}
public void setDeliveryMan(PfUserInfo deliveryMan) {
this.deliveryMan = deliveryMan;
}
}
【注意】：
【 计价方式 】：
每小时
每千克
无
要增加新的计价方式，就得去修改 公司封装类CoProcess 中的 静态Map信息。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
