List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:eu.learnpad.ontology.kpi.data.ExcelParser.java
public List<List<String>> getDataTable() throws IOException, InvalidFormatException { List<List<String>> dataTable = new ArrayList<>(); Integer rowNumber = -2;/*ww w. j a v a 2s . c o m*/ Workbook wb = WorkbookFactory.create(excelFile); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (Sheet sheet : wb) { if (sheet.getSheetName().equals(SHEETNAME)) { for (Row row : sheet) { //stop with the first empty row if (row.getCell(0) == null) { break; } if (rowNumber >= -1) { rowNumber++; dataTable.add(new ArrayList<String>()); } for (Cell cell : row) { String sheetName = sheet.getSheetName(); String cellRow = "Row:" + cell.getRowIndex(); String cellColumn = "Column:" + cell.getColumnIndex(); Object[] o = new Object[] { sheetName, cellRow, cellColumn }; LOGGER.log(Level.INFO, "Processing: Sheet={0} celladress={1}", o); if (rowNumber <= -1 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { continue; } if (rowNumber == -2 && cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getRichStringCellValue().getString().equals(DATACELLNAME)) { rowNumber = -1; continue; } } //Attributes (column headers) if (rowNumber == 0) { dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); } if (rowNumber >= 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { dataTable.get(rowNumber).add(cell.getDateCellValue().toString()); } else { dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { dataTable.get(rowNumber).add(cell.getDateCellValue().toString()); } else { dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue())); break; default: dataTable.get(rowNumber).add(""); } break; default: dataTable.get(rowNumber).add(""); } } } } } } return dataTable; }
From source file:excel.Excel.java
/** * @param args the command line arguments * @throws java.io.IOException/*from www .java2 s. c o m*/ */ public static void main(String args[]) throws IOException { Thread a; // String nameFile = "C:\\Users\\dfcastellanosc.SOPORTECOS\\Downloads\\Files\\Informacin Etapa Productiva.xlsx"; // Process p = Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + nameFile); FileInputStream file = new FileInputStream( new File("C:\\Users\\dfcastellanosc.SOPORTECOS\\Documents\\registroempleados.xlsx")); try (XSSFWorkbook workbook = new XSSFWorkbook(file)) { XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { if (celda.getColumnIndex() == 17) { System.out.println("|" + celda.getDateCellValue() + "|"); } else { System.out.print("|" + celda.getDateCellValue() + "|"); } } else { Double ds = celda.getNumericCellValue(); Long pt = ds.longValue(); if (celda.getColumnIndex() == 17) { System.out.println("|" + pt + "|"); } else { System.out.print("|" + pt + "|"); } } break; case Cell.CELL_TYPE_STRING: if (celda.getColumnIndex() == 17) { System.out.println("|" + celda.getStringCellValue() + "|"); } else { System.out.print("|" + celda.getStringCellValue() + "|"); } break; case Cell.CELL_TYPE_BOOLEAN: if (celda.getColumnIndex() == 17) { System.out.println("|" + celda.getBooleanCellValue() + "|"); } else { System.out.print("|" + celda.getBooleanCellValue() + "|"); } break; } } } workbook.close(); } }
From source file:excel.Reader.java
public void print() { System.out.println("START PRINT"); SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd"); int columnWidth = 15; FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Sheet sheet = wb.getSheetAt(0);/*ww w . j a va 2s. c o m*/ for (Row row : sheet) { //System.out.print("r"); for (Cell cell : row) { //CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); //System.out.print(cellRef.formatAsString()); //System.out.print(" - "); // System.out.print("c"); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //System.out.print("s"); System.out.printf("%-" + columnWidth + "s", cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print("d"); if (DateUtil.isCellDateFormatted(cell)) { System.out.printf("%-" + columnWidth + "s", df.format(cell.getDateCellValue())); } else { if ((cell.getNumericCellValue() % 1.0) != 0.0) System.out.printf("%-" + columnWidth + ".2f", cell.getNumericCellValue()); else System.out.printf("%-" + columnWidth + ".0f", cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print("b"); System.out.printf("%-" + columnWidth + "s", cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: CellValue val = evaluator.evaluate(cell); //System.out.print("f"); switch (val.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.printf("%-" + columnWidth + "s", val.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.printf("%-" + columnWidth + ".2f", val.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.printf("%-" + columnWidth + "s", val.getBooleanValue()); break; default: System.out.printf("%-" + columnWidth + "s", ""); } break; default: System.out.print(""); } } System.out.println(); } }
From source file:ExcelRead.PatientRead.java
public void readFromExcel(String file, JTable table) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0);//from ww w.j a v a2 s . c o m Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: ID = (int) cell.getNumericCellValue(); // table.setValueAt(ID, i, 0); break; case 1: String[] fullName = cell.getStringCellValue().split(" "); name = fullName[1]; surname = fullName[0]; middleName = fullName[2]; // table.setValueAt(cell.getStringCellValue(), i, 1); break; case 2: SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); birthdate = sdf.format(cell.getDateCellValue()); //table.setValueAt(birthdate, i, 2); break; case 3: sex = cell.getStringCellValue(); //table.setValueAt(sex, i, 3); break; case 4: address = cell.getStringCellValue(); // table.setValueAt(address, i, 4); break; case 5: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { lpu_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(lpu_id, i, 5); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { lpu_id = cell.getStringCellValue(); //table.setValueAt(lpu_id, i, 5); break; } case 6: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { crb_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(crb_id, i, 6); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { crb_id = cell.getStringCellValue(); // table.setValueAt(crb_id, i, 6); break; } case 7: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { snils = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(snils, i, 7); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { snils = cell.getStringCellValue(); //table.setValueAt(snils, i, 7); break; } case 8: String[] passport = cell.getStringCellValue().split(" "); //table.setValueAt(cell.getStringCellValue(), i, 8); pass_ser = passport[0]; pass_num = passport[1]; break; case 9: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { old_police = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(old_police, i, 9); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { old_police = cell.getStringCellValue(); // table.setValueAt(old_police, i, 9); break; } case 10: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { new_police = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(new_police, i, 10); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { new_police = cell.getStringCellValue(); //table.setValueAt(new_police, i, 10); break; } case 11: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { phoneNum = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(phoneNum, i, 11); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { phoneNum = cell.getStringCellValue(); //table.setValueAt(phoneNum, i, 11); break; } default: System.out.print("|"); break; } } DefaultTableModel model = (DefaultTableModel) table.getModel(); String[] data = { String.valueOf(ID), surname + " " + name + " " + middleName, birthdate, sex, address, lpu_id, crb_id, snils, pass_ser + " " + pass_num, old_police, new_police, phoneNum }; model.addRow(data); removeAllFields(); } }
From source file:fr.paris.lutece.plugins.appointment.service.ClosingDayService.java
License:Open Source License
/** * Import the closing dates of a given file * //from w w w . j a v a 2 s. co m * @param item * the file in input * @return the list of the closing dates in the file * @throws IOException * if error during reading file */ public static List<LocalDate> getImportClosingDays(FileItem item) throws IOException { HashSet<LocalDate> listDays = new HashSet<LocalDate>(); FileInputStream fis = null; Workbook workbook = null; String strExtension = FilenameUtils.getExtension(item.getName()); if (StringUtils.equals(MARK_EXCEL_EXTENSION_XLSX, strExtension)) { try { fis = (FileInputStream) item.getInputStream(); // Using XSSF for xlsx format, for xls use HSSF workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); // looping over each workbook sheet for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.iterator(); // iterating over each row while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() > 1) { Iterator<Cell> cellIterator = row.cellIterator(); // Iterating over each cell (column wise) in a // particular row. while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); // The Cell Containing String will is name. if (cell.getColumnIndex() == 3) { String strdate = StringUtils.EMPTY; if (cell.getCellType() == 0) { Instant instant = cell.getDateCellValue().toInstant(); LocalDate localDate = instant.atZone(ZoneId.systemDefault()).toLocalDate(); strdate = localDate.format(Utilities.getFormatter()); } if (StringUtils.isNotEmpty(strdate) && strdate.matches(MARK_FORMAT_DATE_REGEX)) { LocalDate date = LocalDate.parse(strdate, Utilities.getFormatter()); listDays.add(date); } } } } } } } finally { if (fis != null) { fis.close(); } if (workbook != null) { workbook.close(); } } } return new ArrayList<LocalDate>(listDays); }
From source file:functions.excels.RowCheck.java
License:Apache License
/** * Vrifie que la ligne donne est juste./*from w ww . ja va 2 s . c o m*/ * @param rowNumber */ public void checkRow() { Cell cell = row.getCell(0); if (cell == null) addError("Pas d'espce."); else { espece_nom = cell.getStringCellValue(); if ((espece = Espece.find.where().eq("espece_nom", espece_nom).findUnique()) == null) { try { EspeceSynonyme syn = EspeceSynonyme.find.where().eq("synonyme_nom", espece_nom).findUnique(); if (syn != null) espece = syn.synonyme_espece; else addError("L'espce " + espece_nom + " n'existe pas."); } catch (PersistenceException e) { addError("Deux espces synonymes ont le mme nom : " + espece_nom); } } } cell = row.getCell(1); if (cell != null) { sexe = cell.getStringCellValue(); if (sexe != null && !sexe.equals("")) { if (sexe.equals("oeuf") || sexe.equals("OEuf") || sexe.equals("Oeuf")) stade_sexe = StadeSexe.find.byId(6); else { if ((stade_sexe = StadeSexe.find.where().eq("stade_sexe_intitule", sexe).findUnique()) == null) addError("Le stade/sexe " + sexe + " n'existe pas."); if (stade_sexe != null && espece != null && !espece.getGroupe().getStadesSexes().contains(stade_sexe)) { addError("Le stade/sexe " + stade_sexe + " n'est pas valable pour le groupe " + espece.getGroupe()); } } } } cell = row.getCell(2); if (cell != null) { try { nombre_dbl = cell.getNumericCellValue(); if (nombre_dbl != 0) { nombre = (int) nombre_dbl; } } catch (IllegalStateException | NumberFormatException e) { addError(nombre_dbl + " n'est pas un entier."); } } cell = row.getCell(3);//Dpartement, on s'en fout. cell = row.getCell(4); if (cell != null) { commune_nom = cell.getStringCellValue(); if (commune_nom != null && !commune_nom.equals("")) { commune = Commune.findFromNomApproximatif(commune_nom); if (commune == null) addError("La commune " + commune_nom + " n'est pas rfrence."); } } cell = row.getCell(5); lieu_dit = cell.getStringCellValue(); cell = row.getCell(6); if (cell == null) addError("Maille UTM non spcifie."); else { utm_str = cell.getStringCellValue(); utm = UTMS.find.byId(utm_str); if (utm == null) addError("Maille UTM " + utm_str + " non existante."); } cell = row.getCell(7); if (cell != null) { try { date_min_date = cell.getDateCellValue(); if (date_min_date != null) { date_min = Calendar.getInstance(); date_min.setTime(date_min_date); } } catch (IllegalStateException e) { addError("Date minimum spcifie invalide."); } } cell = row.getCell(8); if (cell == null) addError("Date non spcifie."); else { try { date_date = cell.getDateCellValue(); date.setTime(date_date); if (date_date == null) addError("La date n'est pas spcifie."); if (date_min != null && date != null && date_min.compareTo(date) >= 0) addError("La date min est suprieure au gale la date."); } catch (IllegalStateException e) { addError("Date invalide."); } } cell = row.getCell(9); if (cell == null) addError("Tmoin non spcifie."); else { String temoins_str = cell.getStringCellValue(); if (temoins_str == null) addError("Tmoin non spcifie."); else { temoins = temoins_str.split(","); membres = new Membre[temoins.length]; for (int i = 0; i < temoins.length; i++) { temoins[i] = temoins[i].trim(); membres[i] = Membre.find.where().eq("membre_nom", temoins[i]).findUnique(); if (membres[i] == null) addError("Le membre '" + temoins[i] + "' n'est pas rfrenc."); } } } cell = row.getCell(10); if (cell != null) determinateur = cell.getStringCellValue(); cell = row.getCell(11); if (cell != null) methodeCapture = cell.getStringCellValue(); else methodeCapture = null; cell = row.getCell(12); if (cell != null) milieu = cell.getStringCellValue(); else milieu = null; cell = row.getCell(13); if (cell != null) essence = cell.getStringCellValue(); else essence = null; cell = row.getCell(14); if (cell != null) remarque = cell.getStringCellValue(); else remarque = null; cell = row.getCell(15); if (cell != null) collection = cell.getStringCellValue(); else collection = null; StringBuilder memo_sb = new StringBuilder(); boolean started = false; if (remarque != null && !remarque.equals("")) { if (!started) { started = true; memo_sb.append(remarque); } else memo_sb.append(" ; " + remarque); } if (methodeCapture != null && !methodeCapture.equals("")) { if (!started) { started = true; memo_sb.append("Mthode de capture : " + methodeCapture); } else memo_sb.append(" ; Mthode de capture : " + methodeCapture); } if (milieu != null && !milieu.equals("")) { if (!started) { started = true; memo_sb.append("Milieu : " + milieu); } else memo_sb.append(" ; Milieu : " + milieu); } if (essence != null && !essence.equals("")) { if (!started) { started = true; memo_sb.append("Essence : " + essence); } else memo_sb.append(" ; Essence : " + essence); } if (collection != null && !collection.equals("")) { if (!started) { started = true; memo_sb.append("Collection : " + collection); } else memo_sb.append(" ; Collection : " + collection); } memo = memo_sb.toString(); }
From source file:gob.dp.sid.registro.controller.ImportarController.java
private void importar(File archivo) { List<Object[]> listaObjetos = new ArrayList<>(); try {/*from w w w. java2 s. c o m*/ wb = WorkbookFactory.create(new FileInputStream(archivo)); Sheet hoja = wb.getSheetAt(0); Iterator filaIterator = hoja.rowIterator(); int indiceFila = -1; while (filaIterator.hasNext()) { indiceFila++; Row fila = (Row) filaIterator.next(); Iterator columnaIterator = fila.cellIterator(); Object[] listaColumna = new Object[7]; int indiceColumna = -1; while (columnaIterator.hasNext()) { indiceColumna++; Cell celda = (Cell) columnaIterator.next(); if (indiceFila == 0) { } else { if (celda != null && indiceColumna < 7) { switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //listaColumna[indiceColumna]= (int)Math.round(celda.getNumericCellValue()); listaColumna[indiceColumna] = celda.getDateCellValue(); break; case Cell.CELL_TYPE_STRING: listaColumna[indiceColumna] = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: listaColumna[indiceColumna] = celda.getBooleanCellValue(); break; default: listaColumna[indiceColumna] = null; break; } } } } if (indiceFila != 0) { listaObjetos.add(listaColumna); } } cargarGestiones(listaObjetos); } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { log.error("importar" + e); } }
From source file:gov.nih.nci.cananolab.util.ExcelParser.java
License:BSD License
public void printSheet(Sheet sheet) { for (Row row : sheet) { for (Cell cell : row) { CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); }/*from ww w . jav a 2s . c om*/ break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: System.out.println(); } } } }
From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java
License:Apache License
private long readDateColumn(int row, String requestedColumnName) { return ((Long) readColumn(row, requestedColumnName, new Function<Cell, Object>() { @Override//from ww w. jav a 2 s .co m public Object apply(Cell cell) { return cell.getDateCellValue().getTime(); } })).longValue(); }
From source file:graphbuilder.ExcelParser.java
private static Object loadCellData(Cell cell) { Object result = null;/*from w w w. j a v a 2s.com*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; } return result; }