List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell
@Override public XSSFCell createCell(int columnIndex)
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copyRow(XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) { destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { XSSFCell oldCell = srcRow.getCell(j); XSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }/*from w w w .ja va 2 s.com*/ copyCell(oldCell, newCell, styleMap); } } }
From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java
License:Open Source License
private <T extends Number> void writeTable(XSSFSheet sheet, List<List<T>> tmp, String title, List<String> headers, List<String> values, boolean isContinuous) { writeTableHeaders(sheet, title, headers); ArrayList<String> rowHeaders = Lists.newArrayList(values); if (!isContinuous) rowHeaders.add("Total"); int counter = 0; int rownum = 4; for (String k : rowHeaders) { int cellnum = 0; XSSFRow row = sheet.createRow(rownum++); XSSFCell cell = row.createCell(cellnum++); cell.setCellValue(k);//from w w w .j a v a 2 s .co m cell.setCellStyle(headerStyle); for (T obj : tmp.get(counter)) { cell = row.createCell(cellnum++); cell.setCellStyle(tableStyle); if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else cell.setCellValue(String.valueOf(obj)); } counter++; } sheet.autoSizeColumn(0); }
From source file:org.optapconf.plannerbenchmark.ConferenceFileIO.java
License:Apache License
@Override public void write(Solution solution, File outputSolutionFile) { Conference conference = (Conference) solution; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Conference"); XSSFRow headerRow = sheet.createRow(0); int x = 1;//from w w w . j a v a2 s . com Map<Room, Integer> roomXMap = new HashMap<>(conference.getRoomList().size()); for (Room room : conference.getRoomList()) { XSSFCell cell = headerRow.createCell(x); cell.setCellValue(room.getName()); roomXMap.put(room, x); x++; } int y = 1; Map<Timeslot, XSSFRow> timeslotRowMap = new HashMap<>(conference.getTimeslotList().size()); for (Timeslot timeslot : conference.getTimeslotList()) { XSSFRow row = sheet.createRow(y); XSSFCell cell = row.createCell(0); cell.setCellValue(timeslot.getDay().getName() + " - " + timeslot.getName()); timeslotRowMap.put(timeslot, row); y++; } for (Talk talk : conference.getTalkList()) { Timeslot timeslot = talk.getTimeslot(); Room room = talk.getRoom(); if (timeslot != null && room != null) { XSSFCell cell = timeslotRowMap.get(timeslot).createCell(roomXMap.get(room)); cell.setCellValue(talk.getTitle()); } else { XSSFCell unassignedCell = sheet.createRow(y).createCell(1); unassignedCell.setCellValue(talk.getTitle()); y++; } } try { try (OutputStream out = new FileOutputStream(outputSolutionFile)) { workbook.write(out); workbook.close(); } } catch (IOException e) { throw new IllegalStateException("Problem writing outputSolutionFile (" + outputSolutionFile + ").", e); } }
From source file:org.ramalapure.userinfoapp.UserInfoApp.java
@Override public void start(Stage primaryStage) { CheckConnection();//ww w . j av a 2 s . c o m fillComboBox(); // create transperant stage //primaryStage.initStyle(StageStyle.TRANSPARENT); primaryStage.setTitle("JavaFX 8 Tutorial 61 - Retrive Database Values Into CheckBox"); primaryStage.getIcons().add(new Image("file:user-icon.png")); BorderPane layout = new BorderPane(); Scene newscene = new Scene(layout, 1200, 700, Color.rgb(0, 0, 0, 0)); Group root = new Group(); Scene scene = new Scene(root, 320, 200, Color.rgb(0, 0, 0, 0)); scene.getStylesheets().add(getClass().getResource("Style.css").toExternalForm()); Color foreground = Color.rgb(255, 255, 255, 0.9); //Rectangila Background Rectangle background = new Rectangle(320, 250); background.setX(0); background.setY(0); background.setArcHeight(15); background.setArcWidth(15); background.setFill(Color.rgb(0, 0, 0, 0.55)); background.setStroke(foreground); background.setStrokeWidth(1.5); VBox vbox = new VBox(5); vbox.setPadding(new Insets(10, 0, 0, 10)); Label label = new Label("Label"); //label.setTextFill(Color.WHITESMOKE); label.setFont(new Font("SanSerif", 20)); TextField username = new TextField(); username.setFont(Font.font("SanSerif", 20)); username.setPromptText("Username"); username.getStyleClass().add("field-background"); PasswordField password = new PasswordField(); password.setFont(Font.font("SanSerif", 20)); password.setPromptText("Password"); password.getStyleClass().add("field-background"); password.setOnKeyPressed(e -> { if (e.getCode() == KeyCode.ENTER) { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } } }); Button btn = new Button("Login"); btn.setFont(Font.font("SanSerif", 15)); btn.setOnAction(e -> { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } }); vbox.getChildren().addAll(label, username, password, btn); root.getChildren().addAll(background, vbox); Button logout = new Button("Logout"); logout.setFont(Font.font("SanSerif", 15)); logout.setOnAction(e -> { primaryStage.setScene(scene); primaryStage.show(); }); layout.setTop(logout); BorderPane.setAlignment(logout, Pos.TOP_RIGHT); BorderPane.setMargin(logout, new Insets(10)); VBox fields = new VBox(5); searchField = new TextField(); searchField.setFont(Font.font("SanSerif", 15)); searchField.setPromptText("Search"); searchField.setMaxWidth(200); Label label1 = new Label("Create New User"); label1.setFont(new Font("Sanserif", 20)); id = new TextField(); id.setFont(Font.font("SanSerif", 15)); id.setPromptText("ID"); id.setMaxWidth(200); fn = new TextField(); fn.setFont(Font.font("SanSerif", 15)); fn.setPromptText("First Name"); fn.setMaxWidth(200); ln = new TextField(); ln.setFont(Font.font("SanSerif", 15)); ln.setPromptText("Last Name"); ln.setMaxWidth(200); em = new TextField(); em.setFont(Font.font("SanSerif", 15)); em.setPromptText("Email"); em.setMaxWidth(200); mobile = new TextField(); mobile.setFont(Font.font("SanSerif", 15)); mobile.setPromptText("Mobile No."); mobile.setMaxWidth(200); un = new TextField(); un.setFont(Font.font("SanSerif", 15)); un.setPromptText("Username"); un.setMaxWidth(200); pw = new PasswordField(); pw.setFont(Font.font("SanSerif", 15)); pw.setPromptText("Password"); pw.setMaxWidth(200); date = new DatePicker(); date.setPromptText("Date of Birth"); date.setMaxWidth(200); date.setStyle("-fx-font-size:15"); ToggleGroup gender = new ToggleGroup(); male = new RadioButton("Male"); male.setToggleGroup(gender); male.setOnAction(e -> { radioButtonLabel = male.getText(); }); female = new RadioButton("Female"); female.setToggleGroup(gender); female.setOnAction(e -> { radioButtonLabel = female.getText(); }); checkBox1 = new CheckBox("Playing"); checkBox1.setOnAction(e -> { checkBoxList.add(checkBox1.getText()); }); checkBox2 = new CheckBox("Singing"); checkBox2.setOnAction(e -> { checkBoxList.add(checkBox2.getText()); }); checkBox3 = new CheckBox("Dancing"); checkBox3.setOnAction(e -> { checkBoxList.add(checkBox3.getText()); }); date.requestFocus(); male.requestFocus(); female.requestFocus(); checkBox1.requestFocus(); checkBox2.requestFocus(); checkBox3.requestFocus(); textArea = new TextArea(); textArea.setFont(Font.font("SanSerif", 12)); textArea.setPromptText("Path Of Selected File Or Files"); textArea.setPrefSize(300, 50); textArea.setEditable(false); fileChooser = new FileChooser(); fileChooser.getExtensionFilters().addAll(new ExtensionFilter("Text Files", "*txt"), new ExtensionFilter("Image Files", "*.png", "*.jpg", "*.gif"), new ExtensionFilter("Audio Files", "*wav", "*.mp3", "*.aac"), new ExtensionFilter("All Files", "*.*")); browse = new Button("Browse"); browse.setFont(Font.font("SanSerif", 15)); browse.setOnAction(e -> { //Single File Selection file = fileChooser.showOpenDialog(primaryStage); if (file != null) { textArea.setText(file.getAbsolutePath()); image = new Image(file.toURI().toString(), 100, 150, true, true);//path, PrefWidth, PrefHeight, PreserveRatio, Smooth imageView = new ImageView(image); imageView.setFitWidth(100); imageView.setFitHeight(150); imageView.setPreserveRatio(true); layout.setCenter(imageView); BorderPane.setAlignment(imageView, Pos.TOP_LEFT); } //Multiple File Selection /*List<File> fileList = fileChooser.showOpenMultipleDialog(primaryStage); if(fileList != null){ fileList.stream().forEach(selectedFiles ->{ textArea.setText(fileList.toString()); }); }*/ }); Button button = new Button("Save"); button.setFont(Font.font("SanSerif", 15)); button.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "INSERT INTO UserDatabase (ID, FirstName, LastName, Email, Username, Password, DOB, Gender, MobileNo, Hobbies, Image) VALUES(?,?,?,?,?,?,?,?,?,?,?)"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User has been created."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); Button update = new Button("Update"); update.setFont(Font.font("SanSerif", 15)); update.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "update UserDatabase set ID=?, FirstName=?, LastName=?, Email=?, Username=?, Password=?, DOB=?, Gender=?, MobileNo=?, Hobbies=?, Image=? where ID='" + id.getText() + "' "; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User details has been updated."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); fields.getChildren().addAll(searchField, label1, id, fn, ln, em, mobile, un, pw, date, male, female, checkBox1, checkBox2, checkBox3, browse, textArea, button); layout.setLeft(fields); BorderPane.setMargin(fields, new Insets(0, 10, 0, 10)); table = new TableView<>(); TableColumn column1 = new TableColumn("ID"); column1.setMaxWidth(50); column1.setCellValueFactory(new PropertyValueFactory<>("ID")); TableColumn column2 = new TableColumn("First Name"); column2.setMinWidth(80); column2.setCellValueFactory(new PropertyValueFactory<>("firstName")); TableColumn column3 = new TableColumn("Last Name"); column3.setMinWidth(80); column3.setCellValueFactory(new PropertyValueFactory<>("lastName")); TableColumn column4 = new TableColumn("Email"); column4.setMinWidth(150); column4.setCellValueFactory(new PropertyValueFactory<>("email")); TableColumn column5 = new TableColumn("Username"); column5.setMinWidth(80); column5.setCellValueFactory(new PropertyValueFactory<>("username")); TableColumn column6 = new TableColumn("Password"); column6.setMinWidth(80); column6.setCellValueFactory(new PropertyValueFactory<>("password")); TableColumn column7 = new TableColumn("DOB"); column7.setMinWidth(70); column7.setCellValueFactory(new PropertyValueFactory<>("DOB")); TableColumn column8 = new TableColumn("Gender"); column8.setMinWidth(50); column8.setCellValueFactory(new PropertyValueFactory<>("Gender")); TableColumn column9 = new TableColumn("Mobile No."); column9.setMinWidth(70); column9.setCellValueFactory(new PropertyValueFactory<>("MobileNo")); TableColumn column10 = new TableColumn("Hobbies"); column10.setMinWidth(100); column10.setCellValueFactory(new PropertyValueFactory<>("Hobbies")); table.getColumns().addAll(column1, column2, column3, column4, column5, column6, column7, column8, column9, column10); table.setTableMenuButtonVisible(true); ScrollPane sp = new ScrollPane(table); //sp.setContent(table); sp.setPrefSize(600, 200); sp.setHbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setVbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setFitToHeight(true); sp.setHmax(3); sp.setHvalue(0); sp.setDisable(false); layout.setRight(sp); BorderPane.setMargin(sp, new Insets(0, 10, 10, 10)); Button load = new Button("Load Table"); load.setFont(Font.font("SanSerif", 15)); load.setOnAction(e -> { refreshTable(); }); ComboBox comboBox = new ComboBox(options); comboBox.setMaxHeight(30); comboBox.setOnAction(e -> { try { String query = "select * from UserDatabase where FirstName = ?"; pst = conn.prepareStatement(query); pst.setString(1, (String) comboBox.getSelectionModel().getSelectedItem()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if ("Male".equals(rs.getString("Gender"))) { male.setSelected(true); } else if ("Female".equals(rs.getString("Gender"))) { female.setSelected(true); } else { male.setSelected(false); female.setSelected(false); } } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); Button delete = new Button("Delete User"); delete.setFont(Font.font("SanSerif", 15)); delete.setOnAction(e -> { Alert alert = new Alert(AlertType.CONFIRMATION); alert.setTitle("Confirmation Dialog"); alert.setHeaderText(null); alert.setContentText("Are you sure to delete?"); Optional<ButtonType> action = alert.showAndWait(); if (action.get() == ButtonType.OK) { try { String query = "delete from UserDatabase where id = ?"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.executeUpdate(); pst.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } clearFields(); fillComboBox(); refreshTable(); }); exportToXL = new Button("Export To Excel"); exportToXL.setFont(Font.font("Sanserif", 15)); exportToXL.setOnAction(e -> { try { String query = "Select * from UserDatabase"; pst = conn.prepareStatement(query); rs = pst.executeQuery(); //Apache POI Jar Link- //http://a.mbbsindia.com/poi/release/bin/poi-bin-3.13-20150929.zip XSSFWorkbook wb = new XSSFWorkbook();//for earlier version use HSSF XSSFSheet sheet = wb.createSheet("User Details"); XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("First Name"); header.createCell(2).setCellValue("Last Name"); header.createCell(3).setCellValue("Email"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.setColumnWidth(3, 256 * 25);//256-character width sheet.setZoom(150);//scale-150% int index = 1; while (rs.next()) { XSSFRow row = sheet.createRow(index); row.createCell(0).setCellValue(rs.getString("ID")); row.createCell(1).setCellValue(rs.getString("FirstName")); row.createCell(2).setCellValue(rs.getString("LastName")); row.createCell(3).setCellValue(rs.getString("Email")); index++; } FileOutputStream fileOut = new FileOutputStream("UserDetails.xlsx");// befor 2007 version xls wb.write(fileOut); fileOut.close(); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Exported in Excel Sheet."); alert.showAndWait(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); importXLToDB = new Button("Import XL TO DB"); importXLToDB.setFont(Font.font("Sanserif", 15)); importXLToDB.setOnAction(e -> { try { String query = "Insert into UserDatabase(ID, FirstName, LastName, Email) values (?,?,?,?)"; pst = conn.prepareStatement(query); FileInputStream fileIn = new FileInputStream(new File("UserInfo.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fileIn); XSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); pst.setInt(1, (int) row.getCell(0).getNumericCellValue()); pst.setString(2, row.getCell(1).getStringCellValue()); pst.setString(3, row.getCell(2).getStringCellValue()); pst.setString(4, row.getCell(3).getStringCellValue()); pst.execute(); } Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Imported From Excel Sheet To Database."); alert.showAndWait(); wb.close(); fileIn.close(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } refreshTable(); }); HBox hbox = new HBox(5); hbox.getChildren().addAll(load, delete, update, comboBox, exportToXL, importXLToDB); layout.setBottom(hbox); BorderPane.setMargin(hbox, new Insets(10, 0, 10, 10)); ListView list = new ListView(options); list.setMaxSize(100, 250); //layout.setLeft(list); //BorderPane.setMargin(list, new Insets(10)); table.setOnMouseClicked(e -> { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); table.setOnKeyReleased(e -> { if (e.getCode() == KeyCode.UP || e.getCode() == KeyCode.DOWN) { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } }); FilteredList<User> filteredData = new FilteredList<>(data, e -> true); searchField.setOnKeyReleased(e -> { searchField.textProperty().addListener((observableValue, oldValue, newValue) -> { filteredData.setPredicate((Predicate<? super User>) user -> { if (newValue == null || newValue.isEmpty()) { return true; } String lowerCaseFilter = newValue.toLowerCase(); if (user.getID().contains(newValue)) { return true; } else if (user.getFirstName().toLowerCase().contains(lowerCaseFilter)) { return true; } else if (user.getLastName().toLowerCase().contains(lowerCaseFilter)) { return true; } return false; }); }); SortedList<User> sortedData = new SortedList<>(filteredData); sortedData.comparatorProperty().bind(table.comparatorProperty()); table.setItems(sortedData); }); primaryStage.setScene(scene); primaryStage.show(); }
From source file:org.rapidpm.data.table.export.Table2XLSX.java
License:Apache License
@Override public ByteArrayOutputStream export(final Table table) { final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet xssfSheet = workbook.createSheet(table.getTableName()); final Collection<ColumnInformation> infoList = table.getColumnInfoList(); final XSSFRow xssfHeaderRow = xssfSheet.createRow(0); for (final ColumnInformation information : infoList) { if (information.isExportable()) { final XSSFCell xssfCell = xssfHeaderRow.createCell(information.getSpaltenNr()); xssfCell.setCellValue(information.getSpaltenName()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); } else {// www. j a v a 2 s . c o m if (logger.isDebugEnabled()) { logger.debug("ColInfo not exportable " + information.getSpaltenName()); } } } final List<Row> tableRowList = table.getRowList(); for (final Row row : tableRowList) { final XSSFRow xssfRow = xssfSheet.createRow(row.getRowNr() + 1); final List<Cell> cellList = row.getCells(); for (final Cell cell : cellList) { if (cell.getColInfo().isExportable()) { final XSSFCell xssfCell = xssfRow.createCell(cell.getColInfo().getSpaltenNr()); final CellTypeEnum cellType = cell.getCellType(); if (cellType.equals(CellTypeEnum.RawData)) { xssfCell.setCellValue(cell.getFormattedValue()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); //JIRA MOD-32 CellType in Abhngigkeit der ValueClass z.B. Number } else if (cellType.equals(CellTypeEnum.RawLink)) { final XSSFCreationHelper helper = workbook.getCreationHelper(); final XSSFHyperlink xssfHyperlink = helper.createHyperlink(Hyperlink.LINK_URL); xssfHyperlink.setAddress(cell.getFormattedValue()); xssfHyperlink.setLabel(cell.getLabel()); xssfCell.setCellValue(cell.getLabel()); xssfCell.setHyperlink(xssfHyperlink); final CellStyle hlink_style = createHyperLinkStyle(workbook); xssfCell.setCellStyle(hlink_style); } else { } } else { if (logger.isDebugEnabled()) { logger.debug("Cell not exportable "); } } } } final ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { workbook.write(outputStream); } catch (IOException e) { logger.error(e); } return outputStream; }
From source file:org.rapidpm.modul.javafx.tableview.filtered.contextmenue.FilteredTableContextMenu.java
License:Apache License
private byte[] convertTable2Xls() throws IOException { //konvertiere final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet xssfSheet = workbook.createSheet("ExcelExport_" + sdf.format(new Date())); final XSSFRow xssfHeaderRow = xssfSheet.createRow(0); final ObservableList<TableColumn> columns = filteredTableView.getColumns(); int colNr = 0; for (final TableColumn column : columns) { final String columnText = column.getText(); final XSSFCell xssfCell = xssfHeaderRow.createCell(colNr); colNr = colNr + 1;//from w w w. jav a 2 s . co m xssfCell.setCellValue(columnText); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); } final ObservableList<FilteredTableDataRow> rowList = filteredTableView.getItems(); int rowNr = 0; for (final FilteredTableDataRow row : rowList) { final XSSFRow xssfRow = xssfSheet.createRow(rowNr); rowNr = rowNr + 1; final String csvRow = row.convertToCSV(); final String[] split = csvRow.split(";"); int cellNr = 0; for (final String s : split) { final XSSFCell xssfCell = xssfRow.createCell(cellNr); cellNr = cellNr + 1; xssfCell.setCellValue(s); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); } } final ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { workbook.write(outputStream); } catch (IOException e) { logger.error(e); } return outputStream.toByteArray(); }
From source file:org.structr.excel.ToExcelFunction.java
License:Open Source License
public Workbook writeExcel(final List list, final String propertyView, final List<String> properties, final boolean includeHeader, final boolean localizeHeader, final String headerLocalizationDomain, final Locale locale, final Integer maxCellLength, final String overflowMode) throws IOException { final Workbook workbook = new XSSFWorkbook(); final CreationHelper factory = workbook.getCreationHelper(); final XSSFSheet sheet = (XSSFSheet) workbook.createSheet(); final Drawing drawing = sheet.createDrawingPatriarch(); int rowCount = 0; int cellCount = 0; XSSFRow currentRow = null; XSSFCell cell = null;// w w w . j a v a 2 s. com if (includeHeader) { currentRow = (XSSFRow) sheet.createRow(rowCount++); cellCount = 0; if (propertyView != null) { final Object obj = list.get(0); if (obj instanceof GraphObject) { for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) { cell = (XSSFCell) currentRow.createCell(cellCount++); String value = key.dbName(); if (localizeHeader) { try { value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain); } catch (FrameworkException fex) { logger.warn("to_excel(): Exception", fex); } } cell.setCellValue(value); } } else { cell = (XSSFCell) currentRow.createCell(cellCount++); cell.setCellValue( "Error: Object is not of type GraphObject, can not determine properties of view for header row"); } } else if (properties != null) { for (final String colName : properties) { cell = (XSSFCell) currentRow.createCell(cellCount++); String value = colName; if (localizeHeader) { try { value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain); } catch (FrameworkException fex) { logger.warn("to_excel(): Exception", fex); } } cell.setCellValue(value); } } } for (final Object obj : list) { currentRow = (XSSFRow) sheet.createRow(rowCount++); cellCount = 0; if (propertyView != null) { if (obj instanceof GraphObject) { for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) { final Object value = ((GraphObject) obj).getProperty(key); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } else { cell = (XSSFCell) currentRow.createCell(cellCount++); cell.setCellValue( "Error: Object is not of type GraphObject, can not determine properties of object"); } } else if (properties != null) { if (obj instanceof GraphObject) { final GraphObject castedObj = (GraphObject) obj; for (final String colName : properties) { final PropertyKey key = StructrApp.key(obj.getClass(), colName); final Object value = castedObj.getProperty(key); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } else if (obj instanceof Map) { final Map castedObj = (Map) obj; for (final String colName : properties) { final Object value = castedObj.get(colName); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } } } return workbook; }
From source file:org.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java
License:Open Source License
protected void fillHeader(XSSFRow row) { for (int i = 0; i < headerArray.length; i++) { XSSFCell cell = row.createCell((short) i); cell.setCellValue(headerArray[i]); cell.setCellStyle(cs);//from www .ja v a 2s. c o m } }
From source file:org.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java
License:Open Source License
protected void fillRow(XSSFRow row, Document document) throws Exception { columnIndex = 0;/*from w w w .j a v a 2 s . com*/ for (String xpath : xpathArray) { String tmp = null; if (DownloadUtil.isJoinField(xpath, concept)) { tmp = getNodeValue(document, xpath); if (fkResovled) { if (colFkMap.containsKey(xpath)) { List<String> fkinfoList = fkMap.get(xpath); if (!fkinfoList.get(0).trim().equalsIgnoreCase("") && !tmp.equalsIgnoreCase("")) { //$NON-NLS-1$ //$NON-NLS-2$ List<String> infoList = getFKInfo(colFkMap.get(xpath), fkinfoList, tmp); if (fkDisplay.equalsIgnoreCase("Id-FKInfo")) { //$NON-NLS-1$ infoList.add(0, tmp); } if (multipleValueSeparator != null && multipleValueSeparator.length() > 0) { tmp = LabelUtil.convertList2String(infoList, multipleValueSeparator); } else { tmp = LabelUtil.convertList2String(infoList, "-"); //$NON-NLS-1$ } } } } } else { tmp = DownloadUtil.getJoinFieldValue(document, xpath, columnIndex); } if (tmp != null) { tmp = tmp.trim(); tmp = tmp.replaceAll("__h", ""); //$NON-NLS-1$ //$NON-NLS-2$ tmp = tmp.replaceAll("h__", ""); //$NON-NLS-1$//$NON-NLS-2$ } else { tmp = ""; //$NON-NLS-1$ } if (entity != null && entity.getTypeModel(xpath) != null) { if (entity.getTypeModel(xpath).getMaxOccurs() != 1 && StringUtils.isNotEmpty(tmp) && multipleValueSeparator != null) { row.createCell((short) columnIndex).setCellValue(tmp.replace(",", multipleValueSeparator)); //$NON-NLS-1$ } else { row.createCell((short) columnIndex).setCellValue(tmp); } columnIndex++; } else { continue; } } }
From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java
private void processWorkbookRow(XSSFRow header, XSSFRow row, Submission sub, List<SearchOrder> SearchOrderList) { int j = 0; // cell counter for (SearchOrder column : SearchOrderList) { switch (column) { case ID:/*www . j av a 2s .c o m*/ header.createCell(j).setCellValue("ID"); if (null != sub.getId()) row.createCell(j).setCellValue(sub.getId()); j++; break; case STUDENT_EMAIL: header.createCell(j).setCellValue("Student email"); if (null != sub.getSubmitter() && null != sub.getSubmitter().getEmail()) row.createCell(j).setCellValue(sub.getSubmitter().getEmail()); j++; break; case STUDENT_NAME: header.createCell(j).setCellValue("Student name"); row.createCell(j).setCellValue( sub.getStudentFormattedName(org.tdl.vireo.model.NameFormat.LAST_FIRST_MIDDLE_BIRTH)); j++; break; case STUDENT_ID: header.createCell(j).setCellValue("Student ID"); if (sub.getSubmitter() != null && sub.getSubmitter().getInstitutionalIdentifier() != null) row.createCell(j).setCellValue(sub.getSubmitter().getInstitutionalIdentifier()); j++; break; case STATE: header.createCell(j).setCellValue("Status"); if (null != sub.getState()) row.createCell(j).setCellValue(sub.getState().getDisplayName()); j++; break; case ASSIGNEE: header.createCell(j).setCellValue("Assignee"); if (null != sub.getAssignee()) row.createCell(j).setCellValue( sub.getAssignee().getFormattedName(org.tdl.vireo.model.NameFormat.FIRST_LAST)); j++; break; case DOCUMENT_TITLE: header.createCell(j).setCellValue("Title"); if (null != sub.getDocumentTitle()) row.createCell(j).setCellValue(sub.getDocumentTitle()); j++; break; case DOCUMENT_ABSTRACT: header.createCell(j).setCellValue("Abstract"); if (null != sub.getDocumentAbstract()) row.createCell(j).setCellValue(sub.getDocumentAbstract()); j++; break; case DOCUMENT_SUBJECTS: header.createCell(j).setCellValue("Subjects"); if (null != sub.getDocumentSubjects()) row.createCell(j).setCellValue(StringUtils.join(sub.getDocumentSubjects(), ";")); j++; break; case DOCUMENT_LANGUAGE: header.createCell(j).setCellValue("Language"); if (null != sub.getDocumentLanguage()) row.createCell(j).setCellValue(sub.getDocumentLanguage()); j++; break; case PUBLISHED_MATERIAL: header.createCell(j).setCellValue("Published material"); if (null != sub.getPublishedMaterial()) row.createCell(j).setCellValue("Yes - " + sub.getPublishedMaterial()); j++; break; case PRIMARY_DOCUMENT: header.createCell(j).setCellValue("Primary document"); if (null != sub.getPrimaryDocument()) row.createCell(j).setCellValue(sub.getPrimaryDocument().getName()); j++; break; case GRADUATION_DATE: header.createCell(j).setCellValue("Graduation date"); StringBuilder sb = new StringBuilder(); String monthName = null; if (sub.getGraduationMonth() != null && sub.getGraduationMonth() >= 0 && sub.getGraduationMonth() <= 11) monthName = new java.text.DateFormatSymbols().getMonths()[sub.getGraduationMonth()]; if (sub.getGraduationYear() != null) sb.append(sub.getGraduationYear()); if (monthName != null) sb.append(" ").append(monthName); row.createCell(j).setCellValue(sb.toString()); j++; break; case DEFENSE_DATE: header.createCell(j).setCellValue("Defense date"); if (sub.getDefenseDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getDefenseDate())); j++; break; case SUBMISSION_DATE: header.createCell(j).setCellValue("Submission date"); if (sub.getSubmissionDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getSubmissionDate())); j++; break; case LICENSE_AGREEMENT_DATE: header.createCell(j).setCellValue("License agreement date"); if (sub.getLicenseAgreementDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getLicenseAgreementDate())); j++; break; case APPROVAL_DATE: header.createCell(j).setCellValue("Approval date"); if (sub.getApprovalDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getApprovalDate())); j++; break; case COMMITTEE_APPROVAL_DATE: header.createCell(j).setCellValue("Committee approval date"); if (sub.getCommitteeApprovalDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getCommitteeApprovalDate())); j++; break; case COMMITTEE_EMBARGO_APPROVAL_DATE: header.createCell(j).setCellValue("Committee embargo approval date"); if (sub.getCommitteeEmbargoApprovalDate() != null) row.createCell(j).setCellValue(sdf.format(sub.getCommitteeEmbargoApprovalDate())); j++; break; case COMMITTEE_MEMBERS: header.createCell(j).setCellValue("Committee members"); StringBuilder cm = new StringBuilder(); int i = 0; for (i = 0; i < sub.getCommitteeMembers().size(); i++) { CommitteeMember member = sub.getCommitteeMembers().get(i); cm.append(member.getFormattedName(NameFormat.LAST_FIRST)); if (member.getRoles().size() > 0) { cm.append(" (").append(member.getFormattedRoles()).append(")"); } if ((i + 1) < sub.getCommitteeMembers().size()) { cm.append(";"); } } row.createCell(j).setCellValue(cm.toString()); j++; break; case COMMITTEE_CONTACT_EMAIL: header.createCell(j).setCellValue("Committee contact email"); if (sub.getCommitteeContactEmail() != null) row.createCell(j).setCellValue(sub.getCommitteeContactEmail()); j++; break; case DEGREE: header.createCell(j).setCellValue("Degree"); if (sub.getDegree() != null) row.createCell(j).setCellValue(sub.getDegree()); j++; break; case DEGREE_LEVEL: header.createCell(j).setCellValue("Degree level"); if (sub.getDegreeLevel() != null) row.createCell(j).setCellValue(sub.getDegreeLevel().name()); j++; break; case PROGRAM: header.createCell(j).setCellValue("Program"); if (sub.getProgram() != null) row.createCell(j).setCellValue(sub.getProgram()); j++; break; case COLLEGE: header.createCell(j).setCellValue("College"); if (sub.getCollege() != null) row.createCell(j).setCellValue(sub.getCollege()); j++; break; case DEPARTMENT: header.createCell(j).setCellValue("Department"); if (sub.getDepartment() != null) row.createCell(j).setCellValue(sub.getDepartment()); j++; break; case MAJOR: header.createCell(j).setCellValue("Major"); if (sub.getMajor() != null) row.createCell(j).setCellValue(sub.getMajor()); j++; break; case EMBARGO_TYPE: header.createCell(j).setCellValue("Embargo type"); String sEmbargos = ""; List<EmbargoType> embargoTypes = sub.getEmbargoTypes(); for (int k = 0; k < embargoTypes.size(); k++) { EmbargoType embargoType = embargoTypes.get(k); sEmbargos += embargoType.getName() + (embargoType.getGuarantor() != EmbargoGuarantor.DEFAULT ? " (" + embargoType.getGuarantor().name() + ")" : "") + ((k + 1) < embargoTypes.size() ? ";" : ""); } row.createCell(j).setCellValue(sEmbargos); j++; break; case DOCUMENT_TYPE: header.createCell(j).setCellValue("Document type"); if (sub.getDocumentType() != null) row.createCell(j).setCellValue(sub.getDocumentType()); j++; break; case UMI_RELEASE: header.createCell(j).setCellValue("UMI release"); if (sub.getUMIRelease() != null) { if (sub.getUMIRelease()) { row.createCell(j).setCellValue("Yes"); } else { row.createCell(j).setCellValue("No"); } } j++; break; case CUSTOM_ACTIONS: header.createCell(j).setCellValue("Custom actions"); String sActions = ""; SettingsRepository settingRepo = Spring.getBeanOfType(SettingsRepository.class); List<CustomActionDefinition> customActions = settingRepo.findAllCustomActionDefinition(); for (int k = 0; k < customActions.size(); k++) { CustomActionDefinition systemAction = customActions.get(k); boolean found = false; for (org.tdl.vireo.model.CustomActionValue subAction : sub.getCustomActions()) { if (systemAction.equals(subAction.getDefinition())) { sActions += " " + subAction.getDefinition().getLabel(); found = true; } } if (!found) { sActions += "? " + systemAction.getLabel(); } if ((k + 1) < customActions.size()) { sActions += '\n'; } } row.createCell(j).setCellValue(sActions); j++; break; case DEPOSIT_ID: header.createCell(j).setCellValue("Deposit ID"); if (sub.getDepositId() != null) row.createCell(j).setCellValue(sub.getDepositId()); j++; break; case REVIEWER_NOTES: header.createCell(j).setCellValue("Reviewer notes"); if (sub.getReviewerNotes() != null) row.createCell(j).setCellValue(sub.getReviewerNotes()); j++; break; case DOCUMENT_KEYWORDS: header.createCell(j).setCellValue("Document Keywords"); if (sub.getDocumentKeywords() != null) row.createCell(j).setCellValue(sub.getDocumentKeywords()); j++; break; case LAST_EVENT_ENTRY: header.createCell(j).setCellValue("Last Event Entry"); if (sub.getLastLogEntry() != null) row.createCell(j).setCellValue(sub.getLastLogEntry()); j++; break; case LAST_EVENT_TIME: header.createCell(j).setCellValue("Last Event Time"); if (sub.getLastLogDate() != null) row.createCell(j).setCellValue(sub.getLastLogDate()); j++; break; case ORCID: header.createCell(j).setCellValue("ORCID"); if (sub.getOrcid() != null) row.createCell(j).setCellValue(sub.getOrcid()); j++; break; } } }