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

Source File: ExcelUtil.java From autopoi with Apache License 2.0

6 votes

/**
 * 获取合并单元格的值
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
    int sheetMergeCount = sheet.getNumMergedRegions();

    for(int i = 0 ; i < sheetMergeCount ; i++){
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();

        if(row >= firstRow && row <= lastRow){

            if(column >= firstColumn && column <= lastColumn){
                Row fRow = sheet.getRow(firstRow);
                Cell fCell = fRow.getCell(firstColumn);
                return getCellValue(fCell) ;
            }
        }
    }

    return null ;
}

Source File: ModifyCellService.java From cs-actions with Apache License 2.0

6 votes

public static boolean isMergedCell(final Sheet worksheet, final int rowIndex, final int columnIndex) {
    int countMRegion = worksheet.getNumMergedRegions();

    for (int i = 0; i < countMRegion; i++) {
        CellRangeAddress range = worksheet.getMergedRegion(i);
        int firstRow = range.getFirstRow();
        int firstColumn = range.getFirstColumn();

        boolean isInRange = range.isInRange(rowIndex, columnIndex);

        if (isInRange) {
            if (!(rowIndex == firstRow && columnIndex == firstColumn && isInRange)) {
                return true;
            }
        }

    }
    return false;
}

Source File: GetRowIndexByConditionService.java From cs-actions with Apache License 2.0

6 votes

public static void getMergedCell(final Sheet sheet, final int firstRowIndex, final int cIndex) {
    final int countMRegion = sheet.getNumMergedRegions();

    for (int i = 0; i < countMRegion; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        final int firstRow = range.getFirstRow();
        final int firstColumn = range.getFirstColumn();

        for (int j = firstRowIndex; j < sheet.getLastRowNum(); j++) {
            final boolean isInRange = range.isInRange(j, cIndex);

            Row row = sheet.getRow(j);
            if (row == null) {
                row = sheet.createRow(j);
            }
            Cell cell = row.getCell(cIndex);
            if (cell == null) {
                cell = row.createCell(cIndex);
            }
            if (isInRange)
                if (!(j == firstRow && cIndex == firstColumn)) {
                    cell.setCellType(CellType.ERROR);
                }
        }
    }
}

Source File: POIUtils.java From xlsmapper with Apache License 2.0

6 votes

/**
 * 指定した範囲の結合を解除する。
 * @param sheet
 * @param mergedRange
 * @return 引数で指定した結合が見つからない場合。
 */
public static boolean removeMergedRange(final Sheet sheet, final CellRangeAddress mergedRange) {
    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notNull(mergedRange, "mergedRange");

    final String mergedAddress = mergedRange.formatAsString(sheet.getSheetName(), true);

    final int num = sheet.getNumMergedRegions();
    for(int i=0; i < num; i ++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        final String rangeAddress = range.formatAsString(sheet.getSheetName(), true);
        if(rangeAddress.equals(mergedAddress)) {
            sheet.removeMergedRegion(i);
            return true;
        }
    }

    return false;
}

Source File: ExcelOperator.java From minsx-framework with Apache License 2.0

6 votes

private static Cell getMergedRegionCell(Sheet sheet, int rowNum, int columnNum) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();
        if (rowNum >= firstRow && rowNum <= lastRow) {
            if (columnNum >= firstColumn && columnNum <= lastColumn) {
                Row fRow = sheet.getRow(firstRow);
                return fRow.getCell(firstColumn);
            }
        }
    }
    return null;
}

Source File: ExcelUtil.java From danyuan-application with Apache License 2.0

6 votes

/**
 * 获取合并单元格的值
 *
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public void setMergedRegion(Sheet sheet) {
	int sheetMergeCount = sheet.getNumMergedRegions();
	
	for (int i = 0; i < sheetMergeCount; i++) {
		// 获取合并单元格位置
		CellRangeAddress ca = sheet.getMergedRegion(i);
		int firstRow = ca.getFirstRow();
		if (startReadPos - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面，则跳过。
			continue;
		}
		int lastRow = ca.getLastRow();
		int mergeRows = lastRow - firstRow;// 合并的行数
		int firstColumn = ca.getFirstColumn();
		int lastColumn = ca.getLastColumn();
		// 根据合并的单元格位置和大小，调整所有的数据行格式，
		for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
			// 设定合并单元格
			sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
			j = j + mergeRows;// 跳过已合并的行
		}
		
	}
}

Source File: ExcelUtil.java From jeasypoi with Apache License 2.0

6 votes

/**
 * 获取合并单元格的值
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
    int sheetMergeCount = sheet.getNumMergedRegions();

    for(int i = 0 ; i < sheetMergeCount ; i++){
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();

        if(row >= firstRow && row <= lastRow){

            if(column >= firstColumn && column <= lastColumn){
                Row fRow = sheet.getRow(firstRow);
                Cell fCell = fRow.getCell(firstColumn);
                return getCellValue(fCell) ;
            }
        }
    }

    return null ;
}

Source File: ExcelUtil.java From autopoi with Apache License 2.0

6 votes

/**
 * 判断指定的单元格是否是合并单元格
 * @param sheet
 * @param row 行下标
 * @param column 列下标
 * @return
 */
public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        if(row >= firstRow && row <= lastRow){
            if(column >= firstColumn && column <= lastColumn){
                return true;
            }
        }
    }
    return false;
}

Source File: ExcelUtil.java From autopoi with Apache License 2.0

6 votes

/**
 * 判断合并了行
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static boolean isMergedRow(Sheet sheet,int row ,int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        if(row == firstRow && row == lastRow){
            if(column >= firstColumn && column <= lastColumn){
                return true;
            }
        }
    }
    return false;
}

Source File: POIUtils.java From xlsmapper with Apache License 2.0

5 votes

/**
 * 指定したセルのアドレスの結合情報を取得する。
 * @since 0.5
 * @param sheet シート情報
 * @param rowIdx 行番号
 * @param colIdx 列番号
 * @return 結合していない場合nullを返す。
 */
public static CellRangeAddress getMergedRegion(final Sheet sheet, final int rowIdx, final int colIdx) {
    ArgUtils.notNull(sheet, "sheet");

    final int num = sheet.getNumMergedRegions();
    for(int i=0; i < num; i ++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(range.isInRange(rowIdx, colIdx)) {
            return range;
        }
    }

    return null;
}

Source File: ReportRunner.java From birt with Eclipse Public License 1.0

5 votes

public boolean mergedRegion( Sheet sheet, int top, int left, int bottom, int right ) {
	for( int i = 0; i < sheet.getNumMergedRegions(); ++i ) {
		CellRangeAddress curRegion = sheet.getMergedRegion(i);
		if( ( curRegion.getFirstRow() == top )
				&& ( curRegion.getFirstColumn() == left )
				&& ( curRegion.getLastRow() == bottom )
				&& ( curRegion.getLastColumn() == right ) ) {
			return true;
		}
	}
	return false;
}

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: ExcelUtils.java From onetwo with Apache License 2.0

5 votes

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

Source File: DefaultCellCommentHandler.java From xlsmapper with Apache License 2.0

5 votes

/**
 * 結合を考慮したセルのコメントを取得する。
 * @param cell 元となるセル。
 * @return コメント。コメントが設定されていなければ、nullを返す。
 */
private Comment getMergedCellComment(final Cell cell) {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        return comment;
    }
    
    final Sheet sheet = cell.getSheet();
    final int size = sheet.getNumMergedRegions();
    
    for(int i=0; i < size; i++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(!range.isInRange(cell)) {
            continue;
        }
        
        // nullでないセルを取得する。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            final Row row = sheet.getRow(rowIdx);
            if(row == null) {
                continue;
            }

            for(int colIdx=range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {
                final Cell valueCell = row.getCell(colIdx);
                if(valueCell == null) {
                    continue;
                }

                comment = valueCell.getCellComment();
                if(comment != null) {
                    return comment;
                }
            }
        }
    }
    
    return null;
    
}

Source File: HorizontalRecordsProcessor.java From xlsmapper with Apache License 2.0

4 votes

/**
 * 結合する反映が既にシート情報に存在しているかどうか判定する。
 * @param sheet シート情報
 * @param region 結合領域の情報
 * @return trueの場合、結合情報が既に存在する。
 */
private boolean isOverMergedRegion(final Sheet sheet, final CellRangeAddress region) {

    final int mergedCount = sheet.getNumMergedRegions();
    for(int i=0; i < mergedCount; i++) {
        final CellRangeAddress existsRegion = sheet.getMergedRegion(i);

        if(POIUtils.intersectsRegion(existsRegion, region) || POIUtils.intersectsRegion(region, existsRegion)) {
            return true;
        }

    }

    return false;

}

Source File: MergedRegionHelper.java From jeasypoi with Apache License 2.0

4 votes

private void getAllMergedRegion(Sheet sheet) {
	int nums = sheet.getNumMergedRegions();
	for (int i = 0; i < nums; i++) {
		handerMergedString(sheet.getMergedRegion(i).formatAsString());
	}
}

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

4 votes

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

Source File: MergedRegionHelper.java From autopoi with Apache License 2.0

4 votes

private void getAllMergedRegion(Sheet sheet) {
	int nums = sheet.getNumMergedRegions();
	for (int i = 0; i < nums; i++) {
		handerMergedString(sheet.getMergedRegion(i).formatAsString());
	}
}

Source File: ExcelUtil.java From jeasypoi with Apache License 2.0

2 votes

/**
 * 判断sheet页中是否含有合并单元格
 * @param sheet
 * @return
 */
public static boolean hasMerged(Sheet sheet) {
    return sheet.getNumMergedRegions() > 0 ? true : false;
}

Source File: ExcelUtil.java From autopoi with Apache License 2.0

2 votes

/**
 * 判断sheet页中是否含有合并单元格
 * @param sheet
 * @return
 */
public static boolean hasMerged(Sheet sheet) {
    return sheet.getNumMergedRegions() > 0 ? true : false;
}

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