POI3.8组件研究（二）---基于User API (HSSF and XSSF)解析Excel2003和2007文件

                在解析生成excel2003和2007时候，由于生成的对象不同可能需要创建workbook的不同对象。

判断代码如下：

	/**
	 * 创建TableView类型的Excel文件
	 * @param excelVo excel模型
	 * @throws IOException
	 */
	public InputStream createTableViewerExcelStream(ExcelVO  excelVo) throws IOException{
		//创建一个EXCEL
		Workbook wb =null;
		//支持2007
		if("xlsx".equals(excelVo.getPrefix())){
			wb=new XSSFWorkbook();
		//支持97 ~2003
		}else{
			wb=new HSSFWorkbook();
		}
		List<SheetVO> sheetList=excelVo.getSheets();
		if(CollectionUtils.isNotEmpty(sheetList)){
			for (int sheet = 0; sheet < sheetList.size(); sheet++) {
				createExcelSheet(wb, sheetList, sheet);
			}
		}
		//存储流信息
	     ByteArrayOutputStream  out = new ByteArrayOutputStream();
	    wb.write(out);
	    
	    
	    //临时存储流信息
	    ByteArrayInputStream in  = new ByteArrayInputStream(out.toByteArray());
	    out.close();
	    return in;
	}

 

 

创建一个sheet的内容如下：

/**
	 * 创建Excel的Sheet
	 * @param wb  Excel的对象
	 * @param sheetList 
	 * @param sheetNum
	 */
	private void createExcelSheet(Workbook wb, List<SheetVO> sheetList, int sheetNum) {
		SheetVO sheetVo=sheetList.get(sheetNum);
		//获取各种样式
		//获取数据格式化对象
		DataFormat dataformat = wb.createDataFormat();
		//获取Sheet的名称
		String sheetName=sheetVo.getSheetName();
		//创建Sheet
		Sheet sheet=wb.createSheet(sheetName);
		   // create 2 cell styles
	    CellStyle cs = wb.createCellStyle();
	    CellStyle cs2 = wb.createCellStyle();
	    DataFormat df = wb.createDataFormat();

	    // create 2 fonts objects
	    Font f = wb.createFont();
	    Font f2 = wb.createFont();

	    // Set font 1 to 12 point type, blue and bold
	    f.setFontHeightInPoints((short) 12);
	    f.setColor( IndexedColors.RED.getIndex() );
	    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

	    // Set font 2 to 10 point type, red and bold
	    f2.setFontHeightInPoints((short) 10);
	    f2.setColor( IndexedColors.RED.getIndex() );
	    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

	    // Set cell style and formatting
	    cs.setFont(f);
	    cs.setDataFormat(df.getFormat("#,##0.0"));

	    // Set the other cell style and formatting
	    cs2.setBorderBottom(cs2.BORDER_THIN);
	    cs2.setDataFormat(df.getFormat("text"));
	    cs2.setFont(f2);
	    
	    
		//获取开始写的行号
		int rowNum=sheetVo.getRowNum();
		//创建标题
		Row headerRow = sheet.createRow(0);
		headerRow.setHeightInPoints(40.0F);
		Cell titleCell = headerRow.createCell(0);
		titleCell.setCellValue(sheetVo.getTitle());
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$T$1"));   
		
		CreationHelper createHelper = wb.getCreationHelper();
		String[] headerTitles=sheetVo.getHeaderTitle();
		if(!ArrayUtils.isEmpty(headerTitles)){
			//创建表头
			Row row = sheet.createRow((short)rowNum);
			for (int index=0; index < headerTitles.length; index++) {
				//创建列信息
				String headerTitle=headerTitles[index];
				Cell cell = row.createCell(index);
		    	cell.setCellValue(createHelper.createRichTextString(headerTitle));
		    	//设置列宽，行高
				sheet.setColumnWidth((short)index, 5000);
			}
			//行记录添加
			rowNum++;
		}
		//编写shett的内容
		List<Map<String,Object>> contentMap=sheetVo.getSheetContentMap();
		if(CollectionUtils.isNotEmpty(contentMap)){
			for (int index = 0; index < contentMap.size(); index++) {
				Map<String,Object> rowMap=contentMap.get(index);
				Row row = sheet.createRow((short)rowNum);
				createCell(wb, dataformat, rowMap, row,sheetVo);
				rowNum++;
			}
		}
	}

	/**
	 * 创建Excel的Cell
	 * @param wb
	 * @param dataformat
	 * @param rowMap
	 * @param row
	 */
	private void createCell(Workbook wb, DataFormat dataformat,
			Map<String, Object> rowMap, Row row,SheetVO sheetVo) {
		String[] headerTitles=sheetVo.getTitles();
		if(MapUtils.isNotEmpty(rowMap)){
			CreationHelper createHelper = wb.getCreationHelper();
			for (int cellNum=0;cellNum<headerTitles.length;cellNum++) {
				CellStyle style;
				//创建列值
				Cell cell = row.createCell(cellNum);
				String key=headerTitles[cellNum];
				Object cellValue=rowMap.get(key);
				
				if(cellValue instanceof String){
					cell.setCellValue(createHelper.createRichTextString((String)cellValue));
				}else if((cellValue instanceof Integer)||(cellValue instanceof Long)){
					cell.setCellValue(createHelper.createRichTextString(cellValue.toString()));
				//针对带小数点的数据的处理
				}else if((cellValue instanceof Double)||(cellValue instanceof Float)){
					    cell.setCellValue(Double.valueOf(cellValue.toString()));
					    style = wb.createCellStyle();
					    style.setDataFormat(dataformat.getFormat("#.##"));
					    //设定样式
					    cell.setCellStyle(style);
				//针对Date格式
				}else if(cellValue instanceof Date){
				    /*  
			         * 定义显示日期的公共格式  
			         * 如:yyyy-MM-dd hh:mm  
			         * */
				    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");   
			        String newdate = sdf.format(new Date()); 
			        // 填充出产日期   
				    cell.setCellValue(createHelper.createRichTextString(newdate));
				}else if(cellValue instanceof Boolean){
					cell.setCellValue((Boolean)cellValue);
				}
			}
		}
	}
	

 

针对excel中的时间格式需要自动转换为数字：

针对POI支持的事件格式如下一种：

org.apache.poi.ss.usermodel.DateUtil:

  private static final Pattern date_ptrn1 = Pattern.compile("^\\[\\$\\-.*?\\]");
  private static final Pattern date_ptrn2 = Pattern.compile("^\\[[a-zA-Z]+\\]");
  private static final Pattern date_ptrn3 = Pattern.compile("^[\\[\\]yYmMdDhHsS\\-/,. :\"\\\\]+0*[ampAMP/]*$");

  private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+|[mM]+|[sS]+)\\]");

 

将HH:MM和HH:MM:SS格式的转换数字的源代码如下：

org.apache.poi.ss.usermodel.DateUtil:

public static double convertTime(String timeStr)
  {
    try
    {
      return convertTimeInternal(timeStr);
    } catch (FormatException e) {
      String msg = "Bad time format '" + timeStr + "' expected 'HH:MM' or 'HH:MM:SS' - " + e.getMessage();

      throw new IllegalArgumentException(msg); }
  }

  private static double convertTimeInternal(String timeStr) throws DateUtil.FormatException {
    int len = timeStr.length();
    if ((len < 4) || (len > 8)) {
      throw new DateUtil.FormatException("Bad length");
    }
    String[] parts = TIME_SEPARATOR_PATTERN.split(timeStr);
    String secStr;
    switch (parts.length)
    {
    case 2:
      secStr = "00"; break;
    case 3:
      secStr = parts[2]; break;
    default:
      throw new DateUtil.FormatException("Expected 2 or 3 fields but got (" + parts.length + ")");
    }
    String hourStr = parts[0];
    String minStr = parts[1];
    int hours = parseInt(hourStr, "hour", 24);
    int minutes = parseInt(minStr, "minute", 60);
    int seconds = parseInt(secStr, "second", 60);

    double totalSeconds = seconds + (minutes + hours * 60) * 60;
    return (totalSeconds / 86400.0D);
  }

 针对YY-MM-dd时间格式的转换时间格式源代码：

public static Date parseYYYYMMDDDate(String dateStr)
  {
    try
    {
      return parseYYYYMMDDDateInternal(dateStr);
    } catch (FormatException e) {
      String msg = "Bad time format " + dateStr + " expected 'YYYY/MM/DD' - " + e.getMessage();

      throw new IllegalArgumentException(msg); }
  }

 

 

注意：在读取excel时候需要解读HH:MM或者HH:MM:SS或者YYYY/MM/DD的转换，必须确定时间的格式，才可以转换。

 

 

 

 

 

 针对2003和2007的excel中向单元格中写入内容是：

         可能有点不同，需要使用CreationHelper生成数据：

            Calling the empty HSSFWorkbook remains as the way to create a new, empty Workbook object. To open an existing Worbook, you should now call WorkbookFactory.create(inp).

For all other cases when you would have called a Usermodel constructor, such as 'new HSSFRichTextString()' or 'new HSSFDataFormat', you should instead use a CreationHelper. There's a method on the Workbook to get a CreationHelper, and the CreationHelper will then handle constructing new objects for you.

 

例如：

	Cell cell = row.createCell(index);
	cell.setCellValue(createHelper.createRichTextString(headerTitle));