controller:
/** * excel导入 * * @param * @return */ @PostMapping(value = "/importExcel") public ObjectResultResponse importExcel(@RequestParam(value = "file") MultipartFile file){ if (file == null || file.isEmpty()) { return this.getObjResultResponse(Const.FAILURE, Const.CODE_SUCCESS, "请上传文件!",null); } try { baseService.importExcel(file); }catch (Exception e){ e.printStackTrace(); return this.getObjResultResponse(Const.FAILURE, Const.CODE_SUCCESS, "导入错误!",null); } return this.getObjResultResponse(Const.SUCCESS, Const.CODE_SUCCESS, "导入成功!",null); }
service: //导入旧数据excel专用 @Transactional public void importExcel(MultipartFile file) throws Exception{ HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); HSSFSheet sheet1 = workbook.getSheetAt(0); HSSFSheet sheet2 = workbook.getSheetAt(1); HSSFSheet sheet3 = workbook.getSheetAt(2); //获取总物理行数,不包括空行隔行 int rows1 = sheet1.getPhysicalNumberOfRows(); HSSFRow row1 = null; HSSFCell cell1 = null; int rows2 = sheet2.getPhysicalNumberOfRows(); HSSFRow row2 = null; HSSFCell cell2 = null; int rows3 = sheet3.getPhysicalNumberOfRows(); HSSFRow row3 = null; HSSFCell cell3 = null; Long levelOne =null; Long levelTwo =null; Long levelThree =null; Long productTypeBaseId =null; Long productPric =null; Long productDescription =null; Long status =null; TypeDictCondition typeDictCondition = null; //第一行为表头,从第二行开始 for (int i = 3; i <= rows1; i++) { row1 = sheet1.getRow(i-1); importProduct(row1, typeDictCondition); } for (int i = 3; i <= rows2; i++) { row2 = sheet2.getRow(i-1); importProduct(row2, typeDictCondition); } for (int i = 3; i <= rows2; i++) { row2 = sheet3.getRow(i-1); importProduct(row3, typeDictCondition); } } public void importProduct(HSSFRow row1, TypeDictCondition typeDictCondition) { Long levelOne; Long levelTwo; HSSFCell cell1; Long levelThree;Product product = new Product(); //获取第一个sheet页中的一级分类值 typeDictCondition.setTypePath(this.getValue(row1.getCell(1)).toString().trim()); levelOne = typeDictService.selectOne(typeDictCondition).getId(); product.setLevelOne(levelOne); //获取第一个sheet页中的二级分类值 typeDictCondition.setTypePath(this.getValue(row1.getCell(1)).toString().trim()+"-"+this.getValue( row1.getCell(2)).toString().trim()); levelTwo = typeDictService.selectOne(typeDictCondition).getId(); product.setLevelTwo(levelTwo); //获取第一个sheet页中的三级分类值 cell1 = row1.getCell(3); if(null != cell1){ typeDictCondition.setTypePath(this.getValue(row1.getCell(1)).toString().trim()+"-"+this.getValue( row1.getCell(2)).toString().trim()+"-"+this.getValue( row1.getCell(3)).toString().trim()); levelThree = typeDictService.selectOne(typeDictCondition).getId(); product.setLevelThree(levelThree); } //产品价格 cell1 = row1.getCell(5); if(null!=cell1){ product.setProductPric(Long.valueOf(this.getValue(cell1).toString().trim())); } //产品名称 cell1 = row1.getCell(4); if(null!=cell1){ product.setProductPric(Long.valueOf(this.getValue(cell1).toString().trim())); } //备注 cell1 = row1.getCell(7); if(null!=cell1){ product.setProductDescription(this.getValue(cell1).toString().trim()); } cell1 = row1.getCell(6); if(null!=cell1){ String temp = this.getValue(cell1).toString().trim(); if("上架".equals(temp)){ }else if("下架".equals(temp)){ product.setStatus(4l); } } insert(product); String code = typeDictService.getProductCode(product.getLevelOne(),product.getLevelTwo(),product.getLevelThree(),product.getId()); product.setProductCode(code); patchById(product); BasicService baseService = new BasicService(); baseService.setServiceName(product.getProductName()); baseService.setProductId(product.getId()); baseService.setServiceCode(product.getProductCode()); basicServiceService.insert(baseService); } //获取单元格中的值 private Object getValue(HSSFCell cell) { int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_BOOLEAN) { return cell.getBooleanCellValue(); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } } else if (type == HSSFCell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else { return "Not defined"; } }