List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:vd10_workbook.EmployeeManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("ds_nhan_vien"); //declare row XSSFRow row;//from w ww .j a v a 2 s . c o m //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); int count = 1; while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); String name = cell.getStringCellValue(); int sex = -1; cell = cellIterator.next(); if (cell.getStringCellValue().equalsIgnoreCase("nam")) { sex = 1; } else { sex = 0; } cell = cellIterator.next(); String date_s = cell.getStringCellValue(); SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd"); Date date = dt.parse(date_s); cell = cellIterator.next(); String cmnd = cell.getStringCellValue(); cell = cellIterator.next(); double salary = cell.getNumericCellValue(); cell = cellIterator.next(); String address = cell.getStringCellValue(); cell = cellIterator.next(); int department = (int) cell.getNumericCellValue(); this.list.add(new Employee(name, sex, date, cmnd, salary, address, department)); if (count == NUMBEROFROWS) { break; } else { count++; } } }
From source file:vd10_workbook.ForeignLanguageManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("ngoai_ngu"); //declare row XSSFRow row;//from w ww.j av a 2 s . c om //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); this.list.add(new ForeignLanguage(cell.getStringCellValue())); } }
From source file:vd10_workbook.RequirementManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("yeu_cau"); //declare row XSSFRow row;/* w ww. j ava 2 s . co m*/ //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); int workTypeID = (int) cell.getNumericCellValue(); cell = cellIterator.next(); int languageID = (int) cell.getNumericCellValue(); this.list.add(new Requirement(workTypeID, languageID)); } }
From source file:vd10_workbook.UserGroupManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("nhom_nguoi_dung"); //declare row XSSFRow row;/*from www .jav a2 s . c om*/ //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String id = cell.getStringCellValue(); this.list.add(new UserGroup(id, name)); } }
From source file:vd10_workbook.UserManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("nguoi_dung"); //declare row XSSFRow row;//from w w w . jav a2 s .co m //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String password = cell.getStringCellValue(); cell = cellIterator.next(); String expansion = cell.getStringCellValue(); cell = cellIterator.next(); int userGroupID = (int) cell.getNumericCellValue(); this.list.add(new User(name, password, expansion, userGroupID)); } }
From source file:vd10_workbook.WorkTypeManagement.java
public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException { this.list = new ArrayList<>(); //Get the sheet at index 0 (fist sheet) XSSFSheet spreadsheet = workbook.getSheet("loai_cong_viec"); //declare row XSSFRow row;/* w ww. ja v a 2s .co m*/ //declare Iterator<Row> to browse row by row Iterator<Row> rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); row = (XSSFRow) rowIterator.next(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); cell = cellIterator.next(); this.list.add(new WorkType(cell.getStringCellValue())); } }
From source file:ventanas.parqueadero.NewimportContract.java
private void importbtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importbtnActionPerformed //validar las fechas Date from = dateDesde.getDate(); Date until = dateHasta.getDate(); boolean isValid = datesValidation(from, until); if (!isValid) { JOptionPane.showMessageDialog(this, "Ya existe contratos en las fechas" + " indicadas", "Error", JOptionPane.ERROR_MESSAGE); return;//from w ww. jav a2s. c om } //Crear un nuevo periodo de contrato Contracts contract = new Contracts(); contract.setDateStart(from); contract.setDateEnd(until); contractsController.create(contract); //barra de progreso progressBar.setMinimum(0); try { //leer el archivo FileInputStream fis = new FileInputStream(this.file); XSSFWorkbook workbook = new XSSFWorkbook(fis); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); int cont = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getCell(0) != null) { cont++; } } progressBar.setMaximum(cont); cont = 0; rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getCell(0) == null) { continue; } //buscar o crear la persona Person person = findPerson(row); //buscar o crear el client ClientProvider client = findClient(person); UserContract usercontract = new UserContract(); usercontract.setClient(client); usercontract.setContract(contract); usercontract.setLicensePlate(row.getCell(2).getStringCellValue()); usercontractController.create(usercontract); cont++; progressBar.setValue(cont); } fis.close(); JOptionPane.showMessageDialog(this, "Importacin realizada con " + "xito", "Informacin", JOptionPane.INFORMATION_MESSAGE, null); this.dispose(); ImportContracts.verTabla(); } catch (FileNotFoundException ex) { Logger.getLogger(NewimportContract.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { JOptionPane.showMessageDialog(this, "Error en la importacin", "Error", JOptionPane.ERROR_MESSAGE, null); Logger.getLogger(NewimportContract.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:View.IHM_ListerDevoir.java
private void btImportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btImportActionPerformed JFileChooser choix = new JFileChooser(); choix.showDialog(choix, null);//from www . j av a2 s.c om File f = choix.getSelectedFile(); try { FileInputStream file = new FileInputStream(f); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); // On copie les eleves de la classe dans notre collection archive DAO_Etudiant dao = new DAO_Etudiant(null); dao.archive(cbxClasse.getSelectedItem().toString()); //Iterate through each rows one by one Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { org.apache.poi.ss.usermodel.Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); if (!cell.getStringCellValue().equals("NOM") && !cell.getStringCellValue().equals("Prnom")) { String sNom = cell.getStringCellValue(); String sPrenom = ""; if (cellIterator.hasNext()) { cell = cellIterator.next(); sPrenom = cell.getStringCellValue(); } if (sNom != "" && sPrenom != "") { Etudiant etud = new Etudiant(sNom, sPrenom, cbxClasse.getSelectedItem().toString()); System.out.println(etud.toString()); DAO_Etudiant dao_etud = new DAO_Etudiant(null); dao_etud.create(etud); } } // //Check the cell type and format accordingly // switch (cell.getCellType()) // { // case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + " "); // break; // case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + ";"); // break; // } } } file.close(); } catch (Exception e) { System.out.println(e.getMessage()); } }
From source file:View.rnvLib.java
public static void main(String[] args) { String url = "C:/Users/tamqu/Desktop/h.rnv.xlsx"; try {/*from w ww. j a v a2 s . com*/ FileInputStream file = new FileInputStream(new File(url)); ArrayList<Person> personas = new ArrayList<>(); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); DecimalFormat dformatDni = new DecimalFormat("00000000"); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one java.util.Iterator<Row> rowIterator = sheet.iterator(); //Saltando cabecera rowIterator.next(); while (rowIterator.hasNext()) { Person persona = new Person(); Row row = rowIterator.next(); Cell c = row.getCell(0); if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) { break; } //For each row, iterate through all the columns java.util.Iterator<Cell> cellIterator = row.cellIterator(); int nrow = 0; String str; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cell.setCellType(Cell.CELL_TYPE_STRING); str = cell.getStringCellValue(); switch (nrow) { case 0: java.lang.System.out.print("Nombres: "); persona.setName(str); break; case 1: java.lang.System.out.print("Apellidos: "); persona.setLastname(str); break; case 2: java.lang.System.out.print("DNI: "); //java.lang.System.out.print(str); str = dformatDni.format(Integer.parseInt(str)); persona.setDni(str); break; case 3: java.lang.System.out.print("Ubigeo: "); persona.setUbigeo(str); break; case 4: java.lang.System.out.print("Huella: "); persona.setFingerprint("../rnv/" + str + ".jpg"); break; case 5: java.lang.System.out.print("Firma: "); persona.setSignature("../rnv/" + str + ".jpg"); break; case 6: java.lang.System.out.print("Habilitado: "); persona.setCitizen(!Boolean.parseBoolean(str)); break; } java.lang.System.out.print(str + " | "); nrow++; } java.lang.System.out.println(""); persona.setDisabled(false); personas.add(persona); } file.close(); Manager.addPeople(personas); } catch (Exception e) { e.printStackTrace(); } }
From source file:WeeklyOPD.OPDDataTransfer.java
License:Open Source License
public static void main(String args[]) { try {/* w ww. j a va 2s . co m*/ FileInputStream file = new FileInputStream( new File("C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\" + dateFolder + "\\" + dateFolder + ".xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Connection conn = connectToDatabaseHMS(); assert (conn != null); Connection conn2 = connectToDatabaseHMSSecurity(); assert (conn2 != null); try { conn.setAutoCommit(false); conn2.setAutoCommit(false); } catch (SQLException sqle) { System.err.println("Could not set autocommit to false"); sqle.printStackTrace(); } //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); //Skip the 1st row rowIterator.next(); /*************************** TO UPDATE ON ERROR ******************************/ //set time time = Time.valueOf("09:00:00"); //set entry number default to 1. entryNumber = 1; /*****************************************************************************/ // //set entry crNo // entryCrNo = 1; Timestamp timestamp = new Timestamp(date.getTime() + time.getTime() + 19800000/*for IST*/); System.out.println(timestamp.toString()); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, get values of each column Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); String dept; if (cell.getCellType() == 1) dept = cell.getStringCellValue(); else { int no = (int) cell.getNumericCellValue(); dept = no + ""; System.out.println(dept); } cell = cellIterator.next(); String type = cell.getStringCellValue(); cell = cellIterator.next(); int crNo; if (cell.getCellType() == 0) crNo = (int) (cell.getNumericCellValue()); else { System.out.println("crNo cell value: " + cell.getStringCellValue()); crNo = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String guardian = cell.getStringCellValue(); cell = cellIterator.next(); String rel = cell.getStringCellValue(); cell = cellIterator.next(); System.out.println("\n cell.getCellType() :" + cell.getCellType()); int ageYrs = 0; if (cell.getCellType() == 0) ageYrs = (int) (cell.getNumericCellValue()); else { System.out.println("age cell value: " + cell.getStringCellValue()); ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String gender = cell.getStringCellValue(); cell = cellIterator.next(); String add = cell.getStringCellValue(); cell = cellIterator.next(); String city = cell.getStringCellValue(); cell = cellIterator.next(); String state = cell.getStringCellValue(); int deptID = getDeptID(dept); assert (deptID != 0); int stateID = getStateID(state); assert (stateID != 0); int drID = getDrID(deptID); assert (drID != 0); // if drID = 0 that means a dept. has been entered which doesn't have a doctor String loginUserName = getLoginUserName(); String userID = getLoginUserID(loginUserName); long OPDNo = Long.parseLong(OPDDATE + entryNumber); boolean queryExecuted = true; if (type.equals("New")) // As only New patients must be registered and have a CR generated { try { String insertSql = "INSERT INTO Reg " + "(Regno, Name, Fname, Relation, AgeY, Sex, Address1, City, State, Department, Date)" + "VALUES(" + crNo + ",'" + name + "','" + guardian + "','" + rel + "'," + ageYrs + ",'" + gender + "','" + add + "','" + city + "'," + stateID + "," + deptID + ",'" + date + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in Reg gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into Reg...!!"); ex.printStackTrace(); } } try { String insertSql = "INSERT INTO OPD " + "(OPDNo, CrNo, PatientType, DepartmentId, DrId, Date, Time, LoginUserName, IsActive)" + "VALUES(" + OPDNo + "," + crNo + ",'" + type + "'," + deptID + "," + drID + ",'" + date + "','" + time + "','" + loginUserName + "','" + true + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in OPD gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into OPD...!!"); ex.printStackTrace(); } try { String updateSql = "UPDATE aspnet_Users " + "SET LastActivityDate='" + timestamp + "'" + "WHERE UserId='" + userID + "'"; System.out.println(updateSql); Statement st = conn2.createStatement(); int val = st.executeUpdate(updateSql); System.out.println("One row in aspnet_Users gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot update timestamp in aspnet_Users...!!"); ex.printStackTrace(); } if (!queryExecuted) //if insertion to any table fails, rollback. try { conn.rollback(); conn2.rollback(); break; // and run program again at any error } catch (SQLException ex) { ex.printStackTrace(); } else try { conn.commit(); conn2.commit(); entryNumber++; // entryCrNo++; time = new Time(time.getTime() + 35000);//add 35 seconds timestamp = new Timestamp(timestamp.getTime() + 35000); } catch (SQLException ex) { ex.printStackTrace(); } } file.close(); if (conn != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn.close(); System.out.println("HMS Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } if (conn2 != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn2.close(); System.out.println("HMS_Security Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }