2021-06-09

导出Excel到istMap中工具类

/**
 * 将Excel数据读取到ListMap
 * @param file
 */
  public static List<Map<String, Object>> readExcelDataToListMap(MultipartFile file) {
      String edition_2003 = "^.+\\.(?i)(xls)$";		// Excel_2003版本
      String edition_2007 = "^.+\\.(?i)(xlsx)$";	// Excel_2007版本
      String fileName = file.getOriginalFilename();	// 获取文件名
      if (StringUtils.isNotBlank(fileName)) {               
      	  try {       		
      	      // 根据Excel版本创建对象
      	      Workbook wb = null;
      	      if (fileName.matches(edition_2003)) {				 
      	          wb = new HSSFWorkbook(file.getInputStream());
      	      } else {
      		  if (fileName.matches(edition_2007)) {                		
      		      wb = new XSSFWorkbook(file.getInputStream());
      		  }
      	      }         		
      	      // 读取Excel里面的数据
      	      if (null != wb) {					
      	         // 得到第一个shell
      	          Sheet sheet = wb.getSheetAt(0);
      	    	  // 得到Excel的行数
      	    	  int totalRows = sheet.getPhysicalNumberOfRows();
      	    	  // 得到Excel的列数(前提是有行数)
      	    	  int totalCells = 0;
      	    	  if (totalRows > 1 && sheet.getRow(0) != null) {
      	    	      totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
      	    	  }
      	    	  List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
      	    	  // 循环Excel行数
      	    	  for (int r = 1; r < totalRows; r++) {
      	    	      Row row = sheet.getRow(r);
    	    	      if (row == null) {
    	    	          continue;
    	    	      }    		
    	    	      // 循环Excel的列
    	    	      Map<String, Object> map = new HashMap<String, Object>();
    	    	      for (int c = 0; c < totalCells; c++) {
    	    	          Cell cell = row.getCell(c);
    	    	          if (null != cell) {
    	    	              int cellType = cell.getCellType();   				
    	    	              if (cellType == 0) {    					
    	    	                  DataFormatter dataFormatter = new DataFormatter();
    	    	                  dataFormatter.addFormat("###########", null);
    	    	                  String str = dataFormatter.formatCellValue(cell);    					
    	    	                  if (StringUtils.isNotBlank(str)) {							
    	    	    	              map.put(c+"",str);
    		                  }
    	    	              }else{
    	    	                  String str = String.valueOf(cell);
    	                          if (StringUtils.isNotBlank(str)) {						
    	        	              map.put(c+"",str);
    	    	                  } 
    	    	              }
    	                  } 
    	              }
    	              // 添加到list
    	              if (map.size() > 0) {				
    	    	         listMap.add(map);
    	              }
      	          }
      	          return listMap;        			        			
               }
             } catch (Exception e) {
          	    e.printStackTrace();
             }
        }                        
        return null;
  }