cellreference.java

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

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.hssf.util;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.record.formula.SheetNameFormatter;

/**
 *
 * @author  Avik Sengupta
 * @author  Dennis Doubleday (patch to seperateRowColumns())
 */
public final class CellReference {
	/**
	 * Used to classify identifiers found in formulas as cell references or not.
	 */
	public static final class NameType {
		public static final int CELL = 1;
		public static final int NAMED_RANGE = 2;
		public static final int COLUMN = 3;
		public static final int BAD_CELL_OR_NAMED_RANGE = -1;
	}
	/** The character ($) that signifies a row or column value is absolute instead of relative */ 
	private static final char ABSOLUTE_REFERENCE_MARKER = '$';
	/** The character (!) that separates sheet names from cell references */ 
	private static final char SHEET_NAME_DELIMITER = '!';
	/** The character (') used to quote sheet names when they contain special characters */
	private static final char SPECIAL_NAME_DELIMITER = '\'';
	
	/**
	 * Matches a run of one or more letters followed by a run of one or more digits.
	 * The run of letters is group 1 and the run of digits is group 2.  
	 * Each group may optionally be prefixed with a single '$'.
	 */
	private static final Pattern CELL_REF_PATTERN = Pattern.compile("\\$?([A-Za-z]+)\\$?([0-9]+)");
	/**
	 * Matches a run of one or more letters.  The run of letters is group 1.  
	 * The text may optionally be prefixed with a single '$'.
	 */
	private static final Pattern COLUMN_REF_PATTERN = Pattern.compile("\\$?([A-Za-z]+)");
	/**
	 * Named range names must start with a letter or underscore.  Subsequent characters may include
	 * digits or dot.  (They can even end in dot).
	 */
	private static final Pattern NAMED_RANGE_NAME_PATTERN = Pattern.compile("[_A-Za-z][_.A-Za-z0-9]*");
	private static final String BIFF8_LAST_COLUMN = "IV";
	private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
	private static final String BIFF8_LAST_ROW = String.valueOf(0x10000);
	private static final int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.length();

	private final int _rowIndex;
	private final int _colIndex;
	private final String _sheetName;
	private final boolean _isRowAbs;
	private final boolean _isColAbs;

	/**
	 * Create an cell ref from a string representation.  Sheet names containing special characters should be
	 * delimited and escaped as per normal syntax rules for formulas.
	 */
	public CellReference(String cellRef) {
		String[] parts = separateRefParts(cellRef);
		_sheetName = parts[0];
		String colRef = parts[1]; 
		if (colRef.length() < 1) {
			throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
		}
		_isColAbs = colRef.charAt(0) == '$';
		if (_isColAbs) {
			colRef=colRef.substring(1);
		}
		_colIndex = convertColStringToIndex(colRef);
		
		String rowRef=parts[2];
		if (rowRef.length() < 1) {
			throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
		}
		_isRowAbs = rowRef.charAt(0) == '$';
		if (_isRowAbs) {
			rowRef=rowRef.substring(1);
		}
		_rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
	}

	public CellReference(int pRow, int pCol) {
		this(pRow, pCol, false, false);
	}
	public CellReference(int pRow, short pCol) {
		this(pRow, pCol & 0xFFFF, false, false);
	}

	public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
		this(null, pRow, pCol, pAbsRow, pAbsCol);
	}
	public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
		// TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
		// so these checks are currently N.Q.R.
		if(pRow < -1) {
			throw new IllegalArgumentException("row index may not be negative");
		}
		if(pCol < -1) {
			throw new IllegalArgumentException("column index may not be negative");
		}
		_sheetName = pSheetName;
		_rowIndex=pRow;
		_colIndex=pCol;
		_isRowAbs = pAbsRow;
		_isColAbs=pAbsCol;
	}

	public int getRow(){return _rowIndex;}
	public short getCol(){return (short) _colIndex;}
	public boolean isRowAbsolute(){return _isRowAbs;}
	public boolean isColAbsolute(){return _isColAbs;}
	/**
	  * @return possibly <code>null</code> if this is a 2D reference.  Special characters are not
	  * escaped or delimited
	  */
	public String getSheetName(){
		return _sheetName;
	}
	
	public static boolean isPartAbsolute(String part) {
		return part.charAt(0) == ABSOLUTE_REFERENCE_MARKER;
	}
	/**
	 * takes in a column reference portion of a CellRef and converts it from
	 * ALPHA-26 number format to 0-based base 10.
	 * 'A' -> 0
	 * 'Z' -> 25
	 * 'AA' -> 26
	 * 'IV' -> 255
	 * @return zero based column index
	 */
	public static int convertColStringToIndex(String ref) {
	
		int pos = 0;
		int retval=0;
		for (int k = ref.length()-1; k >= 0; k--) {
			char thechar = ref.charAt(k);
			if (thechar == ABSOLUTE_REFERENCE_MARKER) {
				if (k != 0) {
					throw new IllegalArgumentException("Bad col ref format '" 
							+ ref + "'");
				}
				break;
			}
			// Character.getNumericValue() returns the values
			//  10-35 for the letter A-Z
			int shift = (int)Math.pow(26, pos);
			retval += (Character.getNumericValue(thechar)-9) * shift;
			pos++;
		}
		return retval-1;
	}

	/**
	 * Classifies an identifier as either a simple (2D) cell reference or a named range name
	 * @return one of the values from <tt>NameType</tt> 
	 */
	public static int classifyCellReference(String str) {
		int len = str.length();
		if (len < 1) {
			throw new IllegalArgumentException("Empty string not allowed");
		}
		char firstChar = str.charAt(0);
		switch (firstChar) {
			case ABSOLUTE_REFERENCE_MARKER:
			case '.':
			case '_':
				break;
			default:
				if (!Character.isLetter(firstChar)) {
					throw new IllegalArgumentException("Invalid first char (" + firstChar 
							+ ") of cell reference or named range.  Letter expected");
				}
		}
		if (!Character.isDigit(str.charAt(len-1))) {
			// no digits at end of str
			return validateNamedRangeName(str);
		}
		Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
		if (!cellRefPatternMatcher.matches()) {
			return validateNamedRangeName(str);
		}
		String lettersGroup = cellRefPatternMatcher.group(1);
		String digitsGroup = cellRefPatternMatcher.group(2);
		if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
			// valid cell reference
			return NameType.CELL;
		}
		// If str looks like a cell reference, but is out of (row/col) range, it is a valid
		// named range name
		// This behaviour is a little weird.  For example, "IW123" is a valid named range name
		// because the column "IW" is beyond the maximum "IV".  Note - this behaviour is version
		// dependent.  In BIFF12, "IW123" is not a valid named range name, but in BIFF8 it is.
		if (str.indexOf(ABSOLUTE_REFERENCE_MARKER) >= 0) {
			// Of course, named range names cannot have '$'
			return NameType.BAD_CELL_OR_NAMED_RANGE;
		}
		return NameType.NAMED_RANGE;
	}

	private static int validateNamedRangeName(String str) {
		Matcher colMatcher = COLUMN_REF_PATTERN.matcher(str);
		if (colMatcher.matches()) {
			String colStr = colMatcher.group(1);
			if (isColumnWithnRange(colStr)) {
				return NameType.COLUMN;
			}
		}
		if (!NAMED_RANGE_NAME_PATTERN.matcher(str).matches()) {
			return NameType.BAD_CELL_OR_NAMED_RANGE;
		}
		return NameType.NAMED_RANGE;
	}
	
	
	/**