Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:IO.FILES.java

public ArrayList<Persona> busquedaConFiltro(Busqueda v) {
    ArrayList<Persona> p = new ArrayList();
    try {/*from  w w  w.ja va2s .c  o  m*/
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta)));
        Sheet hoja = wb.getSheetAt(0);
        Iterator rows = hoja.rowIterator();

        Row row = (Row) rows.next();
        while (rows.hasNext()) {
            row = (Row) rows.next();
            Persona p2 = toPersona(row);
            if (is(v, p2))
                p.add(p2);
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
    return p;
}

From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java

License:Apache License

private List<WorkbookSheet> listSheets(Workbook workbook) throws SpreadsheetException {

    List<WorkbookSheet> list = new ArrayList<>();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        int bestRank = 0;
        SheetProcessor best = null;//from   w  w w. j  av  a 2s  .c o m
        for (SheetProcessor p : sheetProcessors) {
            int rank = rank(sheet, p);
            if (rank > bestRank) {
                bestRank = rank;
                best = p;
            }
        }
        if (best != null) {
            list.add(new WorkbookSheet(sheet, best));
        }
    }
    Collections.sort(list);
    return list;
}

From source file:IO.REDACCIONES.java

public static void write(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException {
    if ((p == null) || (wb == null))
        return;//w  w  w .  j  a  v a  2  s .c  o  m
    Sheet h = wb.getSheetAt(1);
    int inx = 0;
    Iterator it = h.rowIterator();
    while (it.hasNext()) {
        inx++;
        it.next();
    }
    Row row = h.createRow(inx);
    Cell cell1 = row.createCell(0);
    cell1.setCellValue(p.getId());
    Cell cell2 = row.createCell(1);
    cell2.setCellValue(texto);
}

From source file:IO.REDACCIONES.java

public static void overWrite(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException {
    if ((p == null) || (wb == null))
        return;/*from   ww  w. ja va 2  s  .  c  om*/
    Sheet h = wb.getSheetAt(1);
    boolean encontrado = false;
    Row row = null;
    Iterator it = h.rowIterator();
    while (it.hasNext()) {
        row = (Row) it.next();
        if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) {
            encontrado = true;
        }
    }

    if (!encontrado)
        return;
    int aux = row.getRowNum();
    h.removeRow(row);
    row = h.createRow(aux);
    Cell cell1 = row.createCell(0);
    cell1.setCellValue(p.getId());
    Cell cell2 = row.createCell(1);
    cell2.setCellValue(texto);
}

From source file:IO.REDACCIONES.java

public static void remove(String id, Workbook wb) {
    try {/* w  w  w. j  a v  a2 s.  co  m*/
        Sheet hoja = wb.getSheetAt(1);
        Iterator rows = hoja.rowIterator();

        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) {
                FILES.removeRow(hoja, row);
                break;
            }
        }
    } catch (EncryptedDocumentException ex) {
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:IO.REDACCIONES.java

public static String read(String id) throws FileNotFoundException, IOException {
    String salida = "";
    try {//w  w  w  . j  a va2  s.com
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(FILES.ruta)));
        Sheet hoja = wb.getSheetAt(1);
        Iterator rows = hoja.rowIterator();

        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) {
                salida = row.getCell(1).getStringCellValue();
                break;
            }
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        salida = "";
        Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex);
    }
    return salida;
}

From source file:it.dontesta.liferay.example.portal.client.ImportUsersToLiferay.java

License:Open Source License

/**
 * Read users to import from excel file.
 * //from w w  w .  j av a2  s . c o  m
 * @return Returns a list of users who are ready for import.
 */
private static List<UserToImport> getUsersToImportFromExcel() {
    String fileName = (System.getProperty("fileToImport") != null) ? System.getProperty("fileToImport")
            : FILE_TO_IMPORT_USERS;
    InputStream inp = null;
    List<UserToImport> usersList = new ArrayList<UserToImport>();
    UserToImport user = null;
    boolean readyForImport = true;

    try {
        inp = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            LOGGER.debug("Processing row index {}...", row.getRowNum());
            if (row.getRowNum() == 0) {
                LOGGER.debug("First row is the header. Skip this row");
                continue;
            } else {
                user = new UserToImport();
            }
            for (Cell cell : row) {
                LOGGER.debug("Processing cell index {}...", cell.getColumnIndex());
                switch (cell.getColumnIndex()) {
                case 0:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 1:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setScreenName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The username attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 2:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setEmail(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The email attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 3:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setFirstName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 4:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setMiddleName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 5:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLastName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 6:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setGender(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 7:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Calendar calBirthDate = Calendar.getInstance();
                            calBirthDate.setTime(cell.getDateCellValue());

                            user.setBirthDate(calBirthDate);
                            LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                    cell.getDateCellValue());
                        } else {
                            LOGGER.warn("Value cell index {} {}", cell.getColumnIndex(),
                                    cell.getNumericCellValue());
                            LOGGER.warn("Value cell index {} not contain a date type format",
                                    cell.getColumnIndex());
                        }
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 8:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setJobTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    break;
                case 9:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setSiteName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 10:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setRoleName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 11:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLanguageId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 12:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTimeZoneId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 13:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setAccountId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                default:
                    break;
                }
            }
            if (user.getScreenName() == null) {
                LOGGER.warn("The username attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (user.getEmail() == null) {
                LOGGER.warn("The email attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (readyForImport) {
                LOGGER.info("Add user object {} to user list...", user.toString());
                usersList.add(user);
            }
        }
    } catch (FileNotFoundException e) {
        LOGGER.error(e.getMessage());
    } catch (IllegalStateException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    }

    return usersList;
}

From source file:it.greenvulcano.excel.reader.BaseReader.java

License:Open Source License

public void processExcel(Workbook workbook) throws ExcelException {
    cleanUp();/*from   ww w .  j a  v  a2  s.c om*/
    try {
        startProcess();

        // Discover how many sheets there are in the workbook....
        int numSheets = workbook.getNumberOfSheets();

        // and then iterate through them.
        for (int i = 0; i < numSheets; i++) {

            // Get a reference to a sheet and check to see if it contains
            // any rows.
            Sheet sheet = workbook.getSheetAt(i);
            if (processSheet(sheet, i)) {
                // Note down the index number of the bottom-most row and
                // then iterate through all of the rows on the sheet starting
                // from the very first row - number 1 - even if it is missing.
                // Recover a reference to the row and then call another method
                // which will strip the data from the cells and build lines
                // for inclusion in the resulting object.
                int lastRowNum = sheet.getLastRowNum();
                for (int j = 0; j <= lastRowNum; j++) {
                    if (j % 10 == 0) {
                        ThreadUtils.checkInterrupted(getClass().getSimpleName(), "ExcelFile", null);
                    }
                    Row row = sheet.getRow(j);
                    processRow(row, i, j);
                }
            }
        }

        endProcess();
    } catch (Exception exc) {
        throw new ExcelException("Error parsing Excel", exc);
    }
}

From source file:it.unitn.elisco.utils.Utilities.java

public static List<Long> getApprovedQuestionListFromExcel(InputStream fileStream) throws IOException {

    // Build the document from stream
    Workbook workbook = new XSSFWorkbook(fileStream);

    // Get the sheet with data
    Sheet sheet = workbook.getSheetAt(0);

    // Create a list for results
    List<Long> approvedQuestions = new ArrayList<>();

    int rownum = 1;
    Row row;/*from  w w w .jav a 2  s .c om*/
    Cell approvedCell;
    Cell idCell;

    while (sheet.getRow(rownum) != null) {
        row = sheet.getRow(rownum);
        rownum++;
        approvedCell = row.getCell(3);
        idCell = row.getCell(0);
        if (approvedCell != null && idCell != null
                && approvedCell.getStringCellValue().equalsIgnoreCase("SI")) {
            approvedQuestions.add(new Double(idCell.getNumericCellValue()).longValue());
        }
    }

    return approvedQuestions;
}

From source file:itpreneurs.itp.report.archive.CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {

    //     if(args.length == 0) {
    //        throw new IllegalArgumentException("Filename must be given");
    //     }//from ww w. jav  a2s .  c o m

    String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx";

    Workbook wb = WorkbookFactory.create(new File(filename));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}