excel导入demal

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";
    }
}