cellreference.java

来自「EXCEL read and write」· Java 代码 · 共 471 行 · 第 1/2 页

	 * Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be 
	 * interpreted as a cell reference.  Names of that form can be also used for sheets and/or
	 * named ranges, and in those circumstances, the question of whether the potential cell 
	 * reference is valid (in range) becomes important.
	 * <p/>
	 * Note - that the maximum sheet size varies across Excel versions:
	 * <p/>
	 * <blockquote><table border="0" cellpadding="1" cellspacing="0" 
	 *                 summary="Notable cases.">
	 *   <tr><th>Version&nbsp;&nbsp;</th><th>File Format&nbsp;&nbsp;</th>
	 *   	<th>Last Column&nbsp;&nbsp;</th><th>Last Row</th></tr>
	 *   <tr><td>97-2003</td><td>BIFF8</td><td>"IV" (2^8)</td><td>65536 (2^14)</td></tr>
	 *   <tr><td>2007</td><td>BIFF12</td><td>"XFD" (2^14)</td><td>1048576 (2^20)</td></tr>
	 * </table></blockquote>
	 * POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for
	 * this method:
	 * <blockquote><table border="0" cellpadding="1" cellspacing="0" 
	 *                 summary="Notable cases.">
	 *   <tr><th>Input&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</th>
	 *       <th>Result&nbsp;</th></tr>
	 *   <tr><td>"A", "1"</td><td>true</td></tr>
	 *   <tr><td>"a", "111"</td><td>true</td></tr>
	 *   <tr><td>"A", "65536"</td><td>true</td></tr>
	 *   <tr><td>"A", "65537"</td><td>false</td></tr>
	 *   <tr><td>"iv", "1"</td><td>true</td></tr>
	 *   <tr><td>"IW", "1"</td><td>false</td></tr>
	 *   <tr><td>"AAA", "1"</td><td>false</td></tr>
	 *   <tr><td>"a", "111"</td><td>true</td></tr>
	 *   <tr><td>"Sheet", "1"</td><td>false</td></tr>
	 * </table></blockquote>
	 * 
	 * @param colStr a string of only letter characters
	 * @param rowStr a string of only digit characters
	 * @return <code>true</code> if the row and col parameters are within range of a BIFF8 spreadsheet.
	 */
	public static boolean cellReferenceIsWithinRange(String colStr, String rowStr) {
		if (!isColumnWithnRange(colStr)) {
			return false;
		}
		int nDigits = rowStr.length();
		if(nDigits > BIFF8_LAST_ROW_TEXT_LEN) {
			return false; 
		}
		
		if(nDigits == BIFF8_LAST_ROW_TEXT_LEN) {
			// ASCII comparison is valid if digit count is same
			if(rowStr.compareTo(BIFF8_LAST_ROW) > 0) {
				return false;
			}
		} else {
			// apparent row has less chars than max
			// no need to check range
		}
		
		return true;
	}

	private static boolean isColumnWithnRange(String colStr) {
		int numberOfLetters = colStr.length();
		if(numberOfLetters > BIFF8_LAST_COLUMN_TEXT_LEN) {
			// "Sheet1" case etc
			return false; // that was easy
		}
		if(numberOfLetters == BIFF8_LAST_COLUMN_TEXT_LEN) {
			if(colStr.toUpperCase().compareTo(BIFF8_LAST_COLUMN) > 0) {
				return false;
			}
		} else {
			// apparent column name has less chars than max
			// no need to check range
		}
		return true;
	}

	/**
	 * Separates the row from the columns and returns an array of three Strings.  The first element
	 * is the sheet name. Only the first element may be null.  The second element in is the column 
	 * name still in ALPHA-26 number format.  The third element is the row.
	 */
	private static String[] separateRefParts(String reference) {
		
		int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
		String sheetName = parseSheetName(reference, plingPos);
		int start = plingPos+1;

		int length = reference.length();


		int loc = start;
		// skip initial dollars 
		if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
			loc++;
		}
		// step over column name chars until first digit (or dollars) for row number.
		for (; loc < length; loc++) {
			char ch = reference.charAt(loc);
			if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
				break;
			}
		}
		return new String[] {
		   sheetName,
		   reference.substring(start,loc),
		   reference.substring(loc),
		};
	}

	private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
		if(indexOfSheetNameDelimiter < 0) {
			return null;
		}
		
		boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
		if(!isQuoted) {
			return reference.substring(0, indexOfSheetNameDelimiter);
		}
		int lastQuotePos = indexOfSheetNameDelimiter-1;
		if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
			throw new RuntimeException("Mismatched quotes: (" + reference + ")");
		}

		// TODO - refactor cell reference parsing logic to one place.
		// Current known incarnations: 
		//   FormulaParser.GetName()
		//   CellReference.parseSheetName() (here)
		//   AreaReference.separateAreaRefs() 
		//   SheetNameFormatter.format() (inverse)
		
		StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
		
		for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
			char ch = reference.charAt(i);
			if(ch != SPECIAL_NAME_DELIMITER) {
				sb.append(ch);
				continue;
			}
			if(i < lastQuotePos) {
				if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
					// two consecutive quotes is the escape sequence for a single one
					i++; // skip this and keep parsing the special name
					sb.append(ch);
					continue;
				}
			}
			throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
		}
		return sb.toString();
	}

	/**
	 * Takes in a 0-based base-10 column and returns a ALPHA-26
	 *  representation.
	 * eg column #3 -> D
	 */
	protected static String convertNumToColString(int col) {
		// Excel counts column A as the 1st column, we
		//  treat it as the 0th one
		int excelColNum = col + 1;
		
		String colRef = "";
		int colRemain = excelColNum;
		
		while(colRemain > 0) {
			int thisPart = colRemain % 26;
			if(thisPart == 0) { thisPart = 26; }
			colRemain = (colRemain - thisPart) / 26;
			
			// The letter A is at 65
			char colChar = (char)(thisPart+64);
			colRef = colChar + colRef;
		}
		
		return colRef;
	}

	/**
	 *  Example return values:
	 *	<table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
	 *	  <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
	 *	  <tr><td>A1</td><td>Cell reference without sheet</td></tr>
	 *	  <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
	 *	  <tr><td>'O''Brien''s Sales'!A1'&nbsp;</td><td>Sheet name with special characters</td></tr>
	 *	</table>
	 * @return the text representation of this cell reference as it would appear in a formula.
	 */
	public String formatAsString() {
		StringBuffer sb = new StringBuffer(32);
		if(_sheetName != null) {
			SheetNameFormatter.appendFormat(sb, _sheetName);
			sb.append(SHEET_NAME_DELIMITER);
		}
		appendCellReference(sb);
		return sb.toString();
	}
	
	public String toString() {
		StringBuffer sb = new StringBuffer(64);
		sb.append(getClass().getName()).append(" [");
		sb.append(formatAsString());
		sb.append("]");
		return sb.toString();
	}

	/**
	 * Returns the three parts of the cell reference, the
	 *  Sheet name (or null if none supplied), the 1 based
	 *  row number, and the A based column letter.
	 * This will not include any markers for absolute
	 *  references, so use {@link #formatAsString()}
	 *  to properly turn references into strings. 
	 */
	public String[] getCellRefParts() {
		return new String[] {
				_sheetName,
				Integer.toString(_rowIndex+1),
				convertNumToColString(_colIndex)
		};
	}

	/**
	 * Appends cell reference with '$' markers for absolute values as required.
	 * Sheet name is not included.
	 */
	/* package */ void appendCellReference(StringBuffer sb) {
		if(_isColAbs) {
			sb.append(ABSOLUTE_REFERENCE_MARKER);
		}
		sb.append( convertNumToColString(_colIndex));
		if(_isRowAbs) {
			sb.append(ABSOLUTE_REFERENCE_MARKER);
		}
		sb.append(_rowIndex+1);
	}
}