Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getPhysicalNumberOfRows()
The following examples show how to use
org.apache.poi.ss.usermodel.Sheet#getPhysicalNumberOfRows() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| 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;
}
Example 2
| 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;
}
Example 3
| 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;
}
Example 4
| 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());
}
}
}
Example 5
| 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;
}
Example 6
| 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);
}
}
Example 7
| 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);
}
}
}
Example 8
| 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;
}
Example 9
| 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);
}
}
Example 10
| 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());
}*/
}
Example 11
| 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;
}
Example 12
| 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;
}
Example 13
| 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;
}
}
}
}
}
}
Example 14
| 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;
}
Example 15
| 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;
}
}
}
}
}
}
Example 16
| Source File: AbstractSSFRowMapperAdapter.java From onetwo with Apache License 2.0 | 4 votes |
|
@Override
public int getNumberOfRows(Sheet table) {
return table.getPhysicalNumberOfRows();
}
Example 17
| 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;
}
Example 18
| 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");
}
