List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted
public static boolean isCellDateFormatted(Cell cell)
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private Object getCellValue(HSSFCell cell, boolean resolveFormula) { if (cell == null) { return null; }/* w ww . ja va 2s .com*/ Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case HSSFCell.CELL_TYPE_NUMERIC: /*System.out.println("Last evaluated as: " + cell.getNumericCellValue()); result = cell.getNumericCellValue(); break;*/ if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\""); result = cell.getRichStringCellValue(); break; } //result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); System.out.println("Data format for " + cell.getColumnIndex() + " = " + dataFormat); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. //if (dataFormat == 165 || dataFormat == 164 || dataFormat == 14) { if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } if (dataFormat == 0) { // alternative way of getting value : can this be replaced for the entire block result = new HSSFDataFormatter().formatCellValue(cell); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: //result = cell.getStringCellValue(); result = cell.getRichStringCellValue(); System.out.println("String -> " + result); break; default: break; } if (result instanceof Integer) { return String.valueOf((Integer) result); } else if (result instanceof Double) { return String.valueOf(((Double) result)); //.longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java
License:Open Source License
@Override public ValueType getNativeValueType(int columnIndex) throws ParseException { Cell cell = getCurrentCell(columnIndex); final int type = cell.getCellType(); if (type == Cell.CELL_TYPE_BLANK) { return ValueType.EMPTY; } else if (type == Cell.CELL_TYPE_STRING) { return ValueType.STRING; } else if (type == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { return ValueType.DATE; } else {/*from w ww.j a v a2 s.co m*/ return ValueType.NUMBER; } } else if (type == Cell.CELL_TYPE_FORMULA) { if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) { return ValueType.NUMBER; } else { return ValueType.STRING; } } else { return ValueType.STRING; } }
From source file:com.siacra.beans.GrupoBean.java
public void archivoXlsx(String path, FileUploadEvent archivo) { excelResponse = new ArrayList<>(); List<Horario> horas = new ArrayList<>(); try {/*ww w .j a v a2 s .c o m*/ FileInputStream file = new FileInputStream(new File(path + "\\" + archivo.getFile().getFileName())); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /* * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda int cantidad = 0; int cantidad2 = 0; while (rowIterator.hasNext()) { row = rowIterator.next(); if (cantidad2 != 0) { Horario h = new Horario(); UpploadGrupos grupo = new UpploadGrupos(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; cantidad = 1; while (cellIterator.hasNext()) { celda = cellIterator.next(); // if((cantidad%10)==0) // { // System.out.print(grupo.toString()); // excelResponse.add(grupo); // grupo = new UpploadGrupos(); // cantidad=1; // } // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { SimpleDateFormat f = new SimpleDateFormat("HH:mm:ss"); String fecha = f.format(celda.getDateCellValue()); System.out.print(":::::::: " + fecha); Date dos = f.parse(fecha); if (cantidad == 4) { h.setHinicio1(dos); grupo.setInicio1(dos); cantidad++; } else if (cantidad == 5) { h.setHfin1(dos); grupo.setFin1(dos); cantidad++; } else if (cantidad == 7) { h.setHinicio2(dos); grupo.setInicio2(dos); cantidad++; } else if (cantidad == 8) { h.setHfin2(dos); grupo.setFin2(dos); cantidad++; } System.out.println(dos); } // else // { // double numero = celda.getNumericCellValue(); // System.out.println(celda.getNumericCellValue()); // } break; case Cell.CELL_TYPE_STRING: if (cantidad == 1) { grupo.setAsignatura(celda.getStringCellValue()); cantidad++; } else if (cantidad == 2) { grupo.setTipoGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 3) { h.setDia1(celda.getStringCellValue()); grupo.setDia1(celda.getStringCellValue()); cantidad++; } else if (cantidad == 6) { h.setDia2(celda.getStringCellValue()); grupo.setDia2(celda.getStringCellValue()); cantidad++; } else if (cantidad == 9) { grupo.setNumeroGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 10) { grupo.setCupos(celda.getStringCellValue()); cantidad++; } String texto = celda.getStringCellValue(); System.out.println(celda.getStringCellValue()); break; // case Cell.CELL_TYPE_BOOLEAN: // System.out.println(celda.getBooleanCellValue()); // break; }//fin if que obtiene valor de celda } //fin while que recorre celdas System.out.print("objeto:::" + grupo.toString()); System.out.print("objeto:::" + h.toString()); horas.add(h); excelResponse.add(grupo); } // fin if primera iteracion cantidad2++; } // fin while que recorre filas // cerramos el libro excel workbook.close(); } catch (Exception e) { e.printStackTrace(); } procesarListaCargada(); }
From source file:com.ssy.havefun.f3d.F3DDaoImpl.java
public String getCellValue(Cell cell) { String ret = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: ret = ""; break;/*from w w w . j av a 2 s.com*/ case Cell.CELL_TYPE_BOOLEAN: ret = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: ret = null; break; case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); ret = getCellValue(evaluator.evaluateInCell(cell)); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // Date theDate = cell.getDateCellValue(); // ret = simpleDateFormat.format(theDate); } else { ret = NumberToTextConverter.toText(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: ret = cell.getRichStringCellValue().getString(); break; default: ret = null; } return ret; //?trim }
From source file:com.streamsets.pipeline.lib.parser.excel.Cells.java
License:Apache License
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(); }// w w w . j a va2 s . co m 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); } }
From source file:com.tecacet.jflat.excel.PoiExcelReader.java
License:Apache License
private String getCellContentAsString(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().toString(); } else {//ww w . j a v a 2 s .com double d = cell.getNumericCellValue(); // TODO find a flexible enough format for all numeric types return numberFormat.format(d); // return Double.toString(d); } case Cell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.toString(b); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: byte bt = cell.getErrorCellValue(); return Byte.toString(bt); default: return cell.getStringCellValue(); } }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
protected CellData createCellDataForCell(Cell cell) { CellData cellData = new CellData(); cellData.row = cell.getRowIndex() + 1; cellData.col = cell.getColumnIndex() + 1; CellStyle cellStyle = cell.getCellStyle(); cellData.cellStyle = "cs" + cellStyle.getIndex(); cellData.locked = spreadsheet.isCellLocked(cell); try {// w w w . ja va2 s . c om if (!spreadsheet.isCellHidden(cell)) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellData.formulaValue = formulaFormatter.reFormatFormulaValue(cell.getCellFormula(), spreadsheet.getLocale()); try { String oldValue = getCachedFormulaCellValue(cell); String newValue = formatter.formatCellValue(cell, getFormulaEvaluator()); if (!newValue.equals(oldValue)) { changedFormulaCells.add(new CellReference(cell)); } } catch (RuntimeException rte) { // Apache POI throws RuntimeExceptions for an invalid // formula from POI model String formulaValue = cell.getCellFormula(); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(formulaValue); spreadsheet.markInvalidFormula(cell.getColumnIndex() + 1, cell.getRowIndex() + 1); } } } if (cell.getCellStyle().getDataFormatString().contains("%")) { cellData.isPercentage = true; } String formattedCellValue = formatter.formatCellValue(cell, getFormulaEvaluator()); if (!spreadsheet.isCellHidden(cell)) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { formattedCellValue = formattedCellValue.replaceAll("^-(?=0(.0*)?$)", ""); } } if (spreadsheet.isMarkedAsInvalidFormula(cellData.col, cellData.row)) { // The prefix '=' or '+' should not be included in formula value if (cell.getStringCellValue().charAt(0) == '+' || cell.getStringCellValue().charAt(0) == '=') { cellData.formulaValue = cell.getStringCellValue().substring(1); } formattedCellValue = "#VALUE!"; } if (formattedCellValue != null && !formattedCellValue.isEmpty() || cellStyle.getIndex() != 0) { // if the cell is not wrapping text, and is of type numeric or // formula (but not date), calculate if formatted cell value // fits the column width and possibly use scientific notation. cellData.value = formattedCellValue; cellData.needsMeasure = false; if (!cellStyle.getWrapText() && (!SpreadsheetUtil.cellContainsDate(cell) && cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_STRING || (cell.getCellType() == Cell.CELL_TYPE_FORMULA && !cell.getCellFormula().startsWith("HYPERLINK")))) { if (!doesValueFit(cell, formattedCellValue)) { if (valueContainsOnlyNumbers(formattedCellValue) && isGenerallCell(cell)) { cellData.value = cellValueFormatter.getScientificNotationStringForNumericCell( cell.getNumericCellValue(), formattedCellValue, cellStyleWidthRatioMap.get((int) cell.getCellStyle().getIndex()), spreadsheet.getState(false).colW[cell.getColumnIndex()] - 10); } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) { cellData.needsMeasure = true; } } } if (cellStyle.getAlignment() == CellStyle.ALIGN_RIGHT) { cellData.cellStyle = cellData.cellStyle + " r"; } else if (cellStyle.getAlignment() == CellStyle.ALIGN_GENERAL) { if (SpreadsheetUtil.cellContainsDate(cell) || cell.getCellType() == Cell.CELL_TYPE_NUMERIC || (cell.getCellType() == Cell.CELL_TYPE_FORMULA && !cell.getCellFormula().startsWith("HYPERLINK") && !(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING))) { cellData.cellStyle = cellData.cellStyle + " r"; } } } // conditional formatting might be applied even if there isn't a // value (such as borders for the cell to the right) Set<Integer> cellFormattingIndexes = spreadsheet.getConditionalFormatter().getCellFormattingIndex(cell); if (cellFormattingIndexes != null) { for (Integer i : cellFormattingIndexes) { cellData.cellStyle = cellData.cellStyle + " cf" + i; } markedCells.add(SpreadsheetUtil.toKey(cell)); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { cellData.originalValue = cellData.value; } else { cellData.originalValue = getOriginalCellValue(cell); } handleIsDisplayZeroPreference(cell, cellData); } catch (RuntimeException rte) { LOGGER.log(Level.FINEST, rte.getMessage(), rte); cellData.value = "#VALUE!"; } return cellData; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
public String getOriginalCellValue(Cell cell) { if (cell == null) { return ""; }//from w w w. java 2s . c om int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date dateCellValue = cell.getDateCellValue(); if (dateCellValue != null) { return new SimpleDateFormat().format(dateCellValue); } return ""; } return originalValueDecimalFormat.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: return String.valueOf(cell.getErrorCellValue()); } return ""; }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
/** * Determines whether the given cell contains a date or not. * //from w ww . j ava 2s . c o m * @param cell * Cell to examine * @return true if the cell contains a date */ public static boolean cellContainsDate(Cell cell) { return cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell); }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
/** * * @param file/*from w ww . j a va 2 s.com*/ * @return * @throws FileNotFoundException * @throws InvalidFormatException * @throws IOException */ /*this method convert a multipart file to json object */ @Override public JSONObject convert(MultipartFile file, String name) throws FileNotFoundException, InvalidFormatException, IOException { // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx"); File filez = File.createTempFile("fichier", "xslx"); file.transferTo(filez); FileInputStream inp = new FileInputStream(filez); Workbook workbook = WorkbookFactory.create(inp); //Sheet sheet = workbook.getSheetAt( 0 ); Sheet sheet = workbook.getSheet(name); // Start constructing JSON. JSONObject json = new JSONObject(); // Iterate through the rows. JSONArray rows = new JSONArray(); for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); JSONObject jRow = new JSONObject(); // Iterate through the cells. JSONArray cells = new JSONArray(); for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) { Cell cell = cellsIT.next(); // System.out.println(cell.getCellType()); // cells.put(cell.getDateCellValue()); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cells.put(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cells.put(cell.getDateCellValue()); } else { cells.put(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cells.put(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cells.put(cell.getCellFormula()); break; default: System.out.println(); } } jRow.put("cell", cells); rows.put(cells); //rows.put( jRow ); } // Create the JSON. json.put("rows", rows); System.out.println(json.toString()); return json; }