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

/**
 * Also creates cells if they don't exist
 */
private CellRange<HSSFCell> getCellRange(CellRangeAddress range) {
    int firstRow = range.getFirstRow();
    int firstColumn = range.getFirstColumn();
    int lastRow = range.getLastRow();
    int lastColumn = range.getLastColumn();
    int height = lastRow - firstRow + 1;
    int width = lastColumn - firstColumn + 1;
    List<HSSFCell> temp = new ArrayList<HSSFCell>(height * width);
    for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
        for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
            HSSFRow row = getRow(rowIn);
            if (row == null) {
                row = createRow(rowIn);
            }
            HSSFCell cell = row.getCell(colIn);
            if (cell == null) {
                cell = row.createCell(colIn);
            }
            temp.add(cell);
        }
    }
    return SSCellRange.create(firstRow, firstColumn, height, width, temp, HSSFCell.class);
}
 

/**
 * 判断指定的单元格是否是合并单元格
 * @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;
}
 

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

/**
 * location�Ŏw�肵���s�́A�������ꂽ�̈�̈ꗗ���擾���܂�
 * 
 * @param sheet
 * @param location
 * @return
 */
public static List<CellRangeAddress> getMergedRegionList(HSSFSheet sheet,
		int rowNum) {
	List<CellRangeAddress> regionList = new ArrayList<CellRangeAddress>();

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

		int rowFrom = region.getFirstRow();
		int rowTo = region.getLastRow();

		if (rowFrom == rowNum && rowTo == rowNum) {
			regionList.add(region);
		}
	}

	return regionList;
}
 

/**
 * 判断指定的单元格是否是合并单元格
 * @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;
}
 

public static CellRangeAddress getMergedRegion(final HSSFSheet sheet, final CellLocation location) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        final CellRangeAddress region = sheet.getMergedRegion(i);

        final int rowFrom = region.getFirstRow();
        final int rowTo = region.getLastRow();

        if (rowFrom == location.r && rowTo == location.r) {
            final int colFrom = region.getFirstColumn();

            if (colFrom == location.c) {
                return region;
            }
        }
    }

    return null;
}
 

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

protected void removeMergedCell(HandlerState state, int row, int col) {
	for( int mergeNum = 0; mergeNum < state.currentSheet.getNumMergedRegions(); ++mergeNum ) {
		CellRangeAddress region = state.currentSheet.getMergedRegion(mergeNum);
		if( ( region.getFirstRow() == row ) && ( region.getFirstColumn() == col ) ) {
			state.currentSheet.removeMergedRegion(mergeNum);
			break;
		}
	}
	
	for( Iterator<Area> iter = state.rowSpans.iterator(); iter.hasNext(); ) {
		Area area = iter.next();
		Coordinate topLeft = area.getX();
		if( ( topLeft.getRow() == row ) || ( topLeft.getCol() == col ) ) {
			iter.remove();
		}
	}
}
 

private Span getSpan(HSSFSheet 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();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 

/**
 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
 */
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
	int retVal = 0;
	int sheetMergerCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergerCount; i++) {
		CellRangeAddress cra = sheet.getMergedRegion(i);
		int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
		int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
		int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
		int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
		if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
			if (cellCol >= firstCol && cellCol <= lastCol) {
				retVal = lastRow - firstRow + 1; // 得到合并的行数
				break;
			}
		}
	}
	return retVal;
}
 

protected void validateCellRange(Matcher matcher, Cell cell) {
	int desiredR1 = Integer.parseInt( matcher.group(1) );
	int desiredC1 = Integer.parseInt( matcher.group(2) );
	int desiredR2 = Integer.parseInt( matcher.group(3) );
	int desiredC2 = Integer.parseInt( matcher.group(4) );
	
	int actualR1 = cell.getRowIndex() + 1;
	int actualC1 = cell.getColumnIndex() + 1;
	int actualR2 = actualR1;
	int actualC2 = actualC1;
	
	for( int i = 0; i < cell.getSheet().getNumMergedRegions(); ++i) {
		CellRangeAddress cra = cell.getSheet().getMergedRegion(i);
		if( ( cra.getFirstRow() == cell.getRowIndex() ) && ( cra.getFirstColumn() == cell.getColumnIndex() ) ) {
			assertEquals( actualR1, actualR2 );
			assertEquals( actualC1, actualC2 );
			actualR2 = cra.getLastRow() + 1;
			actualC2 = cra.getLastColumn() + 1;
		}
	}
	assertEquals( desiredR1, actualR1 );
	assertEquals( desiredC1, actualC1 );
	assertEquals( desiredR2, actualR2 );
	assertEquals( desiredC2, actualC2 );
}
 

private void setFormRegionStyle(Sheet sheet, CellRangeAddress ca, CellStyle cs) {
	for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {
		Row row = CellUtil.getRow(i, sheet);
		for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {
			Cell cell = CellUtil.getCell(row, j);
			cell.setCellStyle(cs);
		}
	}
}
 

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

public CellRangeAddress getCellRange(ForeachRowInfo row, Cell cell){
	if(ExcelUtils.isEmpty(cellRangeList))
		return null;
	for(CellRangeAddress cr : cellRangeList){
		if(cr.getFirstRow()==row.getOriginRownum() && cr.getFirstColumn()==cell.getColumnIndex()){
			return cr;
		}
	}
	return null;
}
 

public CellRangeAddress getCellRange(int rownum){
	if(ExcelUtils.isEmpty(cellRangeList))
		return null;
	for(CellRangeAddress cr : cellRangeList){
		if(cr.getFirstRow()==rownum){
			return cr;
		}
	}
	return null;
}
 

/**
 * Shifts, grows, or shrinks the merged regions due to a row shift.
 * Merged regions that are completely overlaid by shifting will be deleted.
 *
 * @param startRow the row to start shifting
 * @param endRow   the row to end shifting
 * @param n        the number of rows to shift
 * @return an array of affected merged regions, doesn't contain deleted ones
 */
public List<CellRangeAddress> shiftMergedRegions(int startRow, int endRow, int n) {
    List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>();
    Set<Integer> removedIndices = new HashSet<Integer>();
    //move merged regions completely if they fall within the new region boundaries when they are shifted
    int size = sheet.getNumMergedRegions();
    for (int i = 0; i < size; i++) {
        CellRangeAddress merged = sheet.getMergedRegion(i);

        // remove merged region that are replaced by the shifting,
        // i.e. where the area includes something in the overwritten area
        if(removalNeeded(merged, startRow, endRow, n)) {
            removedIndices.add(i);
            continue;
        }

        boolean inStart = (merged.getFirstRow() >= startRow || merged.getLastRow() >= startRow);
        boolean inEnd = (merged.getFirstRow() <= endRow || merged.getLastRow() <= endRow);

        //don't check if it's not within the shifted area
        if (!inStart || !inEnd) {
            continue;
        }

        //only shift if the region outside the shifted rows is not merged too
        if (!merged.containsRow(startRow - 1) && !merged.containsRow(endRow + 1)) {
            merged.setFirstRow(merged.getFirstRow() + n);
            merged.setLastRow(merged.getLastRow() + n);
            //have to remove/add it back
            shiftedRegions.add(merged);
            removedIndices.add(i);
        }
    }
    
    if(!removedIndices.isEmpty()) {
        sheet.removeMergedRegions(removedIndices);
    }

    //read so it doesn't get shifted again
    for (CellRangeAddress region : shiftedRegions) {
        sheet.addMergedRegion(region);
    }
    return shiftedRegions;
}
 

/**
 * @param range either the range of the entire block to be read, or just the
 *              top row of the cells, in which case the method will stop when
 *              the first empty cell is reached in the first column
 * @param columnTypes An array of data types expected at each column.
 *                 If this array is shorter than the number of column, then the last
 *                 data type is used until the end. So if only one value is given,
 *                 then that is used for the entire block.
 */
public Object[][] readBlock(final String range, final Class<?>... columnTypes) {

    if (columnTypes == null || columnTypes.length == 0) {
        throw new ExcelException("columnTypes cannot be null / empty");
    }

    final CellRangeAddress cra = CellRangeAddress.valueOf(range);
    final AreaReference ar = new AreaReference(range, null);
    final Sheet sheet = workbook.getSheet(ar.getFirstCell().getSheetName());

    final int firstColumn = cra.getFirstColumn();
    final int firstRow = cra.getFirstRow();
    final int lastRow = cra.getLastRow();
    final int height = lastRow - firstRow + 1;
    final int width = cra.getLastColumn() - firstColumn + 1;

    List<Object> result;
    if (height == 1) {
        result = new LinkedList<>();
    } else {
        result = new ArrayList<>(height);
    }

    for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
        final Row row = sheet.getRow(firstRow + rowNum);
        final Object[] resultRow = new Object[width];
        result.add(resultRow);
        for (int colNum = 0; colNum < width; colNum++) {

            Class<?> colType;
            if (colNum < columnTypes.length - 1) {
                colType = columnTypes[colNum];
            } else {
                colType = columnTypes[columnTypes.length - 1];
            }

            final Cell cell = row.getCell(firstColumn + colNum);
            resultRow[colNum] = readCell(cell, colType);
        }

    }

    return result.toArray(new Object[][] {});
}
 

/**
 * 見出し付きのセルの情報を取得する。
 * @param anno マッピング情報が設定されているアノテーション
 * @param processCase 処理ケース
 * @return ラベルや値が設定されているセルの開始情報
 */
public Optional<LabelInfo> handle(final Annotation anno, final ProcessCase processCase) {

    final AnnotationProxy annoProxy = new AnnotationProxy(anno);

    // ラベルの位置を取得する
    final Optional<CellPosition> labelPosition = getLabelPosition(annoProxy);
    if(!labelPosition.isPresent()) {
        return Optional.empty();
    }

    final int column = labelPosition.get().getColumn();
    final int row = labelPosition.get().getRow();

    /*
     * 見出しか結合している場合を考慮する場合
     * ・結合サイズ分で補正する。
     * ・考慮しない場合は、mergedXXXSizeの値は0のまま。
     */
    int mergedRowSize = 0;
    int mergedColumnSize = 0;
    if(annoProxy.labelMerged()) {
        CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, row, column);
        if(mergedRegion != null) {
            mergedRowSize = mergedRegion.getLastRow() - mergedRegion.getFirstRow();
            mergedColumnSize = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn();
        }
    }

    int range = annoProxy.range();
    if(range < 1){
        range = 1;
    }

    // 値が設定されているセルを検索する。
    Point targetPosition = new Point();
    Cell targetCell = null;
    for(int i=0; i < range; i++){
        final int index = annoProxy.skip() + i +1;
        if(annoProxy.type() == LabelledCellType.Left) {
            targetPosition.x = column - index;
            targetPosition.y = row;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        } else if(annoProxy.type() == LabelledCellType.Right) {
            targetPosition.x = column + index + mergedColumnSize;
            targetPosition.y = row;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        } else if(annoProxy.type() == LabelledCellType.Bottom) {
            targetPosition.x = column;
            targetPosition.y = row + index + mergedRowSize;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        }

        if(POIUtils.getCellContents(targetCell, config.getCellFormatter()).length() > 0){
            break;
        }

        if(processCase.equals(ProcessCase.Save)) {
            /*
             * 書き込み時は、属性rangeの範囲を考慮しない。
             * テンプレートファイルの場合、値は空を設定しているため。
             */
            break;
        }
    }

    final LabelInfo info = new LabelInfo();
    info.valueCell = targetCell;
    info.valueAddress = CellPosition.of(targetPosition);
    info.label = POIUtils.getCellContents(POIUtils.getCell(sheet, column, row), config.getCellFormatter());

    return Optional.of(info);
}
 

/**
 * Method creates mapping [rangeName : List<CellRangeAddress>].
 * List contains all merge regions for this named range.
 * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion
 * todo: how to recognize if merge region must be copied with named range
 *
 * @param currentSheet Sheet which contains merge regions
 */
protected void initMergeRegions(HSSFSheet currentSheet) {
    int rangeNumber = templateWorkbook.getNumberOfNames();
    for (int i = 0; i < rangeNumber; i++) {
        HSSFName aNamedRange = templateWorkbook.getNameAt(i);

        String refersToFormula = aNamedRange.getRefersToFormula();
        if (!AreaReference.isContiguous(refersToFormula)) {
            continue;
        }

        AreaReference aref = new AreaReference(refersToFormula, SpreadsheetVersion.EXCEL97);
        Integer rangeFirstRow = aref.getFirstCell().getRow();
        Integer rangeFirstColumn = (int) aref.getFirstCell().getCol();
        Integer rangeLastRow = aref.getLastCell().getRow();
        Integer rangeLastColumn = (int) aref.getLastCell().getCol();

        for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) {
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j);
            if (mergedRegion != null) {
                Integer regionFirstRow = mergedRegion.getFirstRow();
                Integer regionFirstColumn = mergedRegion.getFirstColumn();
                Integer regionLastRow = mergedRegion.getLastRow();
                Integer regionLastColumn = mergedRegion.getLastColumn();

                boolean mergedInsideNamed = isMergeRegionInsideNamedRange(
                        rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn,
                        regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn);

                boolean namedInsideMerged = isNamedRangeInsideMergeRegion(
                        rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn,
                        regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn);

                if (mergedInsideNamed || namedInsideMerged) {
                    String name = aNamedRange.getNameName();
                    SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName());
                    if (mergeRegionsForRangeNames.get(name) == null) {
                        ArrayList<SheetRange> list = new ArrayList<>();
                        list.add(sheetRange);
                        mergeRegionsForRangeNames.put(name, list);
                    } else {
                        mergeRegionsForRangeNames.get(name).add(sheetRange);
                    }
                }
            }
        }
    }
}
 

private void setRepeatingRowsAndColumns(
        CellRangeAddress rowDef, CellRangeAddress colDef) {
    int sheetIndex = _workbook.getSheetIndex(this);
    int maxRowIndex = SpreadsheetVersion.EXCEL97.getLastRowIndex();
    int maxColIndex = SpreadsheetVersion.EXCEL97.getLastColumnIndex();

    int col1 = -1;
    int col2 = -1;
    int row1 = -1;
    int row2 = -1;

    if (rowDef != null) {
        row1 = rowDef.getFirstRow();
        row2 = rowDef.getLastRow();
        if ((row1 == -1 && row2 != -1) || (row1 > row2)
                || (row1 < 0 || row1 > maxRowIndex)
                || (row2 < 0 || row2 > maxRowIndex)) {
            throw new IllegalArgumentException("Invalid row range specification");
        }
    }
    if (colDef != null) {
        col1 = colDef.getFirstColumn();
        col2 = colDef.getLastColumn();
        if ((col1 == -1 && col2 != -1) || (col1 > col2)
                || (col1 < 0 || col1 > maxColIndex)
                || (col2 < 0 || col2 > maxColIndex)) {
            throw new IllegalArgumentException("Invalid column range specification");
        }
    }

    short externSheetIndex =
            _workbook.getWorkbook().checkExternSheet(sheetIndex);

    boolean setBoth = rowDef != null && colDef != null;
    boolean removeAll = rowDef == null && colDef == null;

    HSSFName name = _workbook.getBuiltInName(
            NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
    if (removeAll) {
        if (name != null) {
            _workbook.removeName(name);
        }
        return;
    }
    if (name == null) {
        name = _workbook.createBuiltInName(
                NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
    }

    List<Ptg> ptgList = new ArrayList<Ptg>();
    if (setBoth) {
        final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
        ptgList.add(new MemFuncPtg(exprsSize));
    }
    if (colDef != null) {
        Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2,
                false, false, false, false, externSheetIndex);
        ptgList.add(colArea);
    }
    if (rowDef != null) {
        Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex,
                false, false, false, false, externSheetIndex);
        ptgList.add(rowArea);
    }
    if (setBoth) {
        ptgList.add(UnionPtg.instance);
    }

    Ptg[] ptgs = new Ptg[ptgList.size()];
    ptgList.toArray(ptgs);
    name.setNameDefinition(ptgs);

    HSSFPrintSetup printSetup = getPrintSetup();
    printSetup.setValidSettings(false);
    setActive(true);
}