Java Code Examples for org.apache.poi.ss.usermodel.Cell#toString()

private boolean moreDataToRead(final Sheet sheet, final int firstColumn, final int firstRow, final int lastRow, final int rowNum) {

        final int height = lastRow - firstRow + 1;
        if (height > 1 && firstRow + rowNum > lastRow) {
            return false;
        }

        // check if the cell is empty
        final Row row = sheet.getRow(firstRow + rowNum);
        if (row == null) {
            return false;
        }

        final Cell cell = row.getCell(firstColumn);
        if (cell == null) {
            return false;
        }
        final String str = cell.toString();
        return !(str == null || "".equals(str));
    }
 

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();
}
 

private String resolveFormula(Cell cell) {
	if (formulaEvaluator == null)
		return cell.toString();
	switch (formulaEvaluator.evaluateFormulaCell(cell)) {
	case Cell.CELL_TYPE_BOOLEAN:
		return ((Boolean) cell.getBooleanCellValue()).toString();

	case Cell.CELL_TYPE_NUMERIC:
		if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ){
			//need to check for nulls
			//double myexdate = org.apache.poi.ss.usermodel.DateUtil.getExcelDate(cell.getDateCellValue());
			Date myjavadate = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
			return sdf.format( myjavadate );
		}
		return ((Double) cell.getNumericCellValue()).toString();

	case Cell.CELL_TYPE_STRING:
		return cell.getStringCellValue();

	default:
		return null;
	}
}
 

public static String getCellVal(Cell cell, int i, int j) {
	if(cell == null) return "";
	
	try {
        switch(cell.getCellTypeEnum()) { // 判断cell类型
	        case NUMERIC:
	            // 判断cell是否为日期格式
	            if( org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ) {
	                Date dateCellVal = cell.getDateCellValue();
					return DateUtil.formatCare2Second( dateCellVal );
	            } 
	            else { // 数字,常规类型的数字会自动多出 .0（因转换后是double类型），需要格式化掉
	            	double cellVal = cell.getNumericCellValue();
	            	
	            	NumberFormat f = NumberFormat.getInstance();
	        		f.setMaximumFractionDigits(8); // 最多保留8位小数
					String val = f.format( cellVal );
					
	            	return val.replace(",", "");
	            }
	        case FORMULA:
	        	return ( (XSSFCell)cell ).getCTCell().getV();
	        case STRING:
	        	return cell.getStringCellValue();
	        default:
	        	return cell.toString();
        }
	} 
	catch( Exception e ) {
		throw new BusinessException( "Excel.getCellVal error, location = [" + i + "," + j + "], cell = " + cell, e);
	}
}
 

private Object getCellValue(Cell cell) {
    Object value;
    DecimalFormat df = new DecimalFormat("0");// 格式化 number String
    // 字符
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
    DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
    switch (cell.getCellType()) {
        case XSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case XSSFCell.CELL_TYPE_NUMERIC:
            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = nf.format(cell.getNumericCellValue());
            } else {
                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            }
            break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            value = cell.toString();
    }
    return value;
}
 

private String formatIfData(final Cell cell) {
    if (cell.getCellTypeEnum() == CellType.NUMERIC && isCellDateFormatted(cell)) {
        final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        return simpleDateFormat.format(cell.getDateCellValue());
    }
    return cell.toString();
}
 

/**
 * Default value if null.
 *
 * @param cell
 *            the cell
 * @return the string
 */
private static String defaultValueIfNull(final Cell cell) {
	if (cell != null) {
		return cell.toString();
	} else {
		return "";
	}
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = (cfSpreadSheetData)parameters.get(1);
	Pattern pattern = Pattern.compile( parameters.get(0).getString() );

	cfArrayData	arr	= cfArrayData.createArray(1);

	Iterator<Row>	rowIT	= spreadsheet.getActiveSheet().rowIterator();
	while ( rowIT.hasNext() ){
		Row row	= rowIT.next();
		
		Iterator<Cell>	cellIT	= row.cellIterator();
		while ( cellIT.hasNext() ){
			Cell cell	= cellIT.next();
			String cellValue = null;
			
			if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
				cellValue	= cell.getStringCellValue();
			else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
				cellValue	= String.valueOf( cell.getNumericCellValue() );
			else
				cellValue	= cell.toString();

			if ( pattern.matcher( cellValue ).find() ){
				cfStructData	s	= new cfStructData();
				s.setData( "row", 		new cfNumberData( cell.getRowIndex() + 1 ) );
				s.setData( "column", 	new cfNumberData( cell.getColumnIndex() + 1 ) );
				s.setData( "value", 	new cfStringData( cellValue ) );
				arr.addElement( s );
			}
		}
	}

	return arr;
}
 

@Override
public Row next() {
	List<String> cells = new ArrayList<String>(fieldName2ColumnIndex.size());
	for (Cell cell : iterator.next()) {
		String text;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
			text = myFormatter.format(cell.getNumericCellValue());
		else
			text = cell.toString();
		cells.add(text);
	}
	for (int i = cells.size(); i < fieldName2ColumnIndex.size(); i++)
		cells.add("");
	return new Row(cells, fieldName2ColumnIndex);
}