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

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getBooleanCellValue() . 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: XlsxReader.java    From tablesaw with Apache License 2.0 6 votes vote down vote up
private Boolean isBlank(Cell cell) {
  switch (cell.getCellType()) {
    case STRING:
      if (cell.getRichStringCellValue().length() > 0) {
        return false;
      }
      break;
    case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
          ? cell.getDateCellValue() != null
          : cell.getNumericCellValue() != 0) {
        return false;
      }
      break;
    case BOOLEAN:
      if (cell.getBooleanCellValue()) {
        return false;
      }
      break;
    case BLANK:
      return true;
    default:
      break;
  }
  return null;
}
 
Example 2
Source File: TestController.java    From Mario with Apache License 2.0 6 votes vote down vote up
private Object setCellValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        default:
            System.out.println();
            return "";
    }
}
 
Example 3
Source File: ExcelImportServer.java    From easypoi with Apache License 2.0 6 votes vote down vote up
/**
 * 获取key的值,针对不同类型获取不同的值
 * 
 * @Author JueYue
 * @date 2013-11-21
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
    Object obj = null;
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            obj = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            obj = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            obj = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            obj = cell.getCellFormula();
            break;
    }
    return obj == null ? null : obj.toString().trim();
}
 
Example 4
Source File: XlsxReader.java    From tablesaw with Apache License 2.0 6 votes vote down vote up
private Boolean isBlank(Cell cell) {
  switch (cell.getCellType()) {
    case STRING:
      if (cell.getRichStringCellValue().length() > 0) {
        return false;
      }
      break;
    case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
          ? cell.getDateCellValue() != null
          : cell.getNumericCellValue() != 0) {
        return false;
      }
      break;
    case BOOLEAN:
      if (cell.getBooleanCellValue()) {
        return false;
      }
      break;
    case BLANK:
      return true;
    default:
      break;
  }
  return null;
}
 
Example 5
Source File: CsvColumnKeyRowKeySourceGetter.java    From SimpleFlatMapper with MIT License 6 votes vote down vote up
@Override
public Object getValue(CsvColumnKey key, Row source) {
    final Cell cell = source.getCell(key.getIndex());
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                return null;
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            default:
                return cell.getStringCellValue();
        }
    }
    return null;
}
 
Example 6
Source File: ImportExcel.java    From Shop-for-JavaWeb with MIT License 6 votes vote down vote up
/**
 * 获取单元格值
 * @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();
			}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;
}
 
Example 7
Source File: ExcelPOIHelper.java    From tutorials with MIT License 6 votes vote down vote up
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 8
Source File: ExcelUtils.java    From onetwo with Apache License 2.0 6 votes vote down vote up
public static Object getCellValue(Cell cell){
		if(cell==null)
			return null;
		int type = cell.getCellType();
		Object value = null;
		if(Cell.CELL_TYPE_STRING==type){
//			value = StringUtils.cleanInvisibleUnicode(cell.getStringCellValue().trim());
			value = cell.getStringCellValue().trim();
		}else if(Cell.CELL_TYPE_NUMERIC==type){
			value = cell.getNumericCellValue();
		}else if(Cell.CELL_TYPE_FORMULA==type){
			value = cell.getCellFormula();
		}else if(Cell.CELL_TYPE_BOOLEAN==type){
			value = cell.getBooleanCellValue();
		}else if(Cell.CELL_TYPE_BLANK==type){
			value = "";
		}
		return value;
	}
 
Example 9
Source File: PoiBooleanGetter.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@Override
public boolean getBoolean(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return cell.getBooleanCellValue();
    } else {
        return false;
    }
}
 
Example 10
Source File: XlsUtils.java    From data-prep with Apache License 2.0 5 votes vote down vote up
/**
 *
 * @param cell the cell
 * @param formulaEvaluator the formula to evaluate, if needed
 * @return return the cell value as String
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellType()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case NUMERIC:
        return getNumericValue(cell, null, false);
    case STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}
 
Example 11
Source File: ExcelUtil.java    From springboot-learn with MIT License 5 votes vote down vote up
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;
}
 
Example 12
Source File: ExcelUtil.java    From javautils with Apache License 2.0 5 votes vote down vote up
/**
 * 获取cell数据
 * @param cell
 * @return
 */
private static String getCellValue(Cell cell){
    String value = "";
    if(cell != null) {
        switch (cell.getCellTypeEnum()) {
            case FORMULA:
                value += cell.getCellFormula();
                break;

            case NUMERIC:
                double cellValue = cell.getNumericCellValue();
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date date = HSSFDateUtil.getJavaDate(cellValue);
                    value += DateUtil.format(date, DateUtil.DATE_TIME);
                }else{
                    value += cell.getNumericCellValue();
                }
                break;

            case STRING:
                value += cell.getStringCellValue();
                break;
            case BLANK:
                break;
            case BOOLEAN:
                value += cell.getBooleanCellValue();
                break;
            case ERROR:
                break;
            default:break;
        }
    }
    return value;
}
 
Example 13
Source File: CellValueAndNotFormulaHelper.java    From tutorials with MIT License 5 votes vote down vote up
public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 
Example 14
Source File: ExcelUtils.java    From components with Apache License 2.0 5 votes vote down vote up
/**
 *
 * @param cell
 * @param formulaEvaluator
 * @return return the cell value as String (if needed evaluate the existing formula)
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellTypeEnum()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case NUMERIC:
        return getNumericValue(cell, null, false);
    case STRING:
        //TODO which is better? StringUtils.trim(cell.getStringCellValue())
        return cell.getRichStringCellValue().getString();
    default:
        return "Unknown Cell Type: " + cell.getCellTypeEnum();
    }
}
 
Example 15
Source File: SheetReaderAbs.java    From xcelite with Apache License 2.0 5 votes vote down vote up
protected Object readValueFromCell(Cell cell) {
  if (cell == null) return null;
  Object cellValue = null;
  switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
      cellValue = cell.getBooleanCellValue();
      break;
    case Cell.CELL_TYPE_NUMERIC:
      cellValue = cell.getNumericCellValue();
      break;
    default:
      cellValue = cell.getStringCellValue();
  }
  return cellValue;
}
 
Example 16
Source File: OneClickImporterServiceImpl.java    From molgenis with GNU Lesser General Public License v3.0 4 votes vote down vote up
/** 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 17
Source File: ExcelUtil.java    From LuckyFrameWeb with GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @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 18
Source File: ExcelUtil.java    From RuoYi-Vue with MIT License 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @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 19
Source File: ExcelUtil.java    From supplierShop with MIT License 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @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 || 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: ExcelUtil.java    From SI with BSD 2-Clause "Simplified" License 4 votes vote down vote up
/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}