Java Code Examples for org.apache.poi.ss.usermodel.Name#getRefersToFormula()

The following examples show how to use org.apache.poi.ss.usermodel.Name#getRefersToFormula() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: AbstractHandler.java From birt with Eclipse Public License 1.0

6 votes

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

Example 2

Source File: HyperlinksTest.java From birt with Eclipse Public License 1.0

5 votes

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

Example 3

Source File: Util.java From Knowage-Server with GNU Affero General Public License v3.0

4 votes

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

Example 4

Source File: Excel.java From objectlabkit with Apache License 2.0

4 votes

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

    return namedCell.getRefersToFormula();
}