org.apache.poi.ss.usermodel.Sheet#getPhysicalNumberOfRows

Source File: DefaultRowProcessor.java From onetwo with Apache License 2.0

6 votes

public Row createRow(RowContextData rowContext) {
		Sheet sheet = rowContext.getSheet();
		RowModel rowModel = rowContext.getRowModel();
//		int rowIndex = sheet.getLastRowNum();
		int rowIndex = sheet.getPhysicalNumberOfRows();
//		System.out.println("createRow:"+rowIndex);
		
		int span =0;
		if(rowModel.hasSpan()){
			span = rowContext.parseIntValue(rowModel.getSpan());
		}else{
			span = rowModel.getSpace();
		}
		
		Row row = createRow(sheet, rowIndex, rowModel);
		if(span>0){
			for(int i=1; i<span; i++){
//				sheet.createRow(rowIndex++);
				createRow(sheet, ++rowIndex, rowModel);
			}
		}
		return row;
	}

Source File: ExcelServiceImpl.java From molgenis with GNU Lesser General Public License v3.0

6 votes

@Override
public List<Sheet> buildExcelSheetsFromFile(File file) throws EmptySheetException {
  List<Sheet> sheets = newArrayList();
  try (Workbook workbook = WorkbookFactory.create(file)) {
    int numberOfSheets = workbook.getNumberOfSheets();

    for (int index = 0; index < numberOfSheets; index++) {
      Sheet sheet = workbook.getSheetAt(index);
      if (sheet.getPhysicalNumberOfRows() == 0) {
        throw new EmptySheetException("Sheet [" + sheet.getSheetName() + "] is empty");
      } else if (sheet.getPhysicalNumberOfRows() == 1) {
        throw new MolgenisDataException(
            "Header was found, but no data is present in sheet [" + sheet.getSheetName() + "]");
      } else {
        sheets.add(sheet);
      }
    }

  } catch (IOException | EncryptedDocumentException ex) {
    LOG.error(ex.getLocalizedMessage());
    throw new MolgenisDataException("Could not create excel workbook from file");
  }
  return sheets;
}

Source File: DefaultPOIExcelReader.java From onetwo with Apache License 2.0

6 votes

protected List<?> mapRow(Sheet sheet, int sheetIndex, SheetRowMapper<?> mapper){
		int rowCount = sheet.getPhysicalNumberOfRows();
		
		List<String> names = mapper.mapTitleRow(sheet);
		
//		Row row = null;
		List<Object> datas = new ArrayList<Object>();
		for(int rowIndex=mapper.getDataRowStartIndex(); rowIndex<rowCount; rowIndex++){
//			row = sheet.getRow(rowIndex);
			Object value = mapper.mapDataRow(sheet, names, rowIndex);
			if(value==null)
				continue;
			datas.add(value);
		}
		return datas;
	}

Source File: POIExcelGeneratorImpl.java From onetwo with Apache License 2.0

6 votes

private void buildAutoColumnSize(SheetData sdata){
	Sheet sheet = sdata.getSheet();
	if(sheet.getPhysicalNumberOfRows()>0 && sdata.getSheetModel().isAutoSizeColumn()){
		int cellCount = sheet.getRow(0).getPhysicalNumberOfCells();
		boolean useMerged = sdata.getSheetModel().isUseMergedCells();
		for (int i = 0; i < cellCount; i++) {
			sheet.autoSizeColumn(i, useMerged);
		}
	}else{
		Map<Short, Boolean> columSize = sdata.getSheetModel().getAutoSizeColumnMap();
		if(ExcelUtils.isEmpty(columSize))
			return ;
		for(Entry<Short, Boolean> entry : columSize.entrySet()){
			sheet.autoSizeColumn(entry.getKey(), entry.getValue());
		}
	}
}

Source File: ExcelServiceImpl.java From agile-service-old with Apache License 2.0

6 votes

private Integer getRealRowCount(Sheet sheet) {
    Integer count = 0;
    for (int r = 1; r <= sheet.getPhysicalNumberOfRows(); r++) {
        Row row = sheet.getRow(r);
        if (row == null || (((row.getCell(0) == null || row.getCell(0).toString().equals("") || row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_BLANK)) &&
                (row.getCell(1) == null || row.getCell(1).toString().equals("") || row.getCell(1).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(2) == null || row.getCell(2).toString().equals("") || row.getCell(2).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(3) == null || row.getCell(3).toString().equals("") || row.getCell(3).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(4) == null || row.getCell(4).toString().equals("") || row.getCell(4).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(5) == null || row.getCell(5).toString().equals("") || row.getCell(5).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(6) == null || row.getCell(6).toString().equals("") || row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(7) == null || row.getCell(7).toString().equals("") || row.getCell(7).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(8) == null || row.getCell(8).toString().equals("") || row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(9) == null || row.getCell(9).toString().equals("") || row.getCell(9).getCellType() == XSSFCell.CELL_TYPE_BLANK))) {
            continue;
        }
        count++;
    }
    return count;
}

Source File: ExcelStreamReader.java From onetwo with Apache License 2.0

6 votes

public void read(Workbook workbook, SheeDataModelConsumer consumer){
	Assert.notNull(workbook, "workbook can not be null");
	try {
		int sheetCount = workbook.getNumberOfSheets();
		Sheet sheet = null;
		for(int sheetIndex=0; sheetIndex<sheetCount; sheetIndex++){
			sheet = workbook.getSheetAt(sheetIndex);
			if(sheet.getPhysicalNumberOfRows()<1)
				continue;
			
			for(SheetStreamReader<?> reader : sheetReaders) {
				if (reader.match(sheetIndex)) {
					Object dataInst = reader.onRead(sheet, sheetIndex);
					if (consumer!=null && dataInst!=null) {
						consumer.onSheetReadCompleted(dataInst);
					}
				}
			}
		}
	}catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("read excel file error.", e);
	}
}

Source File: HSSFUnmarshaller.java From poiji with MIT License

6 votes

@Override
public <T> void unmarshal(Class<T> type, Consumer<? super T> consumer) {
    Workbook workbook = workbook();
    Optional<String> maybeSheetName = this.getSheetName(type, options);

    Sheet sheet = this.getSheetToProcess(workbook, options, maybeSheetName.orElse(null));

    int skip = options.skip();
    int maxPhysicalNumberOfRows = sheet.getPhysicalNumberOfRows() + 1 - skip;

    loadColumnTitles(sheet, maxPhysicalNumberOfRows);
    AnnotationUtil.validateMandatoryNameColumns(options, type, columnIndexPerTitle.keySet());

    for (Row currentRow : sheet) {
        if (!skip(currentRow, skip) && !isRowEmpty(currentRow)) {
            internalCount += 1;

            if (limit != 0 && internalCount > limit)
                return;

            T instance = deserializeRowToInstance(currentRow, type);
            consumer.accept(instance);
        }
    }
}

Source File: ExcelStreamReader.java From onetwo with Apache License 2.0

5 votes

public T onRead(Sheet sheet, int sheetIndex) {
	T dataModelInst = createDataModel();
	int rowCount = sheet.getPhysicalNumberOfRows();
	for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
		for(RowStreamReader<T> reader : rowReaders) {
			Row row = sheet.getRow(rowIndex);
			if (reader.match(rowIndex)) {
				reader.onRead(dataModelInst, sheet, sheetIndex, row, rowIndex);
			}
		}
	}
	return dataModelInst;
}

Source File: DefaultPOIExcelReader.java From onetwo with Apache License 2.0

5 votes

/****
 * 
 * @param workbook
 * @param extractor
 * @param startSheet include
 * @param endSheet not include
 * @return
 */
public <T> Map<String, T> readData(Workbook workbook, TableDataExtractor<T, Sheet> extractor, int startSheet, int readCount){
	Assert.notNull(workbook, "workbook can not be null");
	try {
		int sheetCount = workbook.getNumberOfSheets();
		Sheet sheet = null;
		Map<String, T> datas = new LinkedHashMap<String, T>();
		
		if(startSheet<0)
			startSheet = 0;
		if(readCount<0)
			readCount = sheetCount;
		
		int hasReadCount = 0;
		for(int i=startSheet; i<sheetCount; i++){
			if(hasReadCount<readCount){
				sheet = workbook.getSheetAt(i);
				String name = sheet.getSheetName();
				if(sheet.getPhysicalNumberOfRows()<1)
					continue;
				if(ExcelUtils.isBlank(name))
					name = "" + i;
				T extractData = extractor.extractData(sheet);
				datas.put(name, extractData);
				
				hasReadCount++;
			}
		}
		return datas;
	}catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("read excel file error.", e);
	}
}

Source File: ExcelTemplateEngineer.java From onetwo with Apache License 2.0

5 votes

protected void parseSheet(ETSheetContext sheetContext){
		Sheet sheet = sheetContext.getSheet();
		final ExcelTemplateValueProvider provider = sheetContext.getValueProvider();
//		int rowNumbs = sheet.getPhysicalNumberOfRows();
		List<CellRangeAddress> cellRangeList = Lists.newArrayList();
		for(int i=0; i< sheet.getNumMergedRegions(); i++){
			CellRangeAddress cellRange = sheet.getMergedRegion(i);
			cellRangeList.add(cellRange);
			if(logger.isDebugEnabled()){
				logger.debug("find mergedRegion, first row:{}, last row:{}, firstCol: {}, lastCol: {} ",
								cellRange.getFirstRow(), cellRange.getLastRow(),
								cellRange.getFirstColumn(), cellRange.getLastColumn());
			}
		}
		provider.setCellRangeList(cellRangeList);
		
		for (int rowIndex = 0; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if(row==null)
				continue;
			rowIndex = parseRow(sheetContext, row);
		}

		/*for(int i=0; i< sheet.getNumMergedRegions(); i++){
			CellRangeAddress cellRange = sheet.getMergedRegion(i);
			logger.info("==find mergedRegion, first row:{}, last row:{} " + cellRange.getFirstRow(), cellRange.getLastRow());
		}*/
	}

Source File: AbstractRowDirective.java From onetwo with Apache License 2.0

5 votes

public boolean matchEnd(T model, Row row){
	Row lastRow = row;
	Sheet sheet = row.getSheet();
	while(!isMatchEnd(model, lastRow)){
		logger.info("find diretive[{}] list row: {}", getName(), lastRow.getRowNum());
		model.addMatchRow(lastRow);
		
		if(lastRow.getRowNum()+1>sheet.getPhysicalNumberOfRows())
			throw new ExcelException("not end tag matched for: " + model.getDirectiveStart());
		
		lastRow = row.getSheet().getRow(lastRow.getRowNum()+1);
	}
	return true;
}

Source File: ExcelUtil.java From SI with BSD 2-Clause "Simplified" License

4 votes

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}

Source File: ExcelWriter.java From jeesuite-libs with Apache License 2.0

4 votes

/**
 * 合并列
 */
private void mergeColumns(Sheet sheet,CellStyle cellStyle) {
    // 行数
    int rowsCount = sheet.getPhysicalNumberOfRows();
    // 列数
    int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
 
    Row row = null;
    Cell cell1 = null;
    Cell cell2 = null;
 
    int colSpan = 0;
 
    for (int r = 0; r < rowsCount; r++) {
        row = sheet.getRow(r);
        // 重置
        colSpan = 0;
        row = sheet.getRow(r);
        for (int c = 0; c < colsCount; c++) {
            cell1 = row.getCell(c);
            cell2 = row.getCell(c + 1);
            if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                if (c == colsCount - 1) {
                    break;
                } else {
                    continue;
                }
            }
            if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                    // 合并行中连续相同的值的单元格
                    sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                    break;
                }
            }
            if (cell1 != null && cell2 != null) {
                // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    colSpan++;
                } else {
                    // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                    if (colSpan >= 1) {
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        Cell nowCell = sheet.getRow(r).getCell(c - colSpan);
                		nowCell.setCellStyle(cellStyle);
                        // 合并后重置colSpan
                        colSpan = 0;
                        continue;
                    }
                }
            }
 
        }
    }
 
}

Source File: AbstractExtractorTest.java From TomboloDigitalConnector with MIT License

4 votes

protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);

    for (List<Object> rowSpec : rowSpecs) {
        int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
        Row row = sheet.createRow(rowId);

        for (Object cellSpec : rowSpec) {
            // Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
            int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
            if (cellSpec == null) {
                row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
                continue;
            }
            switch (cellSpec.getClass().getCanonicalName()) {
                case "java.lang.Integer":
                    row.createCell(cellId).setCellValue((Integer)cellSpec);
                    break;
                case "java.lang.String":
                    row.createCell(cellId).setCellValue((String)cellSpec);
                    break;
                case "java.lang.Double":
                    row.createCell(cellId).setCellValue((Double)cellSpec);
                    break;
                case "java.lang.Boolean":
                    row.createCell(cellId).setCellValue((Boolean)cellSpec);
                    break;
                case "java.util.Date":
                    row.createCell(cellId).setCellValue((Date)cellSpec);
                    break;
                case "java.util.Calendar":
                    row.createCell(cellId).setCellValue((Calendar)cellSpec);
                    break;
                case "org.apache.poi.ss.formula.Formula":
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
                    Cell cell = row.getCell(row.getLastCellNum());
                    cell.setCellFormula(((Formula)cellSpec).toString());
                    break;
                default:
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
            }
        }
    }
    return workbook;
}

Source File: ExcelWriter.java From azeroth with Apache License 2.0

4 votes

/**
 * 合并列
 */
private void mergeColumns(Sheet sheet, CellStyle cellStyle) {
    // 行数
    int rowsCount = sheet.getPhysicalNumberOfRows();
    // 列数
    int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();

    Row row = null;
    Cell cell1 = null;
    Cell cell2 = null;

    int colSpan = 0;

    for (int r = 0; r < rowsCount; r++) {
        row = sheet.getRow(r);
        // 重置
        colSpan = 0;
        row = sheet.getRow(r);
        for (int c = 0; c < colsCount; c++) {
            cell1 = row.getCell(c);
            cell2 = row.getCell(c + 1);
            if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                if (c == colsCount - 1) {
                    break;
                } else {
                    continue;
                }
            }
            if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                    // 合并行中连续相同的值的单元格
                    sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                    break;
                }
            }
            if (cell1 != null && cell2 != null) {
                // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    colSpan++;
                } else {
                    // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                    if (colSpan >= 1) {
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        Cell nowCell = sheet.getRow(r).getCell(c - colSpan);
                        nowCell.setCellStyle(cellStyle);
                        // 合并后重置colSpan
                        colSpan = 0;
                        continue;
                    }
                }
            }

        }
    }

}

Source File: AbstractSSFRowMapperAdapter.java From onetwo with Apache License 2.0

4 votes

@Override
public int getNumberOfRows(Sheet table) {
	return table.getPhysicalNumberOfRows();
}

Source File: ExcelUtil.java From SI with BSD 2-Clause "Simplified" License

4 votes

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}

Source File: ArchivePrinter.java From steady with Apache License 2.0

3 votes

/**
 * It read an excel file from the Path parameter and output the number of lines in the excel file
 * if it's a normal well-formatted excel file, it will correctly print the number of lines
 * if it's a special excel file in which the sharedStrings.xml has been modified, it will go into an endless loop and result in OutOfMemory
 * Modify the sharedStrings.xml to a repeating format like <!ENTITY lol1 "&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;">
 * @param _p
 * @throws Exception
 */
public static void openSpreadsheet(Path _p) throws Exception {
	XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(_p.toString()));
	Sheet sheet = wb.getSheetAt(0);   
	final int num_rows = sheet.getPhysicalNumberOfRows();
	System.out.println("Spreadsheet [" + _p + "] has [" + num_rows + "] rows");
}

Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getPhysicalNumberOfRows()