List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:View.rnvLib.java
public static void main(String[] args) { String url = "C:/Users/tamqu/Desktop/h.rnv.xlsx"; try {/* w w w .j a va2s. c o m*/ 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 {/*from w w w .j a v a 2 s. 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(); } }
From source file:WeeklyOPD.SplitOldDepartmentwise.java
License:Open Source License
private static void createDepartmentwiseSheets() { HashMap<String, Integer> depttMap = new HashMap<>(); depttMap.put("Medicine", 1); depttMap.put("Surgery", 2); depttMap.put("Obs & Gynae", 3); depttMap.put("Paediatrics", 4); depttMap.put("Orthopaedics", 5); depttMap.put("Ophthalmology", 6); depttMap.put("ENT", 7); depttMap.put("Dental", 8); depttMap.put("Casualty", 9); int depttSheetCreateFlag = 0; System.out.println("In createDepartmentwiseSheets"); Iterator<Row> rowIterator = sheetAllOld.rowIterator(); //Store the first row to be printed as it is. ArrayList<String> heading = new ArrayList<>(); Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) heading.add(cellIterator.next().getStringCellValue()); int rowNums[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1 }; while (rowIterator.hasNext()) { row = rowIterator.next();//w w w .j a v a2s. c o m XSSFSheet sheetToWrite = null; System.out.println("Row: " + row.getRowNum()); Cell cell = row.getCell(0); if ((depttSheetCreateFlag & 1 << (depttMap.get(cell.getStringCellValue()))) == 0) { //that means this deptt came in this sheet for the first time in this row. XSSFSheet sheet = sheets[depttMap.get(cell.getStringCellValue()) - 1] = workbookOld .createSheet(cell.getStringCellValue()); //create heading row in this sheet Row headingRow = sheet.createRow(0); for (int i = 0; i < heading.size(); i++) { String cellString = heading.get(i); Cell headingCell = headingRow.createCell(i); headingCell.setCellValue(cellString);//sets cell type to string too } //mark this deptt. as seen depttSheetCreateFlag |= (1 << (depttMap.get(cell.getStringCellValue()))); } int sheetNum = depttMap.get(cell.getStringCellValue()) - 1; sheetToWrite = sheets[sheetNum]; assert (sheetToWrite != null); //write row to sheetToWrite Row rowNew = sheetToWrite.createRow(rowNums[sheetNum]++); cellIterator = row.cellIterator(); int cellNum = 0; while (cellIterator.hasNext()) { cell = cellIterator.next(); //write cell Cell cellNew = rowNew.createCell(cellNum++); String cellValue; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); cellNew.setCellValue(cellValue); } } }
From source file:writeintoexcel.ReadExcelFileExample.java
private static List getStudentsListFromExcel() { List studentList = new ArrayList(); FileInputStream fis = null;/*ww w .j a va2 s . co m*/ try { fis = new FileInputStream(FILE_PATH); // Using XSSF for xlsx format, for xls use HSSF Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); //looping over each workbook sheet for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); //iterating over each row while (rowIterator.hasNext()) { Student student = new Student(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); //Iterating over each cell (column wise) in a particular row. while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); //The Cell Containing String will is name. if (Cell.CELL_TYPE_STRING == cell.getCellType()) { student.setName(cell.getStringCellValue()); //The Cell Containing numeric value will contain marks } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { //Cell with index 1 contains marks in Maths if (cell.getColumnIndex() == 1) { student.setMaths(String.valueOf(cell.getNumericCellValue())); } //Cell with index 2 contains marks in Science else if (cell.getColumnIndex() == 2) { student.setScience(String.valueOf(cell.getNumericCellValue())); } //Cell with index 3 contains marks in English else if (cell.getColumnIndex() == 3) { student.setEnglish(String.valueOf(cell.getNumericCellValue())); } } } //end iterating a row, add all the elements of a row in list studentList.add(student); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return studentList; }