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

private List<Match> importFromInputStream(InputStream inp) throws IOException, InvalidFormatException {
	final List<Match> matches = new ArrayList<>();

	try (Workbook wb = WorkbookFactory.create(inp)) {
		Sheet sheet = wb.getSheetAt(0);
		for (Row row : sheet) {
			if (row.getRowNum() == 0) {
				continue;
			}
			Match match = convertRowToMatch(row);
			if (match != null) {
				matches.add(match);
			}
		}

		return matches;
	}
}
 

public int parseRow(ETSheetContext sheetContext, Row row){
		for(ETRowDirective d : rowDirectives.values()){
			ETRowContext rowContext = sheetContext.new ETRowContext(row);
			if(d.isMatch(rowContext) ){
				logger.info("match directive[{}], executing...", d.getName());
				try {
					if(d.excecute(rowContext))
						return rowContext.getLastRownumbAfterExecuteTag();
				} catch (Exception e) {
					throw new RuntimeException("execute directive["+d.getName()+"] error:"+e.getMessage(), e);
				}
			}
		}
		ExcelUtils.parseCommonRow(row, sheetContext.getValueProvider());
//		ExcelUtils.copyRow(sheetContext.getSheet(), newRow, repeateRow.getRow());
		return row.getRowNum();
	}
 

/***
 * 向List里面继续添加元素
 * 
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {
	Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(), object.getClass()).invoke(object, new Object[] {});
	Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
	String picId;
	boolean isUsed = false;// 是否需要加上这个对象
	for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);
		String titleString = (String) titlemap.get(i);
		if (param.getExcelParams().containsKey(titleString)) {
			if (param.getExcelParams().get(titleString).getType() == 2) {
				picId = row.getRowNum() + "_" + i;
				saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
			} else {
				saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
			}
			isUsed = true;
		}
	}
	if (isUsed) {
		collection.add(entity);
	}
}
 

@Test
public void testNullValueType() throws Exception {
  try(
      InputStream is = new FileInputStream(new File("src/test/resources/null_celltype.xlsx"));
      Workbook wb = StreamingReader.builder().open(is);
  ) {
    for(Row r : wb.getSheetAt(0)) {
      for(Cell cell : r) {
        if(r.getRowNum() == 0 && cell.getColumnIndex() == 8) {
          assertEquals(NUMERIC, cell.getCellType());
          assertEquals("8:00:00", cell.getStringCellValue());
        }
      }
    }
  }
}
 

private void checkFieldLength(Convert cfAnn, Cell cell, Field field, Row row) throws Exception {

        if (cfAnn.minLength() > -1) {
            if ((int) cell.getNumericCellValue() < cfAnn.minLength()) {
                throw new Exception("in row " + row.getRowNum() + ", " + field.getName() + " field too short " + "(" + (int) cell.getNumericCellValue() + ")" + " min length : " + cfAnn.minLength());
            }
        }
        if (cfAnn.maxLength() > -1) {
            if ((int) cell.getNumericCellValue() > cfAnn.maxLength()) {
                throw new Exception("in row " + row.getRowNum() + " " + field.getName() + " too long " + "(" + (int) cell.getNumericCellValue() + ")" + " max length : " + cfAnn.maxLength());
            }
        }
    }
 

protected int lastRow(Sheet sheet) {
	int max = 0;
	for(Row row : sheet) {
		max = row.getRowNum();
	}
	return max;
}
 

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 TableRange findTableArea(Sheet sheet) {
  // find first row and column with contents
  int row1 = -1;
  int row2 = -1;
  TableRange lastRowArea = null;
  for (Row row : sheet) {
    TableRange rowArea = findRowArea(row);
    if (lastRowArea == null && rowArea != null) {
      if (row1 < 0) {
        lastRowArea = rowArea;
        row1 = row.getRowNum();
        row2 = row1;
      }
    } else if (lastRowArea != null && rowArea == null) {
      if (row2 > row1) {
        break;
      } else {
        row1 = -1;
      }
    } else if (lastRowArea == null && rowArea == null) {
      row1 = -1;
    } else if (rowArea.startColumn < lastRowArea.startColumn
        || rowArea.endColumn > lastRowArea.endColumn) {
      lastRowArea = null;
      row2 = -1;
    } else {
      row2 = row.getRowNum();
    }
  }
  return row1 >= 0 && lastRowArea != null
      ? new TableRange(row1, row2, lastRowArea.startColumn, lastRowArea.endColumn)
      : null;
}
 

public static void removeCellRange(Row row){
	Sheet sheet = row.getSheet();
	for(Cell cell : row){
		for(int i=0; i< sheet.getNumMergedRegions(); i++){
			CellRangeAddress cr = sheet.getMergedRegion(i);
			if(cr.getFirstRow()==row.getRowNum() && cr.getFirstColumn()==cell.getColumnIndex()){
				sheet.removeMergedRegion(i);
			}
		}
	}
}
 

/***
 * 向List里面继续添加元素
 * 
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row,
                             Map<Integer, String> titlemap, String targetId,
                             Map<String, PictureData> pictures, ImportParams params)
                                                                                    throws Exception {
    Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(),
        object.getClass()).invoke(object, new Object[] {});
    Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
    String picId;
    boolean isUsed = false;// 是否需要加上这个对象
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == 2) {
                picId = row.getRowNum() + "_" + i;
                saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}
 

/**
 * Remove a row from this sheet.  All cells contained in the row are removed as well
 *
 * @param row representing a row to remove.
 */
@Override
public void removeRow(Row row) {
    HSSFRow hrow = (HSSFRow) row;
    if (row.getSheet() != this) {
        throw new IllegalArgumentException("Specified row does not belong to this sheet");
    }
    for (Cell cell : row) {
        HSSFCell xcell = (HSSFCell) cell;
        if (xcell.isPartOfArrayFormulaGroup()) {
            String msg = "Row[rownum=" + row.getRowNum() + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
            xcell.notifyArrayFormulaChanging(msg);
        }
    }

    if (_rows.size() > 0) {
        Integer key = Integer.valueOf(row.getRowNum());
        HSSFRow removedRow = _rows.remove(key);
        if (removedRow != row) {
            //should not happen if the input argument is valid
            throw new IllegalArgumentException("Specified row does not belong to this sheet");
        }
        if (hrow.getRowNum() == getLastRowNum()) {
            _lastrow = findLastRow(_lastrow);
        }
        if (hrow.getRowNum() == getFirstRowNum()) {
            _firstrow = findFirstRow(_firstrow);
        }
        _sheet.removeRow(hrow.getRowRecord());
    }
}
 

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

@Test
public void testStringCellValue() throws Exception {
  try(
      InputStream is = new FileInputStream(new File("src/test/resources/blank_cell_StringCellValue.xlsx"));
      Workbook wb = StreamingReader.builder().open(is);
  ) {
    for(Row r : wb.getSheetAt(0)) {
      if(r.getRowNum() == 1) {
        assertEquals("", r.getCell(1).getStringCellValue());
        assertEquals("", r.getCell(1).getRichStringCellValue().getString());
      }
    }
  }
}
 

private TableRange findTableArea(Sheet sheet) {
  // find first row and column with contents
  int row1 = -1;
  int row2 = -1;
  TableRange lastRowArea = null;
  for (Row row : sheet) {
    TableRange rowArea = findRowArea(row);
    if (lastRowArea == null && rowArea != null) {
      if (row1 < 0) {
        lastRowArea = rowArea;
        row1 = row.getRowNum();
        row2 = row1;
      }
    } else if (lastRowArea != null && rowArea == null) {
      if (row2 > row1) {
        break;
      } else {
        row1 = -1;
      }
    } else if (lastRowArea == null && rowArea == null) {
      row1 = -1;
    } else if (rowArea.startColumn < lastRowArea.startColumn
        || rowArea.endColumn > lastRowArea.endColumn) {
      lastRowArea = null;
      row2 = -1;
    } else {
      row2 = row.getRowNum();
    }
  }
  return row1 >= 0 && lastRowArea != null
      ? new TableRange(row1, row2, lastRowArea.startColumn, lastRowArea.endColumn)
      : null;
}
 

/**
 * 图片类型的Cell
 * 
 * @param patriarch
 * 
 * @param entity
 * @param row
 * @param i
 * @param string
 * @param obj
 * @param workbook
 * @throws Exception
 */
private static void createImageCell(Drawing patriarch,
		ExcelExportEntity entity, Row row, int i, String field,
		Object obj, Workbook workbook) throws Exception {
	if(StringUtils.isEmpty(field)){return;}
	row.setHeight((short) (50 * entity.getHeight()));
	row.createCell(i);
	ClientAnchor anchor = workbook instanceof HSSFWorkbook?
			new HSSFClientAnchor(0, 0, 0, 0, (short) i,
			row.getRowNum(), (short) (i + 1), row.getRowNum() + 1):
				new XSSFClientAnchor(0, 0, 0, 0, (short) i,
						row.getRowNum(), (short) (i + 1), row.getRowNum() + 1);
	if (entity.getExportImageType() == 1) {
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		BufferedImage bufferImg;
		try {
			String path = ExcelExportOfTemplateUtil.class.getClassLoader()
					.getResource("") + field;
			path = path.replace("WEB-INF/classes/", "");
			path = path.replace("file:/", "");
			bufferImg = ImageIO.read(new File(path));
			ImageIO.write(
					bufferImg,
					field.substring(field.indexOf(".") + 1,
							field.length()), byteArrayOut);
			patriarch.createPicture(
					anchor,
					row.getSheet()
							.getWorkbook()
							.addPicture(byteArrayOut.toByteArray(),
									Workbook.PICTURE_TYPE_JPEG));
		} catch (IOException e) {
			e.printStackTrace();
		}
	} else {
		byte[] value = (byte[]) (entity.getGetMethods() != null ? getFieldBySomeMethod(
				entity.getGetMethods(), obj) : entity.getGetMethod()
				.invoke(obj, new Object[] {}));
		if (value != null) {
			patriarch.createPicture(anchor, row.getSheet().getWorkbook()
					.addPicture(value, Workbook.PICTURE_TYPE_JPEG));
		}
	}

}
 

public static void copyRow(Sheet worksheet, Row newRow, Row sourceRow) {
Workbook workbook = worksheet.getWorkbook();
      for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
          Cell oldCell = sourceRow.getCell(i);
          Cell newCell = newRow.createCell(i);

          if (oldCell == null) {
              newCell = null;
              continue;
          }

          CellStyle newCellStyle = workbook.createCellStyle();
          newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
          newCell.setCellStyle(newCellStyle);

          if (oldCell.getCellComment() != null) {
              newCell.setCellComment(oldCell.getCellComment());
          }

          if (oldCell.getHyperlink() != null) {
              newCell.setHyperlink(oldCell.getHyperlink());
          }

          newCell.setCellType(oldCell.getCellType());

          switch (oldCell.getCellType()) {
              case Cell.CELL_TYPE_BLANK:
                  newCell.setCellValue(oldCell.getStringCellValue());
                  break;
              case Cell.CELL_TYPE_BOOLEAN:
                  newCell.setCellValue(oldCell.getBooleanCellValue());
                  break;
              case Cell.CELL_TYPE_ERROR:
                  newCell.setCellErrorValue(oldCell.getErrorCellValue());
                  break;
              case Cell.CELL_TYPE_FORMULA:
                  newCell.setCellFormula(oldCell.getCellFormula());
                  break;
              case Cell.CELL_TYPE_NUMERIC:
                  newCell.setCellValue(oldCell.getNumericCellValue());
                  break;
              case Cell.CELL_TYPE_STRING:
                  newCell.setCellValue(oldCell.getRichStringCellValue());
                  break;
          }
      }

      for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
          CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
          if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
              CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                      (newRow.getRowNum() +
                              (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                      )),
                      cellRangeAddress.getFirstColumn(),
                      cellRangeAddress.getLastColumn());
              worksheet.addMergedRegion(newCellRangeAddress);
          }
      }
  }
 

public int getRow() {
  Row row = cell.getRow();
  return row.getRowNum();
}
 

public ForeachRowInfo(Row row) {
	super();
	this.originRownum = row.getRowNum();
	this.row = row;
}
 

public int getRow() {
  Row row = cell.getRow();
  return row.getRowNum();
}
 

/**
 * Call the macro to set Excel data validation
 *
 * @param field    Current field
 * @param row      Current row
 * @param colIndex Current col index
 */
private void addValid(Field field, Row row, int colIndex, Map<String, String[]> boxValues) {
    ExcelDropdownBox ev = field.getAnnotation(ExcelDropdownBox.class);
    ExcelDateValid dv = field.getAnnotation(ExcelDateValid.class);
    ExcelNumericValid nv = field.getAnnotation(ExcelNumericValid.class);
    ExcelCustomValid ecv = field.getAnnotation(ExcelCustomValid.class);
    ExcelRepeatValid epv = field.getAnnotation(ExcelRepeatValid.class);
    int firstRow = row.getRowNum() + 1;
    if (epv != null) {
        ExcelUtils.addRepeatValid(this.context.getSheet(), firstRow, epv.rows() == 0 ? firstRow : epv.rows() + firstRow - 1, colIndex, epv.showErrorBox(), epv.rank(),
                epv.errorTitle(), epv.errorContent());
        return;
    }
    if (ev != null) {
        if ("".equals(ev.link())) {
            ExcelUtils.addDropdownBox(ev.combobox(), ev.showErrorBox(), ev.rank(), ev.errorTitle(), ev.errorContent(), this.context.getWorkbook(), this.context.getSheet(),
                    firstRow, ev.rows() == 0 ? firstRow : ev.rows() + firstRow - 1, colIndex, boxValues == null ? null : boxValues.get(field.getName()));
        } else {
            List<ExcelWriteListener> dropdownListeners = this.context.getWriteListenerCache().get(ExcelCascadingDropdownBoxListener.class);
            if (dropdownListeners == null) {
                return;
            }
            dropdownListeners.forEach(e -> ((ExcelCascadingDropdownBoxListener) e)
                    .addCascadingDropdownBox(ev, this.context.getWorkbook(), this.context.getSheet(), firstRow, ev.rows() == 0 ? firstRow : ev.rows() + firstRow - 1, colIndex, field));
        }
        return;
    }
    if (dv != null) {
        ExcelUtils.addDateValid(dv.operatorType(), dv.expr1(), dv.expr2(), dv.pattern(), this.context.getSheet(), firstRow, dv.rows() == 0 ? firstRow : dv.rows() + firstRow - 1,
                colIndex, dv.showErrorBox(), dv.rank(), dv.errorTitle(), dv.errorContent(), dv.showTip(), dv.tipTitle(), dv.tipContent());
        return;
    }
    if (nv != null) {
        ExcelUtils.addNumericValid(nv.validType(), nv.operatorType(), nv.expr1(), nv.expr2(), this.context.getSheet(), firstRow, nv.rows() == 0 ? firstRow : nv.rows() + firstRow - 1,
                colIndex, nv.showErrorBox(), nv.rank(), nv.errorTitle(), nv.errorContent(), nv.showTip(), nv.tipTitle(), nv.tipContent());
        return;
    }
    if (ecv != null) {
        ExcelUtils.addCustomValid(ecv.formula(), this.context.getSheet(), firstRow, ecv.rows() == 0 ? firstRow : ecv.rows() + firstRow - 1,
                colIndex, ecv.showErrorBox(), ecv.rank(), ecv.errorTitle(), ecv.errorContent());
    }
}