List of usage examples for org.apache.poi.ss.usermodel Cell getErrorCellValue
byte getErrorCellValue();
From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java
public static Object getWithIntCelValue(Cell cell) { Object cellValue = null;// w ww . j ava 2 s . c om switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = cell.getDateCellValue(); } else { cellValue = cell.getNumericCellValue(); Double i = (Double) cellValue; cellValue = i.intValue(); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BLANK: cellValue = new String(""); break; case Cell.CELL_TYPE_ERROR: cellValue = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: cellValue = cell.getCellFormula(); break; } return cellValue; }
From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java
protected static void copyContent(Sheet sheetSource, Sheet sheetDestination) { //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheetSource.iterator(); int i = 0;/* w ww . j a va 2 s .c om*/ while (rowIterator.hasNext()) { Row row = rowIterator.next(); Row rowDestination = sheetDestination.createRow(i); i++; //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int j = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); Cell cellDestination = rowDestination.createCell(j); j++; cellDestination.setCellComment(cell.getCellComment()); // cellDestination.setCellStyle(cell.getCellStyle()); cellDestination.setCellType(cell.getCellType()); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDestination.setCellValue(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellDestination.setCellValue(cell.getDateCellValue()); } else { cellDestination.setCellValue(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: cellDestination.setCellValue(cell.getRichStringCellValue()); break; case Cell.CELL_TYPE_BLANK: cellDestination.setCellValue(cell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: cellDestination.setCellValue(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellDestination.setCellFormula(cell.getCellFormula()); break; } } } }
From source file:org.wandora.application.tools.extractors.excel.AbstractExcelExtractor.java
License:Open Source License
protected String getCellValueAsString(Cell cell, int type) { if (cell != null) { switch (type) { case Cell.CELL_TYPE_ERROR: { return "ERROR" + cell.getErrorCellValue(); }/* w w w.j a v a 2s. c om*/ case Cell.CELL_TYPE_BOOLEAN: { return "" + cell.getBooleanCellValue(); } case Cell.CELL_TYPE_NUMERIC: { if (DateUtil.isCellDateFormatted(cell)) { return dateFormat.format(cell.getDateCellValue()); } else { double value = cell.getNumericCellValue(); String formatString = cell.getCellStyle().getDataFormatString(); int formatIndex = cell.getCellStyle().getDataFormat(); return formatter.formatRawCellContents(value, formatIndex, formatString); } } case Cell.CELL_TYPE_STRING: { return cell.getRichStringCellValue().getString(); } } } return null; }
From source file:plugins.excel.client.util.ExcelReader.java
License:Microsoft Reference Source License
private Object getCellValue(Cell c, int targetType) { int cellType = c.getCellType(); Object val; try {/*ww w . j ava 2s. c om*/ switch (cellType) { case (Cell.CELL_TYPE_STRING): case (Cell.CELL_TYPE_FORMULA): val = c.getStringCellValue(); switch (targetType) { case Types.BOOLEAN: return Boolean.parseBoolean((String) val); case Types.DOUBLE: return Double.parseDouble((String) val); case Types.INTEGER: return Integer.parseInt((String) val); case Types.VARCHAR: return (String) val; case Types.DATE: SimpleDateFormat sdf = new SimpleDateFormat(); try { return sdf.parse((String) val); } catch (ParseException e) { e.printStackTrace(); } } break; case (Cell.CELL_TYPE_NUMERIC): if (DateUtil.isCellDateFormatted(c)) { val = c.getDateCellValue(); switch (targetType) { case Types.BOOLEAN: return (((Date) val).getTime() > 0); case Types.DOUBLE: return (double) ((Date) val).getTime(); case Types.INTEGER: return ((Date) val).getTime(); case Types.VARCHAR: DateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); return df.format((Date) val); case Types.DATE: return (Date) val; } } else { val = c.getNumericCellValue(); switch (targetType) { case Types.BOOLEAN: return ((double) val > 0.0); case Types.DOUBLE: return (double) val; case Types.INTEGER: return (long) val; case Types.VARCHAR: return new Double((double) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) val); return d; } } break; case (Cell.CELL_TYPE_ERROR): val = c.getErrorCellValue(); switch (targetType) { case Types.BOOLEAN: return ((int) val > 0); case Types.DOUBLE: return (double) val; case Types.INTEGER: return (int) val; case Types.VARCHAR: return new Integer((int) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) val); return d; } break; case (Cell.CELL_TYPE_BOOLEAN): val = c.getBooleanCellValue(); switch (targetType) { case Types.BOOLEAN: return (boolean) val; case Types.DOUBLE: return (double) (((boolean) val ? 1 : 0)); case Types.INTEGER: return (int) (((boolean) val ? 1 : 0)); case Types.VARCHAR: return new Boolean((boolean) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) (((boolean) val ? 1 : 0))); return d; } break; } } catch (IllegalStateException e) { Dialog.msgBox( "Could not import cell r:" + c.getRowIndex() + " c: " + c.getColumnIndex() + " because of data type errors in the sheet", "Import Excel File", Dialog.ERROR_MESSAGE); } return null; }
From source file:step.datapool.excel.ExcelFunctions.java
License:Open Source License
/** * Konvertiert unterschiedliche Formate in Strings. * * @param cell Excel Zelle/*from w w w. j av a 2 s . c o m*/ * @param evaluator FormulaEvaluator * @return Wert der Zelle als String */ public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) { boolean isFormulaPatched = false; String initialFormula = null; int chkTyp = cell.getCellType(); if (chkTyp == Cell.CELL_TYPE_FORMULA) { initialFormula = cell.getCellFormula(); // Some formula have to be changed before they can be evaluated in POI String formula = FormulaPatch.patch(initialFormula); if (!formula.equals(initialFormula)) { isFormulaPatched = true; cell.setCellFormula(formula); evaluator.notifySetFormula(cell); } } try { int typ = evaluateFormulaCell(cell, evaluator); if (typ == -1) typ = cell.getCellType(); switch (typ) { case Cell.CELL_TYPE_NUMERIC: /* Datum und Zeit (sind auch Zahlen) */ if (DateUtil.isCellDateFormatted(cell)) { Date dat = cell.getDateCellValue(); GregorianCalendar cal = new GregorianCalendar(); cal.setTime(dat); /* * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00). * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck- * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine * Zeit. */ if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss"); return STD_TIM.format(dat); } SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy"); return STD_DAT.format(dat); // Datumsformat } else { /* int, long, double Formate */ double dbl = cell.getNumericCellValue(); int tryInt = (int) dbl; long tryLong = (long) dbl; if (tryInt == dbl) { return new Integer(tryInt).toString(); // int-Format } else if (tryLong == dbl) { return new Long(tryLong).toString(); // long-Format } // return new Double(dbl).toString(); // double-Format String numberValueString = new Double(dbl).toString(); // double-Format // always use decimal format try { // scale 14 to solve problem like value 0.22 --> 0.219999999999997 BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14, RoundingMode.HALF_UP); // use constructor BigDecimal(String)! String customValueString = getCustomDecimalFormat().format(roundedBigDecimal); if (!customValueString.equals(numberValueString)) { logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'", numberValueString, customValueString); numberValueString = customValueString; // bigdecimal-format } } catch (Exception e) { logger.error("An error occurred trying to convert the cell value number to decimal format " + numberValueString, e); } return numberValueString; } case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_FORMULA: /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat. */ return cell.getCellFormula(); case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: switch (cell.getErrorCellValue()) { case 1: return "#NULL!"; case 2: return "#DIV/0!"; case 3: return "#VALUE!"; case 4: return "#REF!"; case 5: return "#NAME?"; case 6: return "#NUM!"; case 7: return "#N/A"; default: return "#ERR!"; } default: return "ERROR: unknown Format"; } } finally { if (isFormulaPatched) { cell.setCellFormula(initialFormula); evaluator.notifySetFormula(cell); } } }