Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#iterator()

public static void main(String[] args) {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator
						+ TestWriteExcelDemo.EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		// Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		// Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			// For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				// Check the cell type and format accordingly
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t");
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

private void importObjectAccess(XSSFSheet sheet) {

    App app = appService.getApp(sheet.getSheetName());
    if (app == null) {
      return;
    }
    Iterator<Row> rowIter = sheet.iterator();
    Map<Integer, AccessConfig> accessMap = null;
    while (rowIter.hasNext()) {
      if (accessMap == null) {
        accessMap = getAccessConfig(rowIter.next(), app);
        continue;
      }
      createObjectRoles(accessMap, rowIter.next());
    }
  }
 

private void importMenuAccess(XSSFSheet sheet) {

    App app = appService.getApp(sheet.getSheetName().split("-")[0]);
    if (app == null) {
      return;
    }
    Iterator<Row> rowIter = sheet.iterator();
    Map<Integer, AccessConfig> accessMap = null;
    while (rowIter.hasNext()) {
      if (accessMap == null) {
        accessMap = getAccessConfig(rowIter.next(), app);
        continue;
      }
      createMenuRoles(accessMap, rowIter.next());
    }
  }
 

private TupleIterator iteratorRawXLSX() throws IOException {
	FileInputStream reader = new FileInputStream(file);
	XSSFWorkbook workbook = new XSSFWorkbook(reader);
	XSSFSheet 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(XSSFSheet 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);
        }
    }
}
 

public static void readSheetWithFormula() {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		FormulaEvaluator evaluator = workbook.getCreationHelper()
				.createFormulaEvaluator();
		// Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		// Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			// For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				// If it is formula cell, it will be evaluated otherwise no
				// change will happen
				switch (evaluator.evaluateInCell(cell).getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_FORMULA:
					// Not again
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

public RowIterator() {
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
		XSSFSheet sheet = workbook.getSheetAt(0);
		iterator = sheet.iterator();
		fieldName2ColumnIndex = new HashMap<String, Integer>();
		for (Cell header : iterator.next())
			fieldName2ColumnIndex.put(header.toString(), fieldName2ColumnIndex.size());
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

public void processSheet(XSSFSheet 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(XSSFSheet 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(XSSFSheet 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(XSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        processRow(row, tm);
    }
}
 

public void processSheet(XSSFSheet 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 void main(String[] args) throws IOException {
	Map<String,String> countryMap = new HashMap<String, String>();
	List<String> countryShortCodes = new ArrayList<String>();
	List<String> countryNames = new ArrayList<String>();
	
	FileInputStream file = new FileInputStream(new File(
			"ISOCountryCodes.xlsx"));

	// Create Workbook instance holding reference to .xlsx file
	XSSFWorkbook workbook = new XSSFWorkbook(file);

	// Get first/desired sheet from the workbook
	XSSFSheet sheet = workbook.getSheetAt(0);
	
	//Iterate through each rows one by one
       Iterator<Row> rowIterator = sheet.iterator();
       while (rowIterator.hasNext()) 
       {
       	String key="";
   		String value="";
           Row row = rowIterator.next();
           //For each row, iterate through all the columns
           Iterator<Cell> cellIterator = row.cellIterator();
            
           while (cellIterator.hasNext()) 
           {
               Cell cell = cellIterator.next();
               //Check the cell type and format accordingly
               switch (cell.getCellType()) 
               {
                   case Cell.CELL_TYPE_STRING:
                       if(key.equals("")){
                       	key = cell.getStringCellValue().trim();
                       }else{
                       	value = cell.getStringCellValue().trim();
                       }
                       break;
               }
           }
           countryMap.put(value, key);
           countryNames.add(value);
           countryShortCodes.add(key);
           //System.out.println("");
       }
       file.close();
   
       //System.out.println(countryMap);
       //System.out.println(countryNames);
       //System.out.println(countryShortCodes);
       
       Collections.sort(countryNames);
       for(String name : countryNames){
       	System.out.print(name.trim()+":"+countryMap.get(name)+"|");
       }

}
 

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 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;
}