Java Cell.CELL_TYPE_STRING属性代码示例

private char getCharValue(Cell cell) {
	int vType = getCellType(cell);
	switch (vType) {
	case Cell.CELL_TYPE_BLANK:
		return 0;
	case Cell.CELL_TYPE_NUMERIC:
		return (char) cell.getNumericCellValue();
	case Cell.CELL_TYPE_STRING:
		String vStr = cell.getStringCellValue();
		if (vStr.length() == 1)
			return vStr.charAt(0);
		else
			throw new IllegalArgumentException("Can't get char from String whose length > 1");
	default:
		throw new IllegalArgumentException("Can't get char from type " + vType);
	}
}
 

/**
 * 判断空行
 */
private static boolean isBlankRow(Row row) {
    if (row == null) {
        return true;
    }
    boolean result = true;
    Iterator<Cell> cells = row.cellIterator();
    String value = "";
    while (cells.hasNext()) {
        Cell cell = cells.next();
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                value = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
        }
        if (StringUtils.isNotBlank(value)) {
            result = false;
            break;
        }
    }

    return result;
}
 

public static void main(String[] args) {
        //Create Workbook instance holding reference to .xlsx file
        try (FileInputStream file = new FileInputStream(
                new File("Sample.xlsx"))) {
            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
//            Iterator<Row> rowIterator = sheet.iterator();
            for(Row row : sheet) {
                for (Cell cell : row) {
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 

private Object objectFrom(final HSSFWorkbook workbook, final Cell cell) {
    Object cellValue = null;
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        cellValue = cell.getRichStringCellValue().getString();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        cellValue = getNumericCellValue(cell);
    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        cellValue = cell.getBooleanCellValue();
    } else if (cell.getCellType()  == Cell.CELL_TYPE_FORMULA) {
        cellValue = evaluateCellFormula(workbook, cell);
    }
    return cellValue;

}
 

private double getNumbericValue(Cell cell, Class<? extends Number> clazz) {
	double vRet;
	int vType = getCellType(cell);
	switch (vType) {
	case Cell.CELL_TYPE_BLANK:
		vRet = 0;
		break;
	case Cell.CELL_TYPE_NUMERIC:
		vRet = cell.getNumericCellValue();
		break;
	case Cell.CELL_TYPE_STRING:
		String vStr = cell.getStringCellValue();
		vRet = Double.valueOf(vStr);
		break;
	default:
		throw new IllegalArgumentException("Can't get " + clazz.getSimpleName() + " from type " + vType);
	}
	return vRet;
}
 

public static Byte geByte(Cell cell) {
    if (isNullCell(cell)) {
        return null;
    }
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return (byte) cell.getNumericCellValue();
    }
    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        String value = cell.getStringCellValue();
        if(StringUtils.isNotEmpty(value)){
            Byte.valueOf(cell.getStringCellValue());
        }else{
            return null;
        }
    }
    throw new RuntimeException("can not convertWithConstructor cell value to Integer!");
}
 

public void intercepterCellValue(Cell cell, CellWrapper cellWrapper, String interceptor) throws Exception {
	if (cell == null) {
		cellWrapper.setValue(null);
	} else {
		int cellType = cell.getCellType();
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			cellWrapper.setValue(null);
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cellWrapper.setValue(Boolean.valueOf(cell.getBooleanCellValue()));
			break;
		case Cell.CELL_TYPE_ERROR:
			cellWrapper.setValue(cell.getErrorCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			cellWrapper.setValue(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				cellWrapper.setValue(date);
			} else {
				cellWrapper.setValue(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING:
			cellWrapper.setValue(cell.getStringCellValue());
			break;
		}
	}
	logger.debug("before interceptor column number[" + cellWrapper.getColumn() + "] cell value[" + cellWrapper.getValue() + "] cell intercepter[" + interceptor + "]");
	this.intercepterCellValue(cellWrapper, interceptor);
	logger.debug("after interceptor  cell value[" + cellWrapper.getValue() + "]");

}
 

public static Date getDate(Cell cell, String format) throws ParseException {
    if (isNullCell(cell)) {
        return null;
    }
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:// 数字类型
            return getCellDate(cell);
        case Cell.CELL_TYPE_STRING:
            if(StringUtils.isNotEmpty(cell.getStringCellValue())){
                return DateUtils.parseDate(cell.getStringCellValue(), format);
            }else{
                return null;
            }
        default:
            throw new RuntimeException("can not convertWithConstructor cell value to Date!");
    }
}
 

/** 从表格之中读取数据并进行处理
 * @param fieldInfo 
 * @param cell 当前cell
 * @return
 * @throws Exception
 */
public  static Object getValue(ImportFieldInfo fieldInfo, Cell cell) throws Exception{
	int size = fieldInfo.getTypeChain().size();
	Class<?> type = fieldInfo.getTypeChain().get(size - 1);
	String dateFormat = fieldInfo.getDateFormat();
	int cellType = cell.getCellType();
	Object obj = null ;
	switch (cellType) {
	case Cell.CELL_TYPE_BLANK:
		return null;
		
	case Cell.CELL_TYPE_BOOLEAN:
		obj = cell.getBooleanCellValue();
		break;
		
	case Cell.CELL_TYPE_STRING:
		obj = cell.getStringCellValue();
		break;
		
	case Cell.CELL_TYPE_NUMERIC:
		if(DateUtil.isCellDateFormatted(cell)){
			obj = DateUtil.getJavaDate(cell.getNumericCellValue());
		}else if(Number.class.isAssignableFrom(type) || ClassUtils.isBaseNumberType(type)){
			//当pojo字段类型是数字型时以数字形式获取
			obj = cell.getNumericCellValue();				
		}else{
			//其他类型都以string获取
			obj = DATA_FORMATTER.formatCellValue(cell);				
		}
		break;
		
	case Cell.CELL_TYPE_ERROR:
		return null;
	}
	if(fieldInfo.getImportProcessor() != null){
		obj = fieldInfo.getImportProcessor().process(obj);
	}
	obj = ConvertUtils.convertIfNeccesary(obj, type, dateFormat);
	return obj;
}
 

/**
 * 获取单元格值
 *
 * @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;
}
 

public static Long getLong(Cell cell) {
    if (isNullCell(cell)) {
        return null;
    }
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return (long) cell.getNumericCellValue();
    }
    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        return Longs.tryParse(cell.getStringCellValue());
    }
    throw new RuntimeException("can not convertWithConstructor cell value to Long!");
}
 

/** To get cell data from Excel-XLS */
public static Object getCellData_XLS(int RowNum, int ColNum) throws Exception
{
	Object CellData = null;
	try
	{
		xlsCell = xlsWorkSheet.getRow(RowNum).getCell(ColNum);
		if(xlsCell.getCellType() == Cell.CELL_TYPE_STRING )
		{
			String stringCellData = xlsCell.getStringCellValue();
			CellData = stringCellData;
		}
		/*		else if(xlsCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
		{
			double numericCellData =  xlsCell.getNumericCellValue();
			CellData = numericCellData;
		}
		else if(xlsCell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
		{
			boolean booleanCellData =  xlsCell.getBooleanCellValue();
			CellData = booleanCellData;
		}	*/	
		return CellData;
	}
	catch (Exception e)
	{
		return"";
	}
}
 

private String cellVal(Cell cell) {
	String result = "";
	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) 
		result = String.valueOf(cell.getNumericCellValue());
	else if (cell.getCellType() == Cell.CELL_TYPE_STRING) 
		result = cell.getStringCellValue();
	else
		result = "NOT SUPPORTED";
	
	return result;
}
 

/** To get cell data from Excel-XLSX */
public static Object getCellData_XLSX(int RowNum, int ColNum) throws Exception
{
	Object CellData = null;
	try
	{
		xlsxCell = xlsxWorkSheet.getRow(RowNum).getCell(ColNum);
		if(xlsxCell.getCellType() == Cell.CELL_TYPE_STRING )
		{
			String stringCellData = xlsxCell.getStringCellValue();
			CellData = stringCellData;
		}
		/*		else if(xlsxCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
		{
			double numericCellData =  xlsxCell.getNumericCellValue();
			CellData = numericCellData;
		}
		else if(xlsxCell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
		{
			boolean booleanCellData =  xlsxCell.getBooleanCellValue();
			CellData = booleanCellData;
		}	*/	
		return CellData;
	}
	catch (Exception e)
	{
		return"";
	}
}
 

private boolean getBooleanValue(Cell cell) {
	switch (getCellType(cell)) {
	case Cell.CELL_TYPE_BOOLEAN:
		return cell.getBooleanCellValue();
	case Cell.CELL_TYPE_NUMERIC:
		return Double.compare(cell.getNumericCellValue(), 0) != 0;
	case Cell.CELL_TYPE_STRING:
		return Boolean.valueOf(cell.getStringCellValue());
	default:
		return false;
	}
}