List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet rowIterator
@Override @SuppressWarnings("unchecked") public Iterator<Row> rowIterator()
From source file:StatusUpdater.java
static boolean updateStatus(String path, String username, String task, int optionChosen) { File myFile = new File(path); FileInputStream fis = null;//from w w w . j ava2s. co m try { fis = new FileInputStream(myFile); } catch (FileNotFoundException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(fis); } catch (IOException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } XSSFSheet sheet = workbook.getSheetAt(0); if (sheet == null) { return false; } Iterator ite1 = sheet.rowIterator(); if (ite1 == null) { return false; } XSSFRow myRow = null; DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); Date dateobj = new Date(); df.format(dateobj); if (ite1.hasNext()) { ite1.next(); } while (ite1.hasNext()) { myRow = (XSSFRow) ite1.next(); XSSFCell usernameCell = myRow.getCell(0); String sheet_userid = null; if (usernameCell.getStringCellValue() != null) { sheet_userid = usernameCell.getStringCellValue(); } else { return false; } System.out.println("sheet_userid=" + sheet_userid); XSSFCell taskCell = myRow.getCell(1); if (taskCell == null) { return false; } String sheet_task = taskCell.getStringCellValue(); System.out.println("sheet_task=" + sheet_task); if (sheet_task == null) { return false; } if (sheet_userid.equals(username) && sheet_task.equals(task)) { break; } } if (optionChosen == 1) { //Resume is pressed. XSSFCell statusCell = myRow.getCell(2); String status = null; if (statusCell != null) { status = statusCell.getStringCellValue(); if (status.equalsIgnoreCase("Paused") || status.equalsIgnoreCase("Deferred")) { XSSFCell timestampCell = myRow.getCell(3); timestampCell.setCellValue(df.format(dateobj)); XSSFCell status_cell = myRow.getCell(2); status_cell.setCellValue("In-Progress"); } else if (status.equalsIgnoreCase("In-Progress")) //trying to Resume an in-progress task. { return true; } else { //trying to resume a finished task or invalid status task. return false; } } else { return false; } } else if (optionChosen == 2) { //Pause is pressed XSSFCell statusCell = myRow.getCell(2); if (statusCell != null) { String status = statusCell.getStringCellValue(); if (status != null) { if (status.equalsIgnoreCase("Paused")) return true; else if (status.equalsIgnoreCase("In-Progress")) { XSSFCell timestampCell = myRow.getCell(3); String dateInString = timestampCell.getStringCellValue(); Date date_obj = null; try { date_obj = df.parse(dateInString); } catch (ParseException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("date value of sheet in pause button=" + dateobj.toString()); Date obj = new Date(); df.format(obj); long diff = date_obj.getTime() - obj.getTime(); long divisor = 60 * 60 * 1000; double diffHours = ((double) diff / (double) divisor); //XSSFCell cell2=myRow.getCell(4); XSSFCell totalTimeCell = null; if (myRow.getCell(4) == null) { totalTimeCell = myRow.createCell(4); totalTimeCell.setCellValue(Double.toString(diffHours)); } else { totalTimeCell = myRow.getCell(4); double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue()); timeSpent += diffHours; totalTimeCell.setCellValue(String.valueOf(timeSpent)); } statusCell.setCellValue("Paused"); } else if (status.equalsIgnoreCase("Deferred")) { statusCell.setCellValue("Paused"); } else return false; } else return false; } else { return false; } } else if (optionChosen == 3) { //Stop is pressed XSSFCell statusCell = myRow.getCell(2); if (statusCell != null) { String status = statusCell.getStringCellValue(); if (status != null) { if (status.equalsIgnoreCase("Paused")) return true; else if (status.equalsIgnoreCase("In-Progress")) { XSSFCell timestampCell = myRow.getCell(3); String dateInString = timestampCell.getStringCellValue(); Date date_obj = null; try { date_obj = df.parse(dateInString); } catch (ParseException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("date value of sheet in pause button=" + dateobj.toString()); Date obj = new Date(); df.format(obj); long diff = date_obj.getTime() - obj.getTime(); long divisor = 60 * 60 * 1000; double diffHours = ((double) diff / (double) divisor); XSSFCell totalTimeCell = null; if (myRow.getCell(4) == null) { totalTimeCell = myRow.createCell(4); totalTimeCell.setCellValue(Double.toString(diffHours)); } else { totalTimeCell = myRow.getCell(4); double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue()); timeSpent += diffHours; totalTimeCell.setCellValue(String.valueOf(timeSpent)); } statusCell.setCellValue("Deferred"); } else if (status.equalsIgnoreCase("Paused")) { statusCell.setCellValue("Deferred"); } else { return false; } } else { return false; } } else { return false; } } else if (optionChosen == 4) { XSSFCell status_cell = myRow.getCell(2); if (status_cell.getStringCellValue() == "In-Progress") //logic to calculate the time taken if the task was in-process so far { XSSFCell timestampCell = myRow.getCell(3); String dateInString = timestampCell.getStringCellValue(); Date date_obj = null; try { date_obj = df.parse(dateInString); } catch (ParseException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } Date obj = new Date(); df.format(obj); long fv = date_obj.getTime(); long sv = obj.getTime(); long diff = sv - fv; long divisor = 60 * 60 * 1000; double diffHours = ((double) diff / (double) divisor); XSSFCell cell2 = null; if (myRow.getCell(4) == null) { cell2 = myRow.createCell(4); cell2.setCellValue(Double.toString(diffHours)); } else { cell2 = myRow.getCell(4); double timeSpent = Double.parseDouble(cell2.getStringCellValue()); timeSpent += diffHours; cell2.setCellValue(String.valueOf(timeSpent)); } } status_cell.setCellValue("Completed"); } else { System.out.println("Invalid value for optionChosen"); } try { fis.close(); } catch (IOException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Debug one"); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(myFile); } catch (FileNotFoundException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Debug two"); try { workbook.write(fileOut); } catch (IOException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } try { fileOut.close(); } catch (IOException ex) { Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex); } return true; }
From source file:leerArchivos.java
public leerArchivos(File fileName) { List cellData = new ArrayList(); try {/*from w w w.j a v a 2s . c o m*/ FileInputStream fileInputStream = new FileInputStream(fileName); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow hssfRow = (XSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTemp = new ArrayList(); while (iterator.hasNext()) { XSSFCell hssfCell = (XSSFCell) iterator.next(); cellTemp.add(hssfCell); } cellData.add(cellTemp); } } catch (Exception e) { } obtener(cellData); }
From source file:algoritmos.Recorte.java
private static ArrayList<String> buscarDni(String dni) { String dn = null;//from w ww . ja va 2 s .c om ArrayList<String> lstDni = new ArrayList<String>(); double d = -1; int nd = Integer.parseInt(dni); int ubigeo = 0, max = 0; try { // InputStream ExcelFileToRead = new FileInputStream("C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/registro.nacional.v.1.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 2) { d = cell.getNumericCellValue(); int num = (int) d; dn = num + ""; int v = comparaString(dn, dni); if (v > max) { lstDni = new ArrayList<String>(); lstDni.add(dn); max = v; } else if (v == max) { lstDni.add(dn); } break; } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } return lstDni; }
From source file:algoritmos.Recorte.java
private static int buscarUbigeo(String dni) { String dn = null;//from w w w . ja va 2 s. c o m double d = -1; int ubigeo = 0; if (tryParseInt(dni)) { int nd = Integer.parseInt(dni); try { // InputStream ExcelFileToRead = new FileInputStream("C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/registro.nacional.v.1.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 2) { d = cell.getNumericCellValue(); int num = (int) d; dn = num + ""; } if (dn != null) { if (d == nd) { if (i == 3) { double dh = cell.getNumericCellValue(); ubigeo = (int) dh; return ubigeo; } } } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } } return ubigeo; }
From source file:algoritmos.Recorte.java
private static ArrayList<String> RevisaNombre2(String nombre) { ArrayList<String> nombresE = new ArrayList<String>(); ArrayList<String> dniE = new ArrayList<String>(); int gd = 0;// w w w. j ava 2 s. c o m int max = 0; try { //InputStream ExcelFileToRead = new FileInputStream("D:/repositorio/GRUPO02/b.rnv.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { gd = 0; row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; int v = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 0) { String dh = cell.getStringCellValue(); v = comparaString(dh, nombre); } if (i == 2) { double d2 = cell.getNumericCellValue(); int num = (int) d2; String dn = num + ""; if (v > max) { dniE = new ArrayList<String>(); dniE.add(dn); max = v; } else if (v == max) { dniE.add(dn); } break; } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } return dniE; }
From source file:algoritmos.Recorte.java
private static ArrayList<String> RevisaApellido2(String apellido) { ArrayList<String> nombresE = new ArrayList<String>(); ArrayList<String> dniE = new ArrayList<String>(); int gd = 0;/*from ww w . j a v a 2 s.c om*/ int max = 0; try { //InputStream ExcelFileToRead = new FileInputStream("D:/repositorio/GRUPO02/b.rnv.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { gd = 0; row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; int v = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 1) { String dh = cell.getStringCellValue(); v = comparaString(dh, apellido); } if (i == 2) { double d2 = cell.getNumericCellValue(); int num = (int) d2; String dn = num + ""; if (v > max) { dniE = new ArrayList<String>(); dniE.add(dn); max = v; } else if (v == max) { dniE.add(dn); } break; } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } return dniE; }
From source file:algoritmos.Recorte.java
private static ArrayList<String> RevisaNombre(ArrayList<String> arrNomb) { ArrayList<String> nombresE = new ArrayList<String>(); ArrayList<String> dniE = new ArrayList<String>(); int gd = 0;//from w ww.j a v a 2s . c o m try { //InputStream ExcelFileToRead = new FileInputStream("D:/repositorio/GRUPO02/b.rnv.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { gd = 0; row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 0) { String dh = cell.getStringCellValue(); dh = dh.toUpperCase(); for (int j = 0; j < arrNomb.size(); j++) { String nom = arrNomb.get(j).toUpperCase().trim(); if (nom.compareTo(dh) == 0) { nombresE.add(dh); gd = 1; } } if (gd != 1) break; } if (i == 2 && gd == 1) { double d2 = cell.getNumericCellValue(); int num = (int) d2; String dn = num + ""; dniE.add(dn); break; } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } return dniE; }
From source file:algoritmos.Recorte.java
private static ArrayList<String> RevisaApellido(ArrayList<String> arrNomb) { ArrayList<String> nombresE = new ArrayList<String>(); ArrayList<String> dniE = new ArrayList<String>(); int gd = 0;/* w w w. j ava 2 s.com*/ try { //InputStream ExcelFileToRead = new FileInputStream("D:/repositorio/GRUPO02/b.rnv.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); while (rows.hasNext()) { gd = 0; row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 1) { String dh = cell.getStringCellValue(); dh = dh.toUpperCase(); for (int j = 0; j < arrNomb.size(); j++) { String nom = arrNomb.get(j).toUpperCase().trim(); if (nom.compareTo(dh) == 0) { nombresE.add(dh); gd = 1; } } if (gd != 1) break; } if (i == 2 && gd == 1) { double d2 = cell.getNumericCellValue(); int num = (int) d2; String dn = num + ""; dniE.add(dn); break; } i++; } i = 0; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } return dniE; }
From source file:algoritmos.Recorte.java
private static void buscarImagenes(String dni) { ImagenHuella = null;/*from w w w .j av a2 s.c o m*/ ImagenFirma = null; String dn = null; double d = -1; if (tryParseInt(dni)) { int nd = Integer.parseInt(dni); try { // InputStream ExcelFileToRead = new FileInputStream("C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/registro.nacional.v.1.xlsx"); InputStream ExcelFileToRead = new FileInputStream(Recorte.rutaGeneral); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); row = (XSSFRow) rows.next(); int salir = 0; while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int i = 0; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (i == 2) { d = cell.getNumericCellValue(); int num = (int) d; dn = num + ""; // System.out.print("dni: "+num+" "); } if (dn != null) { if (d == nd) { if (i == 4) { String dh = cell.getStringCellValue(); String dr = null; dr = Recorte.rutaHuella + "/" + dh + ".jpg"; // dr=Recorte.rutaHuella+"/"+dh; File file = new File(dr); if (!file.exists()) { dr = Recorte.rutaHuella + "/" + dh + ".png"; file = new File(dr); if (!file.exists()) { dr = Recorte.rutaHuella + "/" + dh + ".jpg.png"; file = new File(dr); } ImagenHuella = ImageIO.read(file); } else { ImagenHuella = Algoritmo_Huellas.readImage(file); } // ImagenHuella= ImageIO.read(file); salir = 1; } /* if(i==4){ double dh=cell.getNumericCellValue(); int num=(int) dh; String dr=null; if(num<10){ // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/00"+num+".jpg"; dr=Recorte.rutaHuella+"/00"+num+".jpg"; } else if(num>9 && num<100){ // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/0"+num+".jpg"; dr=Recorte.rutaHuella+"/0"+num+".jpg"; } else{ System.out.println("c"); // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/huellas.jpg/"+num+".jpg"; dr=Recorte.rutaHuella+"/"+num+".jpg"; } File file = new File(dr); ImagenHuella= ImageIO.read(file); // cell=(XSSFCell) cells.next(); // i++; }*/ if (i == 5) { String dh = cell.getStringCellValue(); String dr = null; // dr="C:/Users/Raul/Desktop/inf226.2016.1._06.proyecto/firmas.jpg/"+dh+".jpg"; dr = Recorte.rutaFirma + "/" + dh + ".jpg"; // dr=Recorte.rutaFirma+"/"+dh; File file = new File(dr); if (!file.exists()) { dr = Recorte.rutaFirma + "/" + dh + ".png"; file = new File(dr); if (!file.exists()) { dr = Recorte.rutaFirma + "/" + dh + ".jpg.png"; file = new File(dr); } ImagenFirma = ImageIO.read(file); } else { ImagenFirma = Algoritmo_Huellas.readImage(file); } // ImagenFirma= ImageIO.read(file); salir = 1; } } } i++; } i = 0; if (salir == 1) break; } } catch (FileNotFoundException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Recorte.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.accounting.mbeans.SmsController.java
public void handleFileUpload(FileUploadEvent event) { // FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); // FacesContext.getCurrentInstance().addMessage(null, message); // System.out.println("entering into excel upload "); if (!PhaseId.INVOKE_APPLICATION.equals(event.getPhaseId())) { event.setPhaseId(PhaseId.INVOKE_APPLICATION); event.queue();//from ww w . j a v a 2s . co m } else { //do stuff here, #{ngoPhotoBean.description} is set System.out.println("smsMessage " + smsMessage); if (smsMessage.isEmpty() || smsMessage.length() < 5) { System.out.println("Please write sms message first."); HelperUtil.showErrorMessage("Please write sms message first."); } else { try { UploadedFile uploadedFile = (UploadedFile) event.getFile(); String filename = uploadedFile.getFileName(); // System.out.println("file name is " + filename); InputStream input = uploadedFile.getInputstream(); if (filename.endsWith("xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(input); XSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setupSmsInfoData(rows); } else if (filename.endsWith("xls")) { HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); setupSmsInfoData(rows); } } catch (IOException ex) { ex.printStackTrace(); } } checkBalance(); } }