You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by on...@apache.org on 2015/11/23 10:30:53 UTC
svn commit: r1715743 - in /poi/trunk/src:
java/org/apache/poi/ss/util/CellAddress.java
java/org/apache/poi/ss/util/CellReference.java
testcases/org/apache/poi/ss/util/TestCellAddress.java
Author: onealj
Date: Mon Nov 23 09:30:53 2015
New Revision: 1715743
URL: http://svn.apache.org/viewvc?rev=1715743&view=rev
Log:
bug 58637: contribution from Hannes Erven: add CellAddress class, distinct from CellReference
Added:
poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java (with props)
poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellAddress.java
- copied, changed from r1715731, poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellReference.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java
Added: poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java?rev=1715743&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java Mon Nov 23 09:30:53 2015
@@ -0,0 +1,160 @@
+/* ====================================================================
+ 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.ss.util;
+
+import java.util.Locale;
+
+/**
+ * <p>This class is a container for POI usermodel row=0 column=0 cell references.
+ * It is barely a container for these two coordinates. The implementation
+ * of the Comparable interface sorts by "natural" order top left to bottom right.</p>
+ *
+ * <p>Use <tt>CellAddress</tt> when you want to refer to the location of a cell in a sheet
+ * when the concept of relative/absolute does not apply (such as the anchor location
+ * of a cell comment). Use {@link CellReference} when the concept of
+ * relative/absolute does apply (such as a cell reference in a formula).
+ * <tt>CellAddress</tt>es do not have a concept of "sheet", while <tt>CellReference</tt>s do.</p>
+ */
+public class CellAddress implements Comparable<CellAddress> {
+ /** A constant for references to the first cell in a sheet. */
+ public static final CellAddress A1 = new CellAddress(0, 0);
+
+ private final int _row;
+ private final int _col;
+
+ /**
+ * Create a new CellAddress object.
+ *
+ * @param row Row index (first row is 0)
+ * @param column Column index (first column is 0)
+ */
+ public CellAddress(int row, int column) {
+ super();
+ this._row = row;
+ this._col = column;
+ }
+
+ /**
+ * Create a new CellAddress object.
+ *
+ * @param address a cell address in A1 format. Address may not contain sheet name or dollar signs.
+ * (that is, address is not a cell reference. Use {@link #CellAddress(CellReference)} instead if
+ * starting with a cell reference.)
+ */
+ public CellAddress(String address) {
+ int length = address.length();
+
+ int loc = 0;
+ // step over column name chars until first digit for row number.
+ for (; loc < length; loc++) {
+ char ch = address.charAt(loc);
+ if (Character.isDigit(ch)) {
+ break;
+ }
+ }
+
+ String sCol = address.substring(0,loc).toUpperCase(Locale.ROOT);
+ String sRow = address.substring(loc);
+
+ // FIXME: breaks if address contains a sheet name or dollar signs from an absolute CellReference
+ this._row = Integer.parseInt(sRow)-1;
+ this._col = CellReference.convertColStringToIndex(sCol);
+ }
+
+ /**
+ * Create a new CellAddress object.
+ *
+ * @param reference a reference to a cell
+ */
+ public CellAddress(CellReference reference) {
+ this(reference.getRow(), reference.getCol());
+ }
+
+ /**
+ * Get the cell address row
+ *
+ * @return row
+ */
+ public int getRow() {
+ return _row;
+ }
+
+ /**
+ * Get the cell address column
+ *
+ * @return column
+ */
+ public int getColumn() {
+ return _col;
+ }
+
+ /**
+ * Compare this CellAddress using the "natural" row-major, column-minor ordering.
+ * That is, top-left to bottom-right ordering.
+ *
+ * @param other
+ * @return <ul>
+ * <li>-1 if this CellAddress is before (above/left) of other</li>
+ * <li>0 if addresses are the same</li>
+ * <li>1 if this CellAddress is after (below/right) of other</li>
+ * </ul>
+ */
+ @Override
+ public int compareTo(CellAddress other) {
+ int r = this._row-other._row;
+ if (r!=0) return r;
+
+ r = this._col-other._col;
+ if (r!=0) return r;
+
+ return 0;
+ }
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if(!(o instanceof CellAddress)) {
+ return false;
+ }
+
+ CellAddress cr = (CellAddress) o;
+ return _row == cr._row
+ && _col == cr._col
+ ;
+ }
+
+ @Override
+ public int hashCode() {
+ return this._row + this._col<<16;
+ }
+
+ @Override
+ public String toString() {
+ return formatAsString();
+ }
+
+ /**
+ * Same as {@link #toString()}
+ * @return A1-style cell address string representation
+ */
+ public String formatAsString() {
+ return CellReference.convertNumToColString(this._col)+(this._row+1);
+ }
+}
Propchange: poi/trunk/src/java/org/apache/poi/ss/util/CellAddress.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java?rev=1715743&r1=1715742&r2=1715743&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java Mon Nov 23 09:30:53 2015
@@ -26,10 +26,17 @@ import org.apache.poi.ss.formula.SheetNa
import org.apache.poi.ss.usermodel.Cell;
/**
- * Common conversion functions between Excel style A1, C27 style
+ * <p>Common conversion functions between Excel style A1, C27 style
* cell references, and POI usermodel style row=0, column=0
* style references. Handles sheet-based and sheet-free references
- * as well, eg "Sheet1!A1" and "$B$72"
+ * as well, eg "Sheet1!A1" and "$B$72"</p>
+ *
+ * <p>Use <tt>CellReference</tt> when the concept of
+ * relative/absolute does apply (such as a cell reference in a formula).
+ * Use {@link CellAddress} when you want to refer to the location of a cell in a sheet
+ * when the concept of relative/absolute does not apply (such as the anchor location
+ * of a cell comment).
+ * <tt>CellReference</tt>s have a concept of "sheet", while <tt>CellAddress</tt>es do not.</p>
*/
public class CellReference {
/**
Copied: poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellAddress.java (from r1715731, poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellReference.java)
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellAddress.java?p2=poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellAddress.java&p1=poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellReference.java&r1=1715731&r2=1715743&rev=1715743&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellReference.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/util/TestCellAddress.java Mon Nov 23 09:30:53 2015
@@ -17,279 +17,94 @@
package org.apache.poi.ss.util;
-import org.apache.poi.ss.SpreadsheetVersion;
-import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.util.CellAddress;
-import junit.framework.AssertionFailedError;
-import junit.framework.TestCase;
+import org.junit.Test;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotEquals;
+import static org.junit.Assert.assertArrayEquals;
+import static org.junit.Assume.assumeTrue;
+
+import java.util.Arrays;
/**
- * Tests that the common CellReference works as we need it to.
+ * Tests that the common CellAddress works as we need it to.
* Note - some additional testing is also done in the HSSF class,
- * {@link org.apache.poi.hssf.util.TestCellReference}
+ * {@link org.apache.poi.hssf.util.TestCellAddress}
*/
-public final class TestCellReference extends TestCase {
+public final class TestCellAddress {
+ @Test
public void testConstructors() {
- CellReference cellReference;
- final String sheet = "Sheet1";
- final String cellRef = "A1";
+ CellAddress cellAddress;
+ final CellReference cellRef = new CellReference("Sheet1", 0, 0, true, true);
+ final String address = "A1";
final int row = 0;
final int col = 0;
- final boolean absRow = true;
- final boolean absCol = false;
-
- cellReference = new CellReference(row, col);
- assertEquals("A1", cellReference.formatAsString());
-
- cellReference = new CellReference(row, col, absRow, absCol);
- assertEquals("A$1", cellReference.formatAsString());
- cellReference = new CellReference(row, (short)col);
- assertEquals("A1", cellReference.formatAsString());
+ cellAddress = new CellAddress(row, col);
+ assertEquals(CellAddress.A1, cellAddress);
- cellReference = new CellReference(cellRef);
- assertEquals("A1", cellReference.formatAsString());
+ cellAddress = new CellAddress(address);
+ assertEquals(CellAddress.A1, cellAddress);
- cellReference = new CellReference(sheet, row, col, absRow, absCol);
- assertEquals("Sheet1!A$1", cellReference.formatAsString());
+ cellAddress = new CellAddress(cellRef);
+ assertEquals(CellAddress.A1, cellAddress);
}
+ @Test
public void testFormatAsString() {
- CellReference cellReference;
-
- cellReference = new CellReference(null, 0, 0, false, false);
- assertEquals("A1", cellReference.formatAsString());
-
- //absolute references
- cellReference = new CellReference(null, 0, 0, true, false);
- assertEquals("A$1", cellReference.formatAsString());
-
- //sheet name with no spaces
- cellReference = new CellReference("Sheet1", 0, 0, true, false);
- assertEquals("Sheet1!A$1", cellReference.formatAsString());
-
- //sheet name with spaces
- cellReference = new CellReference("Sheet 1", 0, 0, true, false);
- assertEquals("'Sheet 1'!A$1", cellReference.formatAsString());
- }
-
- public void testGetCellRefParts() {
- CellReference cellReference;
- String[] parts;
-
- String cellRef = "A1";
- cellReference = new CellReference(cellRef);
- assertEquals(0, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("1", parts[1]);
- assertEquals("A", parts[2]);
-
- cellRef = "AA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("1", parts[1]);
- assertEquals("AA", parts[2]);
-
- cellRef = "AA100";
- cellReference = new CellReference(cellRef);
- assertEquals(26, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("100", parts[1]);
- assertEquals("AA", parts[2]);
-
- cellRef = "AAA300";
- cellReference = new CellReference(cellRef);
- assertEquals(702, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("300", parts[1]);
- assertEquals("AAA", parts[2]);
-
- cellRef = "ZZ100521";
- cellReference = new CellReference(cellRef);
- assertEquals(26*26+25, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("100521", parts[1]);
- assertEquals("ZZ", parts[2]);
-
- cellRef = "ZYX987";
- cellReference = new CellReference(cellRef);
- assertEquals(26*26*26 + 25*26 + 24 - 1, cellReference.getCol());
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("987", parts[1]);
- assertEquals("ZYX", parts[2]);
-
- cellRef = "AABC10065";
- cellReference = new CellReference(cellRef);
- parts = cellReference.getCellRefParts();
- assertNotNull(parts);
- assertEquals(null, parts[0]);
- assertEquals("10065", parts[1]);
- assertEquals("AABC", parts[2]);
- }
-
- public void testGetColNumFromRef() {
- String cellRef = "A1";
- CellReference cellReference = new CellReference(cellRef);
- assertEquals(0, cellReference.getCol());
-
- cellRef = "AA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26, cellReference.getCol());
-
- cellRef = "AB1";
- cellReference = new CellReference(cellRef);
- assertEquals(27, cellReference.getCol());
-
- cellRef = "BA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26+26, cellReference.getCol());
-
- cellRef = "CA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26+26+26, cellReference.getCol());
-
- cellRef = "ZA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26*26, cellReference.getCol());
-
- cellRef = "ZZ1";
- cellReference = new CellReference(cellRef);
- assertEquals(26*26+25, cellReference.getCol());
-
- cellRef = "AAA1";
- cellReference = new CellReference(cellRef);
- assertEquals(26*26+26, cellReference.getCol());
-
-
- cellRef = "A1100";
- cellReference = new CellReference(cellRef);
- assertEquals(0, cellReference.getCol());
-
- cellRef = "BC15";
- cellReference = new CellReference(cellRef);
- assertEquals(54, cellReference.getCol());
- }
-
- public void testGetRowNumFromRef() {
- String cellRef = "A1";
- CellReference cellReference = new CellReference(cellRef);
- assertEquals(0, cellReference.getRow());
-
- cellRef = "A12";
- cellReference = new CellReference(cellRef);
- assertEquals(11, cellReference.getRow());
-
- cellRef = "AS121";
- cellReference = new CellReference(cellRef);
- assertEquals(120, cellReference.getRow());
- }
-
- public void testConvertNumToColString() {
- short col = 702;
- String collRef = new CellReference(0, col).formatAsString();
- assertEquals("AAA1", collRef);
-
- short col2 = 0;
- String collRef2 = new CellReference(0, col2).formatAsString();
- assertEquals("A1", collRef2);
-
- short col3 = 27;
- String collRef3 = new CellReference(0, col3).formatAsString();
- assertEquals("AB1", collRef3);
-
- short col4 = 2080;
- String collRef4 = new CellReference(0, col4).formatAsString();
- assertEquals("CBA1", collRef4);
- }
-
- public void testBadRowNumber() {
- SpreadsheetVersion v97 = SpreadsheetVersion.EXCEL97;
- SpreadsheetVersion v2007 = SpreadsheetVersion.EXCEL2007;
-
- confirmCrInRange(true, "A", "1", v97);
- confirmCrInRange(true, "IV", "65536", v97);
- confirmCrInRange(false, "IV", "65537", v97);
- confirmCrInRange(false, "IW", "65536", v97);
-
- confirmCrInRange(true, "A", "1", v2007);
- confirmCrInRange(true, "XFD", "1048576", v2007);
- confirmCrInRange(false, "XFD", "1048577", v2007);
- confirmCrInRange(false, "XFE", "1048576", v2007);
-
- if (CellReference.cellReferenceIsWithinRange("B", "0", v97)) {
- throw new AssertionFailedError("Identified bug 47312a");
- }
-
- confirmCrInRange(false, "A", "0", v97);
- confirmCrInRange(false, "A", "0", v2007);
- }
-
- public void testInvalidReference() {
- try {
- new CellReference("Sheet1!#REF!");
- fail("Shouldn't be able to create a #REF! refence");
- } catch(IllegalArgumentException e) {}
-
- try {
- new CellReference("'MySheetName'!#REF!");
- fail("Shouldn't be able to create a #REF! refence");
- } catch(IllegalArgumentException e) {}
-
- try {
- new CellReference("#REF!");
- fail("Shouldn't be able to create a #REF! refence");
- } catch(IllegalArgumentException e) {}
- }
-
- private static void confirmCrInRange(boolean expResult, String colStr, String rowStr,
- SpreadsheetVersion sv) {
- if (expResult == CellReference.cellReferenceIsWithinRange(colStr, rowStr, sv)) {
- return;
- }
- throw new AssertionFailedError("expected (c='" + colStr + "', r='" + rowStr + "' to be "
- + (expResult ? "within" : "out of") + " bounds for version " + sv.name());
- }
-
- public void testConvertColStringToIndex() {
- assertEquals(0, CellReference.convertColStringToIndex("A"));
- assertEquals(1, CellReference.convertColStringToIndex("B"));
- assertEquals(14, CellReference.convertColStringToIndex("O"));
- assertEquals(701, CellReference.convertColStringToIndex("ZZ"));
- assertEquals(18252, CellReference.convertColStringToIndex("ZZA"));
-
- assertEquals(0, CellReference.convertColStringToIndex("$A"));
- assertEquals(1, CellReference.convertColStringToIndex("$B"));
-
- try {
- CellReference.convertColStringToIndex("A$");
- fail("Should throw exception here");
- } catch (IllegalArgumentException e) {
- assertTrue(e.getMessage().contains("A$"));
- }
+ assertEquals("A1", CellAddress.A1.formatAsString());
}
- public void testConvertNumColColString() {
- assertEquals("A", CellReference.convertNumToColString(0));
- assertEquals("AV", CellReference.convertNumToColString(47));
- assertEquals("AW", CellReference.convertNumToColString(48));
- assertEquals("BF", CellReference.convertNumToColString(57));
-
- assertEquals("", CellReference.convertNumToColString(-1));
- assertEquals("", CellReference.convertNumToColString(Integer.MIN_VALUE));
- assertEquals("", CellReference.convertNumToColString(Integer.MAX_VALUE));
- assertEquals("FXSHRXW", CellReference.convertNumToColString(Integer.MAX_VALUE-1));
+ @Test
+ public void testEquals() {
+ assertEquals(new CellReference(6, 4), new CellReference(6, 4));
+ assertNotEquals(new CellReference(4, 6), new CellReference(6, 4));
+ }
+
+ @Test
+ public void testCompareTo() {
+ final CellAddress A1 = new CellAddress(0, 0);
+ final CellAddress A2 = new CellAddress(1, 0);
+ final CellAddress B1 = new CellAddress(0, 1);
+ final CellAddress B2 = new CellAddress(1, 1);
+
+ assertEquals(0, A1.compareTo(A1));
+ assertEquals(-1, A1.compareTo(B1));
+ assertEquals(-1, A1.compareTo(A2));
+ assertEquals(-1, A1.compareTo(B2));
+
+ assertEquals(1, B1.compareTo(A1));
+ assertEquals(0, B1.compareTo(B1));
+ assertEquals(-1, B1.compareTo(A2));
+ assertEquals(-1, B1.compareTo(B2));
+
+ assertEquals(1, A2.compareTo(A1));
+ assertEquals(1, A2.compareTo(B1));
+ assertEquals(0, A2.compareTo(A2));
+ assertEquals(-1, A2.compareTo(B2));
+
+ assertEquals(1, B2.compareTo(A1));
+ assertEquals(1, B2.compareTo(B1));
+ assertEquals(1, B2.compareTo(A2));
+ assertEquals(0, B2.compareTo(B2));
+
+ CellAddress[] sorted = {A1, B1, A2, B2};
+ CellAddress[] unsorted = {B1, B2, A1, A2};
+ assumeTrue(!sorted.equals(unsorted));
+ Arrays.sort(unsorted);
+ assertArrayEquals(sorted, unsorted);
+ }
+
+ @Test
+ public void testGetRow() {
+ final CellAddress addr = new CellAddress(6, 4);
+ assertEquals(6, addr.getRow());
+ }
+
+ @Test
+ public void testGetColumn() {
+ final CellAddress addr = new CellAddress(6, 4);
+ assertEquals(4, addr.getColumn());
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org