POI解析Execl工具类

 

代码：

public class PoixUtil {

    private static Logger log = (Logger) LogManager.getLogger(PoixUtil.class.getName());

    

/**
 * 从excel文件中读取里面的内容(以headers为基准) .xls
 * @param file excel文件
 * @param headers 标题code的数组
 * @param beginRow 起始行,从1开始
 * @param endRow 结束行 从1开始，如果为空，就默认为最后一行
 * @param beginCol 起始列,从1开始
 * @param sheetName 要读取哪个sheet，如果为空，那么就取第一个
 * @return 读取的结果集
 * @throws Exception 异常
 */
public static List<LinkedHashMap<String,Object>> readExcel(MultipartFile file,String[] headers, Integer beginRow, Integer endRow, Integer beginCol,String sheetName) throws BusinessException {
   List<LinkedHashMap<String,Object>> rsList = new ArrayList<LinkedHashMap<String,Object>>();
   InputStream is = null;
   XSSFWorkbook workBook = null;
   try{
      is =file.getInputStream();
      workBook = new XSSFWorkbook(is);
      XSSFSheet sheet = null;
      if(!StrUtil.isEmpty(sheetName)){
         sheet = workBook.getSheet(sheetName);
         if(sheet == null){
            throw new Exception("该Excel未找到名字为【"+sheetName+"】的sheet！");
         }
      }else{
         sheet = workBook.getSheetAt(0);
      }
      if(beginRow == null){
         beginRow = 0;
      }
      if(beginCol == null){
         beginCol = 0;
      }

      XSSFRow row = null;
      XSSFCell cell = null;
      LinkedHashMap<String,Object> rowMap = null;
      Integer nowCol = null;
      int cellType = 0;
      Object value = null;

      if(endRow == null){
         endRow = sheet.getLastRowNum();
      }

      for(int i=beginRow; i<= endRow; i++){
         row = sheet.getRow(i);
         if(row == null){
            continue;
         }
         rowMap = new LinkedHashMap<String,Object>();
         nowCol = beginCol;
         for(int j=0;j<headers.length;j++){
            cell = row.getCell(nowCol);
            if(cell == null){
               nowCol++;
               continue;
            }
            cellType = cell.getCellType();//单元格类型
            if(XSSFCell.CELL_TYPE_BLANK == cellType){
               value =null;
            }else if(XSSFCell.CELL_TYPE_BOOLEAN == cellType){
               value = cell.getBooleanCellValue();
            }else if(XSSFCell.CELL_TYPE_ERROR == cellType){
               value = cell.getErrorCellValue();
            }else if(XSSFCell.CELL_TYPE_FORMULA == cellType){
               try {
                  value = String.valueOf(cell.getNumericCellValue());
               } catch (IllegalStateException e) {
                  value = String.valueOf(cell.getRichStringCellValue());
               }
            }else if(XSSFCell.CELL_TYPE_NUMERIC == cellType){
               if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                  SimpleDateFormat sdf = null;
                  if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                        .getBuiltinFormat("h:mm")) {
                     sdf = new SimpleDateFormat("HH:mm");
                  } else {// 日期
                     sdf = new SimpleDateFormat("yyyy-MM-dd");
                  }
                  Date date = cell.getDateCellValue();
                  value = sdf.format(date);
               } else if (cell.getCellStyle().getDataFormat() == 58) {
                  // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                  double valuef = cell.getNumericCellValue();
                  Date date =HSSFDateUtil.getJavaDate(valuef);
                  value = sdf.format(date);
               } else {
                  value= NumberToTextConverter.toText(cell.getNumericCellValue());
               }
            }else if(XSSFCell.CELL_TYPE_STRING == cellType){
               value =StrUtil.trim(cell.getStringCellValue());
            }else{
               throw new Exception("Excel的单元格（第"+row.getRowNum()+"行，第"+cell.getColumnIndex()+"列）不能识别类型！");
            }
            rowMap.put(headers[j], value);
            nowCol++;
         }
         rsList.add(rowMap);
      }
   }catch(Exception e){
      throw new BusinessException(e.getMessage());
   }finally{
      if(workBook != null){
         try{
            workBook.close();
         }catch(Exception e){
            log.error(e);
         }
      }
      if(is != null){
         try{
            is.close();
         }catch(Exception e){
            log.error(e);
         }
      }
   }
   return rsList;
}

}