Example usage for org.apache.poi.hssf.usermodel HSSFSheet iterator

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet iterator.

Prototype

@Override
public Iterator<Row> iterator() 

Source Link

Document

Alias for #rowIterator() to allow foreach loops

Usage

From source file:npv.importer.XlsImporter.java

private void findTag(HSSFSheet sheet, String searchTag) {
    //looking for '#Ri' tag
    HSSFRow row = sheet.getRow(0);//w w  w .  ja  v a2s.com
    Iterator<Row> rowIterator = sheet.iterator();
    Iterator<Cell> cellIterator;
    boolean isFound = false;

    while (rowIterator.hasNext()) {
        if (!isFound) {
            Row rRow = rowIterator.next();
            cellIterator = rRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell rCell = cellIterator.next();
                if (rCell.getCellType() == Cell.CELL_TYPE_STRING && rCell.getStringCellValue().equals(tag)) {
                    rPosition[0] = rRow.getRowNum();
                    rPosition[1] = rCell.getColumnIndex();
                    isFound = true;
                    break;
                }
            }
        } else {
            break;
        }
    }
}

---

From source file:org.deri.tarql.XLSToValues.java

License:Apache License

public TableData read() {
    try {//from  www. j  ava2  s.  co m
        List<Binding> bindings = new ArrayList<Binding>();

        // Read workbook into HSSFWorkbook
        HSSFWorkbook workbook = new HSSFWorkbook(this.is);
        HSSFSheet sheet = workbook.getSheetAt(this.sheet);
        this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter(true);

        // To iterate over the rows
        Iterator<Row> rowIterator = sheet.iterator();

        String[] row;
        try {
            if (varsFromHeader) {
                while (rowIterator.hasNext()) {
                    row = getRow(rowIterator.next());
                    boolean foundValidColumnName = false;
                    for (int i = 0; i < row.length; i++) {
                        if (toVar(row[i]) == null)
                            continue;
                        foundValidColumnName = true;
                    }
                    // If row was empty or didn't contain anything usable
                    // as column name, then try next row
                    if (!foundValidColumnName)
                        continue;
                    for (int i = 0; i < row.length; i++) {
                        Var var = toVar(row[i]);
                        if (var == null || vars.contains(var) || var.equals(TarqlQuery.ROWNUM)) {
                            getVar(i);
                        } else {
                            vars.add(var);
                        }
                    }
                    break;
                }
            }
            rownum = 1;
            while (rowIterator.hasNext()) {
                row = getRow(rowIterator.next());
                // Skip rows without data
                if (isEmpty(row))
                    continue;
                bindings.add(toBinding(row));
                rownum++;
            }

            vars.add(TarqlQuery.ROWNUM);
            //Make sure variables exists for all columns even if no data is available, otherwise ARQ will complain.
            for (int i = 0; i < vars.size(); i++) {
                if (vars.get(i) == null) {
                    getVar(i);
                }
            }
            return new TableData(vars, bindings);
        } finally {
            this.is.close();
        }
    } catch (IOException ex) {
        throw new JenaException(ex);
    }
}

---

From source file:org.fenixedu.ulisboa.specifications.ui.curricularrules.manageanycurricularcourseexceptionsconfiguration.AnyCurricularCourseExceptionsConfigurationController.java

License:Open Source License

private Collection<CompetenceCourse> parseCompetenceCoursesFromXLS(MultipartFile competenceCoursesFile)
        throws IOException {

    if (!competenceCoursesFile.getOriginalFilename().endsWith(".xls")) {
        throw new ULisboaSpecificationsDomainException(
                "error.curricularRules.manageAnyCurricularCourseExceptionsConfiguration.importCompetenceCourses.invalid.file.format");
    }//from ww w. java 2s.c  om

    final Set<CompetenceCourse> result = new HashSet<CompetenceCourse>();

    InputStream inputStream = null;
    HSSFWorkbook workbook = null;
    try {

        inputStream = competenceCoursesFile.getInputStream();
        workbook = new HSSFWorkbook(competenceCoursesFile.getInputStream());

        final HSSFSheet sheet = workbook.getSheetAt(0);
        final Iterator<Row> rowIterator = sheet.iterator();

        //header
        rowIterator.next();

        while (rowIterator.hasNext()) {

            final Row row = rowIterator.next();
            final String code = row.getCell(0).getStringCellValue();
            final CompetenceCourse competenceCourse = CompetenceCourse.find(code);

            if (competenceCourse == null) {
                throw new ULisboaSpecificationsDomainException(
                        "error.curricularRules.manageAnyCurricularCourseExceptionsConfiguration.importCompetenceCourses.competenceCourse.not.found",
                        code);
            }

            result.add(competenceCourse);

        }

        return result;

    } catch (IOException e) {
        throw new ULisboaSpecificationsDomainException("label.unexpected.error.occured");
    } finally {
        IOUtils.closeQuietly(inputStream);
    }
}

---

From source file:org.geoserver.monitor.rest.RequestResourceTest.java

License:Open Source License

@Test
public void testGetAllExcel() throws Exception {
    RequestResource.ExcelFormat format = new RequestResource.ExcelFormat(
            new String[] { "id", "path", "startTime" }, monitor);

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    format.toRepresentation(monitor.getDAO().getRequests()).write(out);

    HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
    HSSFSheet sheet = wb.getSheet("requests");

    Iterator<Row> rows = sheet.iterator();
    Iterator<RequestData> it = monitor.getDAO().getRequests().iterator();

    assertTrue(rows.hasNext());//from www.ja v  a2s  . co  m
    Row row = rows.next();
    assertEquals("id", row.getCell(0).getStringCellValue());
    assertEquals("path", row.getCell(1).getStringCellValue());
    assertEquals("startTime", row.getCell(2).getStringCellValue());

    while (rows.hasNext()) {
        row = rows.next();

        assertTrue(it.hasNext());
        RequestData data = it.next();

        assertEquals((double) data.getId(), row.getCell(0).getNumericCellValue(), 0.1);
        assertEquals(data.getPath(), row.getCell(1).getStringCellValue());
        assertEquals(data.getStartTime(), row.getCell(2).getDateCellValue());
    }

    assertFalse(it.hasNext());
}

---

From source file:org.isource.util.CSVUtils.java

public static void updateSheet(String filename) {

    try {/*from w  w  w . j a  v a  2  s  . c  o m*/
        FileInputStream file = new FileInputStream(new File(filename));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int rowNum = 1;
        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            List<String> line = new ArrayList<String>();

            Cell newCell = row.createCell(row.getPhysicalNumberOfCells());

            if (rowNum == 1) {
                newCell.setCellValue("New Cell");
            } else {
                newCell.setCellType(Cell.CELL_TYPE_FORMULA);
                newCell.setCellFormula("SUM(B2:B9)");
            }

            rowNum++;
        }

        workbook = evaluateFormulas(workbook);
        FileOutputStream out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.close();
        file.close();

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

}

---

From source file:org.isource.util.CSVUtils.java

private static List<List> readWorkbook(HSSFWorkbook workbook) {

    List<List> lines = new ArrayList<List>();

    workbook = evaluateFormulas(workbook);

    HSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();

    while (rowIterator.hasNext()) {

        Row row = rowIterator.next();// w  ww  .  j  av  a  2 s . c  o m

        List<String> line = new ArrayList<String>();

        //For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {

            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                line.add(new Boolean(cell.getBooleanCellValue()).toString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
                    line.add(dateFormat.format(cell.getDateCellValue()));
                } else {
                    line.add(new Double(cell.getNumericCellValue()).toString());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                line.add(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                switch (cell.getCachedFormulaResultType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    line.add(new Double(cell.getNumericCellValue()).toString());
                    break;
                case Cell.CELL_TYPE_STRING:
                    line.add(cell.getRichStringCellValue().toString());
                    break;
                }
                break;
            }
        }

        lines.add(line);
    }

    return lines;
}

---

From source file:org.niord.importer.aton.batch.AbstractDkAtonImportReader.java

License:Open Source License

/**
 * Opens the Excel sheet, reads in the header row and build a map of the column indexes for the given header fields.
 * @param path a path to the the Excel sheet
 * @param colIndex the column index map/*ww w  .  ja v a2  s .c  om*/
 * @param fields the fields to determine column indexes for
 * @return the Excel row iterator pointing to the first data row
 */
private Iterator<Row> parseHeaderRow(Path path, Map<String, Integer> colIndex, String[] fields)
        throws Exception {

    try (FileInputStream inputStream = new FileInputStream(path.toFile())) {
        // Create Workbook instance holding reference to .xls file
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // Get first/desired sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        totalRowNo = sheet.getLastRowNum();

        // Get row iterator
        Iterator<Row> rowIterator = sheet.iterator();
        Row headerRow = rowIterator.next();

        // Get the column indexes of the relevant columns
        Arrays.stream(fields).forEach(f -> updateColumnIndex(headerRow, colIndex, f));

        return rowIterator;
    }
}

---

From source file:org.semtix.gui.auszahlung.auszahlungsmodul.Datenabgleich.java

License:Open Source License

/**
 * Liest eine XLS Datei ein und gleicht sie mit der Datenbank ab
 *
 * @param path /Pfad/angabe/zur/Datei.xyz 
 *///from   ww  w  . j av  a  2s.c  om
public void einlesen(String path) {

    try {

        FileInputStream file = new FileInputStream(new File(path));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            Cell[] cells = new Cell[row.getLastCellNum()];
            int i = 0;
            boolean exmatrikuliert = false;
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();
                cells[i] = cell;

                i++;

                if (i == 5 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equalsIgnoreCase("X")) {
                        // if this cell contains an X we have to see if a)
                        // Antrag is invalid b) Person is Teilimmatrikuliert
                        // c) How many months Teilimmatrikuliert

                        exmatrikuliert = true;

                    }
                }
            }

            // we have to see if a) Antrag is invalid b) Person is
            // Teilimmatrikuliert c) How many months Teilimmatrikuliert
            if (exmatrikuliert) {
                Cell semesterCell = cells[6];

                // Semester aufschlsseln nach Jahr und Typ
                String semesterJahrPerson = null;
                String semesterTypPerson = null;
                if (semesterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String semester = "" + semesterCell.getNumericCellValue();
                    semesterJahrPerson = semester.substring(0, 4).trim();
                    semesterTypPerson = semester.substring(4, 5).trim();
                    if (semesterTypPerson.equals("1")) {
                        semesterTypPerson = "S";
                    } else if (semesterTypPerson.equals("2")) {
                        semesterTypPerson = "W";
                    }
                }

                // get current selected Semester
                String semesterTypAktuell = SemesterConf.getSemester().getSemesterArt().getBuchstabe().trim();
                String semesterJahrAktuell = SemesterConf.getSemester().getSemesterJahr().trim();

                // if its really the same semester get the date of
                // exmatriculation and check how many months
                if (semesterJahrAktuell.equalsIgnoreCase(semesterJahrPerson)
                        && semesterTypAktuell.equalsIgnoreCase(semesterTypPerson)) {
                    // get cell with exmatriculation date
                    Cell exmatriculationDateCell = cells[5];

                    // Find out date of exmatriculation / round up date to next month
                    int exmatriculationmonth = 0;
                    if (HSSFDateUtil.isCellDateFormatted(exmatriculationDateCell)) {
                        Date date = exmatriculationDateCell.getDateCellValue();

                        //                     Calendar cal = Calendar.getInstance();
                        //                     cal.setTime(date);
                        //                     int monat = cal.get(Calendar.MONTH);

                        SimpleDateFormat df = new SimpleDateFormat("MM");
                        exmatriculationmonth = Integer.parseInt(df.format(date));
                        exmatriculationmonth++;
                    }

                    int monateZuschuss = 0;

                    // Sommersemester
                    if (semesterTypAktuell.equals("S")) {

                        monateZuschuss = 6 - (10 - exmatriculationmonth);

                        // Wintersemester
                    } else if (semesterTypAktuell.equals("W")) {
                        if (exmatriculationmonth > 4) {
                            monateZuschuss = 6 - (16 - exmatriculationmonth);
                        } else {
                            monateZuschuss = 6 - (4 - exmatriculationmonth);
                        }
                    }

                    if (monateZuschuss < 6) {

                        Cell manrCell = cells[0];
                        Cell nachnameCell = cells[2];
                        Cell vornameCell = cells[3];

                        String matrikelnummer = "" + manrCell.getNumericCellValue();
                        matrikelnummer = matrikelnummer.substring(0, matrikelnummer.indexOf('.'));

                        String nachname = getStringFromCell(nachnameCell);
                        String vorname = getStringFromCell(vornameCell);

                        // write Teilzuschuss to DB
                        dbhandler.setAntragToTeilzuschuss(monateZuschuss, matrikelnummer);
                    }
                } else {

                    Cell manrCell = cells[0];
                    Cell nachnameCell = cells[2];
                    Cell vornameCell = cells[3];

                    String matrikelnummer = "" + manrCell.getNumericCellValue();
                    matrikelnummer = matrikelnummer.substring(0, matrikelnummer.indexOf('.'));

                    String nachname = getStringFromCell(nachnameCell);
                    String vorname = getStringFromCell(vornameCell);

                    //
                    dbhandler.denyAntrag(AntragAblehnungsgrund.EXMATRIKULIERT.getBegruendung(), matrikelnummer);

                }

            }

        }
        file.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

---

From source file:org.teiid.translator.excel.ExcelExecution.java

License:Open Source License

private Iterator<Row> readXLSFile(File xlsFile) throws TranslatorException {
    try {// w ww  .  j a  v  a2 s . com
        this.xlsFileStream = new FileInputStream(xlsFile);
        Iterator<Row> rowIter = null;
        String extension = ExcelMetadataProcessor.getFileExtension(xlsFile);
        if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$
            HSSFWorkbook workbook = new HSSFWorkbook(this.xlsFileStream);
            HSSFSheet sheet = workbook.getSheet(this.visitor.getSheetName());
            this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            rowIter = sheet.iterator();

        } else if (extension.equalsIgnoreCase("xlsx")) { //$NON-NLS-1$
            XSSFWorkbook workbook = new XSSFWorkbook(this.xlsFileStream);
            XSSFSheet sheet = workbook.getSheet(this.visitor.getSheetName());
            this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            rowIter = sheet.iterator();
        } else {
            throw new TranslatorException(ExcelPlugin.Event.TEIID23000,
                    ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23000));
        }

        // skip up to the first data row
        if (this.visitor.getFirstDataRowNumber() > 0 && rowIter != null) {
            while (rowIter.hasNext()) {
                this.currentRow = rowIter.next();
                if (this.currentRow.getRowNum() >= this.visitor.getFirstDataRowNumber()) {
                    break;
                }
            }
        }
        return rowIter;
    } catch (IOException e) {
        throw new TranslatorException(e);
    }
}

---

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java

License:Open Source License

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    rolesPerColumn = new HashMap();
    while (rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();/*  w ww  .j  a  v a  2s. c o  m*/
        if (isFirst && FIRST_ROW_CONTAINS_ROLES) {
            processRowAsRoles(row, tm);
            isFirst = false;
        } else {
            processRow(row, tm);
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• »