List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted
public static boolean isCellDateFormatted(Cell cell)
From source file:CDatos.Excel.java
/** * Lee una hoja de un excel y devuelve una matriz con los datos * @return Matriz con los datos del excel *///from www . ja v a 2 s .co m public ArrayList getDatosHoja() { ArrayList<ArrayList> filas = new ArrayList(); XSSFSheet sheet = workbook.getSheetAt(hoja); int numColumnas = -1; // Recorremos fila a fila for (int r = 0; r <= sheet.getLastRowNum(); r++) { ArrayList<String> celdas = new ArrayList(); XSSFRow row = sheet.getRow(r); if (row == null) break; else { // En la primera fila se leen las cabeceras, por lo que aprovechamos para // guardar el nmero de columnas porque cuando una fila tiene celdas vacas el tamao // de la lista es menor if (numColumnas == -1) numColumnas = row.getLastCellNum(); // Recorremos celda a celda for (int c = 0; c < numColumnas; c++) { XSSFCell cell = row.getCell(c); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formateador = new SimpleDateFormat("yyyy-MM-dd"); //cellValue = cell.getDateCellValue().toString(); cellValue = formateador.format(cell.getDateCellValue()); } else { cellValue = (Integer.toString((int) cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } celdas.add(cellValue); } filas.add(celdas); } } return filas; }
From source file:ch.oakmountain.tpa.parser.TpaParser.java
License:Apache License
private String getCellValueString(Cell cell) { if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { cell.setCellStyle(timestyle);/*from w w w. j a va2 s . c o m*/ } return formatter.formatCellValue(cell); }
From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java
License:Apache License
/** * ??/* w w w.ja v a2 s . c o m*/ * * @param cell * @param entity * @return */ private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) { if (cell == null) { return ""; } Object result = null; if ("class java.util.Date".equals(classFullName) || "class java.sql.Date".equals(classFullName) || ("class java.sql.Time").equals(classFullName) || ("class java.time.Instant").equals(classFullName) || ("class java.time.LocalDate").equals(classFullName) || ("class java.time.LocalDateTime").equals(classFullName) || ("class java.sql.Timestamp").equals(classFullName)) { //FIX: ?yyyyMMdd cell.getDateCellValue() ? if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) { result = DateUtil.getJavaDate(cell.getNumericCellValue()); } else { String val = ""; try { val = cell.getStringCellValue(); } catch (Exception e) { cell.setCellType(CellType.STRING); val = cell.getStringCellValue(); } result = getDateData(entity, val); if (result == null) { return null; } } if (("class java.time.Instant").equals(classFullName)) { result = ((Date) result).toInstant(); } else if (("class java.time.LocalDate").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); } else if (("class java.time.LocalDateTime").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime(); } else if (("class java.sql.Date").equals(classFullName)) { result = new java.sql.Date(((Date) result).getTime()); } else if (("class java.sql.Time").equals(classFullName)) { result = new Time(((Date) result).getTime()); } else if (("class java.sql.Timestamp").equals(classFullName)) { result = new Timestamp(((Date) result).getTime()); } } else { switch (cell.getCellType()) { case STRING: result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { if ("class java.lang.String".equals(classFullName)) { result = formateDate(entity, cell.getDateCellValue()); } } else { result = readNumericCell(cell); } break; case BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case BLANK: break; case ERROR: break; case FORMULA: try { result = readNumericCell(cell); } catch (Exception e1) { try { result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); } catch (Exception e2) { throw new RuntimeException("???", e2); } } break; default: break; } } return result; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook // w ww .j a v a 2 s .c o m * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }
From source file:cn.study.innerclass.PoiUtil.java
License:Open Source License
public static Object getCellData(Cell cell, FormulaEvaluator formula) { if (cell == null) { return null; }/* w ww. ja v a 2 s . com*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); return cell.getDateCellValue(); } else { System.out.println(cell.getNumericCellValue()); return cell.getNumericCellValue(); } case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); return cell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getStringCellValue()); switch (formula.evaluate(cell).getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(formula.evaluate(cell).getStringValue()); return formula.evaluate(cell).getStringValue(); case Cell.CELL_TYPE_NUMERIC: System.out.println(formula.evaluate(cell).getNumberValue()); return formula.evaluate(cell).getNumberValue(); case Cell.CELL_TYPE_BOOLEAN: System.out.println(formula.evaluate(cell).getBooleanValue()); return formula.evaluate(cell); } default: return null; } }
From source file:co.foldingmap.data.ExcelDataConnector.java
License:Open Source License
/** * Returns a cell value as a DataCell object. * /*from w w w .j ava 2 s. c om*/ * @param cell * @return */ public DataCell getCellText(Cell cell) { DataCell cellText; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellText = new DataCell(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellText = new DataCell(cell.getDateCellValue().toString()); } else { cellText = new DataCell(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: cellText = new DataCell(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: cellText = new DataCell(cell.getCellFormula()); break; default: cellText = new DataCell(""); } return cellText; }
From source file:com.accenture.bean.PlanoExcel.java
public void extraiPlanilha() { try {//w ww . j a va2 s .co m //Leitura FileInputStream arquivo = new FileInputStream(new File(fileName)); // Carregando workbook XSSFWorkbook wb = new XSSFWorkbook(arquivo); // Selecionando a primeira aba XSSFSheet s = wb.getSheetAt(1); // Caso queira pegar valor por referencia CellReference cellReference = new CellReference("M8"); Row row = s.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println("Valor Refe:" + cell.getStringCellValue()); // Fazendo um loop em todas as linhas for (Row rowFor : s) { // FAzendo loop em todas as colunas for (Cell cellFor : rowFor) { try { // Verifica o tipo de dado if (cellFor.getCellType() == Cell.CELL_TYPE_NUMERIC) { // Na coluna 6 tenho um formato de data if (cellFor.getColumnIndex() == 6) { // Se estiver no formato de data if (DateUtil.isCellDateFormatted(cellFor)) { // Formatar para o padrao brasileiro Date d = cellFor.getDateCellValue(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); System.out.println(df.format(d)); } } else { // Mostrar numerico System.out.println(cellFor.getNumericCellValue()); } } else { // Mostrar String System.out.println(cellFor.getStringCellValue()); } } catch (Exception e) { // Mostrar Erro System.out.println(e.getMessage()); } } // Mostrar pulo de linha System.out.println("------------------------"); } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.adobe.acs.commons.data.Variant.java
License:Apache License
private void setValue(Cell cell) { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { cellType = cell.getCachedFormulaResultType(); }//from w ww . ja v a 2s . co m switch (cellType) { case Cell.CELL_TYPE_BOOLEAN: setValue(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: double number = cell.getNumericCellValue(); if (Math.floor(number) == number) { setValue((long) number); } else { setValue(number); } if (DateUtil.isCellDateFormatted(cell)) { setValue(cell.getDateCellValue()); } DataFormatter dataFormatter = new DataFormatter(); if (cellType == Cell.CELL_TYPE_FORMULA) { setValue(dataFormatter.formatCellValue(cell)); } else { CellStyle cellStyle = cell.getCellStyle(); setValue(dataFormatter.formatRawCellContents(cell.getNumericCellValue(), cellStyle.getDataFormat(), cellStyle.getDataFormatString())); } break; case Cell.CELL_TYPE_STRING: setValue(cell.getStringCellValue().trim()); break; case Cell.CELL_TYPE_BLANK: default: clear(); break; } }
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Reads the excel file row by row. Fills the excel import internal structure which is column wise.<p> * // ww w. ja v a 2 s .co m * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnContents(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int rowsNumber = sheet.getPhysicalNumberOfRows(); if (rowsNumber > 0) { // loop over all rows from excel // do not read first column, because here are only user raw names for (int rowCounter = 1; rowCounter < sheet.getPhysicalNumberOfRows(); rowCounter++) { HSSFRow row = sheet.getRow(rowCounter); if ((row != null)) { // get number of rows in excel if ((rowCounter) > m_rowNumber) { m_rowNumber = rowCounter; } // loop over all columns in this row for (int columnCounter = 0; columnCounter < row.getLastCellNum(); columnCounter++) { CmsExcelColumn cmsExcelCol = (CmsExcelColumn) m_colContents.get(new Integer(columnCounter)); if (cmsExcelCol != null) { // read cell HSSFCell cell = row.getCell((short) columnCounter); if (cell != null) { String text = null; try { // read cell content from excel switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case Cell.CELL_TYPE_NUMERIC: // check for date if (DateUtil.isCellDateFormatted(cell) || DateUtil.isValidExcelDate(cell.getNumericCellValue())) { // valid date Date date = DateUtil.getJavaDate(cell.getNumericCellValue()); text = new Long(date.getTime()).toString(); } else { // no valid date text = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } // add to column list cmsExcelCol.addNewCellValue(rowCounter, text); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error(e.toString()); } } } else { // add to column list cmsExcelCol.addNewCellValue(rowCounter, ""); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } } } } } } }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?StringOption??//from ww w.j a v a 2s. c o m * @param cell ? * @return ? */ private StringOption getStringOption(HSSFCell cell) { String str; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = null; break; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { str = "1"; } else { str = "0"; } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(d); str = dateTimeFormat.format(date); } else { double d = cell.getNumericCellValue(); str = Double.toString(d); str = str.replaceAll("\\.0*$", ""); } break; case Cell.CELL_TYPE_STRING: str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default: String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } StringOption stringOption = new StringOption(); stringOption.modify(str); return stringOption; }