Java Cell.getCellType方法代码示例

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的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 (DateUtil.isCellDateFormatted(cell)) {
			return ValueType.DATE;
		} else {
			return ValueType.NUMBER;
		}
	} else if (type == Cell.CELL_TYPE_FORMULA) {
		if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
			return ValueType.NUMBER;
		} else {
			return ValueType.STRING;
		}
	} else {
		return ValueType.STRING;
	}
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
public Object getCellValue(Cell cell){
    Object value = null;
    DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            break;
    }
    return value;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
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();
        }
    }
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
/**
 * 判断空行
 */
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;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
public static int getEmptyRow(Workbook wb, int sheetIndex, String cellRef) {
	final Sheet sheet = wb.getSheetAt(sheetIndex);
	final CellReference cellReference = new CellReference(cellRef); // һ����A1
	boolean flag = false;
	for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
		final Row r = sheet.getRow(i);
		if (r == null) {
			// ����ǿ��У���û���κ����ݡ���ʽ����ֱ�Ӱ������µ����������ƶ�
			sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
			continue;
		}
		flag = false;
		for (final Cell c : r) {
			if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
				flag = true;
				break;
			}
		}
		if (flag) {
			i++;
			continue;
		} else {// ����ǿհ��У�������û�����ݣ�������һ����ʽ��
			if (i == sheet.getLastRowNum())// ����������һ�У�ֱ�ӽ���һ��remove��
				sheet.removeRow(r);
			else// �����û�����һ�У�������������һ��
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
		}
	}
	return sheet.getLastRowNum() + 1;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
/**
 * 对读取excel的数据进行格式转换
 *
 * @param cell
 * @return
 * @throws Exception
 */
private Object dataFormat(Cell cell) throws Exception {
    try {
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                return cellTypeNumeric(cell);
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BLANK:
                return "";
            default:
                return cell.getStringCellValue();
        }
    } catch (Exception e) {
        logger.debug("excel数据格式化错误！");
        throw e;
    }
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
private int getStartRow(Sheet sheet) throws SheetParsingException {

		Iterator<Row> rit = sheet.iterator();
		while (rit.hasNext()) {
			Row row = rit.next();
			Cell cell = row.getCell(0);

			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				/*
				 * In my case first column is Sr.no. and Data starts where sr. no is 1 ,so this
				 * is start row index
				 */
				if (cell.getNumericCellValue() == 1.0)
					return row.getRowNum();
			}
		}
		throw new SheetParsingException("no start index found for data");
	}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
private int getLastRow(Sheet sheet) throws SheetParsingException {
	int lastRowIndex = -1;
	if (sheet.getPhysicalNumberOfRows() > 0) { // getLastRowNum() actually returns an index, not a row number
		lastRowIndex = sheet.getLastRowNum();

		// now, start at end of spreadsheet and work our way backwards until we find a
		// row having data
		for (; lastRowIndex >= 0; lastRowIndex--) {
			Row row = sheet.getRow(lastRowIndex);
			if (row != null) {
				Cell cell = row.getCell(0);
				if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
					break;
			}
		}
		return lastRowIndex;
	} else
		throw new SheetParsingException(sheet.getSheetName() + " is empty");
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
@Override
public boolean isMissing(int columnIndex) {
	Cell cell = getCurrentCell(columnIndex);
	try {
		boolean missing = cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
				|| cell.getCellType() == Cell.CELL_TYPE_ERROR || "".equals(cell.getStringCellValue().trim());
		return missing;
	} catch (IllegalStateException e) {
		return false;
	}
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
/** 从表格之中读取数据并进行处理
 * @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;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
public static Integer getInteger(Cell cell) {
    if (isNullCell(cell)) {
        return null;
    }
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return (int) cell.getNumericCellValue();
    }
    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        return Integer.parseInt(cell.getStringCellValue());
    }
    throw new RuntimeException("can not convertWithConstructor cell value to Integer!");
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
/**
 * 根据HSSFCell类型设置数据（简单导入使用）
 *
 * @param cell 单元格
 * @return 处理后的值
 */
private static String getCellFormatValue(Cell cell) {
    String cellValue;
    if (cell != null) {
        // 判断当前Cell的Type
        switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case Cell.CELL_TYPE_NUMERIC:
            case Cell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(date);
                }
                // 如果是纯数字
                else {
                    //避免科学计数
                    java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
                    nf.setGroupingUsed(false);
                    // 取得当前Cell的数值
                    cellValue = String.valueOf(nf.format(cell.getNumericCellValue()));
                }
                break;
            }
            // 如果当前Cell的Type为STRING
            case Cell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellValue = cell.getStringCellValue();
                break;
            // 默认的Cell值
            default:
                cellValue = " ";
        }
    } else {
        cellValue = "";
    }
    return cellValue;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
private int firstEmptyCellPosition(final Row cells) {
    int columnCount = 0;
    for (Cell cell : cells) {
        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            break;
        }
        columnCount++;
    }
    return columnCount;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
public static Double getDouble(Cell cell) {
    if (isNullCell(cell)) {
        return null;
    }
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return cell.getNumericCellValue();
    }
    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        return Double.parseDouble(cell.getStringCellValue());
    }
    throw new RuntimeException("can not convertWithConstructor cell value to Double!");
}
 

import org.apache.poi.ss.usermodel.Cell; //导入方法依赖的package包/类
public static String getString(Cell cell) {
    if (isNullCell(cell)) {
        return null;
    }
    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        return cell.getStringCellValue();
    }
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return NumberUtil.numberStr(cell.getNumericCellValue());
    }
    throw new RuntimeException("can not convertWithConstructor cell value to String!");
}