Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet.

Prototype

@Override
public XSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillCompletedSheet(XSSFWorkbook wb, ArrayList<AnalysisViewVO> list, boolean mySection) {
    int r;//from w ww.  j  a v  a  2s  . c o m
    SystemUserPermission perm;
    ModulePermission modPerm;
    XSSFSheet sheet;

    perm = userCache.getPermission();
    modPerm = perm.getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();

    sheet = wb.createSheet(Messages.get().todo_completed());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_priority(),
            Messages.get().todo_domain(), Messages.get().todo_section(), Messages.get().todo_test(),
            Messages.get().todo_method(), Messages.get().todo_override(), Messages.get().todo_completed(),
            Messages.get().todo_domainSpecField(), Messages.get().todo_reportTo());

    for (AnalysisViewVO a : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this analysis only if
         * doesn't belong to a deactivated domain e.g. private well
         */
        if (mySection && perm.getSection(a.getSectionName()) == null || !canViewSample(a.getDomain(), modPerm)
                || domains.get(a.getDomain()) == null)
            continue;

        addDataRow(sheet.createRow(r++), a.getAccessionNumber(), a.getPriority(), domains.get(a.getDomain()),
                a.getSectionName(), a.getTestName(), a.getMethodName(), a.getAnalysisResultOverride(),
                a.getCompletedDate(), a.getToDoDescription(), a.getPrimaryOrganizationName());
    }

    setSize(sheet, 10);
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillReleasedSheet(XSSFWorkbook wb, ArrayList<AnalysisViewVO> list, boolean mySection) {
    int r;/* w w w.  j a va  2 s  .c o m*/
    SystemUserPermission perm;
    ModulePermission modPerm;
    XSSFSheet sheet;

    perm = userCache.getPermission();
    modPerm = perm.getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();

    sheet = wb.createSheet(Messages.get().todo_released());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_domain(),
            Messages.get().todo_section(), Messages.get().todo_test(), Messages.get().todo_method(),
            Messages.get().todo_collected(), Messages.get().todo_released(), Messages.get().todo_override(),
            Messages.get().todo_domainSpecField(), Messages.get().todo_reportTo());

    for (AnalysisViewVO a : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this analysis only if
         * doesn't belong to a deactivated domain e.g. private well
         */
        if (mySection && perm.getSection(a.getSectionName()) == null || !canViewSample(a.getDomain(), modPerm)
                || domains.get(a.getDomain()) == null)
            continue;

        addDataRow(sheet.createRow(r++), a.getAccessionNumber(), domains.get(a.getDomain()), a.getSectionName(),
                a.getTestName(), a.getMethodName(),
                TurnaroundUtil.getCombinedYM(a.getCollectionDate(), a.getCollectionTime()),
                a.getReleasedDate().getDate(), a.getAnalysisResultOverride(), a.getToDoDescription(),
                a.getPrimaryOrganizationName());
    }

    setSize(sheet, 10);
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillToBeVerifiedSheet(XSSFWorkbook wb, ArrayList<ToDoSampleViewVO> list) {
    int r;//from   w  w w.  ja va  2  s.  co  m
    XSSFSheet sheet;
    ModulePermission modPerm;

    modPerm = userCache.getPermission().getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();
    sheet = wb.createSheet(Messages.get().todo_toBeVerified());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_domain(),
            Messages.get().todo_collected(), Messages.get().todo_received(), Messages.get().todo_override(),
            Messages.get().todo_domainSpecField(), Messages.get().todo_reportTo());

    for (ToDoSampleViewVO s : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this sample only if doesn't
         * belong to a deactivated domain e.g. private well
         */
        if (!canViewSample(s.getDomain(), modPerm) || domains.get(s.getDomain()) == null)
            continue;
        addDataRow(sheet.createRow(r++), s.getAccessionNumber(), domains.get(s.getDomain()),
                TurnaroundUtil.getCombinedYM(s.getCollectionDate(), s.getCollectionTime()), s.getReceivedDate(),
                s.getSampleResultOverride(), s.getDescription(), s.getPrimaryOrganizationName());
    }

    setSize(sheet, 7);
}

From source file:org.openelis.bean.ToDoExcelHelperBean.java

License:Open Source License

private void fillOtherSheet(XSSFWorkbook wb, ArrayList<AnalysisViewVO> list, boolean mySection) {
    int r;//from w w  w  .  j  av a2s. c  o m
    SystemUserPermission perm;
    ModulePermission modPerm;
    XSSFSheet sheet;

    perm = userCache.getPermission();
    modPerm = perm.getModule("patient");
    if (modPerm == null)
        modPerm = new ModulePermission();

    sheet = wb.createSheet(Messages.get().todo_other());
    r = 0;

    addHeaderRow(sheet.createRow(r++), Messages.get().todo_accNum(), Messages.get().todo_domain(),
            Messages.get().todo_section(), Messages.get().gen_status(), Messages.get().todo_test(),
            Messages.get().todo_method(), Messages.get().todo_collected(), Messages.get().todo_received(),
            Messages.get().todo_override(), Messages.get().todo_domainSpecField(),
            Messages.get().todo_reportTo());

    for (AnalysisViewVO a : list) {
        /*
         * if this sample has patient info, the user must have the right
         * permission to see it; also, show this analysis only if doesn't
         * belong to a deactivated domain e.g. private well
         */
        if (mySection && perm.getSection(a.getSectionName()) == null || !canViewSample(a.getDomain(), modPerm)
                || domains.get(a.getDomain()) == null)
            continue;

        addDataRow(sheet.createRow(r++), a.getAccessionNumber(), domains.get(a.getDomain()), a.getSectionName(),
                statuses.get(a.getAnalysisStatusId()), a.getTestName(), a.getMethodName(),
                TurnaroundUtil.getCombinedYM(a.getCollectionDate(), a.getCollectionTime()), a.getReceivedDate(),
                a.getAnalysisResultOverride(), a.getToDoDescription(), a.getPrimaryOrganizationName());
    }

    setSize(sheet, 7);
}

From source file:org.openepics.names.ui.export.ExcelExport.java

License:Open Source License

private XSSFSheet createSheetWithHeader(XSSFWorkbook workbook, String sheetName, String... columnNames) {
    final XSSFSheet sheet = workbook.createSheet(sheetName);
    final Row row = appendRow(sheet);
    for (String columnName : columnNames) {
        appendCell(row, columnName);/*from w  w  w .  ja v  a2 s  .  c o m*/
    }
    return sheet;
}

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 .  ja v a2  s  .c o m
    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();//w  w  w.  j  a  v 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 {//from  w  ww. j  av  a 2s  .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 ww  w. ja  v a2  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.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java

License:Open Source License

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    XSSFWorkbook workbook = new XSSFWorkbook();
    cs = workbook.createCellStyle();//from  w w  w.  ja va2s  .  c o m
    XSSFFont f = workbook.createFont();
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    XSSFSheet sheet = workbook.createSheet(SHEET_LABEL);
    sheet.setDefaultColumnWidth((short) 20);
    XSSFRow row = sheet.createRow((short) 0);
    try {
        setParameter(request);
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //$NON-NLS-1$
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        fillHeader(row);
        fillSheet(sheet);
    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
    OutputStream out = response.getOutputStream();
    workbook.write(out);
    out.close();
}