org.apache.poi.ss.util.AreaReference Java Examples

protected void updateFormulas(Area templateArea, Area dependentResultArea) {
    HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName());
    HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet);

    AreaReference area = dependentResultArea.toAreaReference();
    for (CellReference cell : area.getAllReferencedCells()) {
        HSSFCell resultCell = getCellFromReference(cell, resultSheet);

        if (resultCell.getCellType() == CellType.FORMULA) {
            Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook);

            for (Ptg ptg : ptgs) {
                if (ptg instanceof AreaPtg) {
                    areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg);
                } else if (ptg instanceof RefPtg) {
                    areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg);
                }
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}
 

/**
 * 指定した範囲の名前を登録する。
 * <p>POI-3.7以上が必要。
 * <p>指定した名前が既に存在する場合は、新しい範囲に書き換える。
 * @param sheet シート
 * @param name 名前
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 * @return
 */
public static Name defineName(final Sheet sheet, final String name,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notEmpty(name, "name");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final Workbook workbook = sheet.getWorkbook();
    Name nameObj = workbook.getName(name);
    if(nameObj == null) {
        nameObj = workbook.createName();
        nameObj.setNameName(name);
    }

    final AreaReference areaRef = buildNameArea(sheet.getSheetName(), startPosition, endPosition,
            sheet.getWorkbook().getSpreadsheetVersion());
    nameObj.setRefersToFormula(areaRef.formatAsString());

    return nameObj;

}
 

/**
 *
 * @param sheetIden may be <code>null</code>
 * @param part1
 * @param part2 may be <code>null</code>
 */
private ParseNode createAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
        SimpleRangePart part2) throws FormulaParseException {
    Ptg ptg;
    if (part2 == null) {
        CellReference cr = part1.getCellReference();
        if (sheetIden == null) {
            ptg = new RefPtg(cr);
        } else {
            ptg = _book.get3DReferencePtg(cr, sheetIden);
        }
    } else {
        AreaReference areaRef = createAreaRef(part1, part2);

        if (sheetIden == null) {
            ptg = new AreaPtg(areaRef);
        } else {
            ptg = _book.get3DReferencePtg(areaRef, sheetIden);
        }
    }
    return new ParseNode(ptg);
}
 

private AreaReference createAreaRef(SimpleRangePart part1, SimpleRangePart part2) {
    if (!part1.isCompatibleForArea(part2)) {
        throw new FormulaParseException("has incompatible parts: '"
                + part1.getRep() + "' and '" + part2.getRep() + "'.");
    }
    if (part1.isRow()) {
        return AreaReference.getWholeRow(_ssVersion, part1.getRep(), part2.getRep());
    }
    if (part1.isColumn()) {
        return AreaReference.getWholeColumn(_ssVersion, part1.getRep(), part2.getRep());
    }
    return new AreaReference(part1.getCellReference(), part2.getCellReference(), _ssVersion);
}
 

public static AreaReference getAreaForRange(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    return new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
}
 

public static CellReference[] getRangeContent(HSSFWorkbook workbook, String rangeName) {
    AreaReference areaForRange = getAreaForRange(workbook, rangeName);
    if (areaForRange == null) {
        return null;
    }

    return areaForRange.getAllReferencedCells();
}
 

/**
 * 名前の範囲の形式を組み立てる。
 * <code>シート名!$A$1:$A:$5</code>
 * @param sheetName シート名
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 * @param sheetVersion シートの形式
 * @return
 */
public static AreaReference buildNameArea(final String sheetName,
        final Point startPosition, final Point endPosition, SpreadsheetVersion sheetVersion) {

    ArgUtils.notEmpty(sheetName, "sheetName");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final CellReference firstRefs = new CellReference(sheetName, startPosition.y, startPosition.x, true, true);
    final CellReference lastRefs = new CellReference(sheetName, endPosition.y, endPosition.x, true, true);

    return new AreaReference(firstRefs, lastRefs, sheetVersion);
}
 

private void validateNamedRange( Workbook workbook, int index, String name, int sheetIndex, int row1, int col1, int row2, int col2 ) {

		Name namedRange = workbook.getNameAt(index);
        assertEquals( name,namedRange.getNameName() );
        assertEquals( sheetIndex, namedRange.getSheetIndex() );
        
        AreaReference ref = new AreaReference( namedRange.getRefersToFormula() );
        
        if( ( row1 == row2 ) && ( col1 == col2 ) ) {
        	assertTrue( ref.isSingleCell() );
        	assertEquals( row1, ref.getFirstCell().getRow() );
        	assertEquals( col1, ref.getFirstCell().getCol() );
        } else {
        	assertTrue( AreaReference.isContiguous( namedRange.getRefersToFormula() ) );
	        assertEquals( row1, Math.min( ref.getFirstCell().getRow(), ref.getLastCell().getRow() ) );
	        assertEquals( col1, Math.min( ref.getFirstCell().getCol(), ref.getLastCell().getCol() ) );
	        assertEquals( row2, Math.max( ref.getFirstCell().getRow(), ref.getLastCell().getRow() ) );
	        assertEquals( col2, Math.max( ref.getFirstCell().getCol(), ref.getLastCell().getCol() ) );
        }
	}
 

public Area3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, AreaReference arearef) {
    super(arearef);
    this.externalWorkbookNumber = externalWorkbookNumber;
    this.firstSheetName = sheetName.getSheetIdentifier().getName();
    if (sheetName instanceof SheetRangeIdentifier) {
        this.lastSheetName = ((SheetRangeIdentifier)sheetName).getLastSheetIdentifier().getName();
    } else {
        this.lastSheetName = null;
    }
}
 

protected final String formatReferenceAsString() {
    CellReference topLeft = new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative());
    CellReference botRight = new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative());

    if(AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
        return (new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL97)).formatAsString();
    }
    return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
 

protected AreaPtgBase(AreaReference ar) {
    CellReference firstCell = ar.getFirstCell();
    CellReference lastCell = ar.getLastCell();
    setFirstRow(firstCell.getRow());
    setFirstColumn(firstCell.getCol() == -1 ? 0 : firstCell.getCol());
    setLastRow(lastCell.getRow());
    setLastColumn(lastCell.getCol() == -1 ? 0xFF : lastCell.getCol());
    setFirstColRelative(!firstCell.isColAbsolute());
    setLastColRelative(!lastCell.isColAbsolute());
    setFirstRowRelative(!firstCell.isRowAbsolute());
    setLastRowRelative(!lastCell.isRowAbsolute());
}
 

public Area3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, String arearef) {
    this(externalWorkbookNumber, sheetName, new AreaReference(arearef, SpreadsheetVersion.EXCEL2007));
}
 

/**
 * Create new merge regions in result sheet identically to range's merge regions from template.
 * Not support copy of frames and rules
 *
 * @param resultSheet            - result sheet
 * @param rangeName              - range name
 * @param firstTargetRangeRow    - first column of target range
 * @param firstTargetRangeColumn - first column of target range
 */
protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName,
                                int firstTargetRangeRow, int firstTargetRangeColumn) {
    int rangeNameIdx = templateWorkbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return;

    HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx);
    AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
    int column = aref.getFirstCell().getCol();
    int row = aref.getFirstCell().getRow();

    List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName);
    if (regionsList != null)
        for (SheetRange sheetRange : regionsList) {
            if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) {
                CellRangeAddress cra = sheetRange.getCellRangeAddress();
                if (cra != null) {
                    int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1;
                    int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1;

                    int regionVOffset = cra.getFirstRow() - row;
                    int regionHOffset = cra.getFirstColumn() - column;

                    CellRangeAddress newRegion = cra.copy();
                    newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn);
                    newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn);

                    newRegion.setFirstRow(regionVOffset + firstTargetRangeRow);
                    newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow);

                    boolean skipRegion = false;

                    for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) {
                        CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex);

                        if (!intersects(newRegion, mergedRegion)) {
                            continue;
                        }

                        skipRegion = true;
                    }

                    if (!skipRegion) {
                        resultSheet.addMergedRegion(newRegion);
                    }
                }
            }
        }
}
 

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

public AreaReference toAreaReference() {
    return new AreaReference(topLeft.toCellReference(), bottomRight.toCellReference(), SpreadsheetVersion.EXCEL97);
}
 

public Area(String name, AreaAlign align, AreaReference areaReference) {
    this(areaReference);
    this.name = name;
    this.align = align;
}
 

public Area(AreaReference areaReference) {
    topLeft = new Cell(areaReference.getFirstCell());
    bottomRight = new Cell(areaReference.getLastCell());
}
 

protected Area2DPtgBase(AreaReference ar) {
	super(ar);
}
 

/**
    * @deprecated use {@link #AreaPtg(AreaReference)} instead
 */
@Deprecated()
@Removal(version="3.19")
   public AreaPtg(String arearef) {
       this(new AreaReference(arearef, SpreadsheetVersion.EXCEL97));
   }
 

public AreaPtg(AreaReference arearef) {
    super(arearef);
}
 

public Area3DPtg(String arearef, int externIdx) {
	super(new AreaReference(arearef, SpreadsheetVersion.EXCEL97));
	setExternSheetIndex(externIdx);
}
 

/**
 * @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[][] {});
}
 

/**
 * {@inheritDoc}
 */
@Override
public AreaReference createAreaReference(CellReference topLeft, CellReference bottomRight) {
    return new AreaReference(topLeft, bottomRight, workbook.getSpreadsheetVersion());
}
 

/**
 * {@inheritDoc}
 */
@Override
public AreaReference createAreaReference(String reference) {
    return new AreaReference(reference, workbook.getSpreadsheetVersion());
}
 

@Override
public Ptg get3DReferencePtg(AreaReference areaRef, SheetIdentifier sheet) {
    int extIx = getSheetExtIx(sheet);
    return new Area3DPtg(areaRef, extIx);
}
 

public Area3DPtg(AreaReference arearef, int externIdx) {
	super(arearef);
	setExternSheetIndex(externIdx);
}
 

public Area3DPxg(SheetIdentifier sheetName, AreaReference arearef) {
    this(-1, sheetName, arearef);
}
 

public Area3DPxg(SheetIdentifier sheetName, String arearef) {
    this(sheetName, new AreaReference(arearef, SpreadsheetVersion.EXCEL2007));
}
 

/**
 * Produce the appropriate Ptg for a 3d area reference
 */
Ptg get3DReferencePtg(AreaReference area, SheetIdentifier sheet);
 

/**
 * Creates an AreaReference.
 *
 * @param reference cell reference
 * @return an AreaReference instance
 */
AreaReference createAreaReference(String reference);