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

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getCellTypeEnum() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

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

Example 2

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

}

Example 3

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

Example 4

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

Example 5

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

Example 6

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

Example 7

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

Example 8

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

Example 9

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

Example 10

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

Example 11

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

Example 12

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

Example 13

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

Example 14

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

Example 15

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

Example 16

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

Example 17

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 + ")");


    }

Example 18

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

Example 19

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

Example 20

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