org.apache.poi.ss.usermodel.Name Java Examples

/**
 * 指定した範囲の名前を登録する。
 * <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;

}
 

protected void createName(HandlerState state, String bookmark, int row1, int col1, int row2, int col2 ) {
	CellReference crFirst = new CellReference( state.currentSheet.getSheetName(), row1, col1, true, true );
	CellReference crLast = new CellReference( row2, col2, true, true );
	String formula = crFirst.formatAsString() + ":" + crLast.formatAsString();

	Name name = state.currentSheet.getWorkbook().getName(bookmark);
	if( name == null ) {
		name = state.currentSheet.getWorkbook().createName();
		name.setNameName( bookmark ); 
		name.setRefersToFormula( formula );
	} else {
		String existingFormula = name.getRefersToFormula();
		try {
			name.setRefersToFormula(existingFormula + "," + formula);
		} catch( FormulaParseException ex ) {
			log.warn( 0, "Unable to add \"" + formula + "\" to name (\"" + bookmark + "\") with existing formula: " + existingFormula, ex );
		}
	}
}
 

/**
 * Adds a name (named range or user defined function) to underlying workbook's names table
 * @param functionName
 */
private void addName(String functionName) {
    final Name name = _book.createName();
    name.setFunction(true);
    name.setNameName(functionName);
    name.setSheetIndex(_sheetIndex);
}
 

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

/**
 * Not supported
 */
@Override
public Name getName(String name) {
    throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Name getNameAt(int nameIndex) {
    throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Name createName() {
    throw new UnsupportedOperationException();
}
 

@Override
public List<? extends Name> getNames(String name) {
    throw new UnsupportedOperationException();
}
 

@Override
public List<? extends Name> getAllNames() {
    throw new UnsupportedOperationException();
}
 

@Override
public void removeName(Name name) {
    throw new UnsupportedOperationException();
}
 

public String namedRangeToRangeAddress(final String namedRange) {
    final int namedCellIndex = getWorkbook().getNameIndex(namedRange);
    final Name namedCell = getWorkbook().getNameAt(namedCellIndex);

    return namedCell.getRefersToFormula();
}
 

private static void copyPrintTitle(Sheet newSheet, Sheet sheetToCopy) {
	int nbNames = sheetToCopy.getWorkbook().getNumberOfNames();
	Name name = null;
	String formula = null;

	String part1S = null;
	String part2S = null;
	String formS = null;
	String formF = null;
	String part1F = null;
	String part2F = null;
	int rowB = -1;
	int rowE = -1;
	int colB = -1;
	int colE = -1;

	for (int i = 0; i < nbNames; i++) {
		name = sheetToCopy.getWorkbook().getNameAt(i);
		if (name.getSheetIndex() == sheetToCopy.getWorkbook().getSheetIndex(sheetToCopy)) {
			if (name.getNameName().equals("Print_Titles") || name.getNameName().equals(XSSFName.BUILTIN_PRINT_TITLE)) {
				formula = name.getRefersToFormula();
				int indexComma = formula.indexOf(",");
				if (indexComma == -1) {
					indexComma = formula.indexOf(";");
				}
				String firstPart = null;
				;
				String secondPart = null;
				if (indexComma == -1) {
					firstPart = formula;
				} else {
					firstPart = formula.substring(0, indexComma);
					secondPart = formula.substring(indexComma + 1);
				}

				formF = firstPart.substring(firstPart.indexOf("!") + 1);
				part1F = formF.substring(0, formF.indexOf(":"));
				part2F = formF.substring(formF.indexOf(":") + 1);

				if (secondPart != null) {
					formS = secondPart.substring(secondPart.indexOf("!") + 1);
					part1S = formS.substring(0, formS.indexOf(":"));
					part2S = formS.substring(formS.indexOf(":") + 1);
				}

				rowB = -1;
				rowE = -1;
				colB = -1;
				colE = -1;
				String rowBs, rowEs, colBs, colEs;
				if (part1F.lastIndexOf("$") != part1F.indexOf("$")) {
					rowBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length());
					rowEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length());
					rowB = Integer.parseInt(rowBs);
					rowE = Integer.parseInt(rowEs);
					if (secondPart != null) {
						colBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length());
						colEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length());
						colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs);
						colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs);
					}
				} else {
					colBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length());
					colEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length());
					colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs);
					colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs);

					if (secondPart != null) {
						rowBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length());
						rowEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length());
						rowB = Integer.parseInt(rowBs);
						rowE = Integer.parseInt(rowEs);
					}
				}

				newSheet.getWorkbook().setRepeatingRowsAndColumns(newSheet.getWorkbook().getSheetIndex(newSheet), colB, colE, rowB - 1, rowE - 1);
			}
		}
	}
}
 

/**
 * Not supported
 */
@Override
public Name getName(String name) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public List<? extends Name> getNames(String s) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public List<? extends Name> getAllNames() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Name getNameAt(int nameIndex) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Name createName() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public void removeName(Name name) {
  throw new UnsupportedOperationException();
}
 

/**
 * As {@link #removeName(String)} is not necessarily unique
 * (name + sheet index is unique), this method is more accurate.
 *
 * @param name the name to remove.
 */
@Override
public void removeName(Name name) {
  int index = getNameIndex((HSSFName) name);
  removeName(index);
}
 

/**
 * Return the underlying workbook
 */
Name createName();