Java Code Examples for org.apache.poi.hssf.usermodel.HSSFSheet#iterator()

public Map<String, BoundaryPoint> parse(InputStream is) throws IOException {
    Map<String, BoundaryPoint> boundaryPoints = new HashMap<>();
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();
    rowIterator.next();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell boundaryPointNameCell = row.getCell(13);
        Cell borderFromCell = row.getCell(14);
        Cell borderToCell = row.getCell(15);
        String boundaryPointName = boundaryPointNameCell.getStringCellValue();
        if (boundaryPointName.equals("-")) {
            continue;
        }
        Country borderFrom = toCountry(borderFromCell.getStringCellValue());
        Country borderTo = toCountry(borderToCell.getStringCellValue());
        boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo));
    }
    return boundaryPoints;
}
 

/**
 * Adds the ministry per year to map.
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addMinistryPerYearToMap(final String name,
		final Map<Integer, List<GovernmentBodyAnnualSummary>> map, final HSSFSheet mySheet) {
	if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

		final int year = Integer.parseInt(mySheet.getSheetName());

		final List<GovernmentBodyAnnualSummary> yearList = new ArrayList<>();
		final Iterator<Row> rowIterator = mySheet.iterator();

		// Skip header row, ignore first
		rowIterator.next();

		while (rowIterator.hasNext()) {
			addGovernmentBodyAnnualSummaryToList(name, year, yearList, rowIterator.next());
		}
		map.put(year, yearList);
	}
}
 

private TupleIterator iteratorRawXLS() throws IOException {
	FileInputStream reader = new FileInputStream(file);
	HSSFWorkbook workbook = new HSSFWorkbook(reader);
	HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
	return new SpreadsheetTupleIterator(sheet.iterator()) {
		@Override
		public void close() {
			try {
				workbook.close();
			} catch (IOException e1) {
			}
			try {
				reader.close();
			} catch (IOException e) {
			}
		}			
	};
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    columnLabels = new HashMap();
    rowLabels = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst) {
            processAsLabels(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

/**
 * Adds the data for year to map.
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addDataForYearToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
		final HSSFSheet mySheet) {
	if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {
		final int year = Integer.parseInt(mySheet.getSheetName());
		final Iterator<Row> rowIterator = mySheet.iterator();

		rowIterator.next();

		while (rowIterator.hasNext()) {
			addGovernmentBodyAnnualSummaryToMap(name, map, year, rowIterator.next());
		}
	}
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    hierarchy = new Topic[1000];
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        processRow(row, tm);
    }
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    occurrenceTypes = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst && FIRST_ROW_CONTAINS_OCCURRENCE_TYPES) {
            processRowAsOccurrenceTypes(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

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();
        if(isFirst && FIRST_ROW_CONTAINS_ROLES) {
            processRowAsRoles(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        processRow(row, tm);
    }
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    languagesPerColumn = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst && FIRST_ROW_CONTAINS_LANGUAGES) {
            processRowAsLanguages(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
    List<String> optionsList = new ArrayList<String>();
    try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
        Iterator<Row> iterator;
        switch(FileMagic.valueOf(bufferedInputStream)) {
            case OOXML:
                log.debug("Input file detected as OOXML.");
                XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
                XSSFSheet datatypeSheet = workbook.getSheetAt(0);
                iterator = datatypeSheet.iterator();
                break;
            case OLE2:
                log.debug("Input file detected as OLE2.");
                HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
                HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
                iterator = legacyDatatypeSheet.iterator();
                break;
            default:
                log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
                try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
                    while(scanner.hasNext()){
                        String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
                        if(StringUtils.isNotBlank(inputString)){
                            optionsList.add(inputString);
                        }
                    }
                } catch(Exception ex){
                    throw new IOException("Error processing the file as text type.", ex);
                }
                return optionsList;
        }

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();
            if(cellIterator.hasNext()) {
                Cell currentCell = cellIterator.next();
                switch(currentCell.getCellType()) {
                    case STRING:
                        if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
                            optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
                        }
                        break;
                    case NUMERIC:
                         optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
                         break;
                    case BOOLEAN:
                        optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
                        break;
                    case FORMULA:
                    case BLANK:
                    case _NONE:
                    case ERROR:
                    default:
                        break;
                }
            }
        }
    } catch (Exception e) {
        throw new IOException("Error converting the file to options list.");
    }

    return optionsList;
}
 

public String parseExcelData(InputStream is) {
	try {
		HSSFWorkbook workbook = new HSSFWorkbook(is);

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

		// Iterate through each rows from first sheet
		Iterator<Row> rowIterator = sheet.iterator();
		currentString = new StringBuilder();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();

			// 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:
					bytesRead++;
					currentString.append(cell.getBooleanCellValue() + "\t");
					break;

				case Cell.CELL_TYPE_NUMERIC:
					bytesRead++;
					currentString.append(cell.getNumericCellValue() + "\t");
					break;

				case Cell.CELL_TYPE_STRING:
					bytesRead++;
					currentString.append(cell.getStringCellValue() + "\t");
					break;

				}
			}
			currentString.append("\n");
		}
		is.close();
	} catch (IOException e) {
		LOG.error("IO Exception : File not found " + e);
	}
	return currentString.toString();

}
 

public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
    List<String> optionsList = new ArrayList<String>();
    try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
        Iterator<Row> iterator;
        switch(FileMagic.valueOf(bufferedInputStream)) {
            case OOXML:
                log.debug("Input file detected as OOXML.");
                XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
                XSSFSheet datatypeSheet = workbook.getSheetAt(0);
                iterator = datatypeSheet.iterator();
                break;
            case OLE2:
                log.debug("Input file detected as OLE2.");
                HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
                HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
                iterator = legacyDatatypeSheet.iterator();
                break;
            default:
                log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
                try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
                    while(scanner.hasNext()){
                        String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
                        if(StringUtils.isNotBlank(inputString)){
                            optionsList.add(inputString);
                        }
                    }
                } catch(Exception ex){
                    throw new IOException("Error processing the file as text type.", ex);
                }
                return optionsList;
        }

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();
            if(cellIterator.hasNext()) {
                Cell currentCell = cellIterator.next();
                switch(currentCell.getCellType()) {
                    case STRING:
                        if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
                            optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
                        }
                        break;
                    case NUMERIC:
                         optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
                         break;
                    case BOOLEAN:
                        optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
                        break;
                    case FORMULA:
                    case BLANK:
                    case _NONE:
                    case ERROR:
                    default:
                        break;
                }
            }
        }
    } catch (Exception e) {
        throw new IOException("Error converting the file to options list.");
    }

    return optionsList;
}