List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:org.ramalapure.userinfoapp.UserInfoApp.java
@Override public void start(Stage primaryStage) { CheckConnection();// w w w .j a va 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.talend.dataprep.qa.util.ExcelComparator.java
License:Open Source License
public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) { int firstRow1 = sheet1.getFirstRowNum(); int lastRow1 = sheet1.getLastRowNum(); boolean equalSheets = true; for (int i = firstRow1; i <= lastRow1; i++) { XSSFRow row1 = sheet1.getRow(i); XSSFRow row2 = sheet2.getRow(i); if (!compareTwoRows(row1, row2)) { equalSheets = false;//w w w . j av a2s . c o m break; } } return equalSheets; }
From source file:org.tsukuba_bunko.lilac.helper.port.impl.ImportDataHelperBase.java
License:Open Source License
/** * @see org.tsukuba_bunko.lilac.helper.port.ImportDataHelper#importData(org.apache.poi.xssf.usermodel.XSSFSheet) *//*w w w .j av a 2s.c o m*/ @Override public void importData(XSSFSheet sheet) { List<String> properties = getProperties(sheet); int lastRowNumber = sheet.getLastRowNum(); for (int i = 1; i <= lastRowNumber; ++i) { XSSFRow row = sheet.getRow(i); Map<String, String> record = readRecord(row, properties); if (record == null) { continue; } String status = record.get("status"); if ("U".equals(status)) { //?? } else if (status == null || "C".equals(status)) { insertRecord(record); } else if ("M".equals(status)) { updateRecord(record); } else if ("D".equals(status)) { deleteRecord(record); } } }
From source file:org.tuxedoberries.transformo.excel.XLSXDataReader.java
License:Open Source License
@Override protected void doRead() { _data = new ArrayList<RowData>(); if (_tmeta == null) { Logger.Error("Table is not set"); return;/*from ww w . j av a2 s. c om*/ } if (_tmeta.TableName == null) { Logger.Error("Table Name is null"); return; } if (_tmeta.TableName.isEmpty()) { Logger.Error("Table Name is not set"); return; } if (_workBook == null) { Logger.Error("Work Book is null"); return; } XSSFSheet sheet = _workBook.getSheet(_tmeta.TableName); if (sheet == null) { Logger.Error("Null sheet %s", _tmeta.TableName); return; } int totalRows = sheet.getLastRowNum(); if (totalRows < XLSXTableMetaReader.ROW_META_COUNT) { Logger.Warning("Total number of rows less than expected [%d]. %d Rows are expected", totalRows, XLSXTableMetaReader.ROW_META_COUNT); return; } for (int i = 3; i <= totalRows; ++i) { RowData rdata = generateData(sheet.getRow(i)); if (rdata != null) { _data.add(rdata); } } }
From source file:org.tuxedoberries.transformo.excel.XLSXTableMetaReader.java
License:Open Source License
private TableMeta generateTableMeta(XSSFSheet sheet) { int totalRows = sheet.getLastRowNum(); if (totalRows < ROW_META_COUNT) { Logger.Warning("Total number of rows less than expected [%d]. %d Rows are expected", totalRows, ROW_META_COUNT);//from www . ja v a 2 s.c om return null; } TableMeta meta = new TableMeta(); meta.TableName = sheet.getSheetName(); if (meta.TableName.startsWith("!")) return null; generateTypes(sheet.getRow(0), meta); generateShortNames(sheet.getRow(1), meta); generateNames(sheet.getRow(2), meta); return meta; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Set all the student data, each row represents one workgroup * // w w w . ja v a 2 s. c om * @param workbook the excel workbook * @param nodeIdToNodeTitlesMap a mapping of node id to node titles * @param workgroupIds a vector of workgroup ids * @param nodeIdList a list of node ids * @param runId the run id * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids */ private void setGetLatestStudentWorkStudentRows(XSSFWorkbook workbook, HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, List<String> nodeIdList, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { XSSFSheet mainSheet = null; int rowCounter = 0; if (workbook != null) { //workbook is not null which means we are generating an xls file //get the sheet mainSheet = workbook.getSheetAt(0); //get the next empty row rowCounter = mainSheet.getLastRowNum() + 1; } //loop through the workgroup ids for (int x = 0; x < workgroupIds.size(); x++) { //create a row for this workgroup Row rowForWorkgroupId = createRow(mainSheet, x + rowCounter); Vector<String> rowForWorkgroupIdVector = createRowVector(); int workgroupColumnCounter = 0; //get a workgroup id String userId = workgroupIds.get(x); int periodId = workgroupIdToPeriodId.get(Integer.parseInt(userId)); /* * create the row that will display the user data such as the actual values * for workgroup id, student login, teacher login, period name, etc. */ workgroupColumnCounter = createUserDataRow(workgroupColumnCounter, rowForWorkgroupId, rowForWorkgroupIdVector, userId, true, true, null); /* * increment the column counter to create an empty column under the header column * that contains Step Title, Step Type, Step Prompt, Node Id, Step Extra */ workgroupColumnCounter++; addEmptyElementsToVector(rowForWorkgroupIdVector, 1); //get the UserInfo object for the workgroup id UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(userId)); //get all the work for a workgroup id List<StepWork> stepWorksForWorkgroupId = vleService.getStepWorksByUserInfo(userInfo); //loop through all the node ids which are ordered for (int y = 0; y < nodeIdList.size(); y++) { //get a node id String nodeId = nodeIdList.get(y); //get the content for the node JSONObject nodeContent = nodeIdToNodeContent.get(nodeId); JSONObject nodeJSONObject = nodeIdToNode.get(nodeId); /* * set the review cells if applicable to this step, this means filling in the * cells that specify the associated workgroup id and associated work and only * applies for review type steps. if the current step/node is not a review cell * this function call will not need to do much besides fill in N/A values * or nothing at all depending on whether we are getting "latestStudentWork" * or "allStudentWork" */ workgroupColumnCounter = setGetLatestStudentWorkReviewCells(teacherWorkgroupIds, stepWorksForWorkgroupId, runId, periodId, userInfo, nodeJSONObject, nodeContent, rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter, "latestStudentWork"); //get all the step works for this node id List<StepWork> stepWorksForNodeId = getStepWorksForNodeId(stepWorksForWorkgroupId, nodeId); //get the latest step work that contains a response StepWork latestStepWorkWithResponse = getLatestStepWorkWithResponse(stepWorksForNodeId); //set the step work data into the row in the given column workgroupColumnCounter = setStepWorkResponse(rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter, latestStepWorkWithResponse, nodeId); if (isAutoGraded(nodeContent)) { //set the auto graded values workgroupColumnCounter = setLatestAutoScoreValues(stepWorksForNodeId, rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter); } //set the latest annotation score and timestamp from any of the teachers workgroupColumnCounter = setLatestAnnotationScore(stepWorksForNodeId, rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter, nodeId); //set the latest annotation comment and timestamp from any of the teachers workgroupColumnCounter = setLatestAnnotationComment(stepWorksForNodeId, rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter); } //write the csv row if we are generating a csv file writeCSV(rowForWorkgroupIdVector); } }
From source file:org.xframium.application.ExcelApplicationProvider.java
License:Open Source License
/** * Read elements./* www .j a v a 2 s . c om*/ * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet(tabName); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; ApplicationRegistry.instance() .addApplicationDescriptor(new ApplicationDescriptor(getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(4)), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)), getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(6)), new HashMap<String, Object>(0))); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.content.provider.ExcelContentProvider.java
License:Open Source License
/** * Read elements.//from www. j a v a 2s . c o m * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet(tabName); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); String keyName = getCellValue(currentRow.getCell(keyColumn)); String[] valueList = new String[lookupColumns.length]; for (int x = 0; x < lookupColumns.length; x++) { valueList[x] = getCellValue(currentRow.getCell(lookupColumns[x])); } ContentData contentData = new DefaultContentData(keyName, valueList); ContentManager.instance().addContentData(contentData); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.device.cloud.ExcelCloudProvider.java
License:Open Source License
/** * Read elements./*from ww w .j av a2s . c o m*/ * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet(tabName); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; CloudRegistry.instance() .addCloudDescriptor(new CloudDescriptor(getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)), getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(4)), getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(7)), getCellValue(currentRow.getCell(6)), getCellValue(currentRow.getCell(8)))); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.device.data.ExcelDataProvider.java
License:Open Source License
/** * Read data.//ww w .ja v a2 s. co m * * @param inputStream the input stream */ private void readData(InputStream inputStream) { BufferedReader fileReader = null; XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet(tabName); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; String driverName = ""; switch (driverType) { case APPIUM: if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("IOS")) driverName = "IOS"; else if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("ANDROID")) driverName = "ANDROID"; else throw new IllegalArgumentException("Appium is not supported on the following OS " + getCellValue(currentRow.getCell(3)).toUpperCase()); break; case PERFECTO: driverName = "PERFECTO"; break; case WEB: driverName = "WEB"; break; } Device currentDevice = new SimpleDevice(getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)), getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(4)), getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(6)), Integer.parseInt(getCellValue(currentRow.getCell(7))), driverName, Boolean.parseBoolean(getCellValue(currentRow.getCell(8))), null); if (currentDevice.isActive()) { if (log.isDebugEnabled()) log.debug("Extracted: " + currentDevice); DeviceManager.instance().registerDevice(currentDevice); } } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }