Example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

Introduction

In this page you can find the example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem.

Prototype


public POIFSFileSystem(InputStream stream) throws IOException

Source Link

Document

Create a POIFSFileSystem from an InputStream.

Usage

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;/*  w  w w  . j a  v  a 2  s.  co m*/
                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;/*from w w  w. jav  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: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  www .j  a va  2s  . c o  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:Contabilidad.Provision.java

public void generaExcel(String noPoliza, String noMes, String ruta) {
    DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00");
    formatoPorcentaje.setMinimumFractionDigits(2);
    File archivoXLS = new File(ruta + "/" + noPoliza + "-" + noMes + ".xls");
    File plantilla = new File("imagenes/Diario.xls");
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {//from   w w  w .  j av  a 2 s.c  o m
        Query query1 = session.createQuery("SELECT DISTINCT reg FROM Asiento reg "
                + "LEFT JOIN reg.excelProvision ex " + "where ex.poliza = " + noPoliza + " AND MONTH(ex.fecha)="
                + noMes + " and ex.tipo='Dr' ORDER BY reg.idAsiento ASC");
        Asiento[] Asientos = (Asiento[]) query1.list().toArray(new Asiento[0]);

        Path FROM = Paths.get("imagenes/Diario.xls");
        Path TO = Paths.get(ruta + "/" + noPoliza + "-" + noMes + ".xls");
        //sobreescribir el fichero de destino, si existe, y copiar los atributos, incluyendo los permisos rwx
        CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING,
                StandardCopyOption.COPY_ATTRIBUTES };
        Files.copy(FROM, TO, options);

        FileInputStream miPlantilla = new FileInputStream(archivoXLS);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla);
        Workbook libro = new HSSFWorkbook(fsFileSystem);
        //Cargamos las cabeceras
        libro.getSheet("Hoja1").createRow(2);
        libro.getSheet("Hoja1").getRow(2).createCell(0).setCellValue("Dr");
        libro.getSheet("Hoja1").getRow(2).createCell(1).setCellValue(Integer.parseInt(noPoliza));
        if (Asientos.length > 0) {
            libro.getSheet("Hoja1").getRow(2).createCell(2)
                    .setCellValue(Asientos[0].getExcelProvision().getConcepto());
            Calendar calendario = Calendar.getInstance();
            calendario.setTime(Asientos[0].getExcelProvision().getFecha());
            libro.getSheet("Hoja1").getRow(2).createCell(3).setCellValue(calendario.get(Calendar.DAY_OF_MONTH));
        }
        double total = 0.0D;
        int renglon = 3;
        for (int ren = 0; ren < Asientos.length; ren++) {
            Registro[] registros = (Registro[]) session.createCriteria(Registro.class)
                    .createAlias("asiento", "asc")
                    .add(Restrictions.eq("asc.idAsiento", Asientos[ren].getIdAsiento()))
                    .add(Restrictions.eq("tipoAsiento", "Dr")).addOrder(Order.desc("tipo"))
                    .addOrder(Order.asc("idRegistro")).list().toArray(new Registro[0]);
            for (int r = 0; r < registros.length; r++) {
                libro.getSheet("Hoja1").createRow(renglon);
                libro.getSheet("Hoja1").getRow(renglon).createCell(1)
                        .setCellValue(registros[r].getCuentas().getIdCuentas());
                libro.getSheet("Hoja1").getRow(renglon).createCell(2)
                        .setCellValue(Integer.parseInt(registros[r].getDepto()));
                libro.getSheet("Hoja1").getRow(renglon).createCell(3).setCellValue(registros[r].getConcepto());
                libro.getSheet("Hoja1").getRow(renglon).createCell(4).setCellValue(registros[r].getCambio());
                if (registros[r].getTipo().compareTo("d") == 0)
                    libro.getSheet("Hoja1").getRow(renglon).createCell(5)
                            .setCellValue(registros[r].getCantidad());
                else
                    libro.getSheet("Hoja1").getRow(renglon).createCell(6)
                            .setCellValue(registros[r].getCantidad());
                renglon++;
            }
        }
        int celda = renglon;
        libro.getSheet("Hoja1").createRow(renglon);
        libro.getSheet("Hoja1").getRow(renglon).createCell(1);//
        libro.getSheet("Hoja1").getRow(renglon).getCell(1).setCellValue("FIN_PARTIDAS");

        FileOutputStream archivo = new FileOutputStream(archivoXLS);
        libro.write(archivo);
        archivo.close();
        //Desktop.getDesktop().open(archivoXLS);
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte");
    }
    if (session != null)
        if (session.isOpen())
            session.close();
}

From source file:controller.FileUploadView.java

public void uploadEstudiantes(FileUploadEvent event) throws SQLException, ClassNotFoundException {

    //// www  .j a  va  2  s. co  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 {

    ///*www  .  j a  v  a  2s  .  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        

    // 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.importarController.java

/**
 * funo para ler um arquivo em Excel//from  w  w w . jav a2 s . c o m
 *
 * @param fileName
 * @param myInput
 * @return
 */
@SuppressWarnings("unchecked")
public List ReadFile(String fileName, FileInputStream myInput) {
    List cellVectorHolder = new ArrayList();
    try {
        //FileInputStream myInput = new FileInputStream(fileName);
        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        Iterator rowIter = mySheet.rowIterator();
        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
            List cellStoreVector = new ArrayList();
            while (cellIter.hasNext()) {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                cellStoreVector.add(myCell);
            }
            cellVectorHolder.add(cellStoreVector);
        }
    } catch (Exception e) {
    }
    return cellVectorHolder;
}

From source file:Creator.TaskManagerPanel.java

/**
 * Reads a file and returns a list of strings which contain all the variable
 * names//  w  ww .j  a  va2  s .c o  m
 *
 * @param filename
 */
public void readXFile(String filename) {

    try {

        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        int idCol = -1, idName = -1;
        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 1; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols) {
                    cols = tmp;
                }
            }

            if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) {
                for (int c = 1; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_id")) {
                        idCol = c;
                        break;
                    }
                }
            } else {
                idCol = 0;
            }

            if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) {
                for (int c = 0; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_name")) {
                        idName = c;
                        break;
                    }
                }
            } else {
                idName = 1;
            }

            if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) {
                for (int c = 0; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_station_id")) {
                        stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue();
                        break;
                    }
                }
            } else {
                stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue();
            }

        }

        if (idName == -1 || idCol == -1) {
            System.out.println("Could not locate io_name or io_id in excel header");
            return;
        }
        if (stationID == -1) {
            System.out.println("Couldnt locate station id");
            return;
        }

        importedIOVariables = new TreeMap<>();
        int io_id;
        String io_name;

        for (int r = 1; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {

                cell = row.getCell(idCol);
                if (cell != null) {
                    io_id = (int) cell.getNumericCellValue();

                    cell = row.getCell(idName);
                    if (cell != null) {
                        io_name = cell.toString().replace("\"", "");
                        // Read both name and id
                        importedIOVariables.put(io_name, io_id);
                    }
                }

            }
        }
        fs.close();
        mf.loadImportedIos(importedIOVariables, 2, stationID);
    } catch (Exception e) {
        System.out.println("Error reading excel file " + e.getMessage());
    }
}

From source file:Creator.WidgetPanel.java

/**
 * Reads a file and returns a list of strings which contain all the variable
 * names/*from   w  w  w  .j  a v  a  2  s. com*/
 *
 * @param filename
 */
public void readXFile(String filename) {

    try {

        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        int idCol = -1, idName = -1;
        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 1; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols) {
                    cols = tmp;
                }
            }

            if (!sheet.getRow(i).getCell(0).toString().equals("io_id")) {
                for (int c = 1; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_id")) {
                        idCol = c;
                        break;
                    }
                }
            } else {
                idCol = 0;
            }
            if (!sheet.getRow(i).getCell(1).toString().equals("io_name")) {
                for (int c = 1; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_name")) {
                        idName = c;
                        break;
                    }
                }
            } else {
                idName = 1;
            }

            if (!sheet.getRow(i).getCell(2).toString().equals("io_station_id")) {
                for (int c = 0; c < cols; c++) {
                    if (sheet.getRow(i).getCell(c).equals("io_station_id")) {
                        stationID = (int) sheet.getRow(1).getCell(c).getNumericCellValue();
                        break;
                    }
                }
            } else {
                stationID = (int) sheet.getRow(1).getCell(2).getNumericCellValue();
            }

        }

        if (idName == -1 || idCol == -1) {
            System.out.println("Could not locate io_name or io_id in excel header");
            return;
        }
        if (stationID == -1) {
            System.out.println("Couldnt locate station id");
            return;
        }

        importedIOVariables = new TreeMap<>();
        int io_id;
        String io_name;

        for (int r = 1; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {

                cell = row.getCell(idCol);
                if (cell != null) {
                    io_id = (int) cell.getNumericCellValue();

                    cell = row.getCell(idName);
                    if (cell != null) {
                        io_name = cell.toString().replace("\"", "");
                        // Read both name and id
                        importedIOVariables.put(io_name, io_id);
                    }
                }

            }
        }

        fs.close();

        mf.loadImportedIos(importedIOVariables, 1, stationID);

    } catch (Exception e) {
        System.out.println("Error reading excel file " + e.getMessage());
    }

}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//from   w ww . ja  va2  s.  c o m
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    final int numberOfSheets = wb.getNumberOfSheets();
    final List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();

        final int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows + 1][columns];
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    wb.close();
    return sheetList;
}