org.apache.poi.ss.usermodel.Cell#getCellTypeEnum

Source File: OriginsSheetV0d9d4.java From tools with Apache License 2.0

6 votes

@SuppressWarnings("deprecation")
	private String validateRow(Row row) {
		for (int i = 0; i < NUM_COLS; i++) {
			Cell cell = row.getCell(i);
			if (cell == null) {
				if (REQUIRED[i]) {
					return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet");
				}
			} else {
				if (i == CREATED_COL) {
					if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) {
						return "Created column in origin spreadsheet is not of type Date";
					}
				}
//				if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
//					return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum());
//				}
			}
		}
		return null;
	}

Source File: PoiUtils.java From easy-excel with MIT License

6 votes

/**
 * 获取指定单元格的值
 * @param cell 指定单元格
 * @return 值
 */
public static String getColumnValue(Cell cell) {
    switch (cell.getCellTypeEnum()) {
        case BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case NUMERIC:
            return String.valueOf(cell.getNumericCellValue());
        case BLANK:
            return "";
        case FORMULA:
            return cell.getCellFormula();
        default:
            return cell.getStringCellValue();
    }

}

Source File: ExcelPOIHelper.java From tutorials with MIT License

6 votes

private String readCellContent(Cell cell) {
    String content;
    switch (cell.getCellTypeEnum()) {
    case STRING:
        content = cell.getStringCellValue();
        break;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            content = cell.getDateCellValue() + "";
        } else {
            content = cell.getNumericCellValue() + "";
        }
        break;
    case BOOLEAN:
        content = cell.getBooleanCellValue() + "";
        break;
    case FORMULA:
        content = cell.getCellFormula() + "";
        break;
    default:
        content = "";
    }
    return content;
}

Source File: Excel2Record.java From boubei-tss with Apache License 2.0

6 votes

static String getCellType(Cell cell) {
	switch(cell.getCellTypeEnum()) {
        case NUMERIC:
            if( org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ) { // 判断cell是否为日期格式
            	return _Field.TYPE_DATETIME;
            } 
            else { // 数字
            	return _Field.TYPE_NUMBER;
            }
        case FORMULA:
        	return _Field.TYPE_STRING;
        case STRING:
        default:
        	return _Field.TYPE_STRING;
    }
}

Source File: OriginsSheetV2d0.java From tools with Apache License 2.0

6 votes

@SuppressWarnings("deprecation")
private String validateRow(Row row) {
	for (int i = 0; i < NUM_COLS; i++) {
		Cell cell = row.getCell(i);
		if (cell == null) {
			if (REQUIRED[i]) {
				return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet");
			}
		} else {
			if (i == CREATED_COL) {
				if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) {
					return "Created column in origin spreadsheet is not of type Date";
				}
			}
		}
	}
	return null;
}

Source File: OriginsSheetV1d1.java From tools with Apache License 2.0

6 votes

@SuppressWarnings("deprecation")
	private String validateRow(Row row) {
		for (int i = 0; i < NUM_COLS; i++) {
			Cell cell = row.getCell(i);
			if (cell == null) {
				if (REQUIRED[i]) {
					return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet");
				}
			} else {
				if (i == CREATED_COL) {
					if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) {
						return "Created column in origin spreadsheet is not of type Date";
					}
				}
//				if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
//					return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum());
//				}
			}
		}
		return null;
	}

Source File: ExcelUtils.java From components with Apache License 2.0

6 votes

/**
 *
 * @param cell
 * @param cellValue
 * @return internal method which switch on the formula result value type then return a String value
 */
private static String getCellValueAsString(Cell cell, CellValue cellValue) {
    if (cellValue == null) {
        return StringUtils.EMPTY;
    }
    switch (cellValue.getCellTypeEnum()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cellValue.getBooleanValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case NUMERIC:
        return getNumericValue(cell, cellValue, cellValue != null);
    case STRING:
        //TODO which is better? StringUtils.trim(cell.getStringCellValue())
        return cell.getRichStringCellValue().getString();
    default:
        return "Unknown Cell Type: " + cell.getCellTypeEnum();
    }
}

Source File: DataValidationEvaluator.java From lams with GNU General Public License v2.0

5 votes

/**
* Note that this assumes the cell cached value is up to date and in sync with data edits
* @param cell
* @param type
* @return true if the cell or cached cell formula result type match the given type
*/
public static boolean isType(Cell cell, CellType type) {
    final CellType cellType = cell.getCellTypeEnum();
    return cellType == type 
          || (cellType == CellType.FORMULA 
              && cell.getCachedFormulaResultTypeEnum() == type
             );
}

Source File: ExcelReaderService.java From abixen-platform with GNU Lesser General Public License v2.1

5 votes

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

Source File: OneClickImporterServiceImpl.java From molgenis with GNU Lesser General Public License v3.0

5 votes

/**
 * Specific columntypes are permitted in the import. The supported columntypes are specified in
 * the method.
 *
 * @param sheet worksheet
 * @param cell cell on worksheet
 * @return Column
 */
private Column createColumnFromCell(Sheet sheet, Cell cell) {
  if (cell.getCellTypeEnum() == CellType.STRING) {
    return Column.create(
        cell.getStringCellValue(),
        cell.getColumnIndex(),
        getColumnDataFromSheet(sheet, cell.getColumnIndex()));
  } else {
    throw new MolgenisDataException(
        String.format(
            "Celltype [%s] is not supported for columnheaders", cell.getCellTypeEnum()));
  }
}

Source File: ExcelPOI.java From boubei-tss with Apache License 2.0

5 votes

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

Source File: Cells.java From datacollector with Apache License 2.0

5 votes

static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
  CellType cellType = cell.getCellTypeEnum();
  // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
  boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
  if (isFormula) {
    cellType = cell.getCachedFormulaResultTypeEnum();
  }

  switch (cellType) {
    case STRING:
      return Field.create(cell.getStringCellValue());
    case NUMERIC:
      Double rawValue = cell.getNumericCellValue();  // resolves formulas automatically and gets value without cell formatting
      String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell);
      boolean numericallyEquivalent = false;
      try {
        numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
      } catch (NumberFormatException e) { }

      if (DateUtil.isCellDateFormatted(cell)) {
        // It's a date, not a number
        java.util.Date dt = cell.getDateCellValue();
        // if raw number is < 1 then it's a time component only, otherwise date.
        return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
      }

      // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
      return Field.create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
      return Field.create(cell.getBooleanCellValue());
    case BLANK:
      return Field.create("");
    default:
      throw new ExcelUnsupportedCellTypeException(cell, cellType);
  }
}

Source File: OneClickImporterServiceImpl.java From molgenis with GNU Lesser General Public License v3.0

4 votes

/** Retrieves the proper Java type instance based on the Excel CellTypeEnum */
private Object getCellValue(Cell cell) {
  Object value;

  // Empty cells are null, instead of BLANK
  if (cell == null) {
    return null;
  }

  switch (cell.getCellTypeEnum()) {
    case STRING:
      value = cell.getStringCellValue();
      break;
    case NUMERIC:
      if (isCellDateFormatted(cell)) {
        try {
          // Excel dates are LocalDateTime, stored without timezone.
          // Interpret them as UTC to prevent ambiguous DST overlaps which happen in other
          // timezones.
          setUserTimeZone(LocaleUtil.TIMEZONE_UTC);
          Date dateCellValue = cell.getDateCellValue();
          value = formatUTCDateAsLocalDateTime(dateCellValue);
        } finally {
          resetUserTimeZone();
        }
      } else {
        value = cell.getNumericCellValue();
      }
      break;
    case BOOLEAN:
      value = cell.getBooleanCellValue();
      break;
    case FORMULA:
      value = getTypedFormulaValue(cell);
      break;
    default:
      value = null;
      break;
  }
  return value;
}

Source File: ExcelReader.java From azeroth with Apache License 2.0

4 votes

private void getCellValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
    LOG.debug("cell:{}, field:{}, type:{}", cell.getCellTypeEnum(), field.getName(), field.getType().getName());
    switch (cell.getCellTypeEnum()) {
        case BLANK:
            break;
        case BOOLEAN:
            field.setBoolean(o, cell.getBooleanCellValue());
            break;
        case ERROR:
            field.setByte(o, cell.getErrorCellValue());
            break;
        case FORMULA:
            field.set(o, cell.getCellFormula());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                if (field.getType().getName().equals(Date.class.getName())) {
                    field.set(o, cell.getDateCellValue());
                } else {
                    field.set(o, format.format(cell.getDateCellValue()));
                }
            } else {
                if (field.getType().isAssignableFrom(Integer.class) || field.getType().getName().equals("int")) {
                    field.setInt(o, (int) cell.getNumericCellValue());
                } else if (field.getType().isAssignableFrom(Short.class) || field.getType().getName().equals("short")) {
                    field.setShort(o, (short) cell.getNumericCellValue());
                } else if (field.getType().isAssignableFrom(Float.class) || field.getType().getName().equals("float")) {
                    field.setFloat(o, (float) cell.getNumericCellValue());
                } else if (field.getType().isAssignableFrom(Byte.class) || field.getType().getName().equals("byte")) {
                    field.setByte(o, (byte) cell.getNumericCellValue());
                } else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
                    field.setDouble(o, cell.getNumericCellValue());
                } else if (field.getType().isAssignableFrom(String.class)) {
                    String s = String.valueOf(cell.getNumericCellValue());
                    if (s.contains("E")) {
                        s = s.trim();
                        BigDecimal bigDecimal = new BigDecimal(s);
                        s = bigDecimal.toPlainString();
                    }
                    //防止整数判定为浮点数
                    if (s.endsWith(".0")) { s = s.substring(0, s.indexOf(".0")); }
                    field.set(o, s);
                } else {
                    field.set(o, cell.getNumericCellValue());
                }
            }
            break;
        case STRING:
            if (field.getType().getName().equals(Date.class.getName())) {
                field.set(o, format.parse(cell.getRichStringCellValue().getString()));
            } else {
                field.set(o, cell.getRichStringCellValue().getString());
            }
            break;
        default:
            field.set(o, cell.getStringCellValue());
            break;
    }
}

Source File: ExcelHelp.java From hy.common.report with Apache License 2.0

4 votes

public final static PartitionMap<String ,RCell> readDatas(Sheet i_Sheet ,Integer i_BeginRow ,Integer i_EndRow)
{
    PartitionMap<String ,RCell> v_Ret      = new TablePartition<String ,RCell>();
    Sheet                       v_Sheet    = i_Sheet;
    int                         v_BeginRow = 0;
    int                         v_EndRow   = 0;
    
    if ( i_BeginRow != null )
    {
        v_BeginRow = i_BeginRow.intValue();
        
        if ( v_BeginRow < 0 )
        {
            v_BeginRow = 0;
        }
    }
    
    if ( i_EndRow != null )
    {
        v_EndRow = i_EndRow.intValue();
    }
    else
    {
        v_EndRow = v_Sheet.getLastRowNum();
    }
    
    for (int v_RowNo=v_BeginRow; v_RowNo<=v_EndRow; v_RowNo++)
    {
        Row v_Row = v_Sheet.getRow(v_RowNo);
        if ( v_Row == null )
        {
            continue;
        }
        
        short v_CellCount = v_Row.getLastCellNum();
        
        for (int v_ColumnNo=0; v_ColumnNo<v_CellCount; v_ColumnNo++)
        {
            Cell v_Cell = v_Row.getCell(v_ColumnNo);
            if ( v_Cell == null )
            {
                continue;
            }
            
            if ( v_Cell.getCellTypeEnum() == CellType.STRING )
            {
                String v_Value = v_Cell.getStringCellValue();
                
                if ( !Help.isNull(v_Value) )
                {
                    RCell        v_RCell    = new RCell(v_RowNo ,v_ColumnNo);
                    List<String> v_Decimals = StringHelp.getString(v_Cell.getCellStyle().getDataFormatString() ,$Decimal);
                    if ( !Help.isNull(v_Decimals) )
                    {
                        v_RCell.setDecimal(v_Decimals.get(0).split("\\.")[1].length());
                    }
                    
                    v_Ret.putRow(v_Value ,v_RCell); // 2018-05-07 修复：不再trim()。因为去空格后，原数据格式会被改变，比如说用户就是想导出空格呢？
                                                    //            其二，当为占位符查询时，也是不去空格的查询，这里去空格后，为查询不到匹配的占位符解析信息的。
                }
            }
            else if ( v_Cell.getCellTypeEnum() == CellType.NUMERIC )
            {
                if ( HSSFDateUtil.isCellDateFormatted(v_Cell) ) 
                {
                    if ( v_Cell.getDateCellValue() != null )
                    {
                        v_Ret.putRow((new Date(v_Cell.getDateCellValue())).getFull() ,new RCell(v_RowNo ,v_ColumnNo));
                    }
                } 
                else 
                {
                    v_Ret.putRow(String.valueOf(v_Cell.getNumericCellValue()) ,new RCell(v_RowNo ,v_ColumnNo));
                }
            }
        }
    }
    
    return v_Ret;
}

Source File: ExcelUtil.java From LuckyFrameWeb with GNU Affero General Public License v3.0

4 votes

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return null;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}

Source File: MyExcelUtils.java From spring-boot with Apache License 2.0

4 votes

/**
     * 根据 cell 格式，自动转换 cell 内容为 String
     *
     * @param cell
     * @param datePattern 日期格式  yyyy-MM-dd , yyyy-MM-dd HH:mm:ss  ...
     * @return
     */
    private static String getFormatCellValue(Cell cell, String datePattern) {

        //如果是日期格式，重新格式化
        if (cell.getCellTypeEnum() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {

            return DateFormatUtils.format(cell.getDateCellValue(), datePattern);

        } else //默认格式化

            return new DataFormatter().formatCellValue(cell).trim();


        /**
         * DataFormatter().formatCellValue(cell) 的源码
         */
//        if (cell == null) {
//            return "";
//        }
//
//        int cellType = cell.getCellType();
//        if (cellType == Cell.CELL_TYPE_FORMULA) {
//            if (evaluator == null) {
//                return cell.getCellFormula();
//            }
//            cellType = evaluator.evaluateFormulaCell(cell);
//        }
//        switch (cellType) {
//            case Cell.CELL_TYPE_NUMERIC :
//
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return getFormattedDateString(cell);
//                }
//                return getFormattedNumberString(cell);
//
//            case Cell.CELL_TYPE_STRING :
//                return cell.getRichStringCellValue().getString();
//
//            case Cell.CELL_TYPE_BOOLEAN :
//                return String.valueOf(cell.getBooleanCellValue());
//            case Cell.CELL_TYPE_BLANK :
//                return "";
//            case Cell.CELL_TYPE_ERROR:
//                return FormulaError.forInt(cell.getErrorCellValue()).getString();
//        }
//        throw new RuntimeException("Unexpected celltype (" + cellType + ")");


    }

Source File: ExcelUtil.java From ruoyiplus with MIT License

4 votes

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}

Source File: ExcelUtil.java From RuoYi-Vue with MIT License

4 votes

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (StringUtils.isNotNull(cell))
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}

Source File: CellFormat.java From lams with GNU General Public License v2.0

3 votes

/**
 * Returns the ultimate cell type, following the results of formulas.  If
 * the cell is a {@link CellType#FORMULA}, this returns the result of
 * {@link Cell#getCachedFormulaResultTypeEnum()}.  Otherwise this returns the
 * result of {@link Cell#getCellTypeEnum()}.
 *
 * @param cell The cell.
 *
 * @return The ultimate type of this cell.
 * @since POI 3.15 beta 3
 * @deprecated POI 3.15 beta 3
 * Will be deleted when we make the CellType enum transition. See bug 59791.
 */
public static CellType ultimateTypeEnum(Cell cell) {
    CellType type = cell.getCellTypeEnum();
    if (type == CellType.FORMULA)
        return cell.getCachedFormulaResultTypeEnum();
    else
        return type;
}

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