POI实现Excel文件内容读取

1.接收接口

@PostMapping("/writeTripToExcel")
@ApiOperation(value = "读取excel文件里面的内容")
public List<PositionAlarmInfo> writeTripToExcel(MultipartFile file){
   return List<PositionAlarmInfo> positionAlarmInfoList=toolService.readTripByExcel(file); 
}

2.内容转换

 public List<PositionAlarmInfo> readTripByExcel(MultipartFile file){
        List<PositionAlarmInfo> positionList=new ArrayList<>();
        try { 
            //获取上传文件的名称
            String name=file.getOriginalFilename();
            int lastIndex=name.lastIndexOf(".");
            String suffix=name.substring(lastIndex);
            InputStream inputStream;
            XSSFWorkbook xssfWorkbook;
            HSSFWorkbook hssfWorkbook;
            Sheet sheet;
            //为了适配不同的Excel文件后缀
            switch (suffix){
                case ".xlsx":
                    inputStream=file.getInputStream();
                    xssfWorkbook=new XSSFWorkbook(inputStream);
                    //获取table中第一个表
                    sheet = xssfWorkbook.getSheetAt(0);
                    break;
                case ".xls":
                    inputStream=file.getInputStream();
                    hssfWorkbook=new HSSFWorkbook(inputStream);
                   /**
                     第二种方式
                     inputStream=file.getInputStream();
                     hssfWorkbook=new HSSFWorkbook(inputStream);
                   */
                    //获取table中第一个表
                    sheet = hssfWorkbook.getSheetAt(0);
                    break;
                 default:
                     throw new Exception("不支持文件类型进行转换");

            }
              }
	/*  
	  //使用迭代器遍历
	  Iterator<Row> rowIterator=sheet.rowIterator();
	  while (rowIterator.hasNext()){
	      Row row=rowIterator.next();
	      Iterator<Cell> cellIterator=row.cellIterator();
	      while (cellIterator.hasNext()){
	          Cell cell=cellIterator.next();
	          //具体的实现
	      }
	
	  }*/
	      //excel表格是行列下标都是从0开始          
              for (int i=1;i<sheet.getLastRowNum();i++){
                PosInfo posInfo=new PosInfo();
                Row row=sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<row.getLastCellNum();j++){
                        Cell cell=row.getCell(j);
                        switch (j){
                            case 1:
                                String no=cell.getStringCellValue();
                                posInfo.setNo(no);
                                break;
                            case 2:
                                String id=cell.getStringCellValue();
                                posInfo.setId(id); 
                                break;
                            case 4:
                                Date date=cell.getDateCellValue();
                                posInfo.setDate(date); 
                                break;
                            case 5:
                                Double price=cell.getNumericCellValue();
                                posInfo.setPrice(price);
                                break;
                            case 6:
                                Double money=cell.getNumericCellValue();
                                posInfo.setMoney(money);
                                break;
                            case 8:
                                String status=cell.getStringCellValue();
                                posInfo.setStatus(status);                               
                                break;
                            default:
                                continue;
                        }
                    }
                    positionList.add(positionAlarmInfo);
//                    Thread.sleep(30000);
//                    messageService.sendMsgKafka("loc-c", JSONObject.toJSONString(positionAlarmInfo));
                }
            }
        }catch (Exception e){
            log.info("excel表格读取失败:{}",e);
            e.printStackTrace();
        }
        return positionList;
    }

备注：excel文件导出