JAVA解析EXCEL解析获取数据

EXCEL解析获取数据

接着上篇继续：（中间删除掉了我自己的业务逻辑部分代码）

public Map<String, Object> analysisExcel(String fileNamer) {
      //存放失败数据
      List<Object> failedList = new ArrayList<Object>();
      boolean hasFailed = false;
      Map<String, Object> jsonMap = new HashMap<String, Object>();
      //获取excel
      File file = new File(fileName);
      if (file == null || !file.exists()) {
          jsonMap.put("err", 9999);
          jsonMap.put("message", "文件路径参数不存在");
          return jsonMap;
      }
      Workbook wb;
      //存放失败信息
      StringBuffer errorInfo = null;
      try {
          wb = WorkbookFactory.create(file);
          Sheet sheet = wb.getSheetAt(0);
          Iterator<Row> it = sheet.rowIterator();
          it.next();
          while (it.hasNext()) {
              String CAR_NUMBER = "";
              try {
                  Row row = it.next();
                  errorInfo = new StringBuffer();
                  
                  //上传的excel中每个字段按顺序解析出来的值：
                  
                  //车主姓名
                  String CAR_OWNER_NAME = ExcelUtil.getCellValue(row.getCell(0));
                  //联系电话
                  String MOBILE_PHONE = ExcelUtil.getCellValue(row.getCell(1));
                  //车牌号 = 车牌号（） + 上牌时间
                  String str = ExcelUtil.getCellValue(row.getCell(2));
			
			解释：
				可以根据自己的业务需求对每个字段的值进行校验，
				将失败信息和数据存放起来（后续可以导出失败信息的excel）                   

                  //正确数据存放
                  Map<String, Object> tempMap = new HashMap<String, Object>();

                  //EXCEL中数据
                  tempMap.put("CAR_OWNER_NAME", CAR_OWNER_NAME);
                  tempMap.put("MOBILE_PHONE", MOBILE_PHONE);
                 

              } catch (Exception e) {
                  log.info("车牌号:" + CAR_NUMBER + "插入数据失败");
                  
                  hasFailed = true;
                  errorInfo.append(CAR_NUMBER);
                  errorInfo.append(",插入数据失败");
                  failedList.add(errorInfo.toString());
                  
                  continue;
              }
          }

      } catch (InvalidFormatException e) {
          e.printStackTrace();
      } catch (IOException e) {
          e.printStackTrace();
      } finally {
          //如果有失败的数据，需要存入到表中
          if (hasFailed && !failedList.isEmpty()) {
              Map<String, String> paramMap = new HashMap<String, String>();
              paramMap.put("TASK_NUMBER", taskNumber);//任务号
              //插入失败信息表中数据（方便后续导出）
              this.exportExcel(failedList, paramMap);
          }
          
      }
      return null;
  }

ExcelUtil工具类：

public class ExcelUtil {
	
    public static String getCellValue(Cell cell) {  
    	if(cell==null){    		
    		return null;
    	}
    	else{
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        } 
    	}
    }
    
    /**
	 * 基于JAVA反射讲结果MAP中的值保存在对应的实体Bean中
	 * 注:Bean中的属性名称必须符合java基本命名规范  例: userName(Bean.setter) ==> user_name (Map.key)
	 * @param objBean 实体Bean
	 * @param rowMap 查询结果Map集合
	 * @return objBean.class
	 * @author niuguokai
	 * */
	public static <T> T setBeanByMap(T objBean, Map<String, Object> rowMap) throws IllegalArgumentException, IllegalAccessException, UnsupportedEncodingException{
		if(rowMap != null){
			for(Field field : objBean.getClass().getDeclaredFields()){
				//常量不做处理
				if(Modifier.isFinal(field.getModifiers()))
					continue;
				//对于Bean中的集合属性，不做处理
				if(field.getType() == Set.class || field.getType() == List.class || field.getType() == Map.class)
					continue;
				field.setAccessible(true);
				Object objValue = rowMap.get(field.getName());
				if(objValue == null){
					//Map.key对应的value为NULL，不走处理
					continue;
				}if(objValue.getClass() == byte[].class){
					//Map.key对应的value为byte[](即MySql.blob类型)，做特殊处理
					objValue = new String((byte[])objValue,"utf-8"); 
				}else if(objValue.getClass() == Timestamp.class){
					//Map.key对应的value为Timestamp，转为java.util.Date类型
					objValue = new Date(((Timestamp)objValue).getTime());
				}
				//System.out.println(field.getName() + ":" + objValue.getClass());
				field.set(objBean, objValue);
			}
		}
		return objBean;
	}
	
	
    public static XSSFWorkbook export(List<Map<String, Object>> dataList,String title,String titleKey) throws IOException {
    	if(StringUtils.isEmpty (title)|| StringUtils.isEmpty (titleKey)){
    		throw new RuntimeException("参数缺失");
    	}    	
		String[] titleList = title.split (",");
		String[] titleKeyList = titleKey.split (",");
		XSSFWorkbook xwb = new XSSFWorkbook ();
		XSSFSheet sheet = xwb.createSheet ("sheet1");
		sheet.setDefaultColumnWidth(30);  
		
		XSSFFont fontTitle = xwb.createFont ();
		fontTitle.setFontName ("微软雅黑");
		fontTitle.setFontHeightInPoints ((short) 12);

		XSSFFont font = xwb.createFont ();
		font.setFontName ("微软雅黑");
		font.setFontHeightInPoints ((short) 10);

		XSSFCellStyle titleStyle = xwb.createCellStyle ();
		titleStyle.setFillForegroundColor (HSSFColor.SKY_BLUE.index);
		titleStyle.setFillPattern (HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setBorderBottom (HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft (HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderRight (HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderTop (HSSFCellStyle.BORDER_THIN);
		titleStyle.setAlignment (HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment (XSSFCellStyle.VERTICAL_CENTER);
		titleStyle.setFont (fontTitle);

		XSSFCellStyle cellStyle = xwb.createCellStyle ();
		cellStyle.setBorderBottom (HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft (HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight (HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop (HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment (HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont (font);

		XSSFRow row0 = sheet.createRow (0);
		row0.setHeight ((short) 1000);
		for (int i = 0; i < titleList.length; i ++)
		{
			XSSFCell cell = row0.createCell (i);
			cell.setCellValue (titleList[i]);
			cell.setCellStyle (titleStyle);
		}
		if(dataList!=null&&!dataList.isEmpty ()){
			for (int j = 0; j < dataList.size (); j ++)
			{
				XSSFRow row = sheet.createRow (j + 1);

				Map<String, Object> dataMap = dataList.get (j);
				for (int k = 0; k < titleKeyList.length; k ++)
				{
				
					String cellContent = dataMap.get (titleKeyList[k]) != null ? dataMap.get (titleKeyList[k]) + "" : "";
					XSSFCell cell = row.createCell (k);
					cell.setCellValue (cellContent);
					cell.setCellStyle (cellStyle);
				}
			}
		}		

		return xwb;
	}
}