List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:misuExcel.excelWrite.java
License:Open Source License
private void outType02() { if (wbSheet != null && names != null && list != null) { Log.info("list size:" + list.size()); Workbook splitWb = null;// w ww .ja v a 2 s . co m if (indexType == 1) splitWb = new XSSFWorkbook(); else if (indexType == 2) splitWb = new HSSFWorkbook(); for (int i = 0; i < list.size(); i++) { ArrayList<Integer> integers = list.get(i); Sheet sheet = splitWb.createSheet(names.get(i)); for (int j = 0; j < integers.size() + splitJpanel.ignore_Row; j++) { Row row = null; Row copy = null; if (j >= splitJpanel.ignore_Row) { row = sheet.createRow(j); copy = wbSheet.getRow(integers.get(j - splitJpanel.ignore_Row)); } else { row = sheet.createRow(j); copy = wbSheet.getRow(j); } for (int k = 0; k < copy.getLastCellNum(); k++) { Cell cell = row.createCell(k); Cell copyCell = copy.getCell(k); if (copyCell != null) { switch (copyCell.getCellType()) { case Cell.CELL_TYPE_STRING: cell.setCellValue(copyCell.getRichStringCellValue().getString().trim()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(copyCell)) { cell.setCellValue(copyCell.getDateCellValue()); } else { cell.setCellValue(copyCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(copyCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellValue(copyCell.getCellFormula()); break; default: cell.setCellValue(copyCell.getStringCellValue().trim()); } } } } } //end for createWB(splitWb, fileReal + "(cut)"); JOptionPane.showMessageDialog(null, fileReal + "(cut)." + _index + "?"); } }
From source file:misuExcel.excelWrite.java
License:Open Source License
private void outType03() { if (wbSheet != null && addWb != null && names != null && list != null) { Sheet sheet = addWb.getSheetAt(sheetNum_target); for (int i = 0; i < list.size(); i++) { ArrayList<Integer> integers = list.get(i); Row copy = wbSheet.getRow(i + addJpanel.ignore_Rowtar); for (int j = 0; j < integers.size(); j++) { Row row = sheet.getRow(integers.get(j)); int numRow = row.getLastCellNum(); for (int k = addJpanel.ignore_Celltar; k < copy.getLastCellNum(); k++) { Cell cell = null;/*ww w. j av a 2s .c o m*/ Cell copyCell = null; if (k != cellNum_target) { copyCell = copy.getCell(k); if (addJpanel.ignore_Celltar > cellNum_target) { cell = row.createCell(k + numRow - addJpanel.ignore_Celltar); } else { cell = row.createCell(k < cellNum_target ? (k + numRow - addJpanel.ignore_Celltar) : (k - 1 + numRow - addJpanel.ignore_Celltar)); } } if (copyCell != null) { switch (copyCell.getCellType()) { case Cell.CELL_TYPE_STRING: cell.setCellValue(copyCell.getRichStringCellValue().getString().trim()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(copyCell)) { cell.setCellValue(copyCell.getDateCellValue()); } else { cell.setCellValue(copyCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(copyCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellValue(copyCell.getCellFormula()); break; default: cell.setCellValue(copyCell.getStringCellValue().trim()); } } } } } //end for createWB(addWb, fileReal + "(add)"); JOptionPane.showMessageDialog(null, fileReal + "(add)." + _index + "?"); } }
From source file:misuExcel.excelWrite.java
License:Open Source License
private void outType04() { if (wbSheet != null && addWb != null && names != null && list != null) { Sheet sheet = addWb.getSheetAt(sheetNum_target); int numRow = sheet.getLastRowNum() + 1; ArrayList<Integer> integers = list.get(0); for (int j = addJpanel.ignore_Rowtar; j <= wbSheet.getLastRowNum(); j++) { Row row = null;// w w w .java2 s .c o m Row copy = null; if (j != cellNum_target) { if ((cellNum_target + 1) > addJpanel.ignore_Rowtar) row = sheet.createRow(j < cellNum_target ? (j + numRow - addJpanel.ignore_Rowtar) : (j + numRow - 1 - addJpanel.ignore_Rowtar)); else row = sheet.createRow(j + numRow - addJpanel.ignore_Rowtar); copy = wbSheet.getRow(j); } if (copy != null) { for (int k = 0; k < copy.getLastCellNum(); k++) { Cell cell = null; if (k >= addJpanel.ignore_Celltar) cell = row.createCell(integers.get((k - addJpanel.ignore_Celltar))); else cell = row.createCell(k); Cell copyCell = copy.getCell(k); if (copyCell != null) { switch (copyCell.getCellType()) { case Cell.CELL_TYPE_STRING: cell.setCellValue(copyCell.getRichStringCellValue().getString().trim()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(copyCell)) { cell.setCellValue(copyCell.getDateCellValue()); } else { cell.setCellValue(copyCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(copyCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellValue(copyCell.getCellFormula()); break; default: cell.setCellValue(copyCell.getStringCellValue().trim()); } } } } } //end for createWB(addWb, fileReal + "(add)"); JOptionPane.showMessageDialog(null, fileReal + "(add)." + _index + "?"); } }
From source file:Model.ExcelModel.java
public String ImportFile(File fileImported, JTable dataTable) { String importResult = "System couldn't import the file"; DefaultTableModel loadModel = new DefaultTableModel(); dataTable.setModel(loadModel);/*from w ww . ja v a 2s . co m*/ dataTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); try { innerWorkbook = WorkbookFactory.create(new FileInputStream(fileImported)); Sheet sheetTemporal = innerWorkbook.getSheetAt(0); Iterator rowIterator = sheetTemporal.rowIterator(); int rowIndex = -1; while (rowIterator.hasNext()) { rowIndex++; Row temporalRow = (Row) rowIterator.next(); Iterator columnIterator = temporalRow.cellIterator(); Object[] columnList = new Object[12]; int columnIndex = -1; while (columnIterator.hasNext()) { columnIndex++; Cell temporalCell = (Cell) columnIterator.next(); if (rowIndex == 0) loadModel.addColumn(temporalCell.getStringCellValue()); else { if (temporalCell != null) { switch (temporalCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: columnList[columnIndex] = (int) Math.round(temporalCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: columnList[columnIndex] = temporalCell.getStringCellValue(); break; default: columnList[columnIndex] = temporalCell.getDateCellValue(); break; }//End Switch/Case get.CellType } //End temporalCell!=null condition } //End Else rowIndex==0 } //End columnIterator if (rowIndex != 0) loadModel.addRow(columnList); } //End rowIterator importResult = "Successful Import"; } catch (Exception e) { } return importResult; }
From source file:Modelo.ModeloExcel.java
public String Importar(File archivo, JTable tablaD) { String respuesta = "No se pudo realizar la importacin."; DefaultTableModel modeloT = new DefaultTableModel(); tablaD.setModel(modeloT);/*from w w w . j ava 2 s . c o m*/ try { 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[5]; int indiceColumna = -1; while (columnaIterator.hasNext()) { indiceColumna++; Cell celda = (Cell) columnaIterator.next(); if (indiceFila == 0) { modeloT.addColumn(celda.getStringCellValue()); } else { if (celda != null) { switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: listaColumna[indiceColumna] = (int) Math.round(celda.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: listaColumna[indiceColumna] = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: listaColumna[indiceColumna] = celda.getBooleanCellValue(); break; default: listaColumna[indiceColumna] = celda.getDateCellValue(); break; } } } } if (indiceFila != 0) modeloT.addRow(listaColumna); } respuesta = "Importacin exitosa"; } catch (Exception e) { } return respuesta; }
From source file:mongodbutils.Filehandler.java
private Object getCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date dt = cell.getDateCellValue(); JSONObject obj = new JSONObject(); obj.put("$date", dt.getTime()); return obj.toString(); //return "" + cell.getDateCellValue(); } else {//from w w w.j a v a 2 s .c o m return cell.getNumericCellValue(); } case Cell.CELL_TYPE_BOOLEAN: return "" + cell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); } return ""; }
From source file:monthly.organizer.MonthlyOrganizer.java
@Override public void start(Stage primaryStage) { wbo = new XSSFWorkbook(); try {// w ww .j a v a2 s. c o m Button merge = new Button("merge"); Button folder = new Button("Choose Folder"); Button file = new Button("Choose Save File"); Label fileLoc = new Label("Not Selected"); Label folderLoc = new Label("Not Selected"); folder.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { DirectoryChooser folderChooser = new DirectoryChooser(); folderChooser.setTitle("Choose Folder Containing Files"); selectedFolder = folderChooser.showDialog(primaryStage); //folderLoc.setText(selectedFolder.getName()); if (null == selectedFolder) folderLoc.setText("No Folder Selected"); else folderLoc.setText(selectedFolder.getName()); } }); file.setOnAction(new EventHandler<ActionEvent>() { @SuppressWarnings("deprecation") @Override public void handle(ActionEvent arg0) { FileChooser fileSaver = new FileChooser(); fileSaver.setTitle("Create Save File"); fileSaver.getExtensionFilters().addAll(new ExtensionFilter( "ExceBeaconController bc = new BeaconController();ll Files", "*.xlsx")); outputFile = fileSaver.showSaveDialog(primaryStage); //outputFile = new File("/home/camen/Desktop" + "/Final_Books" + LocalDate.now() + ".xlsx"); if (null == outputFile) fileLoc.setText("No File Selected"); else fileLoc.setText(outputFile.getName()); } }); merge.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { try { outputStream = new FileOutputStream(outputFile); } catch (FileNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } try ( Stream<Path> paths = Files.walk(Paths.get(selectedFolder.getPath()))) { paths.forEach(filePath -> { if (Files.isRegularFile(filePath) && !filePath.toString().equals(selectedFolder.toPath().toString())) { //System.out.println(filePath); fileNum++; FileInputStream inputStream; try { inputStream = new FileInputStream(filePath.toString()); wb = new XSSFWorkbook(inputStream); CreationHelper createHelper = wbo.getCreationHelper(); Sheet sheetOut = wbo.createSheet(filePath.getFileName().toString()); //add client name to beacon controller clients.add(new Client(filePath.getFileName().toString())); clientNum++; //System.out.println(clientNum + ""); Row rowOut = sheetOut.createRow(0); Cell cellOut; cellOut = rowOut.createCell(0); cellOut.setCellValue(createHelper.createRichTextString("Week")); cellOut = rowOut.createCell(1); cellOut.setCellValue(createHelper.createRichTextString("Behavior/Decel")); cellOut = rowOut.createCell(2); cellOut.setCellValue(createHelper.createRichTextString("Data Input Total")); cellOut = rowOut.createCell(3); cellOut.setCellValue(createHelper.createRichTextString("Measurment Type")); cellOut = rowOut.createCell(4); cellOut.setCellValue(createHelper.createRichTextString("Measurment Unit")); cellOut = rowOut.createCell(5); cellOut.setCellValue( createHelper.createRichTextString("Total Time for Week (Mins)")); int rowCount = 1; for (int k = 0; k < wb.getNumberOfSheets(); k++) { Sheet sheet = wb.getSheetAt(k); double weekTotal = 0; double timeTotal = 0; boolean replace = false; String decelName = null; String replaceName = null; Random rand = null; int n = 0; String measType = null; String measUnit = null; String replacedName = null; String replacedNameOld = null; LocalDateTime date = null; int weekNum = -1; int lastWeek = -1; int year = 0; int rows = sheet.getPhysicalNumberOfRows(); //System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows //+ " row(s)."); for (int r = 0; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); //System.out.println("\nROW " + row.getRowNum() + " has " + cells //+ " cell(s)."); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); if (cell == null) { //c++; cells++; } else { switch (cell.getCellTypeEnum()) { case STRING: if (r == 0 && cell.getRichStringCellValue().getString() .contains("Replacement")) replace = true; if (r == 0 && replace == true && c == 1) { replaceName = cell.getRichStringCellValue().getString(); rand = new Random(); n = rand.nextInt(20) + 1; } else if (r == 0 && replace == false && c == 1) decelName = cell.getRichStringCellValue().getString(); else if (r == 0 && replace == true && c == 2) replacedName = cell.getRichStringCellValue() .getString(); if (r == 1 && c == 0) measType = cell.getRichStringCellValue().getString(); if (r == 1 && c == 1) measUnit = cell.getRichStringCellValue().getString(); //System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { date = cell.getDateCellValue().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate() .atTime(LocalTime.NOON); year = date.getYear(); weekNum = date.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR); if (lastWeek == -1) lastWeek = weekNum; c++; cell = row.getCell(c); weekTotal += cell.getNumericCellValue(); c++; //cell = row.getCell(c); //timeTotal += cell.getNumericCellValue(); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: //System.out.println(cell.getBooleanCellValue()); break; case FORMULA: //System.out.println(cell.getCellFormula()); break; case BLANK: //System.out.println(); break; default: //System.out.println(); } //System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" //+ value); } } //System.out.println(lastWeek + " " + weekNum + " " + weekTotal); if (lastWeek != weekNum && lastWeek != -1) { //weekCount ++; rowOut = sheetOut.createRow(rowCount); Cell cell = rowOut.createCell(0); cell.setCellValue(createHelper.createRichTextString(date.getMonth() + ", Week " + weekNum + " of Year: " + date.getYear())); //create random number to choose behavior cell = rowOut.createCell(1); if (replace == true) { cell.setCellValue( createHelper.createRichTextString(replaceName)); //client.addValue((((date.getYear() - 2014)*53) + weekNum), weekTotal, Arrays.binarySearch(Client.behaviors, replaceName)); //addvalue to random behavior chosen for the week clients.get(clientNum).addValue( (((date.getYear() - 2014) * 53) + weekNum), weekTotal, n); //System.out.println((((date.getYear() - 2014)*53) + weekNum) + " " + weekTotal + " " + Client.behaviors[n] + " " + clients.get(clientNum).getName()); } else cell.setCellValue(createHelper.createRichTextString(decelName)); cell = rowOut.createCell(2); cell.setCellValue(weekTotal); cell = rowOut.createCell(3); cell.setCellValue(createHelper.createRichTextString(measType)); cell = rowOut.createCell(4); cell.setCellValue(createHelper.createRichTextString(measUnit)); //System.out.println(months[wR[weekNum].month-1] + " week " + (wR[weekNum].monthWeek) + ", " + (year+1900) + ": " + weekTotal + " Measurment Type: " + measType + " Measument Unit: " + measUnit); weekTotal = 0; lastWeek = weekNum; //newWeek = false; rowCount++; } } //if(replace == true) //System.out.println(replaceName + " for " + replacedName); //else //System.out.println(decelName); } //controller.addClient(clients.get(clientNum)); //controller.getClients().forEach((names) -> { //System.out.println(names.getName()); //}); wb.close(); inputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }); wbo.write(outputStream); wbo.close(); outputStream.close(); final FXMLLoader loader = new FXMLLoader(getClass().getResource("beacon.fxml")); final Stage stage = new Stage(StageStyle.DECORATED); stage.setScene(new Scene((Pane) loader.load())); final BeaconController controller = loader.<BeaconController>getController(); controller.addClients(clients); controller.setFields(true); stage.showAndWait(); //getHostServices().showDocument(outputFile.toURI().toURL().toExternalForm()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } }); VBox root = new VBox(); root.setId("pane"); folder.setId("button"); file.setId("button"); merge.setId("button"); root.getStylesheets().addAll(this.getClass().getResource("buttonStyle.css").toExternalForm()); root.setPadding(new Insets(180, 200, 30, 200)); root.setAlignment(Pos.BASELINE_CENTER); root.getChildren().addAll(folder, folderLoc, file, fileLoc, merge); root.setSpacing(10); Scene scene = new Scene(root); primaryStage.setScene(scene); primaryStage.show(); } catch (Exception e) { e.printStackTrace(); } }
From source file:mw.sqlitetool.ExcelHelper.java
public Object getCellValue(Cell cell) { if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) { return ""; } else if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { return cell.getBooleanCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_ERROR) { return cell.getErrorCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) { FormulaEvaluator evaluator = _workbook.getCreationHelper().createFormulaEvaluator(); CellValue val = evaluator.evaluate(cell); if (val.getCellType() == cell.CELL_TYPE_BOOLEAN) { return val.getBooleanValue(); } else if (val.getCellType() == cell.CELL_TYPE_NUMERIC) { return val.getNumberValue(); } else if (val.getCellType() == cell.CELL_TYPE_STRING) { return val.getStringValue(); } else if (val.getCellType() == cell.CELL_TYPE_ERROR) { return val.getErrorValue(); }/*from w w w. j a va 2s . com*/ } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } } else if (cell.getCellType() == cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return ""; }
From source file:negocio.parser.ExcelReader.java
@Override public IExcelContent leerArchivo(String ruta) throws Exception { java.util.Date date = new java.util.Date(); Date entrada = new Date(date.getTime()); IExcelContent ec = ExcelContent.getInstantiateExcelContent(); try {//w w w. j a v a2 s . c o m LogDAO dao = new LogDAO(); LogDTO dto = new LogDTO("Leer archivo", "Comienzo de lectura de archivo", entrada.toString(), entrada.toString()); dao.registrarLog(dto); File archivo = new File(ruta); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(archivo)); //crear un libro excel XSSFSheet sheet = workbook.getSheetAt(0); //acceder a la primera hoja Iterator<Row> rowIterator = sheet.iterator(); Row row; boolean sw = true; ArrayList<List<String>> datos = new ArrayList<>(); while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; List<String> fila = new ArrayList<>(); while (cellIterator.hasNext()) { celda = cellIterator.next(); String dato = ""; switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celda)) { dato = celda.getDateCellValue().toString(); } else { dato = celda.getNumericCellValue() + ""; } break; case Cell.CELL_TYPE_STRING: dato = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: dato = celda.getBooleanCellValue() + ""; break; } fila.add(dato); } if (sw) { sw = false; ec.setTitulos(fila); } else { datos.add(fila); } } ec.setDatos(datos); workbook.close(); return ec; } catch (IOException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } return null; }
From source file:net.bafeimao.umbrella.support.data.entity.ExcelEntityParser.java
License:Apache License
private String getCellValue(Cell cell) { Preconditions.checkNotNull("cell"); Object retVal = null;//from www . ja v a 2s . c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: // Get the type of Formula switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: retVal = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: retVal = cell.getNumericCellValue(); break; default: } // retVal = formulaEval.evaluate(cell).formatAsString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) retVal = cell.getDateCellValue(); else retVal = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: retVal = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_STRING: retVal = cell.getStringCellValue(); break; default: retVal = null; } return retVal == null ? null : retVal.toString(); }