Java AreaReference类代码示例

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
private List<Object> getReferenceList(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
	List<Object> references = new LinkedList<>();

	AreaReference areaRef = new AreaReference(validationConstraint.getFormula1());
	CellReference[] cellRefs = areaRef.getAllReferencedCells();
	for (CellReference cellRef : cellRefs) {
		XSSFSheet referenceListSheet;
		if (cellRef.getSheetName() != null) {
			referenceListSheet = sheet.getWorkbook().getSheet(cellRef.getSheetName());
		} else {
			referenceListSheet = sheet;
		}
		Row row = referenceListSheet.getRow(cellRef.getRow());
		if (row != null) {
			Cell cell = row.getCell(cellRef.getCol());
			if (cell != null) {
				Object cellValue = CellValueParser.getCellValue(cell);
				references.add(cellValue);
			}
		}
	}
	return references;
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
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() ) );
        }
	}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
/**
 * 指定した範囲の名前を登録する。
 * <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);
    nameObj.setRefersToFormula(areaRef.formatAsString());
    
    return nameObj;
    
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
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() == HSSFCell.CELL_TYPE_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);
        }
    }
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
/**
 * 名前の範囲の形式を組み立てる。
 * <code>シート名!$A$1:$A:$5</code>
 * @param sheetName シート名
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 * @return
 */
public static AreaReference buildNameArea(final String sheetName,
        final Point startPosition, final Point endPosition) {
    
    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);
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
public static CellReference[] getRangeContent(HSSFWorkbook workbook, String rangeName) {
    AreaReference areaForRange = getAreaForRange(workbook, rangeName);
    if (areaForRange == null) {
        return null;
    }

    return areaForRange.getAllReferencedCells();
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
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);
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
public boolean isOverlapped(Name name) {
	String ref = name.getRefersToFormula();
	int idx = ref.indexOf('!');
	if (idx != -1) {
		ref = ref.substring(idx + 1);
	}
	AreaReference area = new AreaReference(ref);
	CellReference topLeft = area.getFirstCell();
	CellReference bottomRight = area.getLastCell();
	CellRangeAddress cra = new CellRangeAddress(
		topLeft.getRow(), bottomRight.getRow(), 
		topLeft.getCol(), bottomRight.getCol()
	);
	return isOverlapped(cra);
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
private List<String> getNames(XSSFWorkbook workbook, CTStrRef strRef) {
	List<String> ret = new ArrayList<String>();
	try {
		CellReference[] cells = new AreaReference(strRef.getF()).getAllReferencedCells();
		Sheet sheet = workbook.getSheet(cells[0].getSheetName());
		if (sheet == null) {
			throw new Exception(strRef.getF());
		}
		int idx = 0;
		NameInfo[] names = new NameInfo[cells.length];
		for (CellReference ref : cells) {
			names[idx++] = new NameInfo(NameInfo.TYPE_NAME, ExcelUtils.pointToName(ref.getCol(), ref.getRow()));
			String str = null;
			Row row = sheet.getRow(ref.getRow());
			if (row != null) {
				Cell cell = row.getCell(ref.getCol());
				if (cell != null) {
					str = cell.getStringCellValue();
				}
			}
			if (str == null) {
				str = "";
			}
			ret.add(str);
		}
		addNameInfo(names);
	} catch (Exception e) {
		e.printStackTrace();
		ret.clear();
		if (strRef.isSetStrCache()) {
			CTStrData data = strRef.getStrCache();
			for (int i=0; i<data.sizeOfPtArray(); i++) {
				CTStrVal pt = data.getPtArray(i);
				ret.add(pt.getV());
			}
		}
	}
	return ret;
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
private List<Double> getValues(XSSFWorkbook workbook, CTNumRef numRef) {
	List<Double> ret = new ArrayList<Double>();
	try {
		CellReference[] cells = new AreaReference(numRef.getF()).getAllReferencedCells();
		Sheet sheet = workbook.getSheet(cells[0].getSheetName());
		if (sheet == null) {
			throw new Exception(numRef.getF());
		}
		int idx = 0;
		NameInfo[] names = new NameInfo[cells.length];
		for (CellReference ref : cells) {
			names[idx++] = new NameInfo(NameInfo.TYPE_VALUE, ExcelUtils.pointToName(ref.getCol(), ref.getRow()));
			double d = 0.0;
			Row row = sheet.getRow(ref.getRow());
			if (row != null) {
				Cell cell = row.getCell(ref.getCol());
				if (cell != null) {
					d = cell.getNumericCellValue();
				}
			}
			ret.add(d);
		}
		addNameInfo(names);
	} catch (Exception e) {
		e.printStackTrace();
		ret.clear();
		if (numRef.isSetNumCache()) {
			CTNumData data = numRef.getNumCache();
			for (int i=0; i<data.sizeOfPtArray(); i++) {
				CTNumVal pt = data.getPtArray(i);
				ret.add(Double.parseDouble(pt.getV()));
			}
		}
	}
	return ret;
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
public static CellReference getCellReference(Workbook wb, String ref) {
    Name name = wb.getName(ref);
    if(name != null)
        ref = name.getRefersToFormula();
    
    if(ref.indexOf(':') > 0) {
        AreaReference aRef = new AreaReference(ref);
        return aRef.getFirstCell();
    } else {
        return new CellReference(ref);
    }
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
/**
 * @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(String range, Class... columnTypes) {

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

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

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

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

    for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
        Row row = sheet.getRow(firstRow + rowNum);
        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];
            }

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

    }

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

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
public Area(AreaReference areaReference) {
    topLeft = new Cell(areaReference.getFirstCell());
    bottomRight = new Cell(areaReference.getLastCell());
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
public AreaReference toAreaReference() {
    return new AreaReference(topLeft.toCellReference(), bottomRight.toCellReference());
}
 

import org.apache.poi.ss.util.AreaReference; //导入依赖的package包/类
/**
 * 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);
        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<SheetRange>();
                        list.add(sheetRange);
                        mergeRegionsForRangeNames.put(name, list);
                    } else {
                        mergeRegionsForRangeNames.get(name).add(sheetRange);
                    }
                }
            }
        }
    }
}