|
/*** * 解析excel * @param request * @return */ @Override @Transactional public Map parseExcel(HttpServletRequest request) { Map result = new HashMap(); Workbook workbook = null; //验证标识 Boolean isValidatePass = true; //获取文件路径 String path = (String)request.getSession().getAttribute("currFilePath"); if(path==null||path==""){ result.put("statusCode", "300"); result.put("message", "请先上传excel文件再导入"); }else{ //获取文件格式 String fileType = path.substring(path.lastIndexOf(".") + 1, path.length()); try { InputStream stream = new FileInputStream(path); //如果后缀名为xls，使用HSSF if (fileType.equals("xls")) { workbook = new HSSFWorkbook(stream); //如果后缀名是xlsx，使用XSSF }else if (fileType.equals("xlsx")){ workbook = new XSSFWorkbook(stream); } Sheet sheet= workbook.getSheet("sheet1"); //获取行数 int rows=sheet.getPhysicalNumberOfRows(); WmsReceiveOrder receiveOrder =new WmsReceiveOrder(); //获取第一行数据 Row row1 =sheet.getRow(0); if(row1!=null){ //获取采购订单号 row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING); String purchaseCode =row1.getCell(1).getStringCellValue(); receiveOrder.setPurchaseCode(purchaseCode); } //获取第二行数据 Row row2 =sheet.getRow(1); if(row2!=null){ //获取供应商送货单号 row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING); String deliveryCode =row2.getCell(1).getStringCellValue(); receiveOrder.setDeliveryCode(deliveryCode); //日期格式加校验 Cell deliveryTimeCell = row2.getCell(3); if(deliveryTimeCell!=null){ //如果是数值类型 if(deliveryTimeCell.getCellType()==0){ if(HSSFDateUtil.isCellDateFormatted(deliveryTimeCell)){ //获取送货日期 Date deliveryTime =deliveryTimeCell.getDateCellValue(); receiveOrder.setDeliveryTime(deliveryTime); }else{ //设置送货时间为红色 CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); deliveryTimeCell.setCellStyle(style); isValidatePass=false; } }else{ //设置送货时间为红色 CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); deliveryTimeCell.setCellStyle(style); isValidatePass=false; } } //获取供应商编号 row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING); String supplierCode =row2.getCell(5).getStringCellValue(); receiveOrder.setSupplierCode(supplierCode); //获取供应商名称 row2.getCell(6).setCellType(Cell.CELL_TYPE_STRING); String supplierName =row2.getCell(6).getStringCellValue(); receiveOrder.setSupplierName(supplierName); } //获取第三行数据 Row row3 =sheet.getRow(2); if(row3!=null){ //获取ERP入货单号 row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING); String erpInCode =row3.getCell(1).getStringCellValue(); receiveOrder.setErpInCod(erpInCode); Cell inTimeCell= row3.getCell(3); //如果是数值类型 if(inTimeCell!=null&&inTimeCell.getCellType()==0){ if(HSSFDateUtil.isCellDateFormatted(inTimeCell)){ //获取入库日期 Date inTime =row3.getCell(3).getDateCellValue(); receiveOrder.setInTime(inTime); }else{ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); inTimeCell.setCellStyle(style); isValidatePass=false; } }else{ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); inTimeCell.setCellStyle(style); isValidatePass=false; } } receiveOrder.setType(1); receiveOrder.setStatus("1"); //校验子表各条的数量以及生产日期以及物料编号是否存在 for (int currentRow=4;currentRow |