Java Code Examples for org.apache.poi.ss.util.CellRangeAddress#isInRange()

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

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

private void fixLeftBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) {
    if (columnIndex > 1) {
        fixLeftCell(sheet, resultCell.getRowIndex(), columnIndex - 1, cellStyle);
        // fix merged left border
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
                int firstRow = mergedRegion.getFirstRow();
                int lastRow = mergedRegion.getLastRow();

                for (int leftIndex = firstRow; leftIndex <= lastRow; leftIndex++) {
                    fixLeftCell(sheet, leftIndex, columnIndex - 1, cellStyle);
                }
                break;
            }
        }
    }
}
 

private void fixRightBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) {
    fixRightCell(sheet, resultCell.getRowIndex(), columnIndex + 1, cellStyle);
    // fix merged right border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int regionWidth = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;

            for (int rightIndex = firstRow; rightIndex <= lastRow; rightIndex++) {
                fixRightCell(sheet, rightIndex, columnIndex + regionWidth, cellStyle);
            }
            break;
        }
    }
}
 

private void fixUpBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) {
    if (rowIndex > 0) {
        // fix simple up border
        fixUpCell(sheet, rowIndex - 1, columnIndex, cellStyle);
        // fix merged up border
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
                int firstColumn = mergedRegion.getFirstColumn();
                int lastColumn = mergedRegion.getLastColumn();

                for (int upIndex = firstColumn; upIndex <= lastColumn; upIndex++) {
                    fixUpCell(sheet, rowIndex - 1, upIndex, cellStyle);
                }
                break;
            }
        }
    }
}
 

private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) {
    // fix simple down border
    fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle);
    // fix merged down border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;

            for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) {
                fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle);
            }
            break;
        }
    }
}
 

private CellRangeAddress getRangeIfMerged(XSSFCell cell, CellRangeAddress[] mergedRanges) {
    for (int i = 0; i < mergedRanges.length; i++) {
        CellRangeAddress range = mergedRanges[i];
        if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex()))
            return range;
    }
    return null;
}
 

/**
 * 結合を考慮したセルのコメントを取得する。
 * @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;
    
}
 

/**
 * 指定したセルのアドレスの結合情報を取得する。
 * @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;
}
 

/**
 * @param ref
 * @return true if this rule evaluates to true for the given cell
 */
/* package */ boolean matches(CellReference ref) {
    // first check that it is in one of the regions defined for this format
    CellRangeAddress region = null;
    for (CellRangeAddress r : regions) {
        if (r.isInRange(ref)) {
            region = r;
            break;
        }
    }
    
    if (region == null) {
        // cell not in range of this rule
        return false;
    }
    
    final ConditionType ruleType = getRule().getConditionType();
    
    // these rules apply to all cells in a region. Specific condition criteria
    // may specify no special formatting for that value partition, but that's display logic
    if (ruleType.equals(ConditionType.COLOR_SCALE)
        || ruleType.equals(ConditionType.DATA_BAR)
        || ruleType.equals(ConditionType.ICON_SET)) {
       return true; 
    }
    
    Cell cell = null;
    final Row row = sheet.getRow(ref.getRow());
    if (row != null) {
        cell = row.getCell(ref.getCol());
    }
    
    if (ruleType.equals(ConditionType.CELL_VALUE_IS)) {
        // undefined cells never match a VALUE_IS condition
        if (cell == null) return false;
        return checkValue(cell, region);
    }
    if (ruleType.equals(ConditionType.FORMULA)) {
        return checkFormula(ref, region);
    }
    if (ruleType.equals(ConditionType.FILTER)) {
        return checkFilter(cell, ref, region);
    }
    
    // TODO: anything else, we don't handle yet, such as top 10
    return false;
}
 

/**
 *
 * @param source
 *            the sheet containing the data.
 * @param rowNum
 *            the num of the row to copy.
 * @param cellNum
 *            the num of the cell to copy.
 * @return the CellRangeAddress created.
 */
public static CellRangeAddress getMergedRegion(HSSFSheet source, int rowNum, short cellNum) {
	for (int i = 0; i < source.getNumMergedRegions(); i++) {
		CellRangeAddress merged = source.getMergedRegion(i);
		if (merged.isInRange(rowNum, cellNum)) {
			return merged;
		}
	}
	return null;
}