From bf989ca39ec8172b869df6ffc3e4f273155c0386 Mon Sep 17 00:00:00 2001
From: Nick Burch <nick@apache.org>
Date: Tue, 8 Jan 2008 16:20:48 +0000
Subject: [PATCH] Add a new method onto AreaReference to get all the cells
 referenced, not just the corners. Includes tests for this

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@610048 13f79535-47bb-0310-9956-ffa450edef68
---
 src/documentation/content/xdocs/changes.xml   |  1 +
 .../content/xdocs/hssf/quick-guide.xml        |  6 +-
 src/documentation/content/xdocs/status.xml    |  1 +
 .../apache/poi/hssf/usermodel/HSSFSheet.java  | 10 +++
 .../apache/poi/hssf/util/AreaReference.java   | 29 +++++++-
 .../apache/poi/hssf/util/CellReference.java   |  4 ++
 .../poi/hssf/util/TestAreaReference.java      | 68 ++++++++++++++++++-
 7 files changed, 114 insertions(+), 5 deletions(-)

diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml
index b5d60ad17f..d0f4f0e642 100644
--- a/src/documentation/content/xdocs/changes.xml
+++ b/src/documentation/content/xdocs/changes.xml
@@ -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>
diff --git a/src/documentation/content/xdocs/hssf/quick-guide.xml b/src/documentation/content/xdocs/hssf/quick-guide.xml
index a7ca260c7a..b2f729fedc 100644
--- a/src/documentation/content/xdocs/hssf/quick-guide.xml
+++ b/src/documentation/content/xdocs/hssf/quick-guide.xml
@@ -1151,7 +1151,7 @@ Examples:
 
     // 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&lt;crefs.length; i++) {
         HSSFSheet s = wb.getSheet(crefs[i].getSheetName());
         HSSFRow r = sheet.getRow(crefs[i].getRow());
@@ -1177,13 +1177,15 @@ Examples:
     //  another for D12 to D14
     AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
     for (int i=0; i&lt;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&lt;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>
diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index 58d6df9aa6..f4d7c1b07a 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -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>
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
index ba9ff72a7e..b0d8222865 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
@@ -1212,6 +1212,16 @@ public class HSSFSheet
         }
         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 )
diff --git a/src/java/org/apache/poi/hssf/util/AreaReference.java b/src/java/org/apache/poi/hssf/util/AreaReference.java
index ae83431983..8169378e76 100644
--- a/src/java/org/apache/poi/hssf/util/AreaReference.java
+++ b/src/java/org/apache/poi/hssf/util/AreaReference.java
@@ -79,10 +79,37 @@ private int dim;
     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() {
         StringBuffer retval = new StringBuffer();
diff --git a/src/java/org/apache/poi/hssf/util/CellReference.java b/src/java/org/apache/poi/hssf/util/CellReference.java
index 0bc1cee2e3..def58472a8 100644
--- a/src/java/org/apache/poi/hssf/util/CellReference.java
+++ b/src/java/org/apache/poi/hssf/util/CellReference.java
@@ -69,6 +69,10 @@ public class CellReference {
     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
diff --git a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
index b58e498bf0..ccd2a58052 100644
--- a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
+++ b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
@@ -52,6 +52,25 @@ public class TestAreaReference extends TestCase {
         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 @@ public class TestAreaReference extends TestCase {
      * Reported by Arne.Clauss@gedas.de
      */
     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 {
-- 
2.39.5