Author: nick
Date: Tue Jan 8 08:20:48 2008
New Revision: 610048
URL: http://svn.apache.org/viewvc?rev=610048&view=rev
Log:
Add a new method onto AreaReference to get all the cells referenced, not just
the corners. Includes tests for this
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Tue Jan 8 08:20:48
2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Support getting all the
cells referenced by an AreaReference, not just the corner ones</action>
<action dev="POI-DEVELOPERS" type="add">43510 - Add support for
named ranges in formulas, including non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for
hiding and un-hiding sheets, and checking their current hidden status</action>
<action dev="POI-DEVELOPERS" type="fix">44167 - Fix for
non-contiguous named ranges</action>
Modified: poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml Tue Jan 8
08:20:48 2008
@@ -1151,7 +1151,7 @@
// retrieve the cell at the named range and test its contents
AreaReference aref = new AreaReference(aNamedCell.getReference());
- CellReference[] crefs = aref.getCells();
+ CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
HSSFSheet s = wb.getSheet(crefs[i].getSheetName());
HSSFRow r = sheet.getRow(crefs[i].getRow());
@@ -1177,13 +1177,15 @@
// another for D12 to D14
AreaReference[] arefs =
AreaReference.generateContiguous(aNamedCell.getReference());
for (int i=0; i<arefs.length; i++) {
+ // Only get the corners of the Area
+ // (use arefs[i].getAllReferencedCells() to get all cells)
CellReference[] crefs = arefs[i].getCells();
for (int j=0; j<crefs.length; j++) {
// Check it turns into real stuff
HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
HSSFRow r = s.getRow(crefs[j].getRow());
HSSFCell c = r.getCell(crefs[j].getCol());
- // extract the cell contents based on cell type etc.
+ // Do something with this corner cell
}
}
</source>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jan 8 08:20:48
2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Support getting all the
cells referenced by an AreaReference, not just the corner ones</action>
<action dev="POI-DEVELOPERS" type="add">43510 - Add support for
named ranges in formulas, including non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for
hiding and un-hiding sheets, and checking their current hidden status</action>
<action dev="POI-DEVELOPERS" type="fix">44167 - Fix for
non-contiguous named ranges</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Tue Jan 8
08:20:48 2008
@@ -1212,6 +1212,16 @@
}
if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min(
endRow + n, 65535 );
if ( startRow == firstrow || startRow + n < firstrow ) firstrow =
Math.max( startRow + n, 0 );
+
+ // Update any formulas on this sheet that point to
+ // rows which have been moved
+
+ // Update any named ranges defined for this workbook
+ // that point to this sheet and had rows they reference
+ // moved
+ for(int i=0; i<workbook.getNumberOfNames(); i++) {
+ HSSFName name = workbook.getNameAt(i);
+ }
}
protected void insertChartRecords( List records )
Modified: poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java Tue Jan 8
08:20:48 2008
@@ -79,9 +79,36 @@
public int getDim() {
return dim;
}
- /** return the cell references that define this area */
+ /**
+ * Return the cell references that define this area
+ * (i.e. the two corners)
+ */
public CellReference[] getCells() {
return cells;
+ }
+ /**
+ * Returns a reference to every cell covered by this area
+ */
+ public CellReference[] getAllReferencedCells() {
+ // Special case for single cell reference
+ if(cells.length == 1) {
+ return cells;
+ }
+ // Interpolate between the two
+ int minRow = Math.min(cells[0].getRow(), cells[1].getRow());
+ int maxRow = Math.max(cells[0].getRow(), cells[1].getRow());
+ int minCol = Math.min(cells[0].getCol(), cells[1].getCol());
+ int maxCol = Math.max(cells[0].getCol(), cells[1].getCol());
+
+ ArrayList refs = new ArrayList();
+ for(int row=minRow; row<=maxRow; row++) {
+ for(int col=minCol; col<=maxCol; col++) {
+ CellReference ref = new CellReference(row, col,
cells[0].isRowAbsolute(), cells[0].isColAbsolute());
+ ref.setSheetName(cells[0].getSheetName());
+ refs.add(ref);
+ }
+ }
+ return (CellReference[])refs.toArray(new CellReference[refs.size()]);
}
public String toString() {
Modified: poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java Tue Jan 8
08:20:48 2008
@@ -69,6 +69,10 @@
public boolean isRowAbsolute(){return rowAbs;}
public boolean isColAbsolute(){return colAbs;}
public String getSheetName(){return sheetName;}
+
+ protected void setSheetName(String sheetName) {
+ this.sheetName = sheetName;
+ }
/**
* takes in a column reference portion of a CellRef and converts it from
Modified:
poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java?rev=610048&r1=610047&r2=610048&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
(original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java Tue
Jan 8 08:20:48 2008
@@ -52,6 +52,25 @@
assertTrue("row is abs",cf.isRowAbsolute());
assertTrue("col is abs",cf.isColAbsolute());
assertTrue("string is $B$2",cf.toString().equals("$B$2"));
+
+ CellReference[] refs = ar.getAllReferencedCells();
+ assertEquals(4, refs.length);
+
+ assertEquals(0, refs[0].getRow());
+ assertEquals(0, refs[0].getCol());
+ assertNull(refs[0].getSheetName());
+
+ assertEquals(0, refs[1].getRow());
+ assertEquals(1, refs[1].getCol());
+ assertNull(refs[1].getSheetName());
+
+ assertEquals(1, refs[2].getRow());
+ assertEquals(0, refs[2].getCol());
+ assertNull(refs[2].getSheetName());
+
+ assertEquals(1, refs[3].getRow());
+ assertEquals(1, refs[3].getCol());
+ assertNull(refs[3].getSheetName());
}
/**
@@ -59,13 +78,58 @@
* Reported by [EMAIL PROTECTED]
*/
public void testReferenceWithSheet() {
- String ref = "Tabelle1!$B$5";
+ String ref = "Tabelle1!B5";
AreaReference myAreaReference = new AreaReference(ref);
CellReference[] myCellReference = myAreaReference.getCells();
-
+
+ assertEquals(1, myCellReference.length);
assertNotNull("cell reference not null : "+myCellReference[0]);
assertEquals("Not Column B", (short)1,myCellReference[0].getCol());
assertEquals("Not Row 5", 4,myCellReference[0].getRow());
+ assertEquals("Shouldn't be absolute", false,
myCellReference[0].isRowAbsolute());
+ assertEquals("Shouldn't be absolute", false,
myCellReference[0].isColAbsolute());
+
+ assertEquals(1, myAreaReference.getAllReferencedCells().length);
+
+
+ ref = "Tabelle1!$B$5:$B$7";
+ myAreaReference = new AreaReference(ref);
+ myCellReference = myAreaReference.getCells();
+ assertEquals(2, myCellReference.length);
+
+ assertEquals("Tabelle1", myCellReference[0].getSheetName());
+ assertEquals(4, myCellReference[0].getRow());
+ assertEquals(1, myCellReference[0].getCol());
+ assertTrue(myCellReference[0].isRowAbsolute());
+ assertTrue(myCellReference[0].isColAbsolute());
+
+ assertEquals("Tabelle1", myCellReference[1].getSheetName());
+ assertEquals(6, myCellReference[1].getRow());
+ assertEquals(1, myCellReference[1].getCol());
+ assertTrue(myCellReference[1].isRowAbsolute());
+ assertTrue(myCellReference[1].isColAbsolute());
+
+ // And all that make it up
+ myCellReference = myAreaReference.getAllReferencedCells();
+ assertEquals(3, myCellReference.length);
+
+ assertEquals("Tabelle1", myCellReference[0].getSheetName());
+ assertEquals(4, myCellReference[0].getRow());
+ assertEquals(1, myCellReference[0].getCol());
+ assertTrue(myCellReference[0].isRowAbsolute());
+ assertTrue(myCellReference[0].isColAbsolute());
+
+ assertEquals("Tabelle1", myCellReference[1].getSheetName());
+ assertEquals(5, myCellReference[1].getRow());
+ assertEquals(1, myCellReference[1].getCol());
+ assertTrue(myCellReference[1].isRowAbsolute());
+ assertTrue(myCellReference[1].isColAbsolute());
+
+ assertEquals("Tabelle1", myCellReference[2].getSheetName());
+ assertEquals(6, myCellReference[2].getRow());
+ assertEquals(1, myCellReference[2].getCol());
+ assertTrue(myCellReference[2].isRowAbsolute());
+ assertTrue(myCellReference[2].isColAbsolute());
}
private static class HSSFWB extends HSSFWorkbook {
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
