List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java
@Override public List<PushButton> readPushButtonWb(Workbook wb, License license) { List<PushButton> list = new ArrayList<PushButton>(); try {//w w w. j a v a2s .c o m if (wb == null) { throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } Sheet sheet = wb.getSheetAt(0); list = readPushButtonWbs(sheet); } catch (POIXMLException p) { p.printStackTrace(); throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } return list; }
From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java
@Override public List<ControlDeviceLil> readControlDeviceLilWb(Workbook wb, License license) { List<ControlDeviceLil> list = new ArrayList<ControlDeviceLil>(); try {// w ww . j a v a2s.c o m if (wb == null) { throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } Sheet sheet = wb.getSheetAt(0); list = readControlDeviceLilWbs(sheet); } catch (POIXMLException p) { p.printStackTrace(); throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } return list; }
From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java
@Override public List<ControlDeviceFan> readControlDeviceFanWb(Workbook wb, License license) { List<ControlDeviceFan> list = new ArrayList<ControlDeviceFan>(); try {/*from w w w. j ava 2s .c o m*/ if (wb == null) { throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } Sheet sheet = wb.getSheetAt(0); list = readControlDeviceFanWbs(sheet); } catch (POIXMLException p) { p.printStackTrace(); throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !"); } return list; }
From source file:Compras.ComparaCotizacion.java
void importaDatos(Workbook wb, int col) { Sheet hoja = wb.getSheetAt(0); Iterator rowIterator = hoja.rowIterator(); List renglones = new ArrayList(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); List celdas = new ArrayList(); Iterator iterator = hssfRow.cellIterator(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); celdas.add(hssfCell);//from w w w . j a va 2 s . c o m } renglones.add(celdas); } for (int r = 8; r < renglones.size(); r++) { List aux = (List) renglones.get(r); HSSFCell auxCell = (HSSFCell) aux.get(0); HSSFCell auxCell1 = (HSSFCell) aux.get(1); int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue()); if (res != -1) { for (int c = 0; c < aux.size(); c++) { HSSFCell valor = (HSSFCell) aux.get(c); switch (valor.getColumnIndex()) { case 4: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: t_datos.setValueAt("", res, col); break; default: t_datos.setValueAt("", res, col); break; } break; case 7: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 1); break; default: t_datos.setValueAt("", res, col + 1); break; } if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) { model.setCeldaEditable(res, col + 3, true); model.setCeldaEditable(res, col + 4, true); } break; case 10: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 2); break; default: t_datos.setValueAt("", res, col + 2); break; } break; } t_datos.setValueAt(false, res, col + 3); t_datos.setValueAt(false, res, col + 4); } } } }
From source file:Contabilidad.FacturarOrden.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed // TODO add your handling code here: aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); int resp = aviso.showOpenDialog(null); if (resp == aviso.APPROVE_OPTION) { File archivoXLS = aviso.getSelectedFile(); if (archivoXLS.exists() == true) { Session session = HibernateUtil.getSessionFactory().openSession(); try { FileInputStream archivo = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo); Workbook libro1 = new HSSFWorkbook(fsFileSystem); Sheet datos = libro1.getSheetAt(0); Iterator rowIterator; int r = 0; //**********cargamos datos************************ //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel(); t_datos.setModel(ModeloTablaReporte(0, columnas)); rowIterator = datos.rowIterator(); r = 0;//from w ww. java2 s . com while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { double cant = renglon.getCell(0).getNumericCellValue(); double cu = renglon.getCell(3).getNumericCellValue(); double descuento = renglon.getCell(4).getNumericCellValue(); double suma = cant * cu; double desc = descuento / 100; double total = suma - (suma * desc); Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(), renglon.getCell(2).getStringCellValue(), "", cu, descuento, total }; model.addRow(nuevo); } else r = 1; } formatoTabla(); this.sumaTotales(); t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1); t_datos.setColumnSelectionInterval(0, 0); t_datos.requestFocus(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo"); } if (session != null) if (session.isOpen() == true) session.close(); } } }
From source file:Contabilidad.FacturarOrdenLogis.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed // TODO add your handling code here: aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); int resp = aviso.showOpenDialog(null); if (resp == aviso.APPROVE_OPTION) { File archivoXLS = aviso.getSelectedFile(); if (archivoXLS.exists() == true) { Session session = HibernateUtil.getSessionFactory().openSession(); try { FileInputStream archivo = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo); Workbook libro1 = new HSSFWorkbook(fsFileSystem); Sheet datos = libro1.getSheetAt(0); Iterator rowIterator; int r = 0; //**********cargamos datos************************ //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel(); t_datos.setModel(ModeloTablaReporte(0, columnas)); rowIterator = datos.rowIterator(); r = 0;// w w w .j a v a 2 s. c om while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { System.out.println(renglon.getCell(0).getNumericCellValue()); double cant = renglon.getCell(0).getNumericCellValue(); double cu = renglon.getCell(3).getNumericCellValue(); double descuento = renglon.getCell(4).getNumericCellValue(); double suma = cant * cu; double desc = descuento / 100; double total = suma - (suma * desc); Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(), renglon.getCell(2).getStringCellValue(), "", cu, descuento, total }; model.addRow(nuevo); } else r = 1; } formatoTabla(); this.sumaTotales(); t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1); t_datos.setColumnSelectionInterval(0, 0); t_datos.requestFocus(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo"); } if (session != null) if (session.isOpen() == true) session.close(); } } }
From source file:Contabilidad.NuevaFactura.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed // TODO add your handling code here: aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); int resp = aviso.showOpenDialog(null); if (resp == aviso.APPROVE_OPTION) { File archivoXLS = aviso.getSelectedFile(); if (archivoXLS.exists() == true) { Session session = HibernateUtil.getSessionFactory().openSession(); try { FileInputStream archivo = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo); Workbook libro1 = new HSSFWorkbook(fsFileSystem); Sheet datos = libro1.getSheetAt(0); Iterator rowIterator; int r = 0; //**********cargamos datos************************ //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel(); t_datos.setModel(ModeloTablaReporte(0, columnas)); rowIterator = datos.rowIterator(); r = 0;/*from ww w .j a v a2 s. co m*/ while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { System.out.println(renglon.getCell(0).getNumericCellValue()); double cant = renglon.getCell(0).getNumericCellValue(); double cu = renglon.getCell(3).getNumericCellValue(); double descuento = renglon.getCell(4).getNumericCellValue(); double suma = cant * cu; double desc = descuento / 100; double total = suma - (suma * desc); Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(), renglon.getCell(2).getStringCellValue(), cu, descuento, total }; model.addRow(nuevo); } else r = 1; } formatoTabla(); this.sumaTotales(); t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1); t_datos.setColumnSelectionInterval(0, 0); t_datos.requestFocus(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo"); } if (session != null) if (session.isOpen() == true) session.close(); } } }
From source file:controller.FileUploadView.java
public void uploadEstudiantes(FileUploadEvent event) throws SQLException, ClassNotFoundException { ////from w ww . ja v a2s . c o m FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String APELLIDO_PATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); APELLIDO_PATERNO = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); APELLIDO_PATERNO = row.getCell(0).getStringCellValue(); break; } String APELLIDO_MATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); APELLIDO_MATERNO = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); APELLIDO_MATERNO = row.getCell(1).getStringCellValue(); break; } String NOMBRES = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); NOMBRES = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); NOMBRES = row.getCell(2).getStringCellValue(); break; } String CEDULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); CEDULA = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); CEDULA = row.getCell(3).getStringCellValue(); break; } String CARRERA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CARRERA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CARRERA = row.getCell(4).getStringCellValue(); break; } String CURSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); CURSO = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); CURSO = row.getCell(5).getStringCellValue(); break; } String CODIGOMATRICULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); CODIGOMATRICULA = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); CODIGOMATRICULA = row.getCell(6).getStringCellValue(); break; } String DIRECCION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); DIRECCION = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); DIRECCION = row.getCell(7).getStringCellValue(); break; } String TELEFONO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(8)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(8).getNumericCellValue() + " "); TELEFONO = Integer.toString((int) row.getCell(8).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(8).getStringCellValue() + " "); TELEFONO = row.getCell(8).getStringCellValue(); break; } String CELULAR = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(9)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(9).getNumericCellValue() + " "); CELULAR = Integer.toString((int) row.getCell(9).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(9).getStringCellValue() + " "); CELULAR = row.getCell(9).getStringCellValue(); break; } String CORREO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(10)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(10).getNumericCellValue() + " "); CORREO = Integer.toString((int) row.getCell(10).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(10).getStringCellValue() + " "); CORREO = row.getCell(10).getStringCellValue(); break; } String CORREOUCE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(11)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(11).getNumericCellValue() + " "); CORREOUCE = Integer.toString((int) row.getCell(11).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(11).getStringCellValue() + " "); CORREOUCE = row.getCell(11).getStringCellValue(); break; } String CORREOCONTACTO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(12)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(12).getNumericCellValue() + " "); CORREOCONTACTO = Integer.toString((int) row.getCell(12).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(12).getStringCellValue() + " "); CORREOCONTACTO = row.getCell(12).getStringCellValue(); break; } String ESTADOCIVIL = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(13)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(13).getNumericCellValue() + " "); ESTADOCIVIL = Integer.toString((int) row.getCell(13).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(13).getStringCellValue() + " "); ESTADOCIVIL = row.getCell(13).getStringCellValue(); break; } String PAIS = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(14)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(14).getNumericCellValue() + " "); PAIS = Integer.toString((int) row.getCell(14).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(14).getStringCellValue() + " "); PAIS = row.getCell(14).getStringCellValue(); break; } String PROVINCIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(15)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(15).getNumericCellValue() + " "); PROVINCIA = Integer.toString((int) row.getCell(15).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(15).getStringCellValue() + " "); PROVINCIA = row.getCell(15).getStringCellValue(); break; } String CANTON = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(16)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(16).getNumericCellValue() + " "); CANTON = Integer.toString((int) row.getCell(16).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(16).getStringCellValue() + " "); CANTON = row.getCell(16).getStringCellValue(); break; } String SEXO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(17)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(17).getNumericCellValue() + " "); SEXO = Integer.toString((int) row.getCell(17).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(17).getStringCellValue() + " "); SEXO = row.getCell(17).getStringCellValue(); break; } String NACIONALIDAD = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(18)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(18).getNumericCellValue() + " "); NACIONALIDAD = Integer.toString((int) row.getCell(18).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(18).getStringCellValue() + " "); NACIONALIDAD = row.getCell(18).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String sql = "INSERT INTO ESTUDIANTE (APELLIDO_PATERNO,APELLIDO_MATERNO,NOMBRES,CEDULA,CARRERA,CURSO,CODIGO_MATRICULA,DIRECCION,TELEFONO,CELULAR,CORREO,CORREO_UCE,CORREO_CONTACTO,ESTADO_CIVIL,PAIS,PROVINCIA,CANTON,SEXO,NACIONALIDAD) " + "VALUES('" + APELLIDO_PATERNO + "','" + APELLIDO_MATERNO + "','" + NOMBRES + "','" + CEDULA + "','" + CARRERA + "','" + CURSO + "','" + CODIGOMATRICULA + "','" + DIRECCION + "','" + TELEFONO + "','" + CELULAR + "','" + CORREO + "','" + CORREOUCE + "','" + CORREOCONTACTO + "','" + ESTADOCIVIL + "','" + PAIS + "','" + PROVINCIA + "','" + CANTON + "','" + SEXO + "','" + NACIONALIDAD + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }
From source file:controller.FileUploadView.java
public void uploadDocente(FileUploadEvent event) throws SQLException, ClassNotFoundException { ////from w ww. j a v a2 s .c om FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); //readExcelFile(destination+event.getFile().getFileName()); String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String NOMBRE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); NOMBRE = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); NOMBRE = row.getCell(0).getStringCellValue(); break; } String CI = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); CI = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); CI = row.getCell(1).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String EMAIL_INST = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); EMAIL_INST = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); EMAIL_INST = row.getCell(2).getStringCellValue(); break; } String EMAIL_PERSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); EMAIL_PERSO = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); EMAIL_PERSO = row.getCell(3).getStringCellValue(); break; } String CATEGORIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CATEGORIA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CATEGORIA = row.getCell(4).getStringCellValue(); break; } String DEDICACION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); DEDICACION = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); DEDICACION = row.getCell(5).getStringCellValue(); break; } String ESTADO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); ESTADO = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); ESTADO = row.getCell(6).getStringCellValue(); break; } String TIEMPO_HORA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); TIEMPO_HORA = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); TIEMPO_HORA = row.getCell(7).getStringCellValue(); break; } String sql = "INSERT INTO DOCENTE (NOMBRE,CI,EMAIL_INSTITUCIONAL,EMAIL_PERSONAL,CATEGORIA,DEDICACION,ESTADO,TIEMPOHORA) VALUES('" + NOMBRE + "','" + CI + "','" + EMAIL_INST + "','" + EMAIL_PERSO + "','" + CATEGORIA + "','" + DEDICACION + "','" + ESTADO + "','" + TIEMPO_HORA + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }
From source file:Controller.ThreadExcelImport.java
@Override public void run() { //******// w w w. j av a 2 s . co m // CRIA STREAM DAS PLANILHAS // ******************* // stream planilha 1 InputStream stream1 = null; try { stream1 = new FileInputStream(new File(srcFileP1)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .open(stream1); // stream planilha 2 InputStream stream2 = null; try { stream2 = new FileInputStream(new File(srcFileP2)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .open(stream2); //****** // VERIFICA OS CABECALHOS // ******************* // cabealhos da planilha 1 Sheet sheet1 = null; sheet1 = workbook1.getSheetAt(0); // Pega de acordo com o cabealho as opes for (Row r : sheet1) { if (r.getRowNum() > 0) break; for (Integer i = 0; i < headerP1.size(); i++) { for (Cell c : r) { if (c.getStringCellValue().toLowerCase() .equals(headerP1.get(i).getColumnName().toLowerCase())) { // Adiciona o numero da coluna ao header headerP1.get(i).setColumnNumber(c.getColumnIndex()); break; } } if (headerP1.get(i).getColumnNumber() == null) { // Alguma coluna do template est ausente JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase() + " do template no existe como cabealho na planilha 1"); System.exit(0); } } } // cabealhos da planilha 2 Sheet sheet2 = null; sheet2 = workbook2.getSheetAt(0); // Pega de acordo com o cabealho as opes for (Row r : sheet2) { if (r.getRowNum() > 0) break; for (Integer i = 0; i < headerP2.size(); i++) { for (Cell c : r) { if (c.getStringCellValue().toLowerCase() .equals(headerP2.get(i).getColumnName().toLowerCase())) { // Adiciona o numero da coluna ao header headerP2.get(i).setColumnNumber(c.getColumnIndex()); break; } } if (headerP2.get(i).getColumnNumber() == null) { // Alguma coluna do template est ausente JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase() + " do template no existe como cabealho na planilha 2"); System.exit(0); } } } //****** // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1 // ******************* stream2 = null; try { stream2 = new FileInputStream(new File(srcFileP2)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .open(stream2); sheet2 = null; sheet2 = workbook2.getSheetAt(0); for (Row rowP2 : sheet2) { if (rowP2.getRowNum() > 0) { InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName); // calcula o hash String hashChaveP2 = ""; for (String chaveP2 : colunaChave) { Integer columIndex = -1; for (Header he2 : headerP2) { if (he2.getColumnName().equals(chaveP2)) { columIndex = he2.getColumnNumber(); break; } } if (columIndex > -1) { Cell cell = null; cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK); // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 ); hashChaveP2 = DigestUtils .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2); } } for (Header he2 : headerP2) { Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK); objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase()); objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1)); //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() ); } if (hashChaveP2.equals("")) { JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1)) + " da planilha 2 tem as colunas chaves nula"); System.exit(0); } else listaP2.put(hashChaveP2, objInterfaceP2); } } // limpa da memoria a workbook2 try { if (workbook2 != null) workbook2.close(); } catch (IOException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } // limpa da memoria o stream com workbook2 if (stream2 != null) try { stream2.close(); } catch (IOException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } //****** // FAZ A VALIDAO // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING // ******************* // Executa o loop nas linhas da planilha stream1 = null; try { stream1 = new FileInputStream(new File(srcFileP1)); } catch (FileNotFoundException ex) { Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex); } workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .open(stream1); sheet1 = null; sheet1 = workbook1.getSheetAt(0); InterfaceMigracao objInterfaceP1 = null; for (Row rowP1 : sheet1) { // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1 String hashChaveP1 = ""; for (String chaveP1 : colunaChave) { Integer columIndex = -1; for (Header he1 : headerP1) { if (he1.getColumnName().equals(chaveP1)) { columIndex = he1.getColumnNumber(); break; } } if (columIndex > -1) { Cell cell = null; cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK); // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 ); hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1); } } objInterfaceP1 = Factory.getInstance(templateName); // objInterfaceP2 = Factory.getInstance(templateName); objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1)); Notify notify = new Notify(); if (hashChaveP1.equals("")) notify.setLocalizadoP1(false); else { notify.setLocalizadoP1(true); //seta o numero da linha no excel // Preenche o objeto de interface da planilha 1 com seus respectivos dados for (Header he1 : headerP1) { Cell cell = null; cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK); objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase()); } boolean p2Localizado = false; // Preenche o objeto de interface da planilha 2 com seus respectivos dados if (rowP1.getRowNum() > 0) { InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1); if (objInterfaceMigracaoP2 != null) { p2Localizado = true; notify.setEntidadeP2(objInterfaceMigracaoP2); } } notify.setLocalizadoP2(p2Localizado); } isRunning = true; objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1)); notify.setEntidadeP1(objInterfaceP1); notify.setTotalRow((sheet1.getLastRowNum() + 1)); notify.setRunning(isRunning); notify.setHeaderP1(headerP1); notify.setHeaderP2(headerP2); setChanged(); notifyObservers(notify); } isRunning = false; // Notifica os observadores de que a execuo terminou Notify notify = new Notify(); notify.setRunning(false); setChanged(); notifyObservers(notify); listaP2 = null; }