下面列出了org.apache.poi.ss.usermodel.Sheet#getPhysicalNumberOfRows ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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;
}
@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);
}
}
}
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);
}
}
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;
}
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());
}
}
}
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;
}
@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;
}
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;
}
/****
*
* @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);
}
}
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());
}*/
}
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;
}
/**
* 合并列
*/
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;
}
}
}
}
}
}
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;
}
/**
* 合并列
*/
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;
}
}
}
}
}
}
/**
* 엑셀파일로부터 데이터를 읽어 리턴한다. *
*/
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;
}
@Override
public int getNumberOfRows(Sheet table) {
return table.getPhysicalNumberOfRows();
}
/**
* 엑셀파일로부터 데이터를 읽어 리턴한다. *
*/
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;
}
/**
* 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");
}