Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum.

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.yqboots.initializer.core.builder.excel.factory.DomainMetadataFactory.java

License:Apache License

/**
 * Gets the starting points of each domain.
 *
 * @param sheet the Excel sheet//from w w w.  j  a  va 2  s  .  c o  m
 * @return all starting points
 */
private static List<Integer> getStartingPoints(final Sheet sheet) {
    List<Integer> results = new ArrayList<>();
    for (Row row : sheet) {
        // ignore the header
        if (row.getRowNum() < 2) {
            continue;
        }

        Cell module = row.getCell(0);
        Cell domain = row.getCell(1);
        if (module != null && domain != null) {
            results.add(row.getRowNum());
        }
    }

    // ending flag
    results.add(sheet.getLastRowNum() + 1);

    return results;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?/* www  . j a v  a 2s.  c  om*/
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?//w  w w.  j  ava2  s . co  m
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

private static <T> void fill(Class<T> clazz, List<T> result, String fileName, InputStream is) {

    try {/*from  ww w.j  a  v  a 2s  .  c  o  m*/
        Workbook excel = null;
        if (fileName.indexOf(".xlsx") > 0) {
            excel = new XSSFWorkbook(is);// Excel2007
        } else if (fileName.indexOf(".xls") > 0) {
            excel = new HSSFWorkbook(is);// Excel2003

        } else {
            return;
        }
        FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = excel.getSheetAt(0);// ?0
        // ????1
        List<String> header = new ArrayList<String>();
        if (sheet.getLastRowNum() >= 0) {
            Row row = sheet.getRow(0);// ?

            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);// ??

                if (cell != null) {// ?cellStr

                    header.add(cell.getStringCellValue());
                }

            }

        }

        //
        if (sheet.getLastRowNum() > 1) {

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                Row row = sheet.getRow(i);// ?
                if (row == null) {// ??
                    continue;
                }
                Map<String, String> map = genRowMap(row, header, evaluator);
                T t = fill(map, clazz.newInstance());
                result.add(t);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } finally {// ?
        if (is != null) {
            try {
                is.close();

            } catch (IOException ex) {
                Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return;
}

From source file:controller.FileUploadView.java

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

    ////ww w .jav  a  2 s.  com
    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 w  w.  j  a v a 2  s  . 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.ThreadExcelImport.java

@Override
public void run() {

    //******// ww  w . j  a  v  a  2  s.com
    // 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;

}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Select the given sheet to be read from.
 * @param sheet sheet to be selected/* w  w w.jav  a  2  s  .c  om*/
 * @return sheet selected
 */
public Sheet selectSheet(Sheet sheet) {
    if (this.sheet != sheet) {
        this.sheet = sheet;
        firstRow = sheet.getFirstRowNum();
        rowNum = firstRow;
        lastRow = sheet.getLastRowNum();
        currentRow = null;
    }
    return this.sheet;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean processFile(Path input, boolean openFile) {
    boolean result = false;
    int endRow = 0;

    try {//w  ww.  ja  v  a2 s.  co m
        updateMessages(String.format("Inicializando el documento: %s", input.toString()));
        Path copy = createCopy(input);

        if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) {
            Workbook workbook = WorkbookFactory.create(copy.toFile());
            Sheet sheet = workbook.getSheetAt(0);
            Sheet newSheet = workbook.createSheet("Procesado");

            workbook.setSheetName(0, "Crudo");

            endRow = getLasRow(sheet);

            // seccion para la creacion de los encabezados
            updateMessages("Creando la cabecera de los datos");
            createHeaderData(newSheet, getCellStyleHeaderData(workbook));

            // seccion para los values USD
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH),
                    11, 35, 14);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT),
                    35, 49, 38);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD),
                    49, 54, 52);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT),
                    54, 59, 57);

            // seccion para los values units
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH),
                    59, 83, 63);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT),
                    83, 97, 87);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD),
                    97, 102, 101);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT),
                    102, 107, 106);
            //            
            //            // seccion para los values units standars
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH),
                    107, 131, 112);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT),
                    131, 145, 136);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD),
                    145, 150, 150);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT),
                    150, 155, 155);

            // fin de la seccion para la creacion de los encabezados

            // seccion para escribir los CT
            updateMessages("Escribiendo las clases terampeuticas...");
            writeCT(newSheet, sheet, 13, endRow);

            // seccion para escribir los productos
            updateMessages("Escribiendo los productos...");
            writeProducts(newSheet, sheet, 14);

            // seccion para escribir los otros valores
            updateMessages("Escribiendo datos en general...");
            writerOthersValues(newSheet, sheet, 15);

            // seccion para escribir los key competitors
            updateMessages("Escribiendo los Key Competitors...");
            writeKeyCompetitors(newSheet, 3, endRow, 9, 5);

            // seccion para escribir el pais
            XmlContry contry = writeContries(newSheet, 3, 0, input);

            // seccion para escribir la region
            writeRegions(contry, newSheet, 3, 1);

            for (int i = 0; i < 155; i++)
                newSheet.autoSizeColumn(i);

            newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum())));

            String pathOutput = "DAS PF - " + input.getFileName().toString();

            try (FileOutputStream fos = new FileOutputStream(
                    Paths.get(this.out.toString(), pathOutput).toFile())) {

                updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                        Paths.get(this.out.toString(), pathOutput)));

                workbook.write(fos);
            } catch (IOException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                workbook.close();
            }

            if (openFile && Desktop.isDesktopSupported()
                    && Desktop.getDesktop().isSupported(Desktop.Action.OPEN))
                Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile());

            result = true;

            newSheet = null;
            sheet = null;
            workbook = null;

            Files.delete(copy);
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);

        Util.showException("No se pudo guardar el archivo", ex);
    }

    return result;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean consolidateFiles() {
    boolean result = false;
    AtomicInteger rowIndex = new AtomicInteger(3);

    String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx";
    Workbook consolidateWb = new XSSFWorkbook();

    try {/*  w  w  w  .j a v a 2s .c om*/
        Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado");

        Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).sorted((p1, p2) -> {
            String acronym = getAcromynName(p1);
            String acronym2 = getAcromynName(p2);

            return acronym.compareToIgnoreCase(acronym2);
        }).forEach(p -> {
            try {
                Workbook wb = WorkbookFactory.create(p.toFile());
                Sheet sheet = wb.getSheet("Procesado");

                updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s",
                        p.toString(), outputName));

                for (int index = 3; index < sheet.getLastRowNum(); index++) {
                    Row row = sheet.getRow(index);
                    Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement());

                    row.forEach(c -> {
                        if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                            final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType());

                            updateMessages(
                                    String.format("Copiando los datos de la fila: #%d", c.getRowIndex()));

                            switch (c.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                cell.setCellValue(c.getNumericCellValue());

                                break;

                            case Cell.CELL_TYPE_STRING:
                                cell.setCellValue(c.getRichStringCellValue());

                                break;
                            }
                        }
                    });

                    row = null;
                }

                sheet = null;
                wb.close();
                wb = null;
            } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            }
        });

        Path path = Files.list(this.out).filter((p) -> {
            String name = p.toString();

            return (name.endsWith(".xlsx") || name.endsWith(".xls"))
                    && !p.getFileName().toString().equals(outputName);
        }).findFirst().get();

        createHeadersConsolidateFile(consolidateWb, path);

        for (int i = 0; i < 155; i++)
            sheetConsolidate.autoSizeColumn(i);

        sheetConsolidate.setAutoFilter(
                CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum())));

        try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) {
            updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                    Paths.get(this.out.toString(), outputName)));

            consolidateWb.write(fos);

            result = true;
        } catch (IOException ex) {
            Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE,
                    "Ocurrio un error al intenatr guardar el archivo consolidado", ex);
        } finally {
            consolidateWb.close();
        }

    } catch (IOException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
    }

    consolidateWb = null;

    return result;
}