List of usage examples for org.apache.poi.ss.usermodel FormulaError forInt
public static FormulaError forInt(int type) throws IllegalArgumentException
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
public static String getCellValue(Cell cell) { // 2016.01.20 j.amano // ?jxl to poi ????? //------------------------ //??:\-1,000/*from w w w . j a v a2s.c o m*/ //jxl:($1,000)?$????????? //poi:-1000 //------------------------ //??:2016/1/20 //jxl:0020, 1 20, 2016 //poi:2016/01/20 00:00:00 //------------------------ //??:#REF!??? //jxl:#REF! //poi:#REF! //------------------------ //??:1,000 //jxl:" "1,000 //poi:-1000 //------------------------ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: // ?? if (DateUtil.isCellDateFormatted(cell)) { // ???? Date dt = cell.getDateCellValue(); // ???? DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); String sDate = df.format(dt); return sDate; } // ???.0 DecimalFormat format = new DecimalFormat("0.#"); return format.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); return getCellValue(evaluator.evaluateInCell(cell)); case Cell.CELL_TYPE_ERROR: byte errorCode = cell.getErrorCellValue(); FormulaError error = FormulaError.forInt(errorCode); String errorText = error.getString(); return errorText; default: return ""; } } return ""; }
From source file:com.miraisolutions.xlconnect.utils.CellUtils.java
License:Open Source License
public static String getErrorMessage(byte errorCode) { return getErrorMessage(FormulaError.forInt(errorCode)); }
From source file:edu.si.sidora.excel2tabular.TabularCell.java
License:Apache License
@Override public String toString() { if (cell == null) { return EMPTY_STRING; }// w w w.j a v a2s . c o m final int cellType = cell.getCellType(); switch (cellType) { case CELL_TYPE_NUMERIC: { if (isCellDateFormatted(cell)) { final Date date = getJavaDate(cell.getNumericCellValue()); final String dateFmt = cell.getCellStyle().getDataFormatString(); return new CellDateFormatter(dateFmt).format(date); } final Double numericCellValue = cell.getNumericCellValue(); if (isMathematicalInteger(numericCellValue)) { return Integer.toString(numericCellValue.intValue()); } return Double.toString(numericCellValue); } case CELL_TYPE_BLANK: return EMPTY_STRING; case CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_FORMULA: { cell.setCellType(cell.getCachedFormulaResultType()); return toString(); } case CELL_TYPE_STRING: final String stringCellValue = cell.getStringCellValue(); return quote(stringCellValue); case CELL_TYPE_ERROR: return FormulaError.forInt(cell.getErrorCellValue()).getString(); default: final CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); throw new ExcelParsingException( "Unregistered cell type: " + cellType + " at " + cellReference.formatAsString() + "!", new IllegalArgumentException("Not a registered POI cell type: " + cellType)); } }
From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java
License:Apache License
public <T> T getValue(Row row, int column, Class<T> valueType) { if (row == null) { return null; }//from w ww . j ava2 s. c o m Cell cell = row.getCell(column); if (cell == null) { return null; } CellValue cellValue = getCellValue(row, cell, column); if (cellValue == null) { return null; } switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (String.class.isAssignableFrom(valueType)) { return (T) Boolean.toString(cellValue.getBooleanValue()); } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(cellValue.getBooleanValue() ? 1 : 0); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(cellValue.getBooleanValue() ? 1L : 0L); } else { throw new ClassCastException( "Can't convert " + cellValue.getBooleanValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_STRING: String stringValue = cellValue.getStringValue(); if (CompareUtils.isEmpty(stringValue)) { return null; } if ("null".equals(stringValue)) { return null; } if (String.class.isAssignableFrom(valueType)) { return (T) stringValue; } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(stringValue); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(stringValue); } else if (valueType.isEnum()) { return (T) Enum.valueOf((Class<? extends Enum>) valueType, stringValue); } else if (BigDecimal.class.isAssignableFrom(valueType)) { return (T) (CompareUtils.isEmpty(stringValue) ? null : new BigDecimal(stringValue)); } else if (Boolean.class.isAssignableFrom(valueType)) { return (T) Boolean.valueOf("true".equalsIgnoreCase(stringValue) || (!CompareUtils.isEmpty(stringValue) && !"0".equals(stringValue))); } else { throw new ClassCastException("Can't convert " + stringValue + " to " + valueType.getName()); } case Cell.CELL_TYPE_NUMERIC: if (String.class.isAssignableFrom(valueType)) { Format format = formatter.createFormat(cell); if (format == null) { // TODO: do this without creating a BigDecimal each time return (T) new BigDecimal(cellValue.getNumberValue()).toString(); } else { return (T) format.format(cellValue.getNumberValue()); } } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf((int) cellValue.getNumberValue()); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf((int) cellValue.getNumberValue()); } else { throw new ClassCastException( "Can't convert " + cellValue.getNumberValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_ERROR: FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); if (FormulaError.NA.equals(error)) { return null; } else { // System.err.format(" Cell[%d,%d] error code %s\n", r.getRowNum(), column, error); return null; // throw new RuntimeException(String.format("Cell[%d,%d] error code %s", r.getRowNum(), column, error)); } } throw new IllegalArgumentException("Don't know how to convert cell of type " + cellValue.getCellType()); }
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
public static String getCellValue(Workbook wb, Cell cell) { if (cell == null) { return null; }// ww w . j a v a 2s .c o m final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")"; final String result; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: result = null; break; case Cell.CELL_TYPE_BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: String errorResult; try { byte errorCode = cell.getErrorCellValue(); FormulaError formulaError = FormulaError.forInt(errorCode); errorResult = formulaError.getString(); } catch (RuntimeException e) { logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage()); if (cell instanceof XSSFCell) { // hack to get error string, which is available String value = ((XSSFCell) cell).getErrorCellString(); errorResult = value; } else { logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e); throw e; } } result = errorResult; break; case Cell.CELL_TYPE_FORMULA: // result = cell.getCellFormula(); result = getFormulaCellValue(wb, cell); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date == null) { result = null; } else { result = DateUtils.createDateFormat().format(date); } } else { // TODO: Consider not formatting it, but simple using // Double.toString(...) result = _numberFormat.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; default: throw new IllegalStateException("Unknown cell type: " + cell.getCellType()); } logger.debug("cell {} resolved to value: {}", cellCoordinate, result); return result; }
From source file:org.nuclos.server.common.ooxml.ExcelReader.java
License:Open Source License
private static Object getCellValue(Cell cell, int cellType) { switch (cellType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {/* w ww .j a va 2 s.c om*/ return cell.getNumericCellValue(); } case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_ERROR: return FormulaError.forInt(cell.getErrorCellValue()).getString(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); default: throw new IllegalArgumentException("Unknown POI cell type " + cellType); } }