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

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#getNumMergedRegions() . 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: 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 ;
}

Example 2

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

Example 3

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

Example 4

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

Example 5

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

Example 6

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;// 跳过已合并的行
		}
		
	}
}

Example 7

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

Example 8

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

Example 9

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

Example 10

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

Example 11

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

Example 12

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 13

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

Example 14

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

Example 15

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;

}

Example 16

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

Example 17

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

Example 18

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

Example 19

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

Example 20

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