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

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

private static boolean isMergedRegion(Sheet sheet, int rowNum, int columnNum) {
    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 (rowNum >= firstRow && rowNum <= lastRow) {
            if (columnNum >= firstColumn && columnNum <= lastColumn) {
                return true;
            }
        }
    }
    return false;
}
 

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

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

/**
 * 判断合并了行
 * @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;
}
 

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

private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
    // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
    AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
    Ptg[] ptgs = { aptg, };

    if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
        return cra;
    }
    Ptg ptg0 = ptgs[0];
    if (ptg0 instanceof AreaPtg) {
        AreaPtg bptg = (AreaPtg) ptg0;
        return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
    }
    if (ptg0 instanceof AreaErrPtg) {
        return null;
    }
    throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
}
 

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

/**
 *
 * @param rule
 * @return unmodifiable List of all cells in the rule's region matching the rule's condition
 */
public List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule) {
    final List<Cell> cells = new ArrayList<Cell>();
    final Sheet sheet = rule.getSheet();
    
    for (CellRangeAddress region : rule.getRegions()) {
        for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
            final Row row = sheet.getRow(r);
            if (row == null) {
                continue; // no cells to check
            }
            for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) {
                final Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                }
                
                List<EvaluationConditionalFormatRule> cellRules = getConditionalFormattingForCell(cell);
                if (cellRules.contains(rule)) {
                    cells.add(cell);
                }
            }
        }
    }
    return Collections.unmodifiableList(cells);
}
 

/**
 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
 */
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;
}
 

/**
 * 結合を考慮してセルの罫線（右部）を取得する。
 *
 * @param cell セル
 * @return {@literal BorderStyle}
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static BorderStyle getBorderLeft(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRegion = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());

    final Cell target;
    if(mergedRegion == null) {
        // 結合されていない場合
        target = cell;

    } else {
        if(mergedRegion.getFirstColumn() == cell.getColumnIndex()) {
            // 引数のCellが左部のセルの場合
            target = cell;
        } else {
            target = getCell(sheet, mergedRegion.getFirstColumn(), cell.getRowIndex());
        }

    }

    final CellStyle style = target.getCellStyle();
    if(style == null) {
        return BorderStyle.NONE;
    } else {
        return style.getBorderLeftEnum();
    }

}
 

protected CellRangeAddress getColspanRowspanByExcel(int rowIndex, int colIndex) {
    CellRangeAddress result = null;
    Sheet sheet = excel.getSheet();
    int num = sheet.getNumMergedRegions();
    for (int i = 0; i < num; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        if (range.getFirstColumn() == colIndex && range.getFirstRow() == rowIndex) {
            result = range;
        }
    }
    return result;
}
 

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

}
 

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

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 static void copyMergedRegion(HSSFSheet sheet,
		List<CellRangeAddress> regionList, int rowNum) {
	for (CellRangeAddress region : regionList) {
		CellRangeAddress address = new CellRangeAddress(rowNum, rowNum,
				region.getFirstColumn(), region.getLastColumn());
		sheet.addMergedRegion(address);
	}
}
 

private void loadMapColumns(final Sheet sheet, final List<RecordHeader> headers, final List<MergedRecord> mergedRecords,
        final CellPosition beginPosition, final Class<?> recordClass, final Object record, final Configuration config, final LoadingWorkObject work) throws XlsMapperException {

    final List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(
            recordClass, work.getAnnoReader(), XlsMapColumns.class)
            .stream()
            .filter(p -> p.isReadable())
            .collect(Collectors.toList());

    for(FieldAccessor property : properties) {
        final XlsMapColumns mapAnno = property.getAnnotationNullable(XlsMapColumns.class);

        if(!Utils.isLoadCase(mapAnno.cases())) {
            continue;
        }

        Class<?> valueClass = mapAnno.valueClass();
        if(valueClass == Object.class) {
            valueClass = property.getComponentType();
        }

        // get converter (map key class)
        final CellConverter<?> converter = getCellConverter(valueClass, property, config);
        if(converter instanceof FieldFormatter) {
            work.getErrors().registerFieldFormatter(property.getName(), valueClass, (FieldFormatter<?>)converter, true);
        }

        boolean foundPreviousColumn = false;
        final Map<String, Object> map = new LinkedHashMap<>();
        for(RecordHeader headerInfo : headers){
            int hRow = beginPosition.getRow() + headerInfo.getInterval();
            if(Utils.matches(headerInfo.getLabel(), mapAnno.previousColumnName(), config)){
                foundPreviousColumn = true;
                hRow++;
                continue;
            }

            if(Utils.isNotEmpty(mapAnno.nextColumnName()) && Utils.matches(headerInfo.getLabel(), mapAnno.nextColumnName(), config)) {
                break;
            }

            if(foundPreviousColumn){
                final Cell cell = POIUtils.getCell(sheet, beginPosition.getColumn(), hRow);
                property.setMapPosition(record, CellPosition.of(cell), headerInfo.getLabel());
                property.setMapLabel(record, headerInfo.getLabel(), headerInfo.getLabel());

                property.getMapCommentSetter().ifPresent(setter -> 
                config.getCommentOperator().loadMapCellComment(setter, cell, record, headerInfo.getLabel(), property, config));
                
                CellRangeAddress mergedRange = POIUtils.getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
                if(mergedRange != null) {
                    int mergedSize =  mergedRange.getLastColumn() - mergedRange.getFirstColumn() + 1;
                    mergedRecords.add(new MergedRecord(headerInfo, mergedRange, mergedSize));
                } else {
                    mergedRecords.add(new MergedRecord(headerInfo, CellRangeAddress.valueOf(POIUtils.formatCellAddress(cell)), 1));
                }

                try {
                    final Object value = converter.toObject(cell);
                    map.put(headerInfo.getLabel(), value);
                } catch(TypeBindException e) {
                    e.setBindClass(valueClass);  // マップの項目のタイプに変更
                    work.addTypeBindError(e, cell, String.format("%s[%s]", property.getName(), headerInfo.getLabel()), headerInfo.getLabel());
                    if(!config.isContinueTypeBindFailure()) {
                        throw e;
                    }
                }
            }
        }

        if(foundPreviousColumn) {
            property.setValue(record, map);
        }
    }
}
 

public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) {

		ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn()));
		_sharedValueManager.addArrayRecord(arr);
	}
 

private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex();
    int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex();
    int step = templateRowEndIndex - templateRowStartIndex + 1;
    int rowStartIndex = sheetProcessor.getStartRowIndex();

    Set<Integer> configColIndexSet = new HashSet<Integer>();
    for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor.getFieldMapping().export().entrySet()) {
        if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) {
            continue;
        }
        for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping.getValue().entrySet()) {
            if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) {
                continue;
            }
            configColIndexSet.add(indexProcessorMapping.getKey());
        }
    }

    List<? extends DataValidation> dataValidations = sheet.getDataValidations();
    if (dataValidations != null) {
        for (DataValidation dataValidation : dataValidations) {
            if (dataValidation == null) {
                continue;
            }
            CellRangeAddressList cellRangeAddressList = dataValidation.getRegions();
            if (cellRangeAddressList == null) {
                continue;
            }

            CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
            if (cellRangeAddresses == null || cellRangeAddresses.length == 0) {
                continue;
            }

            CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList();
            boolean validationContains = false;
            for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                if (cellRangeAddress == null) {
                    continue;
                }
                if (templateRowEndIndex < cellRangeAddress.getFirstRow()
                    || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row
                    continue;
                }
                for (Integer configColIndex : configColIndexSet) {
                    if (configColIndex < cellRangeAddress.getFirstColumn()
                        || configColIndex > cellRangeAddress.getLastColumn()) {// specify column
                        continue;
                    }
                    if (templateRowStartIndex == templateRowEndIndex) {
                        newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex,
                                                                    sheet.getLastRowNum(), configColIndex);
                        validationContains = true;
                    } else {
                        int start = cellRangeAddress.getFirstRow() > templateRowStartIndex ? cellRangeAddress.getFirstRow() : templateRowStartIndex;
                        int end = cellRangeAddress.getLastRow() < templateRowEndIndex ? cellRangeAddress.getLastRow() : templateRowEndIndex;
                        long lastRow = sheet.getLastRowNum();
                        if (lastRow > end) {
                            long count = (lastRow - templateRowEndIndex) / step;
                            int i = templateRowEndIndex;
                            for (; i < count; i++) {
                                newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex,
                                                                            end + i * step, configColIndex);
                                validationContains = true;
                            }
                            long _start = start + i * step;
                            if (_start <= lastRow) {
                                long _end = end + i * step;
                                _end = _end < lastRow ? _end : lastRow;
                                newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex,
                                                                            (int) _end, configColIndex);
                                validationContains = true;
                            }
                        }
                    }
                }
            }
            if (validationContains) {
                DataValidation newDataValidation = sheet.getDataValidationHelper().createValidation(dataValidation.getValidationConstraint(),
                                                                                                    newCellRangeAddressList);
                sheet.addValidationData(newDataValidation);
            }
        }
    }
}
 

/**
 * 領域の列サイズ（横セル数）を計算します。
 *
 * @since 2.0
 * @param region 領域
 * @return 列サイズ（横セル数）。引数がnullの時は、0を返します。
 */
public static int getColumnSize(final CellRangeAddress region) {
    if(region == null) {
        return 0;
    }
    return region.getLastColumn() - region.getFirstColumn() + 1;
}