Trim data array length to remove trailing empty rows.

Change-Id: I61a6a289ad1c2c757fcea490ada5d40fee08e840

5 files changed, 51 insertions, 0 deletions

diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 6949b6d686d3..4cbf06a84b2b 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -1027,6 +1027,13 @@ public:
                                           SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
                                           SCCOL& rEndCol, SCROW& rEndRow, bool bColumnsOnly ) const;
 
+    /**
+     * Return the last non-empty row position in given columns, or 0 if the
+     * columns are empty. A negative value is returned if the given sheet or
+     * column positions are invalid.
+     */
+    SCROW GetLastDataRow( SCTAB nTab, SCCOL nCol1, SCCOL nCol2 ) const;
+
     SC_DLLPUBLIC void           GetDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
                                     SCCOL& rEndCol, SCROW& rEndRow, bool bIncludeOld, bool bOnlyDown ) const;
     SC_DLLPUBLIC bool           GetCellArea( SCTAB nTab, SCCOL& rEndCol, SCROW& rEndRow ) const;
diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx
index 88166427f4a1..9bf3f903a3d4 100644
--- a/sc/inc/table.hxx
+++ b/sc/inc/table.hxx
@@ -469,6 +469,8 @@ public:
     bool        ShrinkToUsedDataArea( bool& o_bShrunk, SCCOL& rStartCol, SCROW& rStartRow,
                                       SCCOL& rEndCol, SCROW& rEndRow, bool bColumnsOnly ) const;
 
+    SCROW GetLastDataRow( SCCOL nCol1, SCCOL nCol2 ) const;
+
     SCSIZE      GetEmptyLinesInBlock( SCCOL nStartCol, SCROW nStartRow,
                                         SCCOL nEndCol, SCROW nEndRow, ScDirection eDir ) const;
 
diff --git a/sc/source/core/data/document.cxx b/sc/source/core/data/document.cxx
index c6d3c79ffbbc..d16191089249 100644
--- a/sc/source/core/data/document.cxx
+++ b/sc/source/core/data/document.cxx
@@ -1018,6 +1018,15 @@ bool ScDocument::ShrinkToUsedDataArea( bool& o_bShrunk, SCTAB nTab, SCCOL& rStar
     return maTabs[nTab]->ShrinkToUsedDataArea( o_bShrunk, rStartCol, rStartRow, rEndCol, rEndRow, bColumnsOnly);
 }
 
+SCROW ScDocument::GetLastDataRow( SCTAB nTab, SCCOL nCol1, SCCOL nCol2 ) const
+{
+    const ScTable* pTab = FetchTable(nTab);
+    if (!pTab)
+        return -1;
+
+    pTab->GetLastDataRow(nCol1, nCol2);
+}
+
 // connected area
 
 void ScDocument::GetDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
diff --git a/sc/source/core/data/formulacell.cxx b/sc/source/core/data/formulacell.cxx
index 87a3d0515803..be9fd907b558 100644
--- a/sc/source/core/data/formulacell.cxx
+++ b/sc/source/core/data/formulacell.cxx
@@ -3362,6 +3362,18 @@ class GroupTokenConverter
 
         return true;
     }
+
+    SCROW trimLength(SCTAB nTab, SCCOL nCol1, SCCOL nCol2, SCROW nRow, SCROW nRowLen)
+    {
+        SCROW nLastRow = mrDoc.GetLastDataRow(nTab, nCol1, nCol2);
+        if (nLastRow < (nRow + nRowLen - 1))
+            nRowLen = nLastRow - nRow + 1;
+        else if (nLastRow == 0)
+            // Column is empty.
+            nRowLen = 1;
+
+        return nRowLen;
+    }
 public:
     GroupTokenConverter(sc::FormulaGroupContext& rCxt, ScTokenArray& rGroupTokens, ScDocument& rDoc, ScFormulaCell& rCell, const ScAddress& rPos) :
         mrCxt(rCxt), mrGroupTokens(rGroupTokens), mrDoc(rDoc), mrCell(rCell), mrPos(rPos) {}
@@ -3397,6 +3409,9 @@ public:
                         if (isSelfReferenceRelative(aRefPos, aRef.Row()))
                             return false;
 
+                        // Trim data array length to actual data range.
+                        nLen = trimLength(aRefPos.Tab(), aRefPos.Col(), aRefPos.Col(), aRefPos.Row(), nLen);
+
                         // Fetch double array guarantees that the length of the
                         // returned array equals or greater than the requested
                         // length.
@@ -3459,6 +3474,9 @@ public:
                         nArrayLength += nRefRowSize - 1;
                     }
 
+                    // Trim trailing empty rows.
+                    nArrayLength = trimLength(aRefPos.Tab(), aAbs.aStart.Col(), aAbs.aEnd.Col(), aRefPos.Row(), nArrayLength);
+
                     for (SCCOL i = aAbs.aStart.Col(); i <= aAbs.aEnd.Col(); ++i)
                     {
                         aRefPos.SetCol(i);
diff --git a/sc/source/core/data/table1.cxx b/sc/source/core/data/table1.cxx
index 6391eeea3a8a..9af12d690c1e 100644
--- a/sc/source/core/data/table1.cxx
+++ b/sc/source/core/data/table1.cxx
@@ -987,6 +987,21 @@ bool ScTable::ShrinkToUsedDataArea( bool& o_bShrunk, SCCOL& rStartCol, SCROW& rS
             (rStartRow != rEndRow || aCol[rStartCol].HasDataAt( rStartRow)));
 }
 
+SCROW ScTable::GetLastDataRow( SCCOL nCol1, SCCOL nCol2 ) const
+{
+    if (!ValidCol(nCol1) || !ValidCol(nCol2))
+        return -1;
+
+    SCROW nLastRow = 0;
+    for (SCCOL i = nCol1; i <= nCol2; ++i)
+    {
+        SCROW nThis = aCol[i].GetLastDataPos();
+        if (nLastRow < nThis)
+            nLastRow = nThis;
+    }
+
+    return nLastRow;
+}
 
 SCSIZE ScTable::GetEmptyLinesInBlock( SCCOL nStartCol, SCROW nStartRow,
                                         SCCOL nEndCol, SCROW nEndRow, ScDirection eDir ) const