Java HSSFDateUtil类代码示例

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
private String getCellValue(HSSFCell cell){
	if(cell == null) return "";
	
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue();
	case HSSFCell.CELL_TYPE_BOOLEAN : return Boolean.toString(cell.getBooleanCellValue());
	case HSSFCell.CELL_TYPE_NUMERIC : 
		if(HSSFDateUtil.isCellDateFormatted(cell))
			return DateUtils.formatDateTime("yyyyMMdd", HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
		else
			return new BigDecimal(cell.getNumericCellValue()).toPlainString();
	case HSSFCell.CELL_TYPE_FORMULA : return "";
	case HSSFCell.CELL_TYPE_BLANK : return "";
	default:return "";
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
private Object getNumericCellValue( Cell cell ) {

		double val = cell.getNumericCellValue();

		if( isCellDateFormatted(cell) ) {
			String dateFormat = cell.getCellStyle().getDataFormatString();
			return new CellDateFormatter(dateFormat).format( HSSFDateUtil.getJavaDate(val) );

		} else {

			long fixedVal = (long) val;
			if( val - fixedVal == 0 ) {
				if( fixedVal < Integer.MAX_VALUE ) {
					return (int) fixedVal;
				} else {
					return fixedVal;
				}
			} else {
				return cell.getNumericCellValue();
			}
		}

    }
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
public String getCellValue(Cell cell) {
	if (cell == null)
		return ExcelODAConstants.EMPTY_STRING;

	if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
		return resolveFormula(cell);
	}

	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		if(	HSSFDateUtil.isCellDateFormatted(cell) ){		
			Date myjavadate =  HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
			return sdf.format( myjavadate );
		}
		return ((Double) cell.getNumericCellValue()).toString();
	}

	return cell.toString();
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
@Override
public ValueType getNativeValueType(int columnIndex) throws ParseException {
	Cell cell = getCurrentCell(columnIndex);
	final int type = cell.getCellType();
	if (type == Cell.CELL_TYPE_BLANK) {
		return ValueType.EMPTY;
	} else if (type == Cell.CELL_TYPE_STRING) {
		return ValueType.STRING;
	} else if (type == Cell.CELL_TYPE_NUMERIC) {
		if (HSSFDateUtil.isCellDateFormatted(cell)) {
			return ValueType.DATE;
		} else {
			return ValueType.NUMBER;
		}
	} else if (type == Cell.CELL_TYPE_FORMULA) {
		return ValueType.NUMBER;
	} else {
		return ValueType.STRING;
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
/**
 * Get the value of the excel-cell as String.
 * 
 * @param workbook
 *            workbook (excel) for evaluating cell formulas
 * @param cell
 *            cell (excel)
 * 
 * @return the value of the excel-cell as String
 */
static String cellValues2String(XSSFWorkbook workbook, XSSFCell cell) {
	if (cell == null) {
		return null;
	}
	switch (cell.getCellType()) {
	case XSSFCell.CELL_TYPE_NUMERIC:
		if (HSSFDateUtil.isCellDateFormatted(cell)) {
			return new SimpleDateFormat(JExUnitConfig.getStringProperty(JExUnitConfig.ConfigKey.DATE_PATTERN))
					.format(cell.getDateCellValue());
		} else {
			return String.valueOf(cell.getNumericCellValue());
		}
	case XSSFCell.CELL_TYPE_STRING:
		return cell.getStringCellValue();
	case XSSFCell.CELL_TYPE_FORMULA:
		return evaluateCellFormula(workbook, cell);
	case XSSFCell.CELL_TYPE_BLANK:
		return cell.getStringCellValue();
	case XSSFCell.CELL_TYPE_BOOLEAN:
		return String.valueOf(cell.getBooleanCellValue());
	case XSSFCell.CELL_TYPE_ERROR:
		return String.valueOf(cell.getErrorCellValue());
	}
	return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
protected CellValue parse_number(Cell in) {
	CellValue rv = null;

	try {
		if(HSSFDateUtil.isCellDateFormatted(in)) {
			rv = new CellValue("datetime", Report.DATEFORMAT_OUT.format(in.getDateCellValue()));
		} else {
			rv = new CellValue("number", Double.toString(in.getNumericCellValue()));				
		}
	} catch(IllegalStateException e) {
		// Siehe Dokumentation getNumericCellValue
		rv = new CellValue("string", in.getStringCellValue());
	}

	return rv;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
/**
 * 获取单元格值
 *
 * @param row    获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                // val = cell.getNumericCellValue();
                // 当excel 中的数据为数值或日期是需要特殊处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    double d = cell.getNumericCellValue();
                    Date date = HSSFDateUtil.getJavaDate(d);
                    SimpleDateFormat dformat = new SimpleDateFormat(
                            "yyyy-MM-dd");
                    val = dformat.format(date);
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    nf.setGroupingUsed(false);// true时的格式：1,234,567,890
                    val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
/**
 * 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-07-03
 * @version     v1.0
 *
 * @param i_RTemplate      模板对象
 * @param i_TemplateCell   模板中的单元格对象
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataCell       数据中的单元格对象
 * @param io_RSystemValue 系统变量信息
 * @param i_Datas          本行对应的数据
 * @param io_RValue        小计循环的迭代器
 * @return                 
 */
public final static void copyCellByBlankSpace(RTemplate i_RTemplate ,Cell i_TemplateCell ,RWorkbook i_DataWorkbook ,Cell i_DataCell ,RSystemValue io_RSystemValue)
{
    // 复制样式
    i_DataCell.setCellStyle(i_DataWorkbook.getCellStyle(i_RTemplate ,i_TemplateCell.getCellStyle().getIndex()));
    
    // 复制评论
    copyComment(i_RTemplate ,i_TemplateCell ,i_DataWorkbook ,i_DataCell);
    
    // 复制数据类型
    CellType v_CellType = i_TemplateCell.getCellTypeEnum();
    // i_DataCell.setCellType(v_CellType);  不能在此统一设置，原因是：下面代码对类型是有浮动的
    
    if ( v_CellType == CellType.NUMERIC ) 
    {
        i_DataCell.setCellType(v_CellType);
        
        if ( HSSFDateUtil.isCellDateFormatted(i_TemplateCell) ) 
        {
            i_DataCell.setCellValue(i_TemplateCell.getDateCellValue());
        } 
        else 
        {
            i_DataCell.setCellValue(i_TemplateCell.getNumericCellValue());
        }
    }
    else if ( v_CellType == CellType.STRING ) 
    {
        RichTextString v_TemplateRichText = i_TemplateCell.getRichStringCellValue();
        String         v_ValueName        = v_TemplateRichText.toString();
        
        if ( i_RTemplate.isExists(v_ValueName) )
        {
            i_DataCell.setCellType(v_CellType);
            i_DataCell.setCellValue("");
        }
        else 
        {
            i_DataCell.setCellType(v_CellType);
            copyRichTextStyle(i_RTemplate ,v_TemplateRichText ,i_DataWorkbook ,i_DataCell);
        }
    } 
    else if ( v_CellType == CellType.BOOLEAN ) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellValue(i_TemplateCell.getBooleanCellValue());
    } 
    else if ( v_CellType == CellType.FORMULA) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellFormula(i_TemplateCell.getCellFormula());
    } 
    else 
    {
        // Nothing.
        i_DataCell.setCellType(v_CellType);
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
@Override
public ArrayList<MsgEntity> getMsgListFromExcel(File targetFile) throws IOException {
	ArrayList<MsgEntity> list = new ArrayList<MsgEntity>();
	POIFSFileSystem fs = new POIFSFileSystem(targetFile);
	HSSFWorkbook workBook = new HSSFWorkbook(fs);
	try {
		System.out.println("开始解析");
		HSSFSheet sheet = workBook.getSheetAt(0);
		int rows = sheet.getLastRowNum() + 1;
		for (int r = 1; r < rows; r++) {
			HSSFRow row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			MsgEntity msg = new MsgEntity();
			String number = getStringVal(row.getCell(0));
			if(StringUtils.isEmpty(number)){
				continue;
			}
			msg.setTelNumber(number);
			
			String templateId = getStringVal(row.getCell(1));
			if (!StringUtils.isEmpty(templateId) && StringUtils.isNumeric(templateId)
					&& templateId.indexOf(".") == -1) {
				msg.setTemplateId(Long.parseLong(templateId));
			}
			msg.setContent(getStringVal(row.getCell(2)));
			if (HSSFDateUtil.isCellDateFormatted(row.getCell(3))) {
				msg.setSendTime(row.getCell(3).getDateCellValue());
			}
			list.add(msg);
		}
		
	} finally {
		workBook.close();
	}
	return list;

}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellTypeEnum()) {
        case NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return new JsonPrimitive(DateFormatterHolder.formatter.format(cell.getDateCellValue().toInstant()));
            } else {
                return new JsonPrimitive(cell.getNumericCellValue());
            }
        case STRING:
            return new JsonPrimitive(cell.getStringCellValue());
        case FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellTypeEnum()) {
                case NUMERIC:
                    return new JsonPrimitive(cellValue.getNumberValue());
                case STRING:
                    return new JsonPrimitive(cellValue.getStringValue());
                case BLANK:
                    return new JsonPrimitive("");
                case BOOLEAN:
                    return new JsonPrimitive(cellValue.getBooleanValue());
                case ERROR:
                default:
                    return null;
            }
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cell.getBooleanCellValue());
        case ERROR:
        default:
            return null;
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
public static String getValue(Cell cell) throws Exception {
	String temp = null;

	if (cell != null) {
		SimpleDateFormat f = new SimpleDateFormat(YYYY_MM_DD);

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC:
			// 셀값이 날짜일경우
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				temp = f.format(cell.getDateCellValue());
				// 셀값이 숫자일경우
			} else {
				temp = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING:
			temp = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			temp = cell.getCellFormula();
			break;
		/*
		 * case Cell.CELL_TYPE_BLANK: temp = ""; break;
		 */
		case Cell.CELL_TYPE_BOOLEAN:
			temp = String.valueOf(cell.getBooleanCellValue());
			break;
		default:
			temp = "";
			break;
		}

	}
	return temp;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
public boolean isNum(Sheet sheet, int rowInt, int column) {
	Row row = sheet.getRow(rowInt);
	Cell cell = row.getCell(column);
	boolean resultBoolean = false;
	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		if (!HSSFDateUtil.isCellDateFormatted(cell)) {
			resultBoolean = true;
		}
	}
	return resultBoolean;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
/**
 * 根据HSSFCell类型设置数据
 *
 * @param cell
 * @return
 */
private String getCellFormatValue(Cell cell) {
	String cellvalue = "";
	if (cell != null) {
		// 判断当前Cell的Type
		switch (cell.getCellType()) {
		// 如果当前Cell的Type为NUMERIC
		case HSSFCell.CELL_TYPE_NUMERIC:
		case HSSFCell.CELL_TYPE_FORMULA: {
			// 判断当前的cell是否为Date
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// 如果是Date类型则，转化为Data格式
				// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
				// cellvalue = cell.getDateCellValue().toLocaleString();
				// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
				Date date = cell.getDateCellValue();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				cellvalue = sdf.format(date);
			}
			// 如果是纯数字
			else {
				// 取得当前Cell的数值
				cellvalue = String.valueOf(cell.getNumericCellValue());
			}
			break;
		}
			// 如果当前Cell的Type为STRIN
		case HSSFCell.CELL_TYPE_STRING:
			// 取得当前的Cell字符串
			cellvalue = cell.getRichStringCellValue().getString();
			break;
		// 默认的Cell值
		default:
			cellvalue = " ";
		}
	} else {
		cellvalue = "";
	}
	return cellvalue;
}
 

import org.apache.poi.hssf.usermodel.HSSFDateUtil; //导入依赖的package包/类
private String getTypeFromNumericCell(Cell cell) {
    try {
        return HSSFDateUtil.isCellDateFormatted(cell) ? DATE.getName() : NUMERIC.getName();
    } catch (IllegalStateException e) {
        return ANY.getName();
    }
}