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

/**
 * adds a merged region of cells (hence those cells form one)
 *
 * @param region (rowfrom/colfrom-rowto/colto) to merge
 * @param validate whether to validate merged region
 * @return index of this region
 * @throws IllegalArgumentException if region contains fewer than 2 cells
 * @throws IllegalStateException if region intersects with an existing merged region
 * or multi-cell array formula on this sheet
 */
private int addMergedRegion(CellRangeAddress region, boolean validate) {
    if (region.getNumberOfCells() < 2) {
        throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
    }
    region.validate(SpreadsheetVersion.EXCEL97);

    if (validate) {
        // throw IllegalStateException if the argument CellRangeAddress intersects with
        // a multi-cell array formula defined in this sheet
        validateArrayFormulas(region);
    
        // Throw IllegalStateException if the argument CellRangeAddress intersects with
        // a merged region already in this sheet
        validateMergedRegions(region);
    }

    return _sheet.addMergedRegion(region.getFirstRow(),
            region.getFirstColumn(),
            region.getLastRow(),
            region.getLastColumn());
}
 

/**
 * Verify that no merged regions intersect another merged region in this sheet.
 *
 * @throws IllegalStateException if at least one region intersects with another merged region in this sheet
 */
private void checkForIntersectingMergedRegions() {
    final List<CellRangeAddress> regions = getMergedRegions();
    final int size = regions.size();
    for (int i=0; i < size; i++) {
        final CellRangeAddress region = regions.get(i);
        for (final CellRangeAddress other : regions.subList(i+1, regions.size())) {
            if (region.intersects(other)) {
                String msg = "The range " + region.formatAsString() +
                            " intersects with another merged region " +
                            other.formatAsString() + " in this sheet";
                throw new IllegalStateException(msg);
            }
        }
    }
}
 

/**
 * 指定した範囲の結合を解除する。
 * @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;
}
 

private void validateArrayFormulas(CellRangeAddress region) {
    // FIXME: this may be faster if it looped over array formulas directly rather than looping over each cell in
    // the region and searching if that cell belongs to an array formula
    int firstRow = region.getFirstRow();
    int firstColumn = region.getFirstColumn();
    int lastRow = region.getLastRow();
    int lastColumn = region.getLastColumn();
    for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
        HSSFRow row = getRow(rowIn);
        if (row == null) continue;
        
        for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
            HSSFCell cell = row.getCell(colIn);
            if (cell == null) continue;

            if (cell.isPartOfArrayFormulaGroup()) {
                CellRangeAddress arrayRange = cell.getArrayFormulaRange();
                if (arrayRange.getNumberOfCells() > 1 && region.intersects(arrayRange)) {
                    String msg = "The range " + region.formatAsString() + " intersects with a multi-cell array formula. " +
                            "You cannot merge cells of an array.";
                    throw new IllegalStateException(msg);
                }
            }
        }
    }

}
 

private void validateMergedRegions(CellRangeAddress candidateRegion) {
    for (final CellRangeAddress existingRegion : getMergedRegions()) {
        if (existingRegion.intersects(candidateRegion)) {
            throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() +
                    " to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ").");
        }
    }
}