dao层
<insert id ="saveInTarget" parameterType="java.util.List" > INSERT INTO t_target (quantity,tz_quantity,target_id,class_id,datadate, country,h_quantity,h_tz_quantity,type,product_model) values <foreach collection ="list" item="reddemCode" index= "index" separator =","> (#{reddemCode.quantity},#{reddemCode.tzQuantity},#{reddemCode.targetId}, #{reddemCode.classId}, #{reddemCode.datadate},#{reddemCode.countryId},#{reddemCode.hQuantity}, #{reddemCode.hTzQuantity},#{reddemCode.type},#{reddemCode.model}) </foreach > </insert > <delete id="deleteInTarget" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> DELETE FROM t_target WHERE class_id=7 AND DATE_FORMAT(datadate,'%Y%m')=DATE_FORMAT(#{item.datadate},'%Y%m') AND target_id=#{item.targetId} AND type=#{item.type} </foreach> </delete> <delete id="deleteInShopTarget" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> DELETE FROM t_target WHERE class_id=4 AND DATE_FORMAT(datadate,'%Y%m')=DATE_FORMAT(#{item.datadate},'%Y%m') AND target_id=#{item.targetId} AND type=#{item.type} </foreach> </delete>
servie层
public String readIndExcel(File file, String fileName,String targetType) throws IOException; public String readInd2007Excel(File file,String targetType) throws IOException;
service.impl
public String readIndExcel(File file, String fileName, String targetType) throws IOException { String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName .substring(fileName.lastIndexOf(".") + 1); if ("xls".equals(extension)) { //return read2003Excel(file,targetType); throw new IOException("Unsupported file type,the suffix name should be xlsx!"); } else if ("xlsx".equals(extension)) { return readInd2007Excel(file,targetType); } else { //throw new IOException("不支持的文件类型"); throw new IOException("Unsupported file type,the suffix name should be xlsx!"); } } @Override public String readInd2007Excel(File file, String targetType) throws IOException { StringBuffer msg = new StringBuffer(); List<List<Object>> list = new LinkedList<List<Object>>(); try { // 构造 XSSFWorkbook 对象，strPath 传入文件路径 XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file)); // 读取第一章表格内容 XSSFSheet sheet = xwb.getSheetAt(0); Object value = null; XSSFRow ro = sheet.getRow(1); XSSFCell cells = null; List<HashMap<String, Object>> modelList = new LinkedList<HashMap<String, Object>>(); List<HashMap<String, Object>> shopList = new LinkedList<HashMap<String, Object>>(); Set<String> setLine = new HashSet<String>(); for (int j = 3; j < ro.getLastCellNum(); j++) { HashMap<String, Object> hashMap = new HashMap<>(); cells = ro.getCell(j); if (cells == null) { continue; } System.out.println(cells.getCellType()+"-------1-------------"); System.out.println(getCellValueByCell(cells)+"-------2-------------"); if (cells != null && cells.getCellType() != HSSFCell.CELL_TYPE_BLANK && getCellValueByCell(cells) != null && !getCellValueByCell(cells).equals("")) { boolean one = setLine.add(getCellValueByCell(cells)); if (!one) {//判断是否有重复数据 msg.append(getText("sale.error.row") + DateUtil.getExcelColumnLabel(j + 1) + " " + getText("excel.error.lineRE") + " (" + getCellValueByCell(cells) + ") " + "<br/>"); } String productModel = getCellValueByCell(cells); String model = saleTargetDao.getProductModel(productModel, WebPageUtil.getLoginedUser().getPartyId()); if(model==null || model.equals("")){ msg.append(getText("sale.error.row") + 2 + "(" + getCellValueByCell(cells) + ") "+ getText("sale.error.model") + "<br/>"); } String keyModel = saleTargetDao.getKeyModel(WebPageUtil.getLoginedUser().getPartyId(), productModel); if(keyModel==null){ msg.append(getText("sale.error.row") + 2 + "(" + getCellValueByCell(cells) + ") "+ getText("sale.error.keymodel") + "<br/>"); } hashMap.put("line", getCellValueByCell(cells)); modelList.add(hashMap); } } System.out.println("========lineList========" + modelList); List<HashMap<String, Object>> allModelList = new LinkedList<HashMap<String, Object>>(); for (int i = 2; i <= sheet.getLastRowNum(); i++) { XSSFRow row = null; XSSFCell cell = null; row = sheet.getRow(i); if(row == null){ break; } HashMap<String, Object> storeMap = new HashMap<String, Object>();//只能放在循环内，拼接存放数据 String shopName=""; Shop shop=null; String countryId=""; shopName = row.getCell(0).getStringCellValue(); shop = shopDao.getShopByNames(shopName); if (shop == null) { msg.append(getText("sale.error.row") + (i+1) + getText("sale.error.shop") +getText("sale.error.cell")+(1)+" ("+shopName+")"+"<br/>"); } if(row.getCell(0)!=null && row.getCell(0).getCellType()!=HSSFCell.CELL_TYPE_BLANK){ if("7".equals(targetType)){//7识别为门店 shopName = row.getCell(0).getStringCellValue(); shop = shopDao.getShopByNames(shopName); if (shop != null) { storeMap.put("Store", shop.getShopId() == null ? null : shop.getShopId()); storeMap.put("StoreName", shopName); } } } SimpleDateFormat dfd = new SimpleDateFormat("yyyy-MM-dd");// 设置日期格式 SimpleDateFormat format = new SimpleDateFormat("MM/yyyy"); Date date = new Date(); String dt = dfd.format(date); Date dt1 = dfd.parse(dt); Date dt2; String rowDate = ""; if(row.getCell(1)!=null && row.getCell(1).getCellType()!=HSSFCell.CELL_TYPE_BLANK){ if(row.getCell(1).getStringCellValue().length()>7){ msg.append(getText("sale.error.row") + (i+1)+getText("sale.error.cell")+(1+1) + getText("sale.error.dateTime") + "<br/>"); }else{ try{ format.setLenient(false); date = format.parse(row.getCell(1).getStringCellValue());//有异常要捕获 dfd.setLenient(false); String newD = dfd.format(date); date = dfd.parse(newD);//有异常要捕获 dt2 =dfd.parse(newD); rowDate = newD; // if (dt1.getTime() < dt2.getTime()) { // msg.append(getText("sale.error.row") + (i+1)+getText("sale.error.cell")+(1+1) // + getText("sale.error.time") + "<br/>"); // } }catch(Exception e){ msg.append(getText("sale.error.row") + (i+1)+getText("sale.error.cell")+(1+1) + getText("sale.error.dateTime") + "<br/>"); } } }else{ msg.append(getText("sale.error.row") + (i + 1)+getText("sale.error.cell")+(1+1) + getText("sale.error.dateNo") + "<br/>"); } // XSSFCell cell2 = row.getCell(2); //把excel中的单元格数据转化为String类型 // cell2.setCellType(XSSFCell.CELL_TYPE_STRING); // storeMap.put("quantity", cell2.getStringCellValue()); if(row.getCell(1)!=null && row.getCell(1).getCellType()!=HSSFCell.CELL_TYPE_BLANK ){ try{ format.setLenient(false); date = format.parse(row.getCell(1).getStringCellValue());//有异常要捕获 dfd.setLenient(false); String newD = dfd.format(date); date = dfd.parse(newD);//有异常要捕获 dt2 =dfd.parse(newD); // if (dt1.getTime() < dt2.getTime()) { // // } else { // storeMap.put("datadate", newD); // } storeMap.put("datadate", newD); }catch(Exception e){ } } if (row.getCell(0) != null && row.getCell(0).getCellType() != HSSFCell.CELL_TYPE_BLANK && row.getCell(1) != null && row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_BLANK) { if (row.getCell(2) != null && row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_BLANK) { switch (row.getCell(2).getCellType()) { case HSSFCell.CELL_TYPE_STRING: msg.append(getText("sale.error.row") + (i + 1) + " " + getText("sale.error.cell") + DateUtil.getExcelColumnLabel(3) + " " + getText("excel.error.num") + "<br/>"); break; case HSSFCell.CELL_TYPE_FORMULA: msg.append(getText("sale.error.row") + (i + 1) + " " + getText("sale.error.cell") + DateUtil.getExcelColumnLabel(3) + " " + getText("excel.error.num") + "<br/>"); break; case HSSFCell.CELL_TYPE_NUMERIC: storeMap.put("targetQty", (int) row.getCell(2).getNumericCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: break; } } else { msg.append(getText("sale.error.row") + (i + 1) + " " + getText("sale.error.cell") + DateUtil.getExcelColumnLabel(3) + " " + getText("excel.error.quantityNull") + "<br/>"); } } String type="1"; List<Object> linked = new LinkedList<Object>(); for (int m = 0; m < modelList.size(); m++) { if (row.getCell(m + 3) != null && row.getCell(m + 3).getCellType() != HSSFCell.CELL_TYPE_BLANK ) { switch (row.getCell(m + 3).getCellType()) { case HSSFCell.CELL_TYPE_STRING: msg.append(getText("excel.error.row") + (i + 1) + " " + getText("excel.error.cell") + DateUtil.getExcelColumnLabel(m + 3 + 1) + " " + getText("excel.error.num") + "<br/>"); break; case HSSFCell.CELL_TYPE_FORMULA: msg.append(getText("excel.error.row") + (i + 1) + " " + getText("excel.error.cell") + DateUtil.getExcelColumnLabel(m + 3 + 1) + " " + getText("excel.error.num") + "<br/>"); break; case HSSFCell.CELL_TYPE_NUMERIC: HashMap<String, Object> modelMap = new HashMap<String, Object>(); modelMap.put("Line", modelList.get(m).get("line")); countryId = WebPageUtil.getLoginedUser().getPartyId(); modelMap.put("Country", countryId == null ? null : countryId); if (rowDate != null && !rowDate.equals("")) { modelMap.put("datadate", rowDate); } if (type != null && !type.equals("")) { modelMap.put("Type", type); } shopName = row.getCell(0).getStringCellValue(); shop = shopDao.getShopByNames(shopName); if (shop != null) { modelMap.put("Store", shop.getShopId() == null ? null : shop.getShopId()); modelMap.put("StoreName", shopName); } // if(row.getCell(2)!=null && row.getCell(2).getCellType()!=HSSFCell.CELL_TYPE_BLANK){ // modelMap.put("quantity", row.getCell(2).getNumericCellValue()); // }else{ // modelMap.put("quantity", 0); // } modelMap.put("quantity", row.getCell(m+3).getNumericCellValue()); allModelList.add(modelMap); break; case HSSFCell.CELL_TYPE_ERROR: break; } }else { HashMap<String, Object> modelMap = new HashMap<String, Object>(); modelMap.put("Line", modelList.get(m).get("line")); countryId = WebPageUtil.getLoginedUser().getPartyId(); modelMap.put("Country", countryId == null ? null : countryId); if (rowDate != null && !rowDate.equals("")) { modelMap.put("datadate", rowDate); } if (type != null && !type.equals("")) { modelMap.put("Type", type); } shopName = row.getCell(0).getStringCellValue(); shop = shopDao.getShopByNames(shopName); if (shop != null) { modelMap.put("Store", shop.getShopId() == null ? null : shop.getShopId()); modelMap.put("StoreName", shopName); } // if(row.getCell(2)!=null && row.getCell(2).getCellType()!=HSSFCell.CELL_TYPE_BLANK){ // modelMap.put("quantity", row.getCell(2).getNumericCellValue()); // }else{ // modelMap.put("quantity", 0); // } modelMap.put("quantity", 0); allModelList.add(modelMap); } } shopList.add(storeMap); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { continue; } DecimalFormat df = new DecimalFormat("0");// 格式化 number // String 字符 SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 DecimalFormat nf = new DecimalFormat("0");// 格式化数字 switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle() .getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle() .getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: value = ""; break; default: value = cell.toString(); } if (value == null || "".equals(value)) { continue; } linked.add(value); } list.add(linked); } System.out.println("=====size=============" + allModelList.size()); System.out.println("=====allModelList=============" + allModelList); List<SaleTarget> saleTargetList = new ArrayList<SaleTarget>(); List<SaleTarget> deleteTargetList = new ArrayList<SaleTarget>(); List<SaleTarget> shopTargetList = new ArrayList<SaleTarget>(); if(msg.length()<=0){ for (int i = 0; i < allModelList.size(); i++) { String shopName=""; String datadate=""; String type=""; System.out.println(allModelList.get(i).get("Line")+"----------Line-----------------"); SaleTarget excel = new SaleTarget(); if(allModelList.get(i).get("StoreName")!=null && allModelList.get(i).get("datadate")!=null && "".equals(msg.toString()) && allModelList.get(i).get("Line") != null && allModelList.get(i).get("Line") != ""){ type = "1"; excel.setType(1); if(allModelList.get(i).get("Store")!=null){ excel.setTargetId((allModelList.get(i) .get("Store").toString())); } if(allModelList.get(i).get("datadate")!=null){ datadate = allModelList.get(i).get("datadate") .toString(); excel.setDatadate(datadate); } if(allModelList.get(i).get("StoreName")!=null){ shopName = allModelList.get(i).get("StoreName") .toString(); } excel.setCountryId(WebPageUtil.getLoginedUser().getPartyId()); excel.setModel(allModelList.get(i).get("Line") .toString()); excel.setClassId(targetType); System.out.println(allModelList.get(i) .get("quantity").toString()+"----------------------quantity"); BigDecimal bqt = new BigDecimal(allModelList.get(i) .get("quantity").toString()); excel.setQuantity(Long.parseLong(bqt.setScale(0,BigDecimal.ROUND_HALF_UP).toString())); excel.sethQuantity(Integer.parseInt(bqt.setScale(0,BigDecimal.ROUND_HALF_UP).toString())); excel.setTzQuantity(Long.parseLong("0")); saleTargetList.add(excel); deleteTargetList.add(excel); } } System.out.println("=====size=============" + shopList.size()); System.out.println("=====allModelList=============" + shopList); for (int i = 0; i < shopList.size(); i++) { String datadate=""; String type=""; SaleTarget excel = new SaleTarget(); if(shopList.get(i).get("StoreName")!=null && shopList.get(i).get("datadate")!=null && "".equals(msg.toString()) ){ type = "1"; excel.setType(1); if(shopList.get(i).get("Store")!=null){ excel.setTargetId((shopList.get(i) .get("Store").toString())); } if(shopList.get(i).get("datadate")!=null){ datadate = shopList.get(i).get("datadate") .toString(); excel.setDatadate(datadate); } excel.setCountryId(WebPageUtil.getLoginedUser().getPartyId()); excel.setClassId("4"); System.out.println(shopList.get(i) .get("targetQty").toString()+"----------------------targetQty"); BigDecimal bqt = new BigDecimal(shopList.get(i) .get("targetQty").toString()); excel.setQuantity(Long.parseLong(bqt.setScale(0,BigDecimal.ROUND_HALF_UP).toString())); excel.sethQuantity(Integer.parseInt(bqt.setScale(0,BigDecimal.ROUND_HALF_UP).toString())); excel.setTzQuantity(Long.parseLong("0")); shopTargetList.add(excel); deleteTargetList.add(excel); } } } if(saleTargetList.size()>0){ String [] beginDate=saleTargetList.get(0).getDatadate().split("-"); String [] endDate=saleTargetList.get(saleTargetList.size()-1).getDatadate().split("-"); String beg=beginDate[0]+"-"+beginDate[1]+"-01"; String end=endDate[0]+"-"+endDate[1]+"-31"; System.out.println(beg+"---------------------beg-------"); System.out.println(end+"---------------------end-------"); saleTargetDao.deleteInTarget(deleteTargetList); saleTargetDao.saveInTarget(saleTargetList); } if(shopTargetList.size()>0){ String [] beginDate=shopTargetList.get(0).getDatadate().split("-"); String [] endDate=shopTargetList.get(shopTargetList.size()-1).getDatadate().split("-"); String beg=beginDate[0]+"-"+beginDate[1]+"-01"; String end=endDate[0]+"-"+endDate[1]+"-31"; System.out.println(beg+"---------------------beg-------"); System.out.println(end+"---------------------end-------"); saleTargetDao.deleteInShopTarget(deleteTargetList); saleTargetDao.saveInTarget(shopTargetList); } System.out.println(allModelList); if (msg.length() > 0) { return msg.toString(); } else { return ""; } } catch (Exception e) { e.printStackTrace(); log.error(e.getMessage(), e); return msg.toString(); } }
action层
//导入印尼门店目标 public void importIndStore(){ String targetType = request.getParameter("targetType"); try { String errorMsg = saleService.readIndExcel(uploadExcel, uploadExcelFileName,targetType); if ("".equals(errorMsg)) { WebPageUtil.writeBack("success"); } else { WebPageUtil.writeBack(errorMsg); } } catch (Exception e) { String errorMsg = e.getMessage(); if (null == errorMsg || "".equals(errorMsg)) { errorMsg = this.getText("import.error.exist"); } log.error(e.getStackTrace()); e.printStackTrace(); WebPageUtil.writeBack(errorMsg); } }
