package com.gk.firework.Service.ServiceImpl; import com.gk.firework.Domain.BO.SaleOrderDetailInfoBO; import com.google.common.collect.Lists; import java.util.Date; import com.alibaba.fastjson.JSONObject; import com.gk.firework.Domain.*; import com.gk.firework.Domain.Enum.Element; import com.gk.firework.Domain.Enum.EnterpriseSafetySupervision; import com.gk.firework.Domain.Exception.BusinessException; import com.gk.firework.Domain.Utils.*; import com.gk.firework.Domain.Vo.DirectionDetail; import com.gk.firework.Domain.Vo.FireworkDeal; import com.gk.firework.Domain.Vo.Product2JsonVo; import com.gk.firework.Domain.Vo.ProductVo; import com.gk.firework.Service.*; import org.apache.log4j.LogManager; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.management.relation.Role; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Pattern; @Service("ExcelExportService") public class ExcelExportServiceImpl implements ExcelExportService { private Logger logger = LogManager.getLogger(getClass()); String[] districttitle = {"code", "name", "parentcode", "type"}; String[] selfCheckTitle = {"检查类型", "检查内容", "参考判断", "排序", "备注"}; String[] enterpriseTitle = {"企业名称", "单位编号", "安全监管分类", "省", "市", "区", "密码"}; String[] feedTitle = {"反馈内容", "发现时间"}; String[] userTitle = {"用户名", "密码", "单位", "省", "市", "区", "街道", "社区", "角色", "办公电话", "手机号", "邮箱", "部门", "职务"}; String[] userCodeTitle = {"单位", "统一社会信用代码"}; String[] productTitle = {"产品名称", "产品编号", "产品数量"}; String[] contractProductTitle = {"产品名称", "产品编号", "产品数量", "单箱进价"}; String[] productPriceTitle = {"产品名称", "流向码", "售价", "生产厂家", "规格", "含药量", "大类", "小类", "产品级别", "箱含量", "生产日期"}; String[] returnTags = {"电子标签号", "产品名称", "生产厂家", "总装药量", "箱含量", "产品类型", "产品级别", "创建时间", "包装标识码", "进价"}; String[] productDataTitle = {"流向码", "产品名称", "产品大类", "产品小类", "产品级别", "生产厂家", "规格", "含药量", "箱含量", "包装方式", "生产日期"}; String[] companyCodeTitle = {"单位名称", "单位代码"}; String[] saleProductTitle = {"包装标识码"}; String[] saleOrderTitle = {"零售单位名称","购买人身份证号","购买时间","购买数量"}; //正整数 String pattern = "^[0-9]*[1-9][0-9]*$"; Pattern pattern2 = Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$"); @Autowired private DistrictService districtService; @Autowired private SelfCheckService selfCheckService; @Autowired private EnterpriseService enterpriseService; @Autowired private UserService userService; @Autowired private UserRolesService userRolesService; @Autowired private RoleService roleService; @Autowired private EnterpriseFeedService enterpriseFeedService; @Autowired private ProductService productService; @Autowired private ProductPriceService productPriceService; @Autowired private ProductCategoryService productCategoryService; @Autowired private SaleProductService saleProductService; @Autowired private CustomerService customerService; @Autowired private SaleOrderService saleOrderService; @Autowired private ProductLocusService productLocusService; @Override public BooleanReason importDistrictExcel(InputStream in, String operator, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); List districtInfoList = new ArrayList<>(); String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != districttitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((districttitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + districttitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; DistrictInfo districtInfo = new DistrictInfo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,编码不能为空.
"; continue; } else { districtInfo.setCode(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,名称不能为空.
"; continue; } else { districtInfo.setName(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,父编码不能为空.
"; continue; } else { districtInfo.setParentcode(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,类型不能为空.
"; continue; } else { districtInfo.setType(Byte.valueOf(value.toString())); } districtInfoList.add(districtInfo); } for (DistrictInfo districtInfo : districtInfoList) { districtService.save(districtInfo); } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + districtInfoList.size() + "个," + "导入失败" + (sheet.getLastRowNum() - districtInfoList.size()) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public BooleanReason imporSelfCheckExcel(InputStream in, String username, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); List selfCheckInfoList = new ArrayList<>(); String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != selfCheckTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((selfCheckTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; SelfCheckInfo selfCheckInfo = new SelfCheckInfo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,检查类型不能为空.
"; continue; } else { selfCheckInfo.setType(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,检查内容不能为空.
"; continue; } else { selfCheckInfo.setContent(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.
"; continue; } else { selfCheckInfo.setStandard(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { try { selfCheckInfo.setSort(Integer.parseInt(value.toString().trim())); } catch (Exception e) { e.printStackTrace(); } } selfCheckInfo.setCreatedby(username); selfCheckInfo.setModifiedby(username); selfCheckInfo.setCreateddate(new Date()); selfCheckInfo.setModifieddate(new Date()); selfCheckInfo.setIsdel((byte) 0); selfCheckInfoList.add(selfCheckInfo); } selfCheckService.saveBatch(selfCheckInfoList); if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + selfCheckInfoList.size() + "个," + "导入失败" + (sheet.getLastRowNum() - selfCheckInfoList.size()) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public BooleanReason importEnterpriseExcel(InputStream in, UserInfo userInfo, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); List enterpriseList = new ArrayList<>(); Date now = new Date(); String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != enterpriseTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((enterpriseTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Enterprise enterprise = new Enterprise(); Object value = null; row = sheet.getRow(irow); //企业名称 value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,企业名称不能为空.
"; continue; } else if (enterpriseService.isDuplicateEnterpriseName(value.toString().trim(), null)) { retmsg = retmsg + "导入失败,第" + realrow + "行,企业名称重复.
"; continue; } else { enterprise.setEnterprisename(value.toString().trim()); } //单位编号 value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,单位编号不能为空.
"; continue; } else if (enterpriseService.isDuplicateNumber(value.toString().trim(), null)) { retmsg = retmsg + "导入失败,第" + realrow + "行,单位编号重复.
"; continue; } else { enterprise.setEnterprisenumber(value.toString().trim()); } //安全监管分类 //判断监管分类的类型是否能在数据字典找到 value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,安全监管分类不能为空.
"; continue; } else if (!EnterpriseSafetySupervision.isInclude(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,安全监管分类输入错误.
"; continue; } else { enterprise.setSafetysupervision(value.toString().trim()); } //省 value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,省份不能为空.
"; continue; } else { enterprise.setProvince(value.toString().trim()); } //市 value = ExcelProperty.getCellValue(row.getCell(4), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,市不能为空.
"; continue; } else { enterprise.setCity(value.toString().trim()); } //区 value = ExcelProperty.getCellValue(row.getCell(5), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,区不能为空.
"; continue; } else { enterprise.setDistrict(value.toString().trim()); } //密码 String PW_PATTERN = "(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[~!@#$%^&*_.]).{8,}"; value = ExcelProperty.getCellValue(row.getCell(6), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.
"; continue; } else if (!value.toString().matches(PW_PATTERN)) { retmsg = retmsg + "导入失败,第" + realrow + "行,密码必须8位以上,并且包含大小写字母、数字、特殊符号三种以上.
"; continue; } else { enterprise.setPassword(Base64Encrypt.encode(value.toString().getBytes())); } //计算安全监管分类个数 { int i = enterpriseService.countBySafetySupervision(enterprise.getSafetysupervision()); enterprise.setDivideflag((byte) (DivideFlagUtil.enterpriseDivideFlagGenerate(i))); } enterprise.setInfocreatebyname(userInfo.getUsername()); enterprise.setInfocreateby(userInfo.getId()); enterprise.setInfocreatetime(now); enterprise.setInfoupdatetime(now); enterprise.setValidflag(true); String randomStr = Element.random(Element.class).getValue().toString() + Element.random(Element.class).getValue().toString(); while (enterpriseService.isDuplicate2BitCode(randomStr)) { randomStr = Element.random(Element.class).getValue().toString() + Element.random(Element.class).getValue().toString(); } enterprise.setTwobitcode(randomStr); enterpriseService.save(enterprise); enterpriseList.add(enterprise); //保存用户 UserInfo user = new UserInfo(); user.setIsdel((byte) 0); user.setProvince(enterprise.getProvince()); user.setCity(enterprise.getCity()); user.setArea(enterprise.getDistrict()); user.setUsername(enterprise.getEnterprisename()); user.setCompany(enterprise.getEnterprisename()); user.setIssale((byte) 0); //普通用户 user.setType(3); user.setCreatedby(user.getUsername()); user.setCompanyid(enterprise.getId()); user.setCreateddate(new Date()); //设置密码 user.setPassword(enterprise.getPassword()); user.setCompanynumber(enterprise.getEnterprisenumber()); //执行 userService.save(user); //如果有企业角色设置用户角色为企业 UserRolesInfo uri = new UserRolesInfo(); uri.setUserid(user.getId()); if (enterprise.getSafetysupervision().equals(EnterpriseSafetySupervision.PRODUCE.getMsg())) { uri.setRoleid((long) 3); } else { uri.setRoleid((long) 2); } userRolesService.save(uri); //终端机 user.setId(null); user.setUsername(enterprise.getEnterprisenumber()); user.setIssale((byte) 1); user.setStatus((byte) 1); /* Calendar cal = Calendar.getInstance(); cal.setTime(new Date()); cal.add(Calendar.DATE, +365); user.setExpiredate(cal.getTime());*/ userService.save(user); } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + enterpriseList.size() + "个," + "导入失败" + (sheet.getLastRowNum() - enterpriseList.size()) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public BooleanReason importUserExcel(InputStream in, String username, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); List userInfoList = new ArrayList<>(); String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != userTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((userTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; UserInfo userInfo = new UserInfo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,用户名不能为空.
"; continue; } else { userInfo.setUsername(value.toString().trim()); UserInfo userInfoExist = userService.selectByUser(userInfo.getUsername()); if (null != userInfoExist) { retmsg = retmsg + "导入失败,第" + realrow + "行,用户名重复.
"; continue; } } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,密码不能为空.
"; continue; } else { String PW_PATTERN = "(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[~!@#$%^&*_.]).{8,}"; if (!value.toString().trim().matches(PW_PATTERN)) { retmsg = retmsg + "导入失败,第" + realrow + "行,密码必须8位以上,并且包含大小写字母、数字、特殊符号三种以上.
"; continue; } else { userInfo.setPassword(Base64Encrypt.encode(value.toString().trim().getBytes())); } } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,单位不能为空.
"; continue; } else { userInfo.setCompany(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setProvince(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(4), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setCity(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(5), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setArea(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(6), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setTown(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(7), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setCommunity(value.toString().trim()); } Long roleId = null; value = ExcelProperty.getCellValue(row.getCell(8), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { //角色 RoleInfo roleInfo = new RoleInfo(); roleInfo.setName(value.toString().trim()); RoleInfo roleInfoExist = roleService.selectRoleByName(roleInfo); if (roleInfoExist == null) { retmsg = retmsg + "导入失败,第" + realrow + "行,角色不存在.
"; continue; } else { roleId = roleInfoExist.getId(); } } value = ExcelProperty.getCellValue(row.getCell(9), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setPhone(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(10), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { if (value.toString().length() == 11) { userInfo.setMobile(value.toString().trim()); } else { retmsg = retmsg + "导入失败,第" + realrow + "行,手机号必须为11位数.
"; continue; } } value = ExcelProperty.getCellValue(row.getCell(11), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setEmail(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(11), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setDepartment(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(11), evaluator); if (null != value && StringUtils.isNotBlank(value.toString())) { userInfo.setJob(value.toString().trim()); } userInfo.setIsdel((byte) 0); userInfo.setIssale((byte) 0); userInfo.setCreatedby(username); userInfo.setCreateddate(new Date()); userInfo.setLastmodifiedby(username); userInfo.setLastmodifieddate(new Date()); userService.save(userInfo); if (roleId != null) { UserRolesInfo userRolesInfo = new UserRolesInfo(); userRolesInfo.setUserid(userInfo.getId()); userRolesInfo.setRoleid(roleId); userRolesService.save(userRolesInfo); } userInfoList.add(userInfo); } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + userInfoList.size() + "个," + "导入失败" + (sheet.getLastRowNum() - userInfoList.size()) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public List parsingProduct(InputStream inputStream, UserInfo userInfo, boolean isExcel2007, String enterprisenumber) { List productJsons = new ArrayList<>(); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(inputStream); } else { workbook = new HSSFWorkbook(inputStream); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new BusinessException("excel 第1页不能为空"); } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != contractProductTitle.length) { throw new BusinessException("上传文件的列的个数错误"); } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((contractProductTitle[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Product2JsonVo productInfoJson = new Product2JsonVo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); //产品名称 if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[0] + "不能为空."); } //产品编号 value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[1] + "不能为空."); } else { String directionCode = value.toString().trim(); //检查产品编号是否是企业内的产品 int i = productService.countByEnterpriseNumberAndDirectionCode(enterprisenumber, directionCode); assert i <= 1; if (i < 1) { throw new BusinessException("导入失败,第" + realrow + "行," + "产品信息不为所在生产企业的产品.>"); } ProductInfo productInfo = productService.selectByDirection(directionCode); if (productInfo.getIsOld() == 1) { throw new BusinessException("导入失败,第" + realrow + "行," + "产品信息为旧系统产品,已不允许签订合同使用.>"); } productInfoJson.setId(productInfo.getId()); productInfoJson.setName(productInfo.getName()); //使用数据库内容的流向码,区分大小写 productInfoJson.setDirectionCode(productInfo.getDirectionCode()); productInfoJson.setManufacturer(productInfo.getManufacturer()); productInfoJson.setSpecification(productInfo.getSpecification()); productInfoJson.setExplosiveContent(productInfo.getExplosiveContent()); productInfoJson.setType(productInfo.getType()); productInfoJson.setSecondaryType(productInfo.getSecondaryType()); productInfoJson.setLevel(productInfo.getLevel()); //箱含量 productInfoJson.setBoxNumber(productInfo.getBoxNumber()); productInfoJson.setPacking(productInfo.getPacking()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[2] + "不能为空."); } else if (!Pattern.matches(pattern, value.toString().trim())) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[2] + "不为正整数."); } else { productInfoJson.setNum(Integer.valueOf(value.toString().trim())); } value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[3] + "不能为空."); } else if (!pattern2.matcher(value.toString().trim()).matches()) { throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[3] + "不为金额."); } else { productInfoJson.setPrice(new BigDecimal(value.toString().trim())); } productJsons.add(productInfoJson); } } catch (IOException ex) { ex.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } return productJsons; } @Override public List parsingProduct(InputStream inputStream, UserInfo userInfo, boolean isExcel2007) { List productJsons = new ArrayList<>(); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(inputStream); } else { workbook = new HSSFWorkbook(inputStream); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new BusinessException("excel 第1页不能为空"); } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != productTitle.length) { throw new BusinessException("上传文件的列的个数错误"); } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((productTitle[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Product2JsonVo productInfoJson = new Product2JsonVo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); //产品名称 if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[0] + "不能为空."); } //产品编号 value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[1] + "不能为空."); } else { String directionCode = value.toString().trim(); ProductInfo productInfo = productService.selectByDirection(directionCode); productInfoJson.setId(productInfo.getId()); productInfoJson.setName(productInfo.getName()); productInfoJson.setDirectionCode(directionCode); productInfoJson.setManufacturer(productInfo.getManufacturer()); productInfoJson.setSpecification(productInfo.getSpecification()); productInfoJson.setExplosiveContent(productInfo.getExplosiveContent()); productInfoJson.setType(productInfo.getType()); productInfoJson.setSecondaryType(productInfo.getSecondaryType()); productInfoJson.setLevel(productInfo.getLevel()); //箱含量 productInfoJson.setBoxNumber(productInfo.getBoxNumber()); productInfoJson.setPacking(productInfo.getPacking()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[2] + "不能为空."); } else if (!Pattern.matches(pattern, value.toString().trim())) { throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[2] + "不为正整数."); } else { productInfoJson.setNum(Integer.valueOf(value.toString().trim())); } productJsons.add(productInfoJson); } } catch (IOException ex) { ex.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } return productJsons; } @Override public BooleanReason imporEditPriceExcel(InputStream in, String companynumber, String username, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); List productPriceInfoList = new ArrayList<>(); String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != productPriceTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((productPriceTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + productPriceTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; ProductPriceInfo productPriceInfo = new ProductPriceInfo(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,流向码不能为空.
"; continue; } else { productPriceInfo.setItemcode(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,售价不能为空.
"; continue; } else { try { productPriceInfo.setPrice(new BigDecimal(value.toString().trim())); } catch (Exception e) { e.printStackTrace(); } } productPriceInfo.setCompanynumber(companynumber); productPriceInfo.setUpdated(new Date()); productPriceInfo.setUpdator(username); productPriceInfoList.add(productPriceInfo); } for (ProductPriceInfo productInfo : productPriceInfoList) { ProductPriceInfo productPriceInfo = productPriceService.selectByCode(companynumber, productInfo.getItemcode()); if (productPriceInfo != null) { productPriceInfo.setPrice(productInfo.getPrice()); productPriceInfo.setUpdated(new Date()); productPriceInfo.setUpdator(username); productPriceService.updateById(productPriceInfo); } else { ProductPriceInfo productPrice = new ProductPriceInfo(); productPrice.setCompanynumber(companynumber); productPrice.setItemcode(productInfo.getItemcode()); productPrice.setPrice(productInfo.getPrice()); productPrice.setUpdated(new Date()); productPrice.setUpdator(username); productPriceService.save(productPrice); } } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + productPriceInfoList.size() + "个," + "导入失败" + (sheet.getLastRowNum() - productPriceInfoList.size()) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } /** * @Description: 退货电子标签数据解析 * @date 2021/6/3 17:15 */ @Override public List parseProductCode(InputStream in, UserInfo userInfo, boolean isExcel2007) { Workbook workbook = null; try { if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new BusinessException("excel 第1页不能为空"); } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != returnTags.length) { throw new BusinessException("上传文件的列的个数错误"); } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((returnTags[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); } } List allReturnTags = new ArrayList<>(); for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; EnterpriseFeed feed = new EnterpriseFeed(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行,电子标签不能为空.
"); } else { allReturnTags.add(value.toString().trim()); } } return allReturnTags; } catch (IOException e) { e.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } } /** * @Description: 旧系统导入产品信息 * @date 2021/6/30 12:29 */ @Override public List parseProductFromOldSystem(InputStream in, UserInfo user, boolean isExcel2007) { try { Workbook workbook = null; if (isExcel2007) workbook = new XSSFWorkbook(in); if (!isExcel2007) workbook = new HSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) throw new BusinessException("excel 第1页不能为空"); Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != productDataTitle.length) throw new BusinessException("上传文件的列的个数错误"); for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((productDataTitle[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + productDataTitle[i]); } } List result = new ArrayList<>(); ProductInfo product = null; for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Object value = null; product = new ProductInfo(); row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "不能为空."); } else { //流向码 String productCode = value.toString().trim(); product.setDirectionCode(productCode); if (productCode.length() != 10) throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "长度不为10."); //重复判断 List productInfosExist = productService.selectByProduct(product); if (productInfosExist != null && productInfosExist.size() > 0) throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "已经存在."); } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[1] + "不能为空."); } else { //产品名称 String productName = value.toString().trim(); product.setName(productName); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[2] + "不能为空."); } else { //产品大类 String firstType = value.toString().trim(); product.setType(firstType); } value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[3] + "不能为空."); } else { //产品小类 String secondaryType = value.toString().trim(); product.setSecondaryType(secondaryType); } value = ExcelProperty.getCellValue(row.getCell(4), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[4] + "不能为空."); } else { //产品级别 String level = value.toString().trim(); product.setLevel(level); } value = ExcelProperty.getCellValue(row.getCell(5), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[5] + "不能为空."); } else { //生产厂家 String manufacturer = value.toString().trim(); Enterprise enterprise = enterpriseService.selectEnterpriseByName(manufacturer); //查不到企业 或者 不为生产企业 if (enterprise == null || !EnterpriseSafetySupervision.PRODUCE.getMsg().equals(enterprise.getSafetysupervision())) throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[5] + "查找不到企业名称或者不为生产企业."); product.setManufacturer(manufacturer); product.setCompanyNumber(enterprise.getEnterprisenumber()); } value = ExcelProperty.getCellValue(row.getCell(6), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[6] + "不能为空."); } else { //规格 String specification = value.toString().trim(); product.setSpecification(specification); } value = ExcelProperty.getCellValue(row.getCell(7), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "不能为空."); } else { //含药量 String explosiveContentStr = value.toString().trim(); try { BigDecimal explosiveContent = new BigDecimal(explosiveContentStr); product.setExplosiveContent(explosiveContent); //正数 if (explosiveContent.compareTo(BigDecimal.ZERO) < 1) throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "需要大于等于0."); } catch (NumberFormatException e) { e.printStackTrace(); throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "不合法."); } } value = ExcelProperty.getCellValue(row.getCell(8), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "不能为空."); } else { //箱含量 String boxNumberStr = value.toString().trim(); try { int boxNumber = Integer.parseInt(boxNumberStr); product.setBoxNumber(boxNumber); if (boxNumber < 1) throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "需要大于0."); } catch (NumberFormatException e) { e.printStackTrace(); throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "不合法."); } } value = ExcelProperty.getCellValue(row.getCell(9), evaluator); if (null != value && StringUtils.isBlank(value.toString())) { //包装方式 String packing = value.toString().trim(); product.setPacking(packing); } value = ExcelProperty.getCellValue(row.getCell(10), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[10] + "不能为空."); } else { //生产日期 try { String productDateStr = value.toString().trim(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date productDate = sdf.parse(productDateStr); product.setProductDate(productDate); } catch (ParseException e) { e.printStackTrace(); throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[10] + "日期请填写2021-09-27的字符型."); } } //最后校验第一类型和第二类型和产品级别 boolean isExist = productCategoryService.selectOne(product.getType(), product.getSecondaryType(), product.getLevel()); if (!isExist) throw new BusinessException("导入失败,第" + realrow + "行,大小类、级别不合法."); product.setCreatedBy(user.getUsername()); product.setModifiedBy(user.getUsername()); product.setCreatedDate(new Date()); product.setModifiedDate(new Date()); result.add(product); } return result; } catch (IOException e) { e.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } } @Override public BooleanReason importUserCodeExcel(InputStream in, String operator, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); int z = 0; String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != userCodeTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((userCodeTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; UserInfo userInfo = new UserInfo(); Object value = null; row = sheet.getRow(irow); String company = ""; value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,单位名不能为空.
"; continue; } else { company = value.toString().trim(); List userInfos = userService.selectByCompany(company); if (userInfos.size() < 1) { retmsg = retmsg + "导入失败,第" + realrow + "行,单位不存在.
"; continue; } } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,统一社会信用代码不能为空.
"; } else { String code = value.toString().trim(); userService.updateCodeByCompany(company, code); z++; } } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + z + "个," + "导入失败" + (sheet.getLastRowNum() - z) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public BooleanReason importDLCompanyCodeExcel(InputStream in, UserInfo userInfo, boolean isExcel2007) { Date now = new Date(); List updates = new ArrayList<>(); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new BusinessException("excel 第1页不能为空"); } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != companyCodeTitle.length) { throw new BusinessException("上传文件的列的个数错误"); } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((companyCodeTitle[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + companyCodeTitle[i]); } } if (sheet.getLastRowNum() < 1) throw new BusinessException("导入数据为空"); int kk = 0; StringBuffer sb = new StringBuffer(); for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Object value = null; Enterprise upOne = new Enterprise(); row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { sb.append("导入失败,第").append(realrow).append("行,单位名称不能为空.
"); continue; } else { String enterpriseName = value.toString().trim(); Enterprise enterprise = enterpriseService.selectEnterpriseByName(enterpriseName); if (enterprise == null) { sb.append("找不到该企业:").append(enterpriseName).append(",第").append(realrow).append("行"); continue; } upOne.setId(enterprise.getId()); } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { sb.append("导入失败,第").append(realrow).append("行,单位代码不能为空.
"); continue; } else { String code = value.toString().trim(); upOne.setDlcompanycode(code); upOne.setInfoupdatebyname(userInfo.getUsername()); upOne.setInfoupdateby(userInfo.getId()); upOne.setInfoupdatetime(now); } updates.add(upOne); } for (Enterprise e : updates) { enterpriseService.updateCompanyCode(e); } sb.append("成功" + updates.size() + "个,失败" + (sheet.getLastRowNum() - updates.size()) + "个"); BooleanReason booleanReason = new BooleanReason(); booleanReason.addReason(sb.toString()); return booleanReason; } catch (IOException ex) { ex.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } } /** * @Description: 企业反馈导入 * @date 2021/5/13 19:32 */ @Override public void importFeedExcel(InputStream in, UserInfo user, Boolean isExcel2007, Long enterpriseId) { List feeds = new ArrayList<>(); Date now = new Date(); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new BusinessException("excel 第1页不能为空"); } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != feedTitle.length) { throw new BusinessException("上传文件的列的个数错误"); } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((feedTitle[i]))) { throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]); } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; EnterpriseFeed feed = new EnterpriseFeed(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行,反馈内容不能为空.
"); } else { feed.setContent(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { throw new BusinessException("导入失败,第" + realrow + "行,发现时间不能为空.
"); } else if (!DateUtils.isLegalDate(value.toString().length(), value.toString(), "d/M/yy")) { throw new BusinessException("日期格式不正确,应如2020-10-10或者2020/10/10"); } else { java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("d/M/yy"); Date date = sdf.parse(value.toString()); feed.setFindtime(date); } feed.setEnterpriseid(enterpriseId); feed.setCreateby(user.getId()); feed.setCreatetime(now); feed.setCreatebyname(user.getUsername()); feed.setValidflag(true); feeds.add(feed); } enterpriseFeedService.saveBatch(feeds); } catch (ParseException ex) { ex.printStackTrace(); throw new BusinessException("类型转换错误"); } catch (IOException ex) { ex.printStackTrace(); throw new BusinessException("导入失败,请联系管理员"); } } @Override public BooleanReason imporSaleProductExcel(InputStream in, String username, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); int importSize = 0; String retmsg = ""; blret.setValue(false); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != saleProductTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((saleProductTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + saleProductTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; List saleProductInfoList = new ArrayList<>(); Object value = null; row = sheet.getRow(irow); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,流向码不能为空.
"; continue; } else { String directionCodeStr = value.toString().trim(); ProductInfo productInfo = productService.selectByDirection(directionCodeStr.substring(0, 10)); if (FireworkDeal.is22Characters(directionCodeStr)){ ListproductVoList = new ArrayList<>(); DirectionDetail directionDetail = FireworkDeal.dealDirectionCode(directionCodeStr); ProductVo productVo = productService.selectVoByDirection(directionCodeStr); FireworkDeal.getProductVos(directionCodeStr,directionDetail,directionDetail,productVoList,productVo); for (ProductVo productVo1 : productVoList){ SaleProductInfo saleProductInfo = new SaleProductInfo(); saleProductInfo.setItemcode(productInfo.getDirectionCode()); saleProductInfo.setDirectioncode(productVo1.getDirectionCode()); saleProductInfo.setItemname(productVo1.getName()); saleProductInfo.setSpecification(productVo1.getSpecification()); saleProductInfo.setPrice(new BigDecimal(0)); saleProductInfo.setCreatedby(username); saleProductInfo.setCreatedat(new Date()); saleProductInfo.setIsuse((byte)0); saleProductInfoList.add(saleProductInfo); } }else { SaleProductInfo saleProductInfo = new SaleProductInfo(); saleProductInfo.setItemcode(productInfo.getDirectionCode()); saleProductInfo.setDirectioncode(directionCodeStr); saleProductInfo.setItemname(productInfo.getName()); saleProductInfo.setSpecification(productInfo.getSpecification()); saleProductInfo.setPrice(new BigDecimal(0)); saleProductInfo.setCreatedby(username); saleProductInfo.setCreatedat(new Date()); saleProductInfo.setIsuse((byte)0); saleProductInfoList.add(saleProductInfo); } } if (saleProductInfoList.size() > 0){ saleProductService.saveBatch(saleProductInfoList); importSize ++; } } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + importSize + "个," + "导入失败" + (sheet.getLastRowNum() - importSize) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } blret.setValue(true); return blret; } @Override public BooleanReason imporSaleOrderExcel(InputStream in, String username, Boolean isExcel2007) { BooleanReason blret = new BooleanReason(); String retmsg = ""; blret.setValue(false); int size = 0; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { Workbook workbook = null; if (isExcel2007) { workbook = new XSSFWorkbook(in); } else { workbook = new HSSFWorkbook(in); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { blret.addReason("excel 第1页不能为空"); return blret; } Row row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; if (colNum != saleOrderTitle.length) { blret.addReason("上传文件的列的个数错误"); return blret; } for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getStringCellValue(); if (StringUtils.isNotBlank(title[i])) { title[i] = title[i].trim(); } if (!title[i].equals((saleOrderTitle[i]))) { blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + saleOrderTitle[i]); return blret; } } for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) { int realrow = irow + 1; Object value = null; row = sheet.getRow(irow); SaleOrderInfo saleOrderInfo = new SaleOrderInfo(); List detailInfoList = new ArrayList<>(); value = ExcelProperty.getCellValue(row.getCell(0), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,零售单位名称不能为空.
"; continue; }else { Enterprise enterprise = enterpriseService.selectEnterpriseByName(value.toString().trim()); if (enterprise == null){ continue; }else { saleOrderInfo.setCreatedby(enterprise.getEnterprisenumber()); saleOrderInfo.setShop(enterprise.getEnterprisename()); saleOrderInfo.setCompanynumber(enterprise.getEnterprisenumber()); } } value = ExcelProperty.getCellValue(row.getCell(1), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,身份证不能为空.
"; continue; }else { saleOrderInfo.setIdcardnum(value.toString().trim()); } value = ExcelProperty.getCellValue(row.getCell(2), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,购买时间不能为空.
"; continue; }else { String now = sdf.format(new Date()); String purseTime = value.toString().trim()+ now.substring(10); saleOrderInfo.setCreatedat(sdf.parse(purseTime)); } List saleProductInfoList = new ArrayList<>(); value = ExcelProperty.getCellValue(row.getCell(3), evaluator); if (null == value || StringUtils.isBlank(value.toString())) { retmsg = retmsg + "导入失败,第" + realrow + "行,购买数量不能为空.
"; continue; }else { saleOrderInfo.setBoxnum(new BigDecimal(Integer.valueOf(value.toString().trim()))); saleProductInfoList = saleProductService.selectRandom(Integer.valueOf(value.toString().trim())); for (SaleProductInfo saleProductInfo : saleProductInfoList){ SaleOrderDetailInfoBO detailInfo = new SaleOrderDetailInfoBO(); detailInfo.setItemcode(saleProductInfo.getItemcode()); detailInfo.setItemname(saleProductInfo.getItemname()); detailInfo.setDirectioncode(saleProductInfo.getDirectioncode()); detailInfo.setCreatedat(saleOrderInfo.getCreatedat()); detailInfo.setCreatedby(saleOrderInfo.getCompanynumber()); detailInfo.setPrice(new BigDecimal("0")); detailInfo.setSpecification(saleProductInfo.getSpecification()); detailInfo.setReturnflag((byte)0); detailInfoList.add(detailInfo); } } customerService.createOrUpdateCard(saleOrderInfo.getIdcardnum(),detailInfoList.size(),saleOrderInfo.getCreatedat()); CustomerInfo customerInfo = customerService.getCustomerByIdCardNum(saleOrderInfo.getIdcardnum()); UserInfo userInfo = userService.selectByUser(saleOrderInfo.getCompanynumber()); SaleOrderInfo saleOrderInfo1 = saleOrderService.createOrder(userInfo,detailInfoList,"1",detailInfoList.size(),saleOrderInfo.getCreatedat(),customerInfo.getId(),"0","0","0", ""); for (SaleProductInfo saleProductInfo : saleProductInfoList){ saleProductInfo.setIsuse((byte)1); saleProductService.updateById(saleProductInfo); //插入流向轨迹 ProductLocusInfo productLocusInfo = new ProductLocusInfo(saleProductInfo.getDirectioncode(),saleOrderInfo.getCreatedat(),saleOrderInfo.getCreatedat(), userInfo.getCompany(),customerInfo.getId(),ProductLocusInfo.SALES_STATUS,null); productLocusService.insertProductLocus(productLocusInfo); } size ++; } if (StringUtils.isNotBlank(retmsg)) { retmsg = retmsg + "导入成功" + size + "个," + "导入失败" + (sheet.getLastRowNum() - size) + "个。"; blret.addReason(retmsg); return blret; } } catch (IOException e) { logger.error(e.getLocalizedMessage()); blret.addReason("导入错误"); return blret; } catch (ParseException e) { e.printStackTrace(); } blret.setValue(true); return blret; } }