Java Code Examples for org.apache.poi.ss.usermodel.Row#cellIterator()

/**
 * 获取表头数据,设置表头的序号
 * 
 * @param teplateParams
 * @param sheet
 * @return
 */
private Map<String, Integer> getTitleMap(Sheet sheet) {
	Row row = null;
	Iterator<Cell> cellTitle;
	Map<String, Integer> titlemap = new HashMap<String, Integer>();
	for (int j = 0; j < teplateParams.getHeadingRows(); j++) {
		row = sheet.getRow(j + teplateParams.getHeadingStartRow());
		cellTitle = row.cellIterator();
		int i = row.getFirstCellNum();
		while (cellTitle.hasNext()) {
			Cell cell = cellTitle.next();
			String value = cell.getStringCellValue();
			if (!StringUtils.isEmpty(value)) {
				titlemap.put(value, i);
			}
			i = i + 1;
		}
	}
	return titlemap;

}
 

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

/**
 * 获取表头数据,设置表头的序号
 * 
 * @param teplateParams
 * @param sheet
 * @return
 */
private Map<String, Integer> getTitleMap(Sheet sheet) {
    Row row = null;
    Iterator<Cell> cellTitle;
    Map<String, Integer> titlemap = new HashMap<String, Integer>();
    for (int j = 0; j < teplateParams.getHeadingRows(); j++) {
        row = sheet.getRow(j + teplateParams.getHeadingStartRow());
        cellTitle = row.cellIterator();
        int i = row.getFirstCellNum();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = cell.getStringCellValue();
            if (!StringUtils.isEmpty(value)) {
                titlemap.put(value, i);
            }
            i = i + 1;
        }
    }
    return titlemap;

}
 

private cfData	getAllFormulaForSheet( cfSession _session, cfSpreadSheetData	spreadsheet ) throws cfmRunTimeException {
	cfArrayData	array	= cfArrayListData.createArray(1);	
	
	Iterator<Row>	rowIt	= spreadsheet.getActiveSheet().rowIterator();
	while ( rowIt.hasNext() ){
		Row row = rowIt.next();
		
		Iterator<Cell> cellIt	= row.cellIterator();
		while ( cellIt.hasNext() ){
			Cell cell	= cellIt.next();
			
			if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
				cfStructData	s = new cfStructData();
				s.setData( "formula", new cfStringData( cell.getCellFormula() ) );
				s.setData( "row", 		new cfNumberData( row.getRowNum() + 1 ) );
				s.setData( "column", 	new cfNumberData( cell.getColumnIndex() + 1 ) );
				array.addElement( s );
			}
		}
	}
	
	return array;
}
 

private static RelvarHeading buildHeadingFromColumnsInFirstRow(DuplicateHandling duplicates, boolean hasHeadingRow, Iterator<Row> rowIterator) {
	Heading heading = new Heading();		
	if (duplicates == DuplicateHandling.DUP_COUNT)
		heading.add("_DUP_COUNT", TypeInteger.getInstance());
	else if (duplicates == DuplicateHandling.AUTOKEY)
		heading.add("_AUTOKEY", TypeInteger.getInstance());		
	Row row;
	try {
		row = rowIterator.next();
	} catch (NoSuchElementException nsee) {
		return new RelvarHeading(heading);
	}
	Iterator<Cell> cellIterator = row.cellIterator();
       int blankCount = 0;
	while (cellIterator.hasNext()) {
		Cell cell = cellIterator.next();
		String columnName;
		if (hasHeadingRow) {
			columnName = ColumnName.cleanName(cell.toString());
			if (columnName.length() == 0)
				columnName = "BLANK" + ++blankCount;
		} else
			columnName = "COLUMN" + ++blankCount;
		heading.add(ColumnName.cleanName(columnName), TypeCharacter.getInstance());
	}		
	return new RelvarHeading(heading);
}
 

private boolean isBlankRow(Row row) {
  Iterator<Cell> cellIterator = row.cellIterator();
  boolean blankRow = true;
  while (cellIterator.hasNext()) {  
    Object value = readValueFromCell(cellIterator.next());
    if (blankRow && value != null && !String.valueOf(value).isEmpty()) {
      blankRow = false;
    }
  }
  return blankRow;
}
 

private List<Cell> getAllCells(Row row) {
	List<Cell> cells = new ArrayList<>();
	Iterator<Cell> it = row.cellIterator();
	while (it.hasNext()) {
		Cell cell = it.next();
		cells.add(cell);
	}
	return cells;
}
 

@Override
public Collection<Collection<Object>> read() {
  List<Collection<Object>> rows = Lists.newArrayList();
  Iterator<Row> rowIterator = sheet.getNativeSheet().iterator();
  boolean firstRow = true;
  while (rowIterator.hasNext()) {      
    Row excelRow = rowIterator.next();
    if (firstRow && skipHeader) {
      firstRow = false;
      continue;
    }
    List<Object> row = Lists.newArrayList();
    Iterator<Cell> cellIterator = excelRow.cellIterator();
    boolean blankRow = true;
    while (cellIterator.hasNext()) {
      Object value = readValueFromCell(cellIterator.next());
      if (blankRow && value != null && !String.valueOf(value).isEmpty()) {
        blankRow = false;
      }
      row.add(value);
    }
    if (blankRow) continue;
    boolean keepRow = true;
    for (RowPostProcessor<Collection<Object>> rowPostProcessor : rowPostProcessors) {
      keepRow = rowPostProcessor.process(row);
      if (!keepRow) break;
    }
    if (keepRow) {
      rows.add(row);
    }
  }
  return rows;
}
 

public void processRow(Row row, TopicMap tm) {
    Iterator<Cell> cellIterator = row.cellIterator();

    try {
        while(cellIterator.hasNext() && !forceStop()) {
            Cell cell = cellIterator.next();
            processCell(cell, tm);
        }
    }
    catch (Exception ex) {
        log(ex);
    }
}
 

/**
 * 获取表格字段列名对应信息
 * @param rows
 * @param params
 * @param excelCollection
 * @return
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
                                         List<ExcelCollectionParams> excelCollection) {
    Map<Integer, String> titlemap = new HashMap<Integer, String>();
    Iterator<Cell> cellTitle;
    String collectionName = null;
    ExcelCollectionParams collectionParams = null;
    Row row = null;
    for (int j = 0; j < params.getHeadRows(); j++) {
        row = rows.next();
        if(row == null){
            continue;
        }
        cellTitle = row.cellIterator();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = getKeyValue(cell);
            int i = cell.getColumnIndex();
            //用以支持重名导入
            if (StringUtils.isNotEmpty(value)) {
                if (titlemap.containsKey(i)) {
                    collectionName = titlemap.get(i);
                    collectionParams = getCollectionParams(excelCollection, collectionName);
                    titlemap.put(i, collectionName + "_" + value);
                } else if (StringUtils.isNotEmpty(collectionName)
                           && collectionParams.getExcelParams().containsKey(
                               collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }
    return titlemap;
}
 

public void processRowAsLanguages(Row row, TopicMap tm) {
    Iterator<Cell> cellIterator = row.cellIterator();
    while(cellIterator.hasNext()) {
        try {
            String langSI = null;
            Cell cell = cellIterator.next();
            String lang = getCellValueAsString(cell);
            if(lang != null) {
                Topic langTopic = tm.getTopicWithBaseName(lang);
                if(langTopic != null) langSI = langTopic.getOneSubjectIdentifier().toExternalForm();
                else langSI = XTMPSI.getLang(lang);
                langTopic = tm.getTopic(new Locator(langSI));
                languagesPerColumn.put(Integer.toString(cell.getColumnIndex()), langSI);
                
                if(langTopic == null && CREATE_MISSING_LANGUAGE_TOPICS) {
                    langTopic = tm.createTopic();
                    langTopic.addSubjectIdentifier(new Locator(langSI));
                    Topic langTypeTopic = tm.getTopic(XTMPSI.LANGUAGE);
                    if(langTypeTopic != null) {
                        langTopic.addType(langTypeTopic);
                    }
                }
            }
        }
        catch (Exception ex) {
            log(ex);
        }
    }
}
 

public static void readExcelFormula(String fileName) throws IOException{
	
	FileInputStream fis = new FileInputStream(fileName);
	
	//assuming xlsx file
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.iterator();
	while (rowIterator.hasNext()) 
       {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
           
           while (cellIterator.hasNext()) 
           {
           	Cell cell = cellIterator.next();
           	switch(cell.getCellType()){
           	case Cell.CELL_TYPE_NUMERIC:
           		System.out.println(cell.getNumericCellValue());
           		break;
           	case Cell.CELL_TYPE_FORMULA:
           		System.out.println("Cell Formula="+cell.getCellFormula());
           		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
           		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
           			System.out.println("Formula Value="+cell.getNumericCellValue());
           		}
           	}
           }
       }
}
 

private Map<String, Integer> toColNamesMap(Row headerRow) {
  if (headerRow == null) return null;

  Map<String, Integer> columnIdx = new LinkedHashMap<>();
  int i = 0;
  for (Iterator<Cell> it = headerRow.cellIterator(); it.hasNext(); ) {
    try {
      String header =
          AbstractCellProcessor.processCell(ExcelUtils.toValue(it.next()), true, cellProcessors);
      if (header != null) {
        if (columnIdx.containsKey(header)) {
          throw new MolgenisDataException(
              format(
                  "Duplicate column header '%s' in sheet '%s' not allowed",
                  header, headerRow.getSheet().getSheetName()));
        }
        columnIdx.put(header, i++);
      }
    } catch (final IllegalStateException ex) {
      final int row = headerRow.getRowNum();
      final String column = CellReference.convertNumToColString(i);
      throw new IllegalStateException(
          "Invalid value at [" + sheet.getSheetName() + "] " + column + row + 1, ex);
    }
  }
  return columnIdx;
}
 

private RowDto readRowAsRowInMemory(final Row row, final RowDto rowTypes) {
    final RowDto rowDto = new RowDto();
    final Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        rowDto.addColumn(new ColumnDto(formatIfData(cellIterator.next())));
    }
    return rowDto;
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = (cfSpreadSheetData)parameters.get(1);
	Pattern pattern = Pattern.compile( parameters.get(0).getString() );

	cfArrayData	arr	= cfArrayData.createArray(1);

	Iterator<Row>	rowIT	= spreadsheet.getActiveSheet().rowIterator();
	while ( rowIT.hasNext() ){
		Row row	= rowIT.next();
		
		Iterator<Cell>	cellIT	= row.cellIterator();
		while ( cellIT.hasNext() ){
			Cell cell	= cellIT.next();
			String cellValue = null;
			
			if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
				cellValue	= cell.getStringCellValue();
			else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
				cellValue	= String.valueOf( cell.getNumericCellValue() );
			else
				cellValue	= cell.toString();

			if ( pattern.matcher( cellValue ).find() ){
				cfStructData	s	= new cfStructData();
				s.setData( "row", 		new cfNumberData( cell.getRowIndex() + 1 ) );
				s.setData( "column", 	new cfNumberData( cell.getColumnIndex() + 1 ) );
				s.setData( "value", 	new cfStringData( cellValue ) );
				arr.addElement( s );
			}
		}
	}

	return arr;
}
 

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 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();

}
 

/**
 * 读取 excel 文件所有内容到 set 中，利用 set 的唯一性，去掉了相同行，并且保留原来的行序</br>
 * 一次性读取所有数据，没有考虑性能问题。
 * 如果单元格为空白，则返回的行中，无此单元格，所以返回的行的单元格可能是不连续的，如 A 列，C 列 ... ，没有 B 列
 *
 * @param excelFile       excel 文件
 * @param datePattern     日期格式  yyyy-MM-dd , yyyy-MM-dd HH:mm:ss  ...
 * @param sheetNumber     指定的读取 sheet 序号，从 0 开始。null 为全部读取
 * @param isDuplicateLine 是否过滤重复行 。判断重复行的依据是各个单元格内容是否相同
 * @return 包含 excel 数据的集合
 */

public static List<ExcelLine> readExcel(File excelFile, String datePattern, Integer sheetNumber, String sheetName, boolean isDuplicateLine) {

    Optional<Workbook> workbook = getInputWorkbook(excelFile);

    if (!workbook.isPresent())
        return Collections.emptyList();

    // set 可以过滤重复元素。
    Collection<ExcelLine> lines;

    if (isDuplicateLine) // 允许重复
        lines = new LinkedList(); //按照 add 先后排序. LinkList: add,delete 快;  ArrayList: get 定位快
    else
        lines = new LinkedHashSet();    // 利用 LinkedHashSet 来保证元素按照添加顺序排序，默认的比较器

    //判断文件类型
    try {


        if (sheetNumber == null && sheetName != null)
            sheetNumber = workbook.get().getSheetIndex(sheetName);

        // 循环 sheet
        for (int i = 0; i < workbook.get().getNumberOfSheets(); i++) {

            if (sheetNumber != null && i != sheetNumber.intValue())
                continue;

            // 循环行
            for (Row row : workbook.get().getSheetAt(i)) {

                ExcelLine excelLine = new ExcelLine();
                excelLine.setFileName(excelFile.getName());
                excelLine.setSheetName(workbook.get().getSheetName(i));
                excelLine.setSheetNumber(i);
                excelLine.setLineNumber(row.getRowNum());
                //For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                //单行的每个单元格
                //.hasNext() 方法原理：如果单元格为空，结果为 false，直接跳到下一个有内容的单元格。所以返回的行的单元格可能是不连续的，如 A 列，C 列 ... ，没有 B 列
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    ExcelCell excelCell = new ExcelCell();
                    excelCell.setTitle(convertColumnIndexToTitle(cell.getColumnIndex()));
                    excelCell.setValue(getFormatCellValue(cell, datePattern));
                    excelLine.addCellValue(excelCell);

                } //单行完成
                // 添加单行数据
                lines.add(excelLine);

            } //单行循环完成

        } //所有行完成

        workbook.get().close();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("read excel end...");

    //重新包装为 list
    return new ArrayList(lines);
}
 

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 */
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            CellType xlsType = CellType.STRING;

            try {
                xlsType = cell.getCellType() == CellType.FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case BLANK:
                currentType = BLANK;
                break;
            case FORMULA:
            case STRING:
                currentType = STRING.getName();
                break;
            case ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}
 

/**
 * Extracts out the columns in the given excel sheet
 *
 * @param sheet Sheet instance corresponding to the desired Excel sheet
 * @return Array containing the column header data
 * @throws SQLException SQLException
 */
private ColumnInfo[] extractColumnHeaders(Sheet sheet) throws SQLException {
    List<ColumnInfo> headers = new ArrayList<ColumnInfo>();

    /* If hasHeader property is set to false, populate header map with column names following
     * the format 'COLUMN' + 'i' where i corresponds to the column id */
    if (!((TConnection)getConnection()).hasHeader()) {
        int maxColumns = ((TConnection)getConnection()).getMaxColumns();
        for (int i = 0; i < maxColumns; i++) {
            headers.add(new ColumnInfo(i + 1, Constants.COLUMN + (i + 1), sheet.getSheetName(),
                    -1, i + 1));
        }
        return headers.toArray(new ColumnInfo[headers.size()]);
    }
    // Retrieving the first row of the sheet as the header row.
    Row row = sheet.getRow(0);
    if (row != null) {
        Iterator<Cell> itr = row.cellIterator();
        while (itr.hasNext()) {
            Cell cell = itr.next();
            if (cell != null) {
                int cellType = cell.getCellType();
                switch (cellType) {
                    case Cell.CELL_TYPE_STRING:
                        headers.add(new ColumnInfo(cell.getColumnIndex() + 1,
                                cell.getStringCellValue(), sheet.getSheetName(), Types.VARCHAR,
                                cell.getColumnIndex() + 1));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        headers.add(new ColumnInfo(cell.getColumnIndex() + 1,
                                String.valueOf(cell.getNumericCellValue()),
                                sheet.getSheetName(), Types.INTEGER,
                                cell.getColumnIndex() + 1));
                        break;
                    default:
                        throw new SQLException("Invalid column type");
                }
            }
        }
    }
    return headers.toArray(new ColumnInfo[headers.size()]);
}